Chapter 4: Enhanced Entity-Relationship (EER) Modeling

1. Chapter 4: Enhanced Entity-Relationship (EER) Modeling Data Modeling and Database Design

2. Remember! • Presentation Layer ER Diagram Contains hashes and oval • Design-specific ER Diagram (Coarse-granularity) Uses (min, max) notation Maps deletion rules • Design-specific ER Diagram (Fine-granularity) Maps attribute characteristics into ER diagram Decomposes multi-valued attributes Decomposes m:n relationships

3. Enhanced Entity-Relationship (EER) Model • Enhanced Entity-Relationship (EER) modeling is an extension to the ER modeling that incorporates additional constructs • Central constructSuperclass/subclass (SC/sc) relationship • More specifically: • Specialization/generalization • Categorization • Aggregation

4. Superclass/Subclass Relationship (SC/sc)

5. An Instance Diagram

6. Think About It… • Theoretically, one has three choices of modeling furniture/chair/table/sofa • Model three separate entity types for chair, table, and sofa, and create three separate relationship types with the entity type store • Model furniture as an entity type with an attribute called furniture_type; then chair, table, and sofa would be values of that attribute • Model furniture as a superclass/subclass

7. SC/sc Relationships • There are two basic kinds of Sc/sc relationships • Specialization/Generalization: One superclass (SC) is related to one or more subclasses (sc) • Categorization: One subclass (sc) is related to one or more superclasses (SC)

8. Vignette 1

9. Modeling Vignette 1: Alternative 1

10. Modeling Vignette 1: Alternative 2

11. Modeling Vignette 1: Alternative 3

12. Properties of SC/sc Relationships • An entity that exists in a subclass can be associated with only one superclass entity • An entity cannot exist in the database merely by being a member of a subclass; it must also be a member of an associated superclass • An entity that is a member of a superclass can be optionally included as a member of any number of its subclasses • It is not required that every member of a superclass be a member of a subclass

13. Properties of SC/sc Relationships (continued) • Type Inheritance Property: A subclass inherits all the attributes of the superclass to which it is related • In addition, it will also inherit all the relationship types in which the superclass participates • A subclass may also have its own specific attributes in addition to the attributes inherited • Likewise, a subclass may have its own specific relationship(s) with other entity types (i.e., inter-entity class relationships)

14. Note That… • The cardinality ratio of any SC/sc relationship is always 1:1 • The participation of the subclass in a SC/sc relationship is always total • A subclass inherits all attributes as well as all relationship types that a superclass possesses (type inheritance property)

15. Specialization and Generalization • Specialization is the process of generating subgroups (‘sc’s) of a generic entity class (SC) by specifying the distinguishing properties (attributes) of the subgroups (= top-down approach) • Generalization, on the other hand, crystallizes the common properties (attributes) shared by a set of entity types (‘sc’s) into a generic entity type (SC) (= bottom-up approach) • Notation: circle + fork (indicating subset) • Read: “is-a”

16. Specialization and Generalization (continued) • Completeness ConstraintThe participation of the superclass in a specialization/ generalization is referred to as the completeness constraint and can assume one of two values: total or partial • Total specialization means that every entity of the superclass must participate in this specialization/ generalization relationship (indicated by a solid line from the superclass to the specialization/generalization symbol (i.e., the circle)) • Partial specialization means that there may be entities present in the superclass that do not participate in this specialization/generalization (indicated by a dotted line)

17. Specialization and Generalization (continued) • Disjointness ConstraintUsed to specify that the subclasses of a specialization must be: • Disjointed (indicated by ‘D’), i.e., an entity of the superclass cannot be a member of more than one subclass • Overlapped across subclasses (indicated by ‘O’) • Please note that you have to specify both: completeness and disjointness constraints!

18. Notation

19. An EER Model of Vignette 1

20. Sample Data Sets For EER Model of Vignette 1

21. An Extension of Vignette 1: Multiple Specializations

22. Sample Data Sets For Vignette 1 Extension

23. An EER Model of Vignette 2

24. Specialization/Generalization Hierarchy • Example of a Hierarchy: [STUDENT -> STUDENT_ATHLETE -> FOOTBALL_PLAYER -> {DEFENSIVE_PLAYER, OFFENSIVE_PLAYER}] shows a 3-level hierarchy • InheritanceA subclass inherits the attributes and relationship types of not just the immediate parent, but also of the predecessor superclasses in the hierarchy all the way up to the root of the specialization hierarchy

25. An EER Model of Vignette 3

26. Specialization/Generalization Lattice • In a specialization lattice, an entity type can participate as a subclass in more than one specialization (i.e., a child can have more than one parent) • InheritanceThe subclass will inherit all the attributes and relationship types from the superclasses of all the specializations participating in the lattice and the predecessor hierarchy of all these superclasses • This is called multiple type inheritance, and the subclass in the lattice is referred to as a shared subclass

27. The Categorization Construct • A categorization occurs when a subclass is associated with more than one superclass of different entity types • The subclass is called category • An entity that is a member of the category (subclass) must exist in ONLY ONE of the superclasses in the categorization relationship • Example: A financial donor can be an individual, a company, or a foundation  no “is-a” relationship! • Notation: ‘U’ (= union, the subclass is a subset of the union of the superclasses)

28. Categorization Example

29. Characteristics of a Categorization • There is only one subclass in each categorization • The cardinality ratio is 1:1 within and across the SC/sc relationship • The participation of the subclass in the categorization is always total

30. Characteristics of a Categorization (continued) • Each superclass may exhibit either total or partial participation • A category can either be: • A total category (i.e., the category is the union of all the superclass entities) • A partial category (i.e., the category is a true subset of the union of all the superclass entities) • A category possesses the property of selective type inheritance (i.e., it inherits attributes of one entity type only) • Often a unique identifier for a category must be manufactured (which is called a surrogate key)

31. Sample Data Sets For Categorization Example

32. Choosing the Appropriate EER Construct Exercise: • Part1: Consider an automobile dealership in the state of Texas; the dealership typically stocks cars, trucks, vans, and sport utility vehicles (SUVs) • Part 2: Not all vehicles in the dealership are registered vehicles though • How would we model REGISTERED_VEHICLE? • One exception exists in which a categorization and a generalization/specialization are mutually substitutable constructs: in the case of a total category!

33. Choosing the Appropriate EER Construct (continued)

34. Choosing the Appropriate EER Construct (continued)

35. Choosing the Appropriate EER Construct (continued)

36. Choosing the Appropriate EER Construct (continued)

37. The Aggregation Construct • An aggregation allows us to model a “whole/part” relationship as an “is-a-part-of” relationship between a subclass and a superclass • An entity in the aggregate contains superclass entities from ALL SC/sc relationships in which it participates • Inheritance: Selective type inheritance connotes the inheritance of attributes and relationships from ALL superclass entities contained in the specific aggregation • Notation: ‘A’ • Read: “is-part-of” • Note that an aggregate can never be partial

38. An Example of Aggregation

39. An Aggregation Hierarchy

40. Aggregation Versus Categorization

41. A Fine-granular Design-Specific EER Diagram for Vignette 3

42. A Presentation Layer EER Diagram For Bearcat, Inc.

43. A Fine-granular Design-Specific EER Diagram For Bearcat, Inc.