Mapping EER Model Constructs to Relations Questions and Exercises Ho Chi Minh City University of Technology ∗∗∗ DATABASE SYSTEM Mapping EER Model Constructs to Relations Group 1 March 2019 Mapping EER Model Constructs to Relations Questions and Exercises Members list Group 1: 1. Đinh Quốc Cường 1710712 2. Trần Thanh Quang 1712802 3. Trần Đình Tấn 1713093 4.
Đinh Hoàng Kim 1711872 5. Võ Quý Giang 1711130 Mapping EER Model Constructs to Relations Questions and Exercises Overview Mapping EER Model Constructs to Relations Mapping of Specialization or Generalization Mapping of Shared Subclasses (Multiple Inheritance) Mapping of Categories (Union Types) Questions and Exercises Questions Exercise Mapping EER Model Constructs to Relations Questions and Exercises Mapping EER Model Constructs to Relations Mapping of Specialization or Generalization Mapping of Shared Subclasses (Multiple Inheritance) Mapping of Categories (Union Types) Questions and Exercises Questions Exercise Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization • There are several options for mapping a number of subclasses that together form a specialization (or alternatively, that are generalized into a superclass). Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization • There are several options for mapping a number of subclasses that together form a specialization (or alternatively, that are generalized into a superclass). • Two main options: • Map specialization into a single table.
• Map specialization into multiple tables. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization • There are several options for mapping a number of subclasses that together form a specialization (or alternatively, that are generalized into a superclass). • Two main options: • Map specialization into a single table. • Map specialization into multiple tables.
• We use: • Attrs(R) - the attributes of a relation R. • PK(R) - primary key of R. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Step 8 • Options for Mapping Specialization of Generalization: Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Step 8 • Options for Mapping Specialization of Generalization: • m subclasses {S1 , S2 ,. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Step 8 • Options for Mapping Specialization of Generalization: • m subclasses {S1 , S2 ,.
• Superclass C, the attributes of C are {k, a1 , a2 ,. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Step 8 • Options for Mapping Specialization of Generalization: • m subclasses {S1 , S2 ,. • Superclass C, the attributes of C are {k, a1 , a2 ,. • We have the following options.
Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Option 8A Multiple relations—superclass and subclasses. • Create a relation L for superclass C with: Attrs(L) = {k, a1 , a2 , ., an } and PK(L) = k. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Option 8A Multiple relations—superclass and subclasses. • Create a relation L for superclass C with: Attrs(L) = {k, a1 , a2 , ., an } and PK(L) = k.
• Create a relation Li for each subclass Si with Attrs(Li ) = {k} ∪ {attributes of Si } and PK(Li ) = k Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Option 8A Multiple relations—superclass and subclasses. • Create a relation L for superclass C with: Attrs(L) = {k, a1 , a2 , ., an } and PK(L) = k. • Create a relation Li for each subclass Si with Attrs(Li ) = {k} ∪ {attributes of Si } and PK(Li ) = k This option works for any specialization (total or partial, disjoint or overlapping). Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization textbfExample Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Mapping the EER schema using option 8A Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Option 8B Multiple relations—subclass relations only.
Create a relation Li for each subclass Si with: • Attrs(Li ) = {attributes Si }∪{k, a1 , a2 , ., an } • PK(Li ) = k Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Option 8B Multiple relations—subclass relations only. Create a relation Li for each subclass Si with: • Attrs(Li ) = {attributes Si }∪{k, a1 , a2 , ., an } ∪ {attributes of S1 } ∪. ∪ {attributes of Sn } ∪ {t} • PK(L) = k Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Option 8C Single relation with one type attribute Create a single relation L with: • Arrts(L) = {k, a1 , a2 , ., an } ∪ {attributes of S1 } ∪. ∪ {attributes of Sn } ∪ {t} • PK(L) = k Attribute t is called a type (or discriminating) attribute whose value indicates the subclass to which each tuple belongs, if any.
Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Option 8C Single relation with one type attribute Create a single relation L with: • Arrts(L) = {k, a1 , a2 , ., an } ∪ {attributes of S1 } ∪. ∪ {attributes of Sn } ∪ {t} • PK(L) = k Attribute t is called a type (or discriminating) attribute whose value indicates the subclass to which each tuple belongs, if any. This option works only for a specialization whose subclasses are disjoint, and has the potential for generating many NULL values if many specific (local) attributes exist in the subclasses. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Example Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Option 8D Single relation with multiple type attributes Create a single relation L with: • Arrts(L) = {k, a1 , a2 , ., an } ∪ {attributes of S1 } ∪.
∪ {attributes of Sm } ∪ {t1 , t2 , ., an } ∪ {attributes of S1 } ∪. ∪ {attributes of Sm } ∪ {t1 , t2 , ., tm } • PK(L) = k Each ti is a Boolean type attribute indicating whether or not a tuple belongs to subclass Si. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Option 8D Single relation with multiple type attributes Create a single relation L with: • Arrts(L) = {k, a1 , a2 , ., an } ∪ {attributes of S1 } ∪. ∪ {attributes of Sm } ∪ {t1 , t2 , ., tm } • PK(L) = k Each ti is a Boolean type attribute indicating whether or not a tuple belongs to subclass Si.
This option is used for a specialization whose subclasses are overlapping (but will also work for a disjoint specialization). Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Example Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Specialization or Generalization Example Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Shared Subclasses Shared subclass: • is a subclass of several superclasses (multiple inheritance) and all have the same key attribute. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Shared Subclasses Shared subclass: • is a subclass of several superclasses (multiple inheritance) and all have the same key attribute. • the shared subclass would be modeled as a category (union type) Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Shared Subclasses Shared subclass: • is a subclass of several superclasses (multiple inheritance) and all have the same key attribute.
• the shared subclass would be modeled as a category (union type) • We can apply any of the options discussed in step 8 and subject to the restrictions discussed. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Shared Subclasses Example Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Shared Subclasses • Option 8C is used in the EMPLOYEE relation. • and option 8D is used in the STUDENT relation. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Categories A category (Union type) • is a subclass of the union two or more superclass.
Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Categories A category (Union type) • is a subclass of the union two or more superclass. • a member entity of category must exist in only one its superclass so attribute inheritance works more selectively. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Categories A category (Union type) • is a subclass of the union two or more superclass. • a member entity of category must exist in only one its superclass so attribute inheritance works more selectively.
• can be total or partial. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of Categories A category (Union type) • is a subclass of the union two or more superclass. • a member entity of category must exist in only one its superclass so attribute inheritance works more selectively. • can be total or partial.
• the superclass of a category may have different key attributes or the same key attribute. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of categories Category have different keys: Mapping EER Model Constructs to Relations Questions and Exercises Mapping of categories Category have the same keys: Mapping EER Model Constructs to Relations Questions and Exercises Mapping of categories Mapping of categories • For mapping a category whose defining superclass have different key attributes, we cannot use any one of them to identify all entities in the relation, so it is customary to specify a new key attribute, called a surrogate key, when creating a relation to correspond to the category. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of categories Mapping of categories • For mapping a category whose defining superclass have different key attributes, we cannot use any one of them to identify all entities in the relation, so it is customary to specify a new key attribute, called a surrogate key, when creating a relation to correspond to the category. • For a category whose superclass have the same key, there is no need for a surrogate key.
Mapping EER Model Constructs to Relations Questions and Exercises Mapping of categories Step 9 • Let C1 , C2 , ., Cm be the entity types participating in the union and S be the category. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of categories Step 9 • Let C1 , C2 , ., Cm be the entity types participating in the union and S be the category. • Create a relation to correspond to the category S. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of categories Step 9 • Let C1 , C2 , ., Cm be the entity types participating in the union and S be the category.
• Create a relation to correspond to the category S. • Specify a surrogate key ks so that PK(S) = ks. Mapping EER Model Constructs to Relations Questions and Exercises Mapping of categories Step 9 • Let C1 , C2 , ., Cm be the entity types participating in the union and S be the category. • Create a relation to correspond to the category S.
• Specify a surrogate key ks so that PK(S) = ks. • Add ks to each attribute of Ci as a foreign key.