1 / 58

Entity-Relationship (E-R) Model

Entity-Relationship (E-R) Model. COURSE #. CTITILE. PROF. SCHED. ROOM. COURSE. ENROLL. GRADE. STUDENT. SNAME. CREDITS. STUID. MAJOR. * Sometimes it’s natural to classify the entities in an entity set into subclass, then we will use Class Hierarchies. Class Hierarchies. name. lot.

paul
Download Presentation

Entity-Relationship (E-R) 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. Entity-Relationship (E-R) Model COURSE# CTITILE PROF SCHED ROOM COURSE ENROLL GRADE STUDENT SNAME CREDITS STUID MAJOR

  2. * Sometimes it’s natural to classify the entities in an entity set into subclass, then we will use Class Hierarchies. Class Hierarchies name lot ssn Employees Hourly wage Hours worked ISA contractid Hourly Emps Contract Emps

  3. * A relationship set is an association between entity sets. Sometimes we have to model a relationship between a collection of entities and relationships, then we need to use aggregation. Aggregation name ssn lot Employees monitors until Started-on dname since pid pbudget did budget Sponsors Projects Department

  4. Conceptual design using the ER model • Should a concept be modeled as an entity or an attribute ? • Should a concept be modeled as an entity or a relationship ? • What are the relationships ? Should we use binary or ternary relationships ? • Should we use aggregation ?

  5. UNARY RELATIONSHIP • Unary relationship : is an associate of 1 entity set. * Note : an entity set is a group of related entities. Dr. Lee Student * Entity set = * Relation = * Attribute =

  6. BINARY RELATIONSHIP • Binary relationship : is an associate of 2 entities sets . Works_In Employees Department ssn name lot dname budget did * Entity set = * Relation = * Attribute =

  7. TERNARY RELATIONSHIP Ternary relationship : is an associate of 3 entities sets . Works_In Employees Department ssn name lot did budget dname Locations add cap * Entity set = * Relation = * Attribute =

  8. QUARTERY RELATIONSHIP Quartery relationship : is an associate of 4 entities sets. Product Works_In Employees Department Locations * Entity set = * Relation = * Attribute =

  9. Additional features of the ER model • In the ER model allow us to draw important distinctions about the data. • One of the features of the ER model is : Key Constraints Note : A key constraints is also known as Primary key. A primary key is a candidate key selected to uniquely identify all other attribute values in any given row, can not contain null entries.

  10. An example of Key Constraints on Manage name Since dname budget did ssn lot Manages Department Employees ssn = Social Security Number. did = Department id.

  11. Weak Entities name cost Pname --------- age ssn lot Policy Department Employees A weak entity : is an entity of which is existence depends on other entities.

  12. Entity versus Attribute from to name dname budget did ssn lot Works-In2 Department Employees * Entity set = * Relation = * Attribute =

  13. Entity versus Relationship dname name did budget ssn lot Manages Department Employees since apptnum Mgr-Appts dbudget

  14. Binary versus Ternary Relationship name Pname --------- age ssn lot Covers Dependents Employees Policies policyid cost

  15. Using a Ternary Relationship instead of Aggregation Started-on dname did budget pid pbudget Sponsors Departments Projects Employees lot ssn name

  16. ONE TO MANY RELATIONSHIP customer-name customer-street loan-number amount customer-city customer-id borrower loan customer

  17. MANY TO ONE RELATIONSHIP customer-name customer-street loan-number amount customer-city customer-id borrower loan customer

  18. ONE TO ONE RELATIONSHIP customer-name customer-street loan-number amount customer-city customer-id borrower loan customer

  19. E-R Diagram with Composite, Multivalued, and Derived Attributes street-name middle-name Street-number apartment-number last-name first-name street name city customer-id address state Customer zip-code age date-of-birth phone-number

  20. WEAK ENTITY SETS An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary key is termed a strong entity set. For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner entity set. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship. The identifying relationship is many to one from the weak entity set to the identifying entity set, and the participation of the weak entity set in the relationship is total.

  21. DISCRIMINATOR The discriminator of a weak entity set is a set of attributes that allows this distinction to be made. For example, the discriminator of a weak entity set payment is the attribute payment-number, since, for each loan a payment number uniquely identifies one single payment for that loan. The discriminator of a weak entity set is also called the partial key of the entity set. Note: although each payment entity is distinct, payments for different loans may share the same payment-number. Thus, payment entity set does not have a primary key; it is a weak entity set. The primary key of a weak entity set is formed by the primary key of the identifying entity set, plus the weak entity set’s discriminator.

  22. E-R DIAGRAM WITH A WEAK ENTITY SET payment-date amount In a E-R diagrams, a doubly outlined box indicates a weak entity set, and a doubly outlined diamond indicates the corresponding identifying relationship. We underline the discriminator of a weak entity set with a dashed line. loan-number payment-number payment-amount loan-payment loan payment E-R diagram with a weak entity set

  23. Entity/Relationship Model Diagrams to represent designs. • Entity like object, = “thing.” • Entity set like class = set of “similar” entities/objects. • Attribute = property of entities in an entity set, similar to fields of a struct. • In diagrams, entity set  rectangle;attribute  oval. name phone ID Students height

  24. Relationships • Connect two or more entity sets. • Represented by diamonds. Taking Students Courses

  25. Relationship Set Think of the “value” of a relationship set as a table. • One column for each of the connected entity sets. • One row for each list of entities, one from each set, that are connected by the relationship. Students Courses Sally CS180 Sally CS111 Joe CS180 … …

  26. Multiway Relationships Usually binary relationships (connecting two E.S.) suffice. • However, there are some cases where three or more E.S. must be connected by one relationship. • Example: relationship among students, courses, TA's (and graders). Possibly, this E/R diagram is OK: Taking Students Courses Assisting TA/Graders

  27. Works in CS180, because each TA (or grader) is a TA of all students. Connection student-TA is only via the course. • But what if students were divided into sections, each headed by a TA? • Then, a student in CS180 would be related to only one of the TA's for CS180. Which one? • Need a 3-way relationship to tell.

  28. Courses Students Courses TAs Ann CS180 Jan Sue CS180 Pat Bob CS180 Jan … … … Enrolls Students TAs

  29. Beers-Bars-Drinkers Example • Our running example for the course. name addr license Frequents Serves Bars Likes Beers Drinkers name manf name addr

  30. Multiplicity of Relationships Representation of Many-One • E/R: arrow pointing to “one.” • Rounded arrow = “exactly one.” Many-many Many-one One-one

  31. Example:Drinkers Have Favorite Beers name addr license Frequents Serves Bars Likes Beers Drinkers Favorite name manf name addr

  32. One-One Relationships Put arrows in both directions. Design Issue: Is the rounded arrow justified? Design Issue: Here, manufacturer is an E.S. In earlier diagrams it is an attribute. Which is right? Best-seller Manfs Beers

  33. Attributes on Relationships price • Shorthand for 3-way relationship: Sells Bars Beers price Prices Sells Bars Beers

  34. A true 3-way relationship. • Price depends jointly on beer and bar. • Notice arrow convention for multiway relationships: “all other E.S. determine one of these.” • Not sufficiently general to express any possibility. • However, if price, say, depended only on the beer, then we could use two 2-way relationships: price-beer and beer-bar. • Or better: just make price an attribute of beer.

  35. Converting Multiway to 2-Way • Baroque in E/R, but necessary in certain “object-oriented” models. • Create a new connecting E.S. to represent rows of a relationship set. • E.g., (Joe's Bar, Bud, $2.50) for the Sells relationship. • Many-one relationships from the connecting E.S. to the others. BBP The-Bar The-Beer The-Price Bars Beers Price

  36. Roles Sometimes an E.S. participates more than once in a relationship. • Label edges with roles to distinguish. Husband Wife d1d2 d3 d4 … … Married husband wife Drinkers

  37. Buddy1 Buddy2 d1d2 d1 d3 d2d1 d2 d4 … … Buddies • Notice Buddies is symmetric, Married not. • No way to say “symmetric” in E/R. Design Question Should we replace husband and wife by one relationship spouse? 1 2 Drinkers

  38. More Design Issues 1. Subclasses. 2. Keys. 3. Weak entity sets. (Next class.)

More Related