Understanding Entity-Relationship Model: Overview and Notations

1. Entity‐Relationship Model is a detailed, logical representation of the data for an organization or for a businessarea. • What are the entities and relationships in the enterprise? • What information about these entities and their relationships should we store in thedatabase? • What are the integrity constraints or business rules thatholds? • The model must be as ‘open’ as possible and not tied to any technology or to any particular businessmethodology. • Introduced in 1976 by PeterChen. Overview of Entity‐RelationshipModel ISE230

2. Subtype: A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings • Supertype: A generic entity type that has a relationship with one or moresubtypes • AttributeInheritance: • Subtype entities inherit values of all attributes of the supertype • An instance of a subtype is also an instance of the supertype Supertypes andSubtypes ISE230

3. EERNotations ISE230

4. EER Notations (MicrosoftVisio) Different modeling tools may have different notation for the same modelingconstructs ISE230

5. All employee subtypes will have employee number, name, address, and date‐ hired Supertype/SubtypeExample Each employee subtype will also have its own attributes ISE230

6. Relationships at the supertype level indicate that all subtypes will participate in therelationship • The instances of a subtype may participate in a relationship unique tothatsubtype. Inthissituation, the relationship is shown at the subtypelevel Relationships andSubtypes ISE230

7. Both outpatients and resident patients are cared for by aresponsible physician Only resident patients are assigned to abed Another Example:Hospital ISE230

8. When to use sub/super‐typerelationships? • There are attributes that apply to some (but not all) instances of an entity type. • Some (again not all) instances of an entity type participate in arelationship Word ofcaution! ISE230 10

9. Generalization: The process of defining a more general entity type (supertype) from a set of more specialized entity types (subtypes) and forming supertype/subtype relationships. • BOTTOM‐UP • Specialization: The process of defining one or more specialized entity types (subtypes) of thesupertype. • TOP‐DOWN Generalization andSpecialization ISE230

10. Three entity types: CAR, TRUCK, andMOTORCYCLE Example ofGeneralization All these types of vehicles have commonattributes ISE230

11. Generalization to VEHICLEsupertype Example of Generalization(2) So we put the shared attributesin asupertype Note: no subtype for motorcycle, since it has no uniqueattributes ISE230

12. Example ofSpecialization Only applies to manufacturedparts Applies only to purchasedparts ISE230

13. Example of Specialization(2) Createdtwo subtypes Note: multivalued attribute was replaced by an associative entity relationship to anotherentity ISE230

14. Completeness Constraints: Whether an instance of a supertype must also be a member of at least one subtype. • No further sub‐type(s) of the super‐type ispossible. • Yes: Total Specialization Rule (doubleline) • No: Partial Specialization Rule (singleline) Constraints inSupertype/Subtype ISE230

15. Total specializationrule Example of CompletenessConstraint A patient must be either an outpatient or a residentpatient ISE230

16. Partial specializationrule Example of CompletenessConstraint A vehicle could be a car, atruck, ornothing. ISE230 20

17. DisjointnessConstraints: Whether an instanceofa supertype may simultaneously be a member of two (or more)subtypes • Disjoint Rule: An instance of the supertype can be only ONE of thesubtypes • Overlap Rule: An instance of the supertype could be more than one of thesubtypes Constraints inSupertype/Subtype ISE230

18. Disjointrule Example of DisjointnessConstraint A patient can either be outpatient or resident, but not both ISE230

19. Overlaprule Example of DisjointnessConstraint A part may be both purchased and manufactured ISE230

20. Subtype Discriminator: An attribute of the supertype whose values determine the targetsubtype(s) • Disjoint – a simple attribute with alternative values to indicate the possiblesubtypes • Overlapping – a composite attribute whose subparts pertain to different subtypes. Each subpart contains a boolean value to indicate whether or not the instance belongs to the associatedsubtype Constraints inSupertype/Subtype ISE230

21. Subtype Discriminator (DisjointRule) A simple attribute with different possiblevalues indicating thesubtype ISE230

22. A composite attribute with sub‐attributes indicating“yes” or “no” to determine whether it is of eachsubtype Subtype Discriminator (OverlapRule) ISE230

23. Supertype/SubtypeHierarchy ISE230

24. Problem: EER diagrams are difficult to read when there are too many entities and relationships • Solution: Group entities and relationships intoclusters • Entity cluster: Set of one or more entity types and associated relationships grouped into a single abstract entitytype EntityClusters ll ISE230

25. Pine Valley FurnitureCompany Related groups of entitiescould become clusters ISE230

26. EERD of PVF EntityClusters Morereadable, isn’tit? ISE230

27. Manufacturing EntityCluster ISE230

28. Summary • Basic business rules are data names anddefinitions • Data modeling notations frequently used today is the entity‐relationship datamodel. • E‐R model constructs: entities, entity types, relationships, relationship types, attributes, identifiers, cardinality constraints. • EssentialReading • Modern Database Management (8th Ed.), Chapter3 • What isNext? • Relational Model Conclusion ISE230