Transforming Conceptual ER/EER Models to Relational Tables

Introduction: From Blueprint to Physical Database

Designing an Entity-Relationship (ER) or Enhanced ER (EER) diagram is only half the battle. Once the business blueprint is approved, the database engineer must systematically convert that conceptual diagram into actual Relational Database Tables.

There is a standardized, algorithmic set of rules used worldwide to transform every ER construct into relational tables with appropriate Primary and Foreign keys.

1. Mapping Regular (Strong) Entity Sets and Attributes

Rule 1: Regular Entity Sets

• For each strong entity set E, create a new relation R that includes all simple attributes of E.
• Choose one of the key attributes of E as the Primary Key of R.
• Example: Entity STUDENT(Roll_No, Name, DOB, Gender) becomes table:
  STUDENT(<u>Roll_No</u>, Name, DOB, Gender)

Rule 2: Composite Attributes

• Do not create a column for the composite attribute itself.
• Instead, flatten the composite attribute by including only its simple component attributes as columns!
• Example: If Address is composite with (City, State, Pincode), the resulting table has columns:
  CUSTOMER(<u>Customer_ID</u>, Name, City, State, Pincode)

Rule 3: Multi-Valued Attributes

• For each multi-valued attribute A, create a brand-new separate table.
• The new table contains:
  1. The multi-valued attribute A.
  2. The Primary Key of the parent entity as a Foreign Key.
• The Primary Key of the new table is the composite of both fields!
• Example: If EMPLOYEE(Emp_ID, Name) has multi-valued attribute Skill:
  Table 1: EMPLOYEE(<u>Emp_ID</u>, Name)
  Table 2: EMP_SKILLS(<u>Emp_ID, Skill</u>) (where Emp_ID references EMPLOYEE).

2. Mapping Weak Entity Sets (Models without External Keys)

Rule 4: Weak Entities

• A weak entity does not possess a primary key of its own; it relies on an owner entity.
• Create a relation R containing all simple attributes of the weak entity.
• Add the Primary Key of the identifying owner entity as a Foreign Key in R.
• The Primary Key of R is the composite of the Owner’s Primary Key + Partial Discriminator of the weak entity!
• Example: Weak entity DEPENDENT(Dep_Name, Relationship) owned by EMPLOYEE(Emp_ID):
  Table: DEPENDENT(<u>Emp_ID, Dep_Name</u>, Relationship)
  (where Emp_ID is a foreign key with ON DELETE CASCADE).

3. Mapping Relationships (Binary 1:1, 1:N, M:N)

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

• Example: CITIZEN(Citizen_ID, Name) and PASSPORT(Passport_No, Issue_Date).
• Method: Add Citizen_ID as a unique Foreign Key in PASSPORT:
  PASSPORT(<u>Passport_No</u>, Issue_Date, Citizen_ID) (with UNIQUE(Citizen_ID)).

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

• Example: One DEPARTMENT employs many EMPLOYEEs.
• Method: Always put the Primary Key of the “One” side into the “Many” side table!
  Table 1: DEPARTMENT(<u>Dept_ID</u>, Dept_Name)
  Table 2: EMPLOYEE(<u>Emp_ID</u>, Name, Salary, Dept_ID)
  (where Dept_ID in EMPLOYEE is a Foreign Key referencing DEPARTMENT).

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

• Example: STUDENT enrolls in COURSE with attribute Grade.
• Method: A new Junction Table must be created:
  Table 1: STUDENT(<u>Roll_No</u>, Name)
  Table 2: COURSE(<u>Course_ID</u>, Title)
  Table 3: ENROLLMENT(<u>Roll_No, Course_ID</u>, Grade)
  (Both Roll_No and Course_ID are foreign keys, and their combination forms the composite primary key).

4. Mapping Recursive (Unary) Relationships

When an entity relates to itself (e.g., an Employee is managed by another Employee):

• Add a recursive Foreign Key column within the same table referencing its own primary key!
• Table: EMPLOYEE(<u>Emp_ID</u>, Name, Salary, Manager_ID)
  (where Manager_ID is a Foreign Key referencing Emp_ID in the same table!).

5. Mapping EER Specialization and Generalization (4 Options)

When transforming Superclasses and Subclasses (e.g., VEHICLE into CAR and TRUCK), designers choose between four options based on disjointness and queries:

1. Option A (Multiple Relations for Superclass and Subclasses):
   • Create 1 table for Superclass: VEHICLE(<u>Vehicle_ID</u>, Price, Model).
   • Create tables for each subclass containing its local attributes + Vehicle_ID as FK:
     CAR(<u>Vehicle_ID</u>, Num_Doors) and TRUCK(<u>Vehicle_ID</u>, Tonnage).
   • Best for: Disjoint or overlapping; preserves normalized data.
2. Option B (Subclass Tables Only):
   • Only create tables for CAR(<u>Vehicle_ID</u>, Price, Model, Num_Doors) and TRUCK(...).
   • Best for: Total specialization with disjoint subclasses (no generic vehicles exist).
3. Option C (Single Relation with One Type Attribute):
   • Combine everything into one giant table with a discriminator column:
     VEHICLE(<u>Vehicle_ID</u>, Price, Model, Vehicle_Type, Num_Doors, Tonnage).
   • Unused attributes are set to NULL.
   • Best for: Disjoint subclasses when query speed is critical and NULL overhead is acceptable.
4. Option D (Single Relation with Multiple Boolean Flags):
   • Similar to Option C, but uses boolean flags (Is_Car, Is_Truck) for overlapping subclasses.

6. Mapping Aggregated Object Sets

When an aggregated relationship [EMPLOYEE - WORKS_ON - PROJECT] relates to another entity MACHINERY:

• Create the relation for the aggregated relationship:
  WORKS_ON(<u>Emp_ID, Project_ID</u>, Start_Date).
• Create a new relation connecting the aggregated key to the new entity:
  USES(<u>Emp_ID, Project_ID, Machine_ID</u>, Hours_Used).

💡 DAVV Exam Preparation Tips

5-Mark Question: “How do you transform a Many-to-Many (M:N) relationship and a Multi-Valued attribute from an ER diagram into relational tables?”

• Explain why M:N relationships require a separate junction table with composite primary keys.
• Explain why multi-valued attributes require a new child table referencing the parent’s primary key.

10-Mark Question: “Describe the step-by-step mapping algorithm to convert an ER and EER diagram into relational tables. Cover Strong entities, Weak entities, 1:1, 1:N, M:N, Recursive relationships, and Specialization/Generalization options.”

• Detail Rules 1 through 4 (Strong, Composite, Multivalued, Weak entities).
• Illustrate 1:1, 1:N, and M:N mapping with schemas.
• Detail the Recursive employee-manager self-join.
• Present the 4 options for EER superclass/subclass mapping.