databaseim isu1 chapter 7 er- and eer-to-relational mapping fundamentals of database systems
TRANSCRIPT
IM ISU 1Database
Chapter 7
ER- and EER-to-Relational
Mapping
Fundamentals of
Database Systems
IM ISU 2Database
Chapter Outline
ER-to-Relational Mapping Algorithm Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relation 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.
Mapping EER Model Constructs to Relations Step 8: Options for Mapping Specialization or Generalization. Step 9: Mapping of Union Types (Categories).
IM ISU 3Database
ER-to-Relational Mapping Algorithm
STEP 1: Regular entity type mapping» For each regular (strong) entity type E in the ER
schema, create a relation R that includes all the simple attributes of E
» Include only the simple component attributes of a composite attribute
» Choose one of the key attributes of E as primary key for R
» If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of R
IM ISU 4Database
ER-to-Relational Mapping (cont.)
IM ISU 5Database
ER-to-Relational Mapping (cont.)
IM ISU 6Database
ER-to-Relational Mapping (cont.)
multivalued attribute
IM ISU 7Database
ER-to-Relational Mapping (cont.)
IM ISU 8Database
ER-to-Relational Mapping (cont.) STEP 2: Weak entity type mapping
» For each weak entity type W with owner entity type E, create a relation R, and include all simple attributes (or simple components of composite attributes) of W as attributes of R
» Include as foreign key attributes of R the primary key attribute(s) of the relation(s) corresponding to E
» The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any
IM ISU 9Database
ER-to-Relational Mapping (cont.)
IM ISU 10Database
ER-to-Relational Mapping (cont.) STEP 3: Mapping 1:1 relationship
» For each binary 1:1 relationship type R, identify the relations S and T that correspond to the entity types participating in R
» Choose one of the relations—S, say—and include as foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S
» Include all the simple attributes (or simple components of composite attributes) of the 1:1 relationship type R as attributes of S
IM ISU 11Database
ER-to-Relational Mapping (cont.)
ST
IM ISU 12Database
ER-to-Relational Mapping (cont.) STEP 4: Mapping 1:N relationship
» For each regular binary 1:N relationship type R, identify the relation S that corresponds to the N-side of the relationship type
» Include as foreign key in S the primary key of the relation T that represents the other entity type in R
» Include any simple attributes (or simple components of composite attributes) of the 1:N relationship type as attributes of S
IM ISU 13Database
ER-to-Relational Mapping (cont.)
IM ISU 14Database
ER-to-Relational Mapping (cont.)
S T
IM ISU 15Database
ER-to-Relational Mapping (cont.)
S
T
IM ISU 16Database
ER-to-Relational Mapping (cont.) STEP 5: Mapping M:N relationship
» For each binary M:N relationship type R, create a new relation S to represent R
» Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S
» Include any simple attributes of the M:N relationship type (or simple components of composite attributes) as attributes of S
» Note: We cannot represent an M:N relationship type by a single foreign key attribute in one of the participating relations
IM ISU 17Database
ER-to-Relational Mapping (cont.)
IM ISU 18Database
ER-to-Relational Mapping (cont.)
STEP 6: Mapping multivalued attributes» For each multivalued attribute A, create a new rel
ation R » Include an attribute corresponding to A, plus the
primary key attribute K—as a foreign key in R—of the relation that represents the entity type or relationship type that has A as an attribute.
» The primary key of R is the combination of A and K
» If the multivalued attribute is composite, we include its simple components
IM ISU 19Database
ER-to-Relational Mapping (cont.)
IM ISU 20Database
ER-to-Relational Mapping (cont.)
STEP 7: Mapping n-ary relationship» For each n-ary relationship type R, where n > 2, c
reate a new relation S to represent R » Include as foreign key attributes in S the primary
keys of the relations that represent the participating entity types
» Include any simple attributes of the n-ary relationship type (or simple components of composite attributes) as attributes of S
» The primary key of S is usually a combination of all the foreign keys
IM ISU 21Database
ER-to-Relational Mapping (cont.)
IM ISU 22Database
ER-to-Relational Mapping (cont.)
Summary ER-to-Relational Models ER Model Relational Model
Entity type "Entity" relation
1:1 or 1:N relationship type Foreign key (or "relationship" relation)
M:N relationship type "Relationship" relation and two foreign keys
n-ary relationship type "Relationship" relation and n foreign keys
Simple attribute Attribute
Composite attribute Set of simple component attributes
Multivalued attribute Relation and foreign key
Value set Domain
Key attribute Primary (or secondary) key
IM ISU 23Database
EER-to-Relational Mapping
Mapping of Specialization (or Generalization) STEP 8:
» Convert each specialization with m subclasses {S
1, S2, . . ., Sm} and (generalized) superclass C, where the attributes of C are {k, a1, . . ., an} and k is the (primary) key, into relation schemas using one of the four following options:
IM ISU 24Database
EER-to-Relational Mapping (cont.) Option 8A: Multiple relations--Superclass and subclass
es» 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
attributes Attrs(Li) = {k} {attributes of Si} and PK(Li) = k. » Note: This option works for any constraints on the specializa
tion: disjoint or overlapping, total or partial. But the constraint must hold for each Li.
<k>(Li) <k>(L)
IM ISU 25Database
Example of Option 8A
IM ISU 26Database
EER-to-Relational Mapping (cont.)
Option 8B: Multiple relations--Subclasses only» Create a relation Li for each subclass Si, 1 i m, w
ith the attributes Attrs(Li) = {attributes of Si} {k, a1, . . ., an} and PK(Li) = k.
» Note: This option works well only when both the disjoint and total constraints hold.
– If the specialization is not total, an entity that does not belong to any of the subclasses Si is lost.
– If the specialization is not disjoint, an entity belonging to more than one subclass will have its inherited attributes from the superclass C stored redundantly in more than one Li
IM ISU 27Database
Example of Option 8B
IM ISU 28Database
EER-to-Relational Mapping (cont.)
Option 8C: Single relation with one type attribute» Create a single relation L with attributes Attrs(L) = {k, a1, . . ., an} {attributes of S1} . . . {attributes of Sm} {t} and PK(L) = k.
» Note: This option is for a specialization whose subclasses are disjoint, and t is a type (or discriminating) attribute that indicates the subclass to which each tuple belongs, if any.
» Warning: This option has the potential for generating a large number of null values.
IM ISU 29Database
Example of Option 8C
IM ISU 30Database
EER-to-Relational Mapping (cont.)
Option 8D: Single relation with multiple type attributes» Create a single relation schema L with attributes
Attrs(L) = {k, a1, . . ., an} {attributes of S1} . . . {attributes of Sm} {t1, t2, . . ., tm} and PK(L) = k.
» Note: This option is for a specialization whose subclasses are overlapping (but will also work for a disjoint specialization), and each ti, 1 i m, is a Boolean attribute indicating whether a tuple belongs to subclass Si.
IM ISU 31Database
Example of Option 8D
IM ISU 32Database
EER-to-Relational Mapping (cont.)
After notes:» Options 8A and 8B can be called the multiple
relation options, whereas options 8C and 8D can be called the single relation options.
IM ISU 33Database
EER-to-Relational Mapping (cont.) Mapping of Shared Subclasses
A shared subclass is a subclass of several superclasses. These classes must all have the same key attribute
We can apply any of the options discussed in step 8 to a shared subclass, although usually option 8A is used.
Example» In Figure 7.6, options 8C and 8D are used for the
shared subclass STUDENT_ASSISTANT in EMPLOYEE and STUDENT, respectively
IM ISU 34Database
EER-to-Relational Mapping (cont.)
IM ISU 35Database
EER-to-Relational Mapping (cont.)
IM ISU 36Database
EER-to-Relational Mapping (cont.)
Step 9: Mapping of Categories A category is a subclass of the union of two o
r more superclasses that can have different keys because they can be of different entity types
For mapping a category whose defining superclasses have different keys» It is customary to specify a new key attribute, call
ed a surrogate key
IM ISU 37Database
EER-to-Relational Mapping (cont.)
» Create a relation schema corresponding to the category and include any attributes of the category in this relation
» Add the surrogate key attribute as foreign key to each relation corresponding to a superclass of the category
Example» OWNER category shown in Figure 4.8, which is
a subset of the union of three entity types PERSON, BANK, and COMPANY
IM ISU 38Database
EER-to-Relational Mapping (cont.)
IM ISU 39Database
EER-to-Relational Mapping (cont.)
IM ISU 40Database
EER-to-Relational Mapping (cont.)
For a category whose superclasses have the same key» The mapping process is similar but there is no need
for a surrogate key
Example» VEHICLE category in Figure 4.8
IM ISU 41Database
EER-to-Relational Mapping (cont.)
IM ISU 42Database
EER-to-Relational Mapping (cont.)
IM ISU 43Database
Chapter Summary
ER-to-Relational Mapping Algorithm Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relation 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.
Mapping EER Model Constructs to Relations Step 8: Options for Mapping Specialization or Generalization. Step 9: Mapping of Union Types (Categories).