Terse Notes — Fundamentals of Database Systems Part 9
Relational Database Design by ER- and EER-to-Relational Mapping
Book Credit to Elmasri Ramez And Navathe Shamkant
Book: Fundamentals Of Database System, 7th Edition by Elmasri Ramez And Navathe Shamkant
Ch 9 — Relational Database Design by ER- and EER-to-Relational Mapping
Step 1 — create tables for all strong entity types.
Step 2 — create tables for all weak entity types.
Step 3 — for mapping 1 to 1 binary relationship types:
1. Foreign key approach — best way is to choose an entity that has total participation in a relation. Then include a foreign key in it — this foreign key is the primary key of the entity that it must relate to (bc total participation)
2. Merged relation approach — merge two entities into one — only if both participations are total.
3. Cross-reference or relationship relation approach — required for many to many relationships. Create a lookup table.
Step 4 — for mapping 1 to many relationship types:
1. Foreign key approach — for the table that has the many side of the relationship, include in it a foreign key relating to the primary key of the other relation.
2. Relationship relation approach — create a lookup table.
Step 5 — for mapping many to many relationship types — create a lookup table
Step 6 — For mapping multivalued attributes — create a new table that includes the attributes plus the primary key from the relating table (which will be a foreign key here).
Step 7 — for mapping n-ary relationship types: create a lookup table and in this table create foreign keys that are the primary keys of all the other tables it relates to.
Continuing on with EER types…
Step 8 — how to map subclasses that form a specialization or are generalized into a superclass. Several options exist depending on the constraints.
8a. Multiple relations for super class and subclass. Create multiple tables. One main and other subclasses that include the main primary key.
8b. Multiple relations for subclasses only. Create tables for each subclass — only for total participation and disjointedness constraint. NOT overlapping
8c. Single relation with one type attribute. Create a single table with all the attributes — only for subclasses that are disjoint.
8d. Single relation with multiple type attributes. Create a single relation schema with a boolean type attribute that holds T/F for whether the tuple belongs to a subclass. This covers overlapping subclasses.
Step 9 — Mapping of Union Types — for mapping categories that have different keys in superclass. Create a surrogate key.
Review Questions
Mapping ER Model Constructs to Relations
- Entity Type: For each regular (strong) entity type E, create a relation R that includes all simple attributes of E. Choose one of the key attributes as the primary key for R. If E has a composite key, the set of simple attributes forming it will be the primary key of R.
- Weak Entity Type: For each weak entity type W with owner E, create a relation for W that includes all simple attributes of W, plus the primary key of the relation for E as a foreign key in the relation for W. The primary key of W’s relation is the combination of its partial key and the foreign key referencing E.
- Binary 1:1 Relationship: Use the foreign key approach — choose one entity relation S and include the primary key of the other entity T as a foreign key in S. Include any simple attributes of the relationship as attributes of S.
- Binary 1:N Relationship: Use the foreign key approach — identify the relation S for the entity on the N-side and include the primary key of the other entity T as a foreign key in S. Include any simple relationship attributes in S.
- Binary M:N Relationship: Create a new relation S to represent the relationship. Include foreign keys referencing the primary keys of the participating entities. Any simple relationship attributes become attributes of S. The primary key of S is the combination of the foreign keys.
- N-ary Relationship: Create a new relation S with foreign keys referencing the primary keys of the participating entities. Simple relationship attributes become attributes of S. The primary key is usually the combination of all foreign keys, unless some participating entities have cardinality 1.
Mapping EER Constructs to Relations
- Specialization/Generalization:
- Option 8A: Create a relation L for the superclass with its attributes. Create a relation Li for each subclass Si with the subclass attributes plus a foreign key referencing L’s primary key.
- Option 8B: Create a relation Li for each subclass with all superclass and subclass attributes. Only for total, disjoint constraints.
- Option 8C: Create a single relation with attributes of superclass and all subclasses. Use null values and discriminator for subclass attributes. Only for disjoint constraints.
The choice depends on the constraints — total/partial, disjoint/overlapping inheritance.