ER and EER to Relational Mapping

Relational Database Design

Based on a conceptual schema design.
Uses a seven-step algorithm to convert the basic ER model constructs into relations.
Includes additional steps for the EER model.

ER-to-Relational Mapping Algorithm

The mapping will create tables with simple single-valued attributes
Step 1: Mapping of Regular Entity Types
- For each regular entity type, create a relation R that includes all the simple attributes of E.
- These are called entity relations.
- Each tuple represents an entity instance.
Step 2: Mapping of Weak Entity Types
- For each weak entity type, create a relation R and include all simple attributes of the entity type as attributes of R.
- Include the primary key attribute of the owner entity as foreign key attributes of R.
Step 3: Mapping of Binary 1:1 Relationship Types
- For each binary 1:1 relationship type, identify relations that correspond to entity types participating in R.
- Possible approaches:
  - Foreign key approach
  - Merged relationship approach
  - Cross-reference or relationship relation approach
Step 4: Mapping of Binary 1:N Relationship Types
- For each regular binary 1:N relationship type:
  - Identify the relation that represents the participating entity type at the N-side of the relationship type (call it S).
  - Include the primary key of the other entity type as a foreign key in S.
  - Include simple attributes of the 1:N relationship type as attributes of S.
- Alternative approach:
  - Use the relationship relation (cross-reference) option as in the third option for binary 1:1 relationships.
Step 5: Mapping of Binary M:N Relationship Types
- For each binary M:N relationship type:
  - Create a new relation S.
  - Include the primary keys of participating entity types as foreign key attributes in S.
  - Include any simple attributes of the M:N relationship type.
Step 6: Mapping of Multivalued Attributes
- For each multivalued attribute (call it A):
  - Create a new relation (R).
  - The primary key of R is the combination of A and K (primary key of the entity).
  - If the multivalued attribute is composite, include its simple components.
Step 7: Mapping of N-ary Relationship Types
- For each n-ary relationship type R:
  - Create a new relation S to represent R.
  - Include primary keys of participating entity types as foreign keys.
  - Include any simple attributes as attributes.

Discussion and Summary of Mapping for ER Model Constructs

In a relational schema, relationship types are not represented explicitly.
They are represented by having two attributes A and B: one a primary key and the other a foreign key.

Mapping EER Model Constructs to Relations

Extends the ER-to-relational mapping algorithm.
Step 8: Options for Mapping Specialization or Generalization
- Option 8A: Multiple relations—superclass and subclasses
  - For any specialization (total or partial, disjoint or overlapping).
- Option 8B: Multiple relations—subclass relations only
  - Subclasses are total.
  - Specialization has disjointedness constraint.
- Option 8C: Single relation with one type attribute
  - A type or discriminating attribute indicates the subclass of the tuple.
  - Subclasses are disjoint.
  - Potential for generating many NULL values if many specific attributes exist in the subclasses.
- Option 8D: Single relation with multiple type attributes
  - Subclasses are overlapping.
  - Will also work for a disjoint specialization.
Mapping of Shared Subclasses (Multiple Inheritance)
- Apply any of the options discussed in step 8 to a shared subclass.
Step 9: Mapping of Union Types (Categories)
- Defining superclasses have different keys.
- Specify a new key attribute, called a surrogate key.

Summary

Map conceptual schema design in the ER model to a relational database schema.
Algorithm for ER-to-relational mapping is used.
The COMPANY database example is used for illustration.
Additional steps in the algorithm for mapping constructs from the EER model into the relational model are included.