The Entity-Relationship Model - PowerPoint PPT Presentation

the entity relationship model n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
The Entity-Relationship Model PowerPoint Presentation
Download Presentation
The Entity-Relationship Model

play fullscreen
1 / 74
The Entity-Relationship Model
200 Views
Download Presentation
claudia-mcintyre
Download Presentation

The Entity-Relationship Model

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. The Entity-Relationship Model Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu Part II. CS3431: C-Term 2013

  2. Entities with Different Attribute Types (Recap) Multivalued Attribute: major Primitive Attribute: sNumber Composite Attribute: address Student entity type with all its attributes DoB Age sNumber Derived Attribute: Age Age

  3. Binary Relationships (Recap) pNumber sName sPrice product date quantity supplies buys sName cName supplier consumer sLoc cLoc Attributes can be attached to Entity Sets or Relationships

  4. Multi-Way Relationships (Recap) Model the relationship Supplier supplies Products to Consumers Ternary relationship (three-way)

  5. Recursive Relationship Types and Roles Refer to the same entity set in the relationship Recursive relationship type :Part-Subpart Roles: There are Parts that play the role of superPart There are Parts that play the role of subPart If two entities in the same entity set have a relationship  Recursive relationship

  6. Recursive Relationships: Another Example • Employees & Managers ID supervisor Supervise Employee supervised Name

  7. More Elements in ER Model • Key Constraints • Cardinality Constraints • Weak Entities • Subclass Entities (ISA Relationships) • Principles for Good Design

  8. Keys of Entity Sets • Remember entity set is a group of entitieswith the same type • Key of Entity Set • Set of attributes that uniquely identify each entity • Examples: • “Car” VIN • “Person” SSN • “WPI Student” University ID • “US Student”  UniversityName + UnivesityID • A key has to be unique within the scope of your application • Does not have to be globally unique Customer Car

  9. Types of Keys • A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity • “Person” SSN, SSN + FirstName • “Account”  AccountNumber + AccountType • A candidate key of an entity set is a minimal super key • “Person” SSN • “Account”  AccountNumber • “US Student” SSN, UniversityName + UnivesityID • Each candidate key is a super key but not vice versa • A primary key is one from, possibly several, candidate keys  Pick one and declare it as “primary key” • “Student”  SSN, StudentID, FirstName + MiddleName + LastName

  10. Keys Summary • Key combination of one or more attributes that uniquely identify an entity • Types: • Super key • Candidate key • Primary key Only primary keys are modeled in ERD

  11. Primary Keys in ERD • Select only one key to be the primary key • Primary key is modeled by “underline” under its set of attributes • Good Practice: • Select singleton and number fields whenever possible

  12. Multi-Attributes Primary Key Key for Movie is <title, year> Key for Student is sNumber We can represent key for entity set consisting of more than one attribute (e.g.: Movie)

  13. Keys of Relationships • Relationship without attributes • The combination of primary keys of the participating entity sets forms a key of a relationship set • (customer_id, load_number) is the key of borrower

  14. Keys of Relationships (Cont’d) • Relationship with attributes • Attributes of the relationship may (not always) participate inside the key + the external keys • (sNumber, cNumber) is the key of Taken Grade Date project

  15. Keys of Relationships (Cont’d) • Relationship with attributes • Attributes of the relationship may (not always) participate inside the key + the external keys • (sNumber, cNumber, Date) is the key of Taken Grade Date project In this ERD: student can take the same course on different dates

  16. More Elements in ER Model • Key Constraints • Cardinality Constraints • Weak Entities • Subclass Entities (ISA Relationships) • Principles for Good Design

  17. Cardinality Constraints • Express the number of entities to which another entity can be associated via a relationship set • Most useful in describing binary relationship sets • For a binary relationship set the mapping cardinality must be one of the following types: • One to one • One to many • Many to one • Many to many

  18. Mapping Cardinalities

  19. Mapping Cardinalities (Cont’d)

  20. Representing Cardinalities in ERD • In a relationship: • “” : Represent “many”(including 0) • “” : Represent “one”(including 0) • “”: Represent “one” (must be one) A course can be taken by “many” students. A student is taking “many” courses.

  21. One-To-Many Relationship

  22. One-To-Many Relationship • In the one-to-many relationship a loan is associated with at most onecustomer via borrower, a customer is associated with many(including 0) loans via borrower A customer can take many loans A loan can be taken by one (and at least one) customer

  23. Many-To-One Relationship

  24. Many-To-One Relationship • In a many-to-one relationship a loan is associated with many(including 0) customers via borrower, a customer is associated with at most oneloan via borrower A customer can take at most one loan A loan can be taken by many customers

  25. Many-To-Many Relationship • In a many-to-many relationship a loan is associated with many(including 0) customers via borrower, a customer is associated with many loan via borrower

  26. Degree of Cardinalities How : Expressed using (min, max) (0, 5) (3, 60) • Student can take manycourses, and a course can be taken by manystudents • Student can take 0 to 5 courses, and a course can be taken by 3 to 60 students

  27. pNumber pName Part superPart subPart Contains quantity Cardinality Constraints for Recursive Relationships A Part may contain manysubparts A Part can be subpart in manysuperParts

  28. Cardinality Constraints for Recursive Relationships A Part can have manysubParts A Part can be subpart for at most onesuperPart

  29. Revisit this example… • Employees & Managers ….. Add cardinalities ID supervisor Supervise Employee supervised Name Semantics: • Manager can supervise many employees • Employee is supervised by one manager

  30. Cardinality Constraints for Multi-way Relationships pNumber pName Product sName cName Supply Supplier Consumer sLoc cLoc price qty Every Supplier supplies some Product to some Consumer To add degree constraints, introduce a new entity set and create multiple binary relationships !!!

  31. Adding Cardinality Constraints to Multi-way Relationships pNumber pName Product cName sName in Consumer Supplier cLoc sLoc consumes supplies Supp_Cons_ Prod What is the key of this entity ??? (Weak Entity) price qty

  32. More Elements in ER Model • Key Constraints • Cardinality Constraints • Weak Entities • Subclass Entities (ISA Relationships) • Principles for Good Design

  33. Weak Entity Sets • An entity set that does not have a primary key is referred to as a weak entity set • Its attributes are not enough to form a key • The existence of a weak entity set depends on the existence of an identifying entity set • It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set Course number is unique only within the department Weak entity set Identifying entity set

  34. Weak Entity Sets • Discriminator(or partial key) of a weak entity set • The set of attributes that uniquely identify a weak entity given its identifying entity • Primary key of a weak entity set • The composition of the primary key of the identifying entity set + the weak entity set’s discriminator • Identifying entity has to exist for each weak entity • Cannot have a course without a corresponding department • (dNumber, cNumber) is the primary key for Course discriminator

  35. Representing a Weak Entity Set • Weak entity set is represented by double rectangles • Weak relationship (supporting relationship) is represented by double diamonds • Weak relationship is one-many from the weak entity to the identifying entity

  36. Again: It Depends on Your Application/Assumptions • If you assume the course number is unique within a department • “Course” is a weak entity set • If you assume the course number is unique across all departments • “Course”  is a strong entity set offers Course Stating your assumptions in text is very important !!!

  37. Revisit Previous Example … pNumber pName Product cName sName in Consumer Supplier cLoc sLoc consumes supplies Supp_Cons_ Prod Weak Entity price qty

  38. What about an Exercise !!! Lets interactively design a database for a Hotel

  39. Example: Hotel Database • A Hotel has many branches • Hotel  name, logo, address of HQ, Tel., manager, star rating • Branch  Id, address, Tel., Total capacity • Each branch has many rooms with different types and numbers. A room type defines • Room size, Number of beds • Has TV or not, Has Balcony or not • Guests can stay in a hotel for a period of time • Guests have unique ID, name, address, Tel. • We need to capture, the length of the stay, start date, end date, money paid

  40. Ver. 1 Name Branch Room Hotel Num Beds Capacity Capacity Has Balcony Has TV Manager Rating Tel. HQ Add. Tel. Add. Type ID Num • Observations: • Room type is modeled as attribute (causes redundancy) • Room number, is it numeric like 1001? If so, how come to be unique across branches?

  41. Ver. 2 Name Branch Type Room Hotel Num Beds Capacity Capacity Has Balcony Has TV Tel. Rating Manager HQ Add. Add. Type Tel. Num ID • Observations: • Lets add relationships

  42. Ver. 3 contains has Of type Name Type Room Branch Hotel Num Beds Has Balcony Has TV Capacity Capacity Tel. Rating Manager HQ Add. Tel. Type Add. ID Num Common mistake: Do not add “Branch ID” as an attribute to “Room” entity set. It is already captured by the weak relationship “contains”.

  43. Back to the Requirements • A Hotel has many branches • Hotel  name, logo, address of HQ, Tel., manager, star rating • Branch  Id, address, Tel., Total capacity • Each branch has many rooms with different types and numbers. A room type defines • Room size, Number of beds • Has TV or not, Has Balcony or not • Guests can stay in a hotel for a period of time • Guests have unique ID, name, address, Tel. • We need to capture, the length of the stay, start date, end date, money paid

  44. Ver. 4 contains has Of type Name Length of stay Money Paid Name Start date End date Branch Guest Type Room Hotel • Observations: • “Stay” attributes should not be part of “Guest” Num Beds Capacity Has TV Capacity Has Balcony Tel. Rating Manager HQ Add. Tel. Add. Type Add. Tel. ID Num ID

  45. Ver. 5 contains has Of type Name Length of stay Money Paid Name Start date End date Branch Guest Type Room Hotel Num Beds Capacity Capacity Has Balcony Has TV Manager Rating Tel. HQ Add. Tel. Add. Add. Tel. Type ID ID Num • Observations: • Still not quite right.. • “Stays-in” 1-M or M-M?? • (Guest should be able to stay in diff. rooms) Stays in

  46. Ver. 6 contains has Of type Name Length of stay Money Paid Name Start date End date Branch Guest Type Room Hotel Num Beds Capacity Capacity Has Balcony Has TV Manager Rating Tel. HQ Add. Tel. Add. Add. Tel. Type ID ID Num • Observations: • Not done yet… • In this model, a guest cannot stay in the same room over diff visits!!! Stays in

  47. Ver. 7 contains has Of type Name Length of stay Money Paid Name Start date End date Branch Guest Type Room Hotel Num Beds Capacity Capacity Has Balcony Has TV Manager Rating Tel. HQ Add. Tel. Add. Add. Tel. Type ID ID Num • Observations: • Start_date part of key • Length of stay  derived attribute Stays in

  48. More Elements in ER Model • Key Constraints • Cardinality Constraints • Weak Entities • Subclass Entities (ISA Relationships) • Principles for Good Design

  49. ISA Relationship Types • Similar to “subclass” concept in Object-Oriented languages • Entity sets share some common attributes but differ in others • Sometimes called “Specialization/Generalization” • Example • Students can be UGStudents or GradStudents • UGStudents take undergrad Classes • GradStudents can be TAs or RAs • GradStudents are advised by Professors

  50. ISA Example • All attributes of “student” are inherited in the other entity sets • Each entity set, e.g., “Freshman”, can have its own additional attributes