Set 12 Chapter 8 Enhanced Entity-Relationship (EER) Model and Chapter 9.2 mapping EER to relations

1. Set 12 Chapter 8 Enhanced Entity-Relationship (EER) Model and Chapter 9.2 mapping EER to relations

2. Chapter 8 Outline • Subclasses, Superclasses, and Inheritance • Specialization and Generalization • Constraints and Characteristics of Specialization and Generalization Hierarchies • Modeling of UNION Types Using Categories • A Sample UNIVERSITY EER Schema

3. The Enhanced Entity-Relationship (EER) Model • Enhanced ER (EER) model • Created to design more accurate database schemas • Reflect the data properties and constraints more precisely • More complex requirements than traditional applications

4. Subclasses, Superclasses, and Inheritance • EER model includes all modeling concepts of the ER model • In addition, EER includes: • Subclasses and superclasses • Specialization and generalization • Category or union type • Attribute and relationshipinheritance

5. Subclasses, Superclasses, and Inheritance (cont’d.) • Enhanced ER or EER diagrams • Diagrammatic technique for displaying these concepts in an EER schema • Subtype or subclass of an entity type • Subgroupings of entities that are meaningful • Represented explicitly because of their significance to the database application • Generate a subclass because you need it for a relationship

6. Subclasses, Superclasses, and Inheritance (cont’d.) • Terms for relationship between a superclass and any one of its subclasses • Superclass/subclass • Supertype/subtype • Class/subclass relationship • Type inheritance • Subclass entity inherits all attributes and relationships of superclass

8. Specialization and Generalization • Specialization • Process of defining a set of subclasses of an entity type • Defined on the basis of some distinguishing characteristic of the entities in the superclass • Subclass can define: • Specific additional attributes • Specific new relationships

10. Specialization and Generalization (cont’d.) • Certain attributes may apply to some but not all entities of the superclass • Some relationship types may be participated in only by members of the subclass

11. Generalization • Generalize into a single superclass • Original entity types are special subclasses • Generalization • Process of defining a generalized entity type from the given entity types

12. Go from (a) to (b) by generalization

13. Constraints and Characteristics of Specialization and Generalization Hierarchies • Constraints that apply to a single specialization or a single generalization • Differences between specialization/ generalization lattices and hierarchies This should be called a graph, not a lattice

14. Constraints on Specialization and Generalization • May be several or one subclass • Determine entity subtype: • Predicate-defined (or condition-defined) subclasses • Attribute-defined specialization • User-defined

15. Example of Attribute-defined specialization

16. Constraints on Specialization and Generalization (cont’d.) • Disjointness constraint • Specifies that the subclasses of the specialization must be disjoint • Completeness (or totalness) constraint • May be total or partial – use double line for total as for entities in a relationship • total means every member of the superclassmust be a member of a subclass • Disjointness and completeness constraints are independent • Overlap constraint • subclasses might overlap • Overlap and completeness are independent

19. Specialization and Generalization Hierarchiesand Graphs • Specialization hierarchy • Every subclass participates as a subclass in only one class/subclass relationship • Results in a tree structure or strict hierarchy • Fig. 8.1 is a tree • Specialization graph • Subclass can be a subclass in more than one class/subclass relationship

20. At Western, this would be overlap

21. Specialization and Generalization Hierarchiesand Graphs (cont’d.) • Multiple inheritance • Subclass with more than one superclass • If attribute (or relationship) originating in the same superclass inherited more than once via different paths in the graph • Included only once in shared subclass • Single inheritance • Some models and languages are limited to single inheritance

22. Example with multiple inheritance

23. Specialization and Generalization • Specialization • Start with entity type then define subclasses by successive specialization • Top-down conceptual refinement process • Generalization • Several classes with common features are generalized into a superclass; • original classes become its subclasses • Bottom-up conceptual synthesis

24. Modeling of UNION Types Using Categories • Union type or a category • Represents a single superclass/subclass relationship with more than one superclass • Subclass represents a collection of objects that is a subset of the UNION of distinct entity types • Attribute inheritance works more selectively • Category can be total or partial • Some modeling methodologies do not have union types

25. Categories (UNION TYPES) (2) • Example: In a database for vehicle registration, a vehicle owner can be a PERSON, a BANK (holding a lien on a vehicle) or a COMPANY. • A category (UNION type) called OWNER is created to represent a subset of the union of the three superclasses COMPANY, BANK, and PERSON • A category member must exist in at least one of its superclasses • Difference from shared subclass, which is a: • subset of the intersection of its superclasses • shared subclass member must exist in all of its superclasses

26. Example with Union types

27. A Sample UNIVERSITY EER Schema, Design Choices, and Formal Definitions • The UNIVERSITY Database Example • UNIVERSITY database • Students and their majors • Transcripts, and registration • University’s course offerings

29. Design Choices for Specialization/Generalization • Many specializations and subclasses can be defined to make the conceptual model accurate • If all the subclasses of a specialization/generalization have few specific attributes and no specific relationships • Can be merged into the superclass • Replace with one or more type attributes that specify the subclass or subclasses that each entity belongs to

30. Design Choices for Specialization/Generalization (cont’d.) • Union types and categories should generally be avoided • Choice of disjoint/overlapping and total/partial constraints on specialization/generalization • Driven by rules in miniworld being modeled

31. Mapping EER diagrams to relational databases • Recall we had 7 steps for mapping ER diagrams to relations: Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types Step 4: Mapping of Binary 1:N Relationship Types Step 5: Mapping of Binary M:N Relationship Types Step 6: Mapping of Multivalued Attributes Step 7: Mapping of N-ary Relationship Types

32. Mapping of Specialization or Generalization • Step 8: Options for Mapping Specialization or Generalization • Option 8A: Multiple relations—superclass and subclasses • For any specialization (total or partial, disjoint or overlapping) • Option 8B: Multiple relations—subclass relations only • Subclasses are total • Specialization has disjointedness constraint • Option 8C: Single relation with one type attribute • A single type or discriminating attribute indicates subclass of tuple • Works if subclasses are disjoint • Potential for generating many NULL values if many specific attributes exist in the subclasses • Option 8D: Single relation with multiple type attributes • Works if subclasses are overlapping • Will also work for a disjoint specialization

33. Start with Convert each specialization with • one superclass C, and • m subclasses {S1, S2, …., Sm} where the attributes of C are {k, a1, … an} and k is the (primary) key,

34. 8A: Multiple relations-Superclass and subclasses • Create a relation L for C • with attributes Attrs(L) = {k, a1, … an} and • Primary Key (L) = k. • Create a relation Li for each subclass Si, 1 < i < m, • with the attributes Attrs(Li) = {k} U {attributes of Si} and PK(Li) = k. • This option works for any specialization (total or partial, disjoint or over-lapping).

35. Using method 8A d

36. 8B:Multiple relations-Subclass relations only • Create a relation Li for each subclass Si, 1 < i < m, • with the attributes Attr(Li) = {attributes of Si} U {k, a1 …, an} and • Primary Key (Li) = k. • This option only works for a specialization whose subclasses are total (every entity in the superclass must belong to (at least) one of the subclasses).

37. Using Method 8B d Tonnage

38. 8C: Single relation with one type attribute t • Create a single relation L • with attributes Attrs(L) = {k, a1, … an} U {attributes of S1} U…U {attributes of Sm} U {t} and Primary Key(L) = k. • The attribute t is called a type (or discriminating) attribute that indicates the subclass to which each tuple belongs

39. Using Method 8C d EngType

40. 8D: Single relation with multiple type attributes • Create a single relation L • with attributes Attrs(L) = {k, a1, … an} U {attributes of S1} U…U {attributes of Sm} U {t1, t2,…,tm} and Primary Key (L) = k. • Each ti, 1 < I < m, is a Boolean type attribute indicating whether or not a tuple belongs to the subclass Si.

41. Using Method 8D

42. Mapping of Shared Subclasses (Multiple Inheritance) Should be 8.7

43. Step 9: mapping of Union Types • The different types making up the union types probably have different keys (different formats e.g. between a driver’s licence no. and a company name) • make up a new key, which can be called a surrogate key, as in the next example • this is very awkward, which is why the text advises against using union types in a design