1 / 32

Overview of Database Systems

Overview of Database Systems. CSCE 315 – Programming Studio Spring 2010 Team Project 1, Lecture 1. Project. The first team project (next week) will involve putting together a very basic database system There will be a few lectures to give you an overview of database systems

Download Presentation

Overview of Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Overview of Database Systems CSCE 315 – Programming Studio Spring 2010 Team Project 1, Lecture 1

  2. Project • The first team project (next week) will involve putting together a very basic database system • There will be a few lectures to give you an overview of database systems • This is nowhere close to what you would get in a full database course • Slides adapted from Jennifer Welch (some of hers were from Jeffrey Ullman)

  3. Database Systems • Systems designed to manage very large amounts of data, and to query that data to pull out useful information • Often, key considerations include: • Efficiency • Reliability • Ease of access (querying, distributed)

  4. Creating a Database • A database schema determines what will be represented in the database • This should be tightly controlled by a database manager • Specified through a data definition language

  5. Querying Databases • Once database has been populated, users can query the data • A data manipulation language controls how the user can specify queries, (and thus what types of queries are allowed) • SQL is probably the most well-known

  6. Other Database Topics • “Real” database courses include lots of other things that we’ll be ignoring here • More complete theory behind design • Query optimization • Efficient storage • Processing Transactions – grouped queries that provide atomic operations • Scheduling, logging, recovery

  7. Entity-Relationship Model • Way of expressing (in diagrammatic form) a database design • Kinds of data and how they connect • Easy first way to think about databases • Later, relational model described

  8. Entities and Attributes • Entities are things • Entity sets are collections of those things • Attributes are properties of entity sets

  9. Entity Sets and Attributes Name Party Senator Bill State Text Name Years

  10. Relationships • Connect two or more entity sets Name Contributed Party Organization Senator Lobbyist State Sponsored Name Years Wrote Bill Name Text

  11. Values of Relationships • The “value” of an entity set is the entities it contains • The “value” of a relationship is a list of currently related entities (one from each entity set)

  12. Multi-Way Relationships • E.g. Lobbyist lobbied Senator about Bill Name Party Organization Senator Lobbied Lobbyist State Name Years Bill Name Text

  13. Relationship Types • Consider binary relationships (two entity groups in a relationship) • One-to-one • Each entity can have at most one in the other category • e.g. entity groups: Baseball player, Team • relationship: Team MVP • A team can only have one MVP, and a player can only be MVP for one team.

  14. Relationship Types • Consider binary relationships (two entity groups in a relationship) • One-to-one • Many-to-one • Each entity of first set can go to at most one of the second set • e.g. entity groups: Person, Town • relationship: BornIn • A person can is born in only one town, but a town can have many people born there

  15. Relationship Types • Consider binary relationships (two entity groups in a relationship) • One-to-one • Many-to-one • Many-to-many • Any number from one set to the other • e.g. Senators can sponsor many bills, and each bill can be sponsored by many Senators

  16. Diagrams of Relationships • Arrow shows “to one” Lived In Born In Person Town MVP Baseball Player Team

  17. Attributes on Relationships • Can be converted to multi-way diagrams Born In Person Town Hospital

  18. Attributes on Relationships • Can be converted to multi-way diagrams Born In Person Town Hospitals Hospital

  19. Attributes on Relationships • Note arrows Injured Person Date Hospitals Hospital

  20. Roles • If multiple references to same entity set, label edges by roles Team Lead Team Programmer Students Tester

  21. Subclass • Fewer entities, more properties Name Elected Official Party isa U.S. Representative District

  22. Subclass • Entity in all subclasses Elected Official Name isa isa U.S. Representative Republican District District

  23. Keys • A key is a set of attributes for an entity set such that no two entities agree on all the attributes. • We must have a key for every entity set Elected Official Name For an isa hierarchy, only root can have a key. Party isa U.S. Representative District

  24. Key for multiple attributes • Must choose one set of attributes First Name Last Name Number Baseball Player Position Birthdate Nationality Salary Team

  25. Key for multiple attributes • Must choose one set of attributes First Name Last Name Number Baseball Player Position Birthdate Nationality Salary Team

  26. Key for multiple attributes • Must choose one set of attributes First Name Last Name Number Baseball Player Position Birthdate Nationality Salary Team

  27. Weak entity sets • Need “help” to determine key First Name Last Name Number Note arrrow: indicates many to one. Baseball Player Position Birthdate Plays On Team Nationality Salary Name City

  28. Design Techniques • Avoid redundancy • Say the same thing two ways First Name Last Name Number Baseball Player Position Birthdate Plays On Team Team Name Salary Name City

  29. Design Techniques • Avoid redundancy • Say the same thing two ways First Name Last Name Number Baseball Player Position Birthdate Team Name Team Town

  30. Design Techniques • Don’t use entity set if attribute will do • Entity lists should either • Have some non-key attribute • Be the “many” in a many-one/many-many relationship Plays On Name Baseball Player Team Name City

  31. Design Techniques • Don’t use entity set if attribute will do • Entity lists should either • Have some non-key attribute • Be the “many” in a many-one/many-many relationship Name Baseball Player Team

  32. Design Techniques • Don’t overuse weak entity sets • Usually use unique key for each entity set (e.g. UIN, SSN, VIN) • Not always possible, though

More Related