CH 9 - Relational Database Design by ER- and EER-to-Relational Mapping Flashcards
Show how each ER model construct can be mapped to the relational model: Entity type
Entity relation
Show how each ER model construct can be mapped to the relational model: 1:1 or 1:N relationship type
Foreign key (or relationship relation)
1: 1
- Foreign key approach: one total participation
- Merged relation approach: 2 total participation
- Cross-reference or relationship relation approach, avoid excessive NULL val- ues in the foreign key.
1: N
- Foreign key approach
- Cross-reference or relationship relation approach, avoid excessive NULL val- ues in the foreign key.
Show how each ER model construct can be mapped to the relational model: M:N relationship type
Relationship relation and two foreign keys
Show how each ER model construct can be mapped to the relational model: n-ary relationship type
Relationship relation and n foreign keys
Show how each ER model construct can be mapped to the relational model: Simple attribute
Attribute
Show how each ER model construct can be mapped to the relational model: Composite attribute
Set of simple component attributes
Show how each ER model construct can be mapped to the relational model: Multivalued attribute
Relation and foreign key
Show how each ER model construct can be mapped to the relational model: Value set
Domain
Show how each ER model construct can be mapped to the relational model: Key attribute
Primary (or secondary) key
Mapping Specialization or Generalization
m subclasses {S1, S2, … , Sm} and (generalized) super- class C, where the attributes of C are {k, a1, … , an} and k is the (primary) key, into relation schemas using one of the 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 attributes Attrs(Li) = {k} ∪ {attributes of Si} and PK(Li) = k. This option works for any specialization (total or partial, disjoint or overlapping).
■ Option 8B: Multiple relations—subclass relations only. Create a relation Li for each subclass Si, 1 ≤ i ≤ m, with the attributes Attrs(Li) = {attributes of Si} ∪ {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). Additionally, it is only recommended if the specialization has the disjointedness constraint (see Section 4.3.1). If the specialization is overlapping, the same entity may be duplicated in several relations.
■ Option 8C: Single relation with one type attribute. Create a single relation L with attributes Attrs(L) = {k, a1, ..., an} ∪ {attributes of S1} ∪ ... ∪ {attri- butes of Sm} ∪ {t} and PK(L) = k. The 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 sub- classes are disjoint, and has the potential for generating many NULL values if many specific (local) attributes exist in the subclasses.
■ 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. Each ti, 1 ≤ i ≤ m, is a Boolean type attribute indicating whether or not a tuple belongs to subclass Si. This option is used for a specialization whose sub- classes are overlapping (but will also work for a disjoint specialization).
Mapping of Union Types (Categories).
- For mapping a category whose defining superclasses have different keys, it is customary to specify a new key attri- bute, called a surrogate key, when creating a relation to correspond to the union type.
- nclude the surrogate key attribute as foreign key in each relation corre- sponding to a superclass of the category,
- For a category whose superclasses have the same key, such as VEHICLE in Figure 4.8, there is no need for a surrogate key
