Enhanced Entity-Relationship (EER) Modeling.pptx

THE ENHANCED E-R MODEL (EERD) Chapter 1 Modern Database Management- 12th. Edition (ch3), Jeffrey A. Hoffer, Heikki Topi, V. Ramesh 1 Fundamenta...

THE ENHANCED E-R MODEL (EERD) Chapter 1 Modern Database Management- 12th. Edition (ch3), Jeffrey A. Hoffer, Heikki Topi, V. Ramesh 1 Fundamentals OF Database Systems- 7th Edition (ch4), Ramez Elmasri, Shamkant B. Navathe Transforming EERD Diagrams into Relations (cont.) Mapping Supertype/Subtype Relationships One relation for supertype and for each subtype Supertype attributes (including identifier and subtype discriminator) go into supertype relation Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation 1:1 relationship established between supertype and each subtype, with supertype as primary table Supertype/subtype relationships Mapping Supertype/subtype relationships to relations Resulting relations These are implemented as one-to-one relationships  Mapping EER Model Constructs to Relations  Step 8: Options for Mapping Specialization or Generalization. Convert each specialization with m subclasses {S1, S2,….,Sm} and generalized superclass C, where the attributes of C are {k,a1,…an} and k is the (primary) key, into relational schemas using one of the four following options: Option 8A: Multiple relations-Superclass and subclasses. Create a relation L for C with attributes Attrs(L) = {k,a1,…an} and PK(L) = k. Create a relation Li for each subclass Si, 1 < i < m, with the attributesAttrs(Li) = {k} U {attributes of Si} and PK(Li)=k. This option works for any specialization (total or partial, disjoint of over-lapping). Option 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 PK(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). FIGURE 4.4 EER diagram notation for an attribute- defined specialization on JobType. FIGURE 7.4 Options for mapping specialization or generalization. (a) Mapping the EER schema in Figure 4.4 using option 8A. FIGURE 4.3 Generalization. (b) Generalizing CAR and TRUCK into the superclass VEHICLE. FIGURE 7.4 Options for mapping specialization or generalization. (b) Mapping the EER schema in Figure 4.3b using option 8B. Mapping EER Model Constructs to Relations (cont) Option 8C: Single relation with one type attribute. Create a single relation L with attributes Attrs(L) = {k,a1,…an} U {attributes of S1} U…U {attributes of Sm} U {t} and PK(L) = k. The attribute t is called a type (or discriminating) attribute that indicates the subclass to which each tuple belongs Option 8D: Single relation with multiple type attributes. Create a single relation schema L with attributes Attrs(L) = {k,a1,…an} U {attributes of S1} U…U {attributes of Sm} U {t1, t2,…,tm} and PK(L) = k. Each ti, 1 < I < m, is a Boolean type attribute indicating whether a tuple belongs to the subclass Si. FIGURE 4.4 EER diagram notation for an attribute- defined specialization on JobType. FIGURE 7.4 Options for mapping specialization or generalization. (c) Mapping the EER schema in Figure 4.4 using option 8C. FIGURE 4.5 EER diagram notation for an overlapping (nondisjoint) specialization. FIGURE 7.4 Options for mapping specialization or generalization. (d) Mapping Figure 4.5 using option 8D with Boolean type fields Mflag and Pflag.  Mapping EER Model Constructs to Relations (cont)  Mapping of Shared Subclasses (Multiple Inheritance) A shared subclass, such as STUDENT_ASSISTANT, is a subclass of several classes, indicating multiple inheritance. These classes must all have the same key attribute; otherwise, the shared subclass would be modeled as a category. We can apply any of the options discussed in Step 8 to a shared subclass, subject to the restriction discussed in Step 8 of the mapping algorithm. Below both 8C and 8D are used for the shared class STUDENT_ASSISTANT. FIGURE 4.7 A specialization lattice with multiple inheritance for a UNIVERSITY database. FIGURE 7.5 Mapping the EER specialization lattice in Figure 4.6 using multiple options.  Mapping Exercise 7.4. Exercise FIGURE 7.7 An ER schema for a SHIP_TRACKING database.