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.