Advanced ER Mapping

The basic ER mapping rules cover simple entities, single-valued attributes, and binary 1:N relationships. Real-world databases involve more complex constructs.

This chapter covers how to map:

• M:N binary relationships
• Multi-valued attributes
• Weak entities with composite keys
• Ternary and higher-degree relationships
• Generalization and specialization hierarchies
• Aggregation
• Recursive relationships

These are essential for designing non-trivial databases and appear frequently in system design and schema design interviews.

---

Learning Objectives

After completing this chapter, you will be able to:

• Map M:N relationships into separate junction tables.
• Map multi-valued attributes into child tables.
• Map weak entities with proper composite keys.
• Map ternary relationships correctly.
• Choose between different strategies for mapping generalization hierarchies.
• Recognize when a recursive relationship is needed.
• Apply aggregation to simplify complex ER diagrams.

---

Mapping M:N (Many-to-Many) Relationships

An M:N relationship cannot be represented by adding a foreign key to either side — that would create duplication.

Rule

Create a separate junction table (also called associative table or bridge table) containing the primary keys of both entities as foreign keys.

Example

ER Diagram:

Student ── M:N Enrolls ── Course

Mapped Tables:

Students
┌──────────────┐
│ Student_ID   │ PK
│ Name         │
└──────────────┘

Courses
┌──────────────┐
│ Course_ID    │ PK
│ Title        │
└──────────────┘

Enrollments (Junction Table)
┌──────────────────┐
│ Student_ID       │ PK, FK
│ Course_ID        │ PK, FK
│ Enrollment_Date  │
└──────────────────┘

The composite primary key {Student_ID, Course_ID} ensures no duplicate enrollments.

With Relationship Attributes

If the M:N relationship has its own attributes (like Enrollment_Date, Grade), they go into the junction table.

---

Mapping Multi-Valued Attributes

A multi-valued attribute can store multiple values for a single entity.

Rule

Create a separate table for the multi-valued attribute. The primary key of the original entity becomes a foreign key in this table. The composite primary key is usually {Entity_PK, Attribute_Value}.

Example

ER Diagram:

Employee
├── Emp_ID (PK)
├── Name
├── Phone_Numbers (multi-valued)

Mapped Tables:

Employees
┌──────────────┐
│ Emp_ID       │ PK
│ Name         │
└──────────────┘

Employee_Phones
┌──────────────────┐
│ Emp_ID           │ PK, FK
│ Phone            │ PK
│ Phone_Type       │
└──────────────────┘

This design allows an employee to have zero, one, or multiple phone numbers.

---

Mapping Weak Entities

A weak entity depends on a strong (owner) entity. It has a partial key — a discriminator that is unique only within the context of the owner.

Rule

The weak entity becomes a table. Its primary key is a composite of the owner’s primary key plus the partial key.

Example

ER Diagram:

Employee ── 1:N ── Dependent (weak)

Mapped Tables:

Employees
┌──────────────┐
│ Emp_ID       │ PK
│ Name         │
└──────────────┘

Dependents
┌──────────────────┐
│ Emp_ID           │ PK, FK
│ Dependent_Name   │ PK (partial key)
│ Age              │
│ Relationship     │
└──────────────────┘

Dependent_Name alone is not unique (two employees may both have a dependent named “John”). But {Emp_ID, Dependent_Name} is unique.

---

Mapping Ternary Relationships

A ternary relationship involves three entities simultaneously.

Rule

Create a junction table whose primary key is the combination of the primary keys of all three participating entities.

Example

ER Diagram:

Supplier ── M ──|             |── N ── Product
                  ── Supplies ──
Part ─────── P ──|             |

Interpretation:
- A supplier supplies a specific part for a specific product.

Mapped Tables:

Suppliers
┌──────────────┐
│ Supplier_ID  │ PK
│ Name         │
└──────────────┘

Products
┌──────────────┐
│ Product_ID   │ PK
│ Name         │
└──────────────┘

Parts
┌──────────────┐
│ Part_ID      │ PK
│ Description  │
└──────────────┘

Supplies (Ternary Junction)
┌──────────────────┐
│ Supplier_ID      │ PK, FK
│ Product_ID       │ PK, FK
│ Part_ID          │ PK, FK
│ Quantity         │
└──────────────────┘

The composite PK {Supplier_ID, Product_ID, Part_ID} ensures no duplicate supply records.

---

Mapping Generalization and Specialization

Generalization is the process of extracting common attributes into a superclass entity. Specialization is the reverse — creating subclasses with specific attributes.

Strategy 1: Single Table (All in One)

One table with all attributes. Subclass-specific columns are nullable.

Pros	Cons
Simple, no joins	Many NULLs, wastes space

Example

CREATE TABLE Employees (
    Emp_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Salary DECIMAL(10,2),
    Hourly_Rate DECIMAL(10,2),  -- NULL for salaried
    Contract_End_Date DATE       -- NULL for permanent
);

Use when: Subclasses have few specific attributes.

---

Strategy 2: Separate Tables for Each Subclass

Each subclass becomes its own table with its own attributes and a foreign key to the superclass.

Pros	Cons
No NULLs, clean schema	Requires joins to get complete data

Example

CREATE TABLE Employees (
    Emp_ID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Salaried_Employees (
    Emp_ID INT PRIMARY KEY,
    Salary DECIMAL(10,2),
    FOREIGN KEY (Emp_ID) REFERENCES Employees(Emp_ID)
);

CREATE TABLE Hourly_Employees (
    Emp_ID INT PRIMARY KEY,
    Hourly_Rate DECIMAL(10,2),
    Contract_End_Date DATE,
    FOREIGN KEY (Emp_ID) REFERENCES Employees(Emp_ID)
);

Use when: Subclasses have many distinct attributes.

---

Strategy 3: Total/Disjoint Specialization

When the specialization is total (every superclass entity must belong to a subclass) and disjoint (an entity belongs to at most one subclass), you can directly store subclass-specific attributes in the subclass tables without a superclass table.

Example

CREATE TABLE Salaried_Employees (
    Emp_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Salary DECIMAL(10,2)
);

CREATE TABLE Hourly_Employees (
    Emp_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Hourly_Rate DECIMAL(10,2)
);

Use when: The specialization is total, disjoint, and subclasses have very different attributes.

---

Mapping Aggregation

Aggregation treats a relationship itself as an entity so it can participate in another relationship.

Rule

Map the relationship and its entities normally. Then treat the junction table of that relationship as an entity when linking to another entity.

Example

ER Diagram:

(Employee ── M:N ── Project) ── M:N ── Manager

The Works_On relationship (Employee-Project) is aggregated and then linked to Manager.

Mapped Tables:

CREATE TABLE Employees (...);
CREATE TABLE Projects (...);
CREATE TABLE Works_On (
    Emp_ID INT,
    Project_ID INT,
    Hours INT,
    PRIMARY KEY (Emp_ID, Project_ID)
);
CREATE TABLE Manages_Aggregated (
    Manager_ID INT,
    Emp_ID INT,
    Project_ID INT,
    FOREIGN KEY (Manager_ID) REFERENCES Employees(Emp_ID),
    FOREIGN KEY (Emp_ID, Project_ID) REFERENCES Works_On(Emp_ID, Project_ID)
);

---

Mapping Recursive Relationships

A recursive relationship is when an entity relates to itself.

Example: Employee-Manager

An employee reports to another employee (the manager).

CREATE TABLE Employees (
    Emp_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Manager_ID INT,
    FOREIGN KEY (Manager_ID) REFERENCES Employees(Emp_ID)
);

Manager_ID is a foreign key referencing the same table.

---

Example: M:N Recursive (Course Prerequisites)

A course may have multiple prerequisites, and a course may be a prerequisite for many others.

CREATE TABLE Courses (
    Course_ID INT PRIMARY KEY,
    Title VARCHAR(100)
);

CREATE TABLE Prerequisites (
    Course_ID INT,
    Prerequisite_ID INT,
    PRIMARY KEY (Course_ID, Prerequisite_ID),
    FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID),
    FOREIGN KEY (Prerequisite_ID) REFERENCES Courses(Course_ID)
);

---

Summary of Mapping Rules

ER Construct	Mapping Strategy
Strong Entity	One table, attribute → column, key → PK
Weak Entity	One table, composite PK (owner PK + partial key)
Simple Attribute	Column in the entity’s table
Composite Attribute	Multiple columns (one per component)
Multi-valued Attribute	Separate table (entity PK + value as composite PK)
Derived Attribute	Not stored (computed when needed)
Binary 1:1	FK on either side (prefer the side with total participation)
Binary 1:N	FK on the N-side
Binary M:N	Junction table (both PKs as composite PK)
Ternary or n-ary	Junction table (all PKs as composite PK)
Generalization	Single table (nullable) or separate tables per subclass
Recursive	FK referencing the same table

---

Interview Deep Dive

---

Key Takeaways

• M:N relationships need a junction table.
• Multi-valued attributes become separate tables.
• Weak entities use composite primary keys including the owner’s PK.
• Ternary relationships use a junction table with three foreign keys.
• Generalization can use single-table or separate-table strategies.
• Aggregation treats a relationship as an entity for further relationships.
• Recursive relationships use self-referencing foreign keys.
• Each mapping strategy has tradeoffs between simplicity, storage, and query complexity.