3. ER/EER to Relational Model Mapping

ER/EER to Relational Model Mapping

ER (Entity–Relationship) and EER (Enhanced Entity–Relationship) diagrams are conceptual models.
To implement them in a database, we convert (map) them into the Relational Model (tables).

 

1. ER to Relational Model Mapping Steps

Step 1: Mapping Strong Entities

·       Each strong entity becomes a table.

·       Key attribute → Primary Key (PK).

Example:
Entity: Student(ID, Name, Age)
Table: Student(ID PK, Name, Age)

 

Step 2: Mapping Weak Entities

·       Weak entity → table

·       Includes its partial key + primary key of owner entity.

·       Combined key becomes the primary key.

Example:
Dependent(Name, Age) weak entity owned by Employee(EID).
Table: Dependent(Name, EID PK, Age)

 

Step 3: Mapping Relationships

A. 1:1 (One-to-One)

Add PK of one table as foreign key in the other.

B. 1:N (One-to-Many)

Add PK of the “one” side as FK in the “many” side.

C. M:N (Many-to-Many)

Create a new relation (table):

·       Primary keys of both entities become composite PK.

·       Include any relationship attributes.

Example:
Table: Enroll(StudentID PK/FK, CourseID PK/FK, Grade)

 

Step 4: Mapping Multivalued Attributes

·       Each multivalued attribute becomes a separate table.

·       Attribute + primary key of the main entity = composite key.

Example:
Skill(skillName, EmployeeID PK)

 

Step 5: Mapping Derived Attributes

·       Usually not stored, calculated when needed.

 

2. EER to Relational Model Mapping

EER extends ER with:
✔ Specialization
✔ Generalization
✔ Categories (Union types)
✔ Inheritance constraints

Mapping Specialization/Generalization

There are 4 main strategies:

 

A. Top-Down Mapping (One table for superclass + one per subclass)

Superclass table → common attributes
Subclass tables → specific attributes + PK of superclass as FK/PK.

 

B. Single-Table (One table for entire hierarchy)

All attributes (superclass + subclass) kept in one large table.
Unused attributes are NULL.

 

C. Separate Tables for Each Subclass

Superclass table is removed; only subclass tables created.
Subclass tables include all inherited attributes.

 

D. Mixed Strategy

Depends on constraints like disjoint/overlapping and total/partial.

 

Advantages of ER/EER to Relational Mapping

✔ 1. Simplifies Database Implementation

ER/EER diagrams give a clear structure; mapping converts them into tables easily.

✔ 2. Maintains Data Integrity

Relationships (1:1, 1:N, M:N) become foreign keys, ensuring consistency.

✔ 3. Supports Normalization

Reduces redundancy and anomalies.

✔ 4. Provides a Blueprint for Developers

Easy to understand and implement in SQL.

✔ 5. EER Mapping supports Inheritance

More efficient representation of real-world hierarchical data.

 

Disadvantages of ER/EER to Relational Mapping

 1. Loss of Some Semantics

Relational models cannot directly represent:

·       inheritance

·       overlapping categories

·       constraints (e.g., conditional constraints)

 2. Mapping Can Become Complex

Especially for:

·       M:N relationships

·       weak entities

·       multivalued attributes

·       large inheritance hierarchies (EER)

 3. Increased Number of Tables

Multivalued attributes, M:N relationships create more tables → more joins.

 4. Performance Issues

More tables and joins may slow queries.

 5. Some EER Features Are Hard to Implement

E.g.,:

·       partial vs total specialization

·       disjoint vs overlapping constraints
These require complex rules or triggers.