1 / 50

The Entity-Relationship Model

The Entity-Relationship Model. Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu. Part II. Entities with Different Attribute Types (Recap). Multivalued Attribute: major. Primitive Attribute: sNumber. Composite Attribute: address. Student entity type with all its attributes. DoB. Age.

dyami
Télécharger la présentation

The 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. The Entity-Relationship Model Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu Part II. CS3431-B11

  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 (Recap) Refer to the same entity 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

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

  7. 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” WPI ID • A key has to be unique within the scope of your application • Does not have to be globally unique • Example: • “US Student” SSN, UniversityName + UnivesityID

  8. 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

  9. 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

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

  11. 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

  12. 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 What if we do not underline “Date” attribute ??

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

  14. 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

  15. Mapping Cardinalities

  16. Mapping Cardinalities (Cont’d)

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

  18. 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 One-to-Many from Customer to Loan

  19. 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

  20. 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

  21. 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

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

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

  24. 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 !!!

  25. 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

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

  27. 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

  28. 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

  29. 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

  30. 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 !!!

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

  32. What about an Exercise !!! Lets interactively design a database for a hospital

  33. 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

  34. ISA Relationship Types (Cont’d) • Top-down design process • Build entities with the common attributes, then build sub-entities with distinctive attributes from other entities in the set • These sub-entities become lower-level entity sets that have attributes or participate in relationships that do not apply to the general higher-level entity set • In ERD, represented by a trianglecomponent labeled ISA (E.g. customer “is a”person) • Attribute inheritance • Lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked

  35. ISA Relationship: Example I

  36. ISA Relationship: Example II

  37. ISA Relationship: Generalization • Bottom-up design process • Combine a number of entity sets that share the same features into a higher-level entity set • Specialization and generalization are simple inversions of each other • They are represented in an E-R diagram in the same way • The terms specialization and generalization are used interchangeably.

  38. Multiple ISA Relationships • Can have multiple specializations of an entity set based on different features • E.g. permanent_employeevs. temporary_employee, in addition to officer vs. secretary vs. teller • Each particular employee would be • A member of one of permanent_employeeor temporary_employee, • And also a member of one of officer, secretary, or teller

  39. Multiple ISA Relationships: Example permanent ISA temporary

  40. ISA Relationship: Constraints • Constraint on which entities can be members of a given lower-level entity set • Example: all customers over 65 years are members of senior-citizen entity set; senior-citizen ISA person • Denoted in ERD on the ISA edge • Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization. • Disjoint • An entity can belong to only one lower-level entity set • Overlapping • An entity can belong to more than one lower-level entity set • Denoted in ERD by writing “disjoint” or “overlapping”next to ISA triangle, by default  “disjoint”

  41. ISA Relationship: Constraints (Cont’d) • Completeness constraint -- specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization • Total: An entity must belong to one of the lower-level entity sets • Partial: An entity need not belong to one of the lower-level entity sets

  42. Example Overlapping & Partial

  43. ISA Relationship: Keys & Multiplicity • Key of sub-entities is inherited from the super-entities • Multiplicity is 1:1 person_id is the primary key

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

  45. Summary of Symbols used in ERD

  46. Coming up with a good design for your application • No single right design, there can be many… • Put clear, reasonable assumptions and make a design that captures the assumptions • Without stating the assumptions, others can claim your design is wrong !!! • It is like art, common sense and experience make a difference

  47. Guidelines Toward a Good Design (I) • Convey “real” application requirements • Utilize meaningful names • Try simpler construct first • Avoid redundancy, do not store the same data in multiple places • Be as precise as possible (E.g., cardinality constraints) • Don’t over specify (limits input)

  48. Guidelines Toward a Good Design (II) • Do not overuse non-binary relationships • They are harder to understand and interpret • Do not create entity sets with single attributes • They may be better as attributes of other entity sets • Do not overuse ISA relationships • There are always some commonalities between things  this does not mean they should inherit from common ancestor

  49. Guidelines Toward a Good Design (III) • Choose meaningful relationships • Know when to add attributes to entity sets vs. relationships • Some business constraints will not be captured in the design • E.g., For a customer to get a load, the sum of the previous loans to him/her must be < MaxLoan

  50. Summary of Entity-Relationship Model • Concepts • Entity, Entity Sets, Weak Entity Sets • Relationships Types • binary, ternary, multi-way, recursive, weak, ISA • Attributes • For entity sets or relationship types • Simple, composite, derived, multi-valued • Constraints – key, cardinality • Guidelines for Good Design

More Related