1 / 24

From ER Diagrams to the Relational Model

From ER Diagrams to the Relational Model. Rose-Hulman Institute of Technology Curt Clifton. Review – Entity Sets and Attributes. Entity set: collection of “things” in the DB Attribute: property of an entity. calories. name. Soda. Kinds of Attributes. Simple – single atomic value

ronna
Download Presentation

From ER Diagrams to the Relational Model

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. From ER Diagrams to the Relational Model Rose-Hulman Institute of Technology Curt Clifton

  2. Review – Entity Sets and Attributes • Entity set: collection of “things” in the DB • Attribute: property of an entity calories name Soda

  3. Kinds of Attributes • Simple – single atomic value • Soda name, calories • Composite – several sub-attributes • PersonName(First,Middle,Last) • Multi-valued – set of values for one attribute • Car color, Degrees earned • (Somewhat rare, makes some searches harder)

  4. Review – Keys • Let us tell entities apart • The key for an entity set is a subset of the attributes for that entity set, such that no two entities agree on all the attributes calories name Soda

  5. Review – Relationships • Associate 2 or more entity sets • Constraints • Maximums shown with numbers • Read like: a subject-verb-number-object • Participation shown with double line • Read like: a subject-has to-verb… Most Likes N Person Soda 1

  6. ER Design Techniques • Avoid redundancy and don’t duplicate data • Don’t use entity set when attribute will do • Limit use of weak entity sets

  7. Redundancy • Wastes space • Leads to inconsistency • For example: manf addr name name Madeby N 1 Soda Manf

  8. Failed Attempt At Fix • Delete Manf entity set • Add address to Soda manf Manf addr name Soda

  9. Successful Fix • Eliminate manf attribute from Soda • Use relationship to find manufacturer info. addr name name Madeby N 1 Soda Manf

  10. Don’t Use Unnecessary Entity Sets • Entity set should… • Have at least one non-key attribute OR • Be the “many” in a many-one or many-many relationship addr name name Madeby N 1 Soda Manf

  11. Bad Entity Set • Suppose we didn’t have manufacturer address name name Madeby N 1 Soda Manf

  12. Avoid Weak Entity Sets • Don’t try to be too clever • Can usually just add a unique ID • Government has done this for their databases: • Social Security Numbers • Vehicle Identification Numbers • But… • Don’t trust uniqueness of IDs assigned by others

  13. Why Use Weak Entity Sets At All? • Federated Databases, for example… • All students in Indiana receiving state aid • All players on FIFA soccer teams • One query sent to multiple DB • Still want a Conceptual DB Schema • But no global authority to assign unique IDs

  14. The Relational Model • Originated as theoretical idea • “A Relational Model of Data for Large Shared Data Banks”, E. F. Codd, Comm. of the ACM, 13(6), June 1970 • http://www.acm.org/classics/nov95/s1p3.html • Revolutionized databases • Led to 1981 ACM Turing Award • The “Nobel Prize of computing”

  15. Relations • (Semi-) Formally • Tuple: an ordered list • n-tuple: an ordered list of length n • Relation: a set of n-tuples • Informally: • Relation: a table with unique rows • Rows = tuples; Columns = attributes; • Values in column = domain • Database: a collection of relations

  16. Some Other Terms • Relation schema • Describes a relation • RelationName (AttrName1, AttrName2,…) • Or RelationName (AttrName1:type, …) • Database schema • Set of all the relation schema for the DB’s relations

  17. Why is the Relational Model Dominant? • Very simple – just one data structure • Matches a “list the items” mentality • Easy to manipulate tables with UI • Forms basic foundation for SQL • Relational model based on sets • SQL based on bags (a.k.a., multi-sets)

  18. From ER Diagrams to Relations • Entity sets become relations • Columns are attributes of entity set • Relationships also become relations • Columns are keys of participating entity sets

  19. Example: Basic Entity Sets calories name addr Soda name Customer

  20. Example: Basic Relationship addr name calories name Likes Customer Soda MostLikes Namedbuddy 1 BuddyWith Naming buddy since

  21. Simplifying! • Can avoid relations formany-one relationships • Just add key of the one to the relation of the many Customer name addr MostLikes 1 calories name Soda

  22. Over Simplifying! • What happens if we try to eliminate relation for a many-many relationship? • Consider treating Likes as we did Most Likes • Redundancy • Data loss

  23. Need enough columns to make rows unique! So… All attributes of weak entity set + Key from supporting relationship Weak Entity Sets name number name city Plays on Player Team 1

  24. Entity Sets with Subclasses • Use nulls, or • Use multiple relations • “ER Style” • How should we choose which to use? name calories Soda isa sweetener DietSoda

More Related