1 / 39

CS422 Principles of Database Systems Entity-Relationship Model

CS422 Principles of Database Systems Entity-Relationship Model. Chengyu Sun California State University, Los Angeles. Adapted from Jeffrey Ullman’s lecture notes at http://www-db.stanford.edu/~ullman/dscb.html. Schema Design. Problem in Real World. Tables in RDBM. classroom. ?. teacher.

cpeeples
Download Presentation

CS422 Principles of Database Systems Entity-Relationship 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. CS422 Principles of Database SystemsEntity-Relationship Model Chengyu Sun California State University, Los Angeles Adapted from Jeffrey Ullman’s lecture notes at http://www-db.stanford.edu/~ullman/dscb.html

  2. Schema Design Problem in Real World Tables in RDBM classroom ? teacher computer projector student student student computer computer

  3. Entity-Relationship (ER) Model • Sort of an object-oriented approach • minus the operation/method part • A graphical representation of the design – ER Diagram • Easily converted to relational model Problem ER Model Tables

  4. name addr Bars license ER Diagram name manf Beers Sells Entity Set Attribute Frequents Likes Relationship Drinkers name addr

  5. Entity Set and Attributes • Entity Set is similar to class in an OO language • Attributes are the properties of an entity set • Similar to the class variables in an OO language • Must have simple values like numbers or strings – cannot be collection or composite type

  6. Instances of An Entity Set • Entity – object in an OO language name manf (Bud, Anheuser-Busch) (Miller, Miller Brewing) (Bud Lite, Anheuser-Busch) Beers name addr (Joe’s Bar, 113 Main St, Full) (Sue’s Bar, 20 East St, Beer) Bars license

  7. name addr Bars license Relationship • Instances of a relationship?? name manf Beers Sells

  8. An entity of either set can be connected to many entities of the other set Many-to-Many Relationship

  9. The relationship Favorite between Drinkers and Beers Many-to-One Relationship

  10. Many-to-One in ER Diagram Likes • An arrow is used to indicate the “one” side • There could be multiple relationships between two entity sets Drinkers Beers Favorite

  11. The relationship Best-seller between Manufactures and Beers One-to-One Relationship

  12. One-to-One in ER Diagram Best-seller • Arrows on both ends Manufactures Beers ?? ?? Brew

  13. Multiway Relationship • Sometimes we need a relationship that connects more than two entity sets. • Suppose drinkers will only drink certain beers at certain bars.

  14. A 3-Way Relationship name addr name manf Bars Beers license Preferences Drinkers name addr

  15. Instances of the Preferences Relationship Bar Drinker Beer Joe’s Bar Ann Miller Sue’s Bar Ann Bud Sue’s Bar Ann Pete’s Ale Joe’s Bar Bob Bud Joe’s Bar Bob Miller Joe’s Bar Cal Miller Sue’s Bar Cal Bud Lite

  16. “Arrows” in Multi-way Relationships Bars Preferences • What does an arrow mean in a multi-way relationship?? • Can we add any arrows in the Preferences relationship?? ?? ?? Beers ?? Drinkers

  17. Attributes of Relationships • Sometimes it’s useful to attach an attribute to a relationship. Sells Bars Beers price

  18. Married Drinkers Roles • An entity set may appear in the same relationship more than once. • Label the edges with names called Roles husband wife

  19. A Different Perspective Husband (Drinkers) Married Wife (Drinkers)

  20. Subclass Special case More properties No multiple inheritance Represented by the isa triangle Subclasses Beers name manf isa Ales color

  21. Keys • A key is an attribute or a set of attributes that uniquely identify an entity in an entity set.

  22. Keys in ER Diagram First_Name Last_Name CIN Student ?? Courses ?? Quarter Name Section

  23. Rules about Keys • Each entity set must have a key • If there are multiple keys, choose one of them as the primary key • Super class must have all the key attributes

  24. Weak Entity Set • Entity set E is said to be weak if in order to identify entities of E uniquely, we need to follow one or more many-one relationships from E and include the key of the related entities from the connected entity sets.

  25. Weak Entity Set Example • What’s the key for Players?? name number name Plays-on Players Teams

  26. Representing Weak Entity Sets • The key of a weak entity set consists of its own key attributes and the key attributes of the supporting set name number name Plays-on Players Teams

  27. Referential Integrity • A stronger many-to-one or one-to-one relationship At most one Exact one

  28. Representing Referential Integrity Best-seller Manufactures Beers

  29. Design Principles • Faithfulness • Avoid redundancy • Don’t use an entity set when an attribute would do • Limit the use of weak entity set

  30. Avoid Redundancy • Redundancy wastes space, and more importantly, encourages inconsistency.

  31. Example: Good name name addr ManfBy Beers Manfs

  32. Example: Bad name name addr ManfBy Beers Manfs manf

  33. Example: Bad name manf manfAddr Beers

  34. Entity Set vs. Attributes • An entity set should satisfy at least one of the following conditions: • It is more than the name of something; it has at least one non-key attribute, or • It is the “many” in a many-one or many-many relationship.

  35. Example: Good name name addr ManfBy Beers Manfs

  36. Example: Good name manf Beers

  37. Example: Bad name name ManfBy Beers Manfs

  38. Don’t Overuse Weak Entity Set • We can usually create unique IDs for entity sets.

  39. Exercise • Student, Grades, Courses • w/o relationship attributes

More Related