13 - ER-to-Relational Mapping

🎯 Learning Objectives

• Convert an ER diagram into a set of relational tables
• Determine the minimum number of tables needed
• Handle weak entities, multi-valued attributes, and specialization
• Map relationships of various cardinalities correctly

📋 Prerequisites

• 12 - ER Model — Entity sets, attributes, relationships, cardinality
• 04 - SQL DDL — CREATE TABLE, PRIMARY KEY, FOREIGN KEY

📖 Core Content

13.1 Intuition: From Blueprint to Building

• Entity set → Table
• Attribute → Column
• Relationship → Foreign key or junction table
• Key → Primary key constraint

Why This Matters: ER-to-relational mapping is a core skill tested heavily in exams (~3% of all questions). After this, you can go from problem description → ER diagram → SQL tables.

13.2 Mapping Steps (Overview)

13.3 Step 1: Strong Entity Sets

sql
CREATE TABLE instructor (
    ID        VARCHAR(5) PRIMARY KEY,
    name      VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    salary    NUMERIC(8,2)
);

13.4 Step 2: Weak Entity Sets

• All attributes of the weak entity
• The primary key of the identifying strong entity set (as a foreign key) Primary key = discriminator + identifying strong entity's primary key. ER: Payment(payment_number, amount) depends on Loan(loan_number) Relational:

sql
CREATE TABLE payment (
    payment_number NUMERIC(3,0),
    loan_number    VARCHAR(5),
    amount         NUMERIC(10,2),
    PRIMARY KEY (payment_number, loan_number),
    FOREIGN KEY (loan_number) REFERENCES loan(loan_number)
);

13.5 Step 3: 1:1 Relationships

Approach A: Merge into one table (preferred if both are total participation)

sql
CREATE TABLE team_captain (
    team_code   VARCHAR(5) PRIMARY KEY,
    team_name   VARCHAR(20),
    player_num  NUMERIC(3,0),
    player_name VARCHAR(20)
);

Approach B: Foreign key in either table (choose one side)

sql
-- Option 1: FK in student table (most common)
CREATE TABLE student (
    ID       VARCHAR(5) PRIMARY KEY,
    name     VARCHAR(20),
    advisor_ID VARCHAR(5),  -- FK to instructor
    FOREIGN KEY (advisor_ID) REFERENCES instructor(ID)
);
-- Option 2: FK in instructor table
CREATE TABLE instructor (
    ID         VARCHAR(5) PRIMARY KEY,
    name       VARCHAR(20),
    advisee_ID VARCHAR(5),  -- FK to student
    FOREIGN KEY (advisee_ID) REFERENCES student(ID)
);

13.6 Step 4: 1:N (or N:1) Relationships

sql
CREATE TABLE instructor (
    ID        VARCHAR(5) PRIMARY KEY,
    name      VARCHAR(20),
    dept_name VARCHAR(20),
    salary    NUMERIC(8,2),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);

Rule: Always put the FK on the "many" side table.

13.7 Step 5: M:N Relationships

• Primary keys of both entity sets (as foreign keys)
• Relationship attributes (if any) ER: Student M — N Course with attribute grade

sql
CREATE TABLE takes (
    ID        VARCHAR(5),
    course_id VARCHAR(8),
    semester  VARCHAR(6),
    year      NUMERIC(4,0),
    grade     VARCHAR(2),
    PRIMARY KEY (ID, course_id, semester, year),
    FOREIGN KEY (ID) REFERENCES student(ID),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

13.8 Step 6: Multi-valued Attributes

sql
CREATE TABLE student_phone (
    ID           VARCHAR(5),
    phone_number VARCHAR(15),
    PRIMARY KEY (ID, phone_number),
    FOREIGN KEY (ID) REFERENCES student(ID)
);

13.9 Step 7: Specialization/Generalization

Approach A: One table per entity (with parent's key)

sql
CREATE TABLE employee (
    ID        VARCHAR(5) PRIMARY KEY,
    name      VARCHAR(20),
    salary    NUMERIC(8,2)
);
CREATE TABLE secretary (
    ID         VARCHAR(5) PRIMARY KEY,
    typing_speed NUMERIC(4,0),
    FOREIGN KEY (ID) REFERENCES employee(ID)
);
CREATE TABLE engineer (
    ID        VARCHAR(5) PRIMARY KEY,
    degree    VARCHAR(20),
    FOREIGN KEY (ID) REFERENCES employee(ID)
);

Approach B: Single table (for disjoint specialization)

sql
CREATE TABLE employee (
    ID           VARCHAR(5) PRIMARY KEY,
    name         VARCHAR(20),
    salary       NUMERIC(8,2),
    employee_type VARCHAR(10) CHECK (type IN ('Secretary', 'Engineer')),
    typing_speed NUMERIC(4,0),  -- NULL for engineers
    degree       VARCHAR(20)    -- NULL for secretaries
);

13.10 Worked Examples

Example 1: Complete Mapping

• Entities: instructor(ID, name), department(dept_name, building), course(course_id, title)
• Relationships: belongs_to (instructor → department, N:1), teaches (instructor → course, 1:N) Mapping:

sql
-- Strong entities
CREATE TABLE department (
    dept_name VARCHAR(20) PRIMARY KEY,
    building  VARCHAR(15)
);
CREATE TABLE instructor (
    ID        VARCHAR(5) PRIMARY KEY,
    name      VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
CREATE TABLE course (
    course_id VARCHAR(8) PRIMARY KEY,
    title     VARCHAR(50),
    instructor_id VARCHAR(5),  -- FK for 1:N relationship
    FOREIGN KEY (instructor_id) REFERENCES instructor(ID)
);

Example 2: Minimum Tables

sql
CREATE TABLE team_captain (
    team_code   VARCHAR(5) PRIMARY KEY,
    team_name   VARCHAR(20),
    player_num  NUMERIC(3,0),
    player_name VARCHAR(20)
);

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Mapping M:N as 1:N

Pitfall 2: Wrong Direction for FK in 1:N

Pitfall 3: Forgetting the Weak Entity's Full Key

📝 Practice Questions

Q1. How do you map a 1:N relationship to tables?

Answer

Add the primary key of the "1" side table as a foreign key in the "N" side table.

Example: If Department (1) has many Instructors (N), add dept_name as a FK in the instructor table. Each instructor stores which department they belong to.

Q2. When do you need a separate junction table?

Answer

A separate junction table is needed for:

1. M:N relationships — Student m:n Course → junction table takes
2. Multi-valued attributes — Person has many phones → junction table person_phone
3. Relationships with their own attributes — takes has grade

Q3. Map the following ER diagram to tables:

• Entity: Employee(emp_id, name)
• Entity: Project(proj_id, title)
• Relationship: works_on (M:N with hours attribute)

Answer

sql
CREATE TABLE employee (
    emp_id INTEGER PRIMARY KEY,
    name   VARCHAR(50)
);

CREATE TABLE project (
    proj_id INTEGER PRIMARY KEY,
    title   VARCHAR(50)
);

CREATE TABLE works_on (
    emp_id   INTEGER,
    proj_id  INTEGER,
    hours    NUMERIC(4,1),
    PRIMARY KEY (emp_id, proj_id),
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id),
    FOREIGN KEY (proj_id) REFERENCES project(proj_id)
);

Q4. What is the minimum number of tables needed for two entities with a 1:1 relationship (partial on both sides)?

Answer

2 tables (minimum).

Each entity gets its own table. Since participation is partial (not everyone participates), the relationship is represented by adding a foreign key in one of the tables (whichever you choose).

Example:

sql
CREATE TABLE person (id INTEGER PRIMARY KEY, name VARCHAR(50));
CREATE TABLE passport (num VARCHAR(20) PRIMARY KEY, person_id INTEGER UNIQUE, FOREIGN KEY (person_id) REFERENCES person(id));

Q5. How do you map a weak entity set?

Answer

Create a table with:

1. All attributes of the weak entity
2. The primary key of the identifying strong entity (as FK)
3. Primary key = discriminator + strong entity's primary key

Example — Weak entity Payment depends on strong entity Loan:

sql
CREATE TABLE payment (
    payment_no  INTEGER,
    loan_no     VARCHAR(5),
    amount      NUMERIC(10,2),
    date        DATE,
    PRIMARY KEY (payment_no, loan_no),
    FOREIGN KEY (loan_no) REFERENCES loan(loan_no)
);

Q6. Compare the two approaches for mapping specialization.

Answer

Example: Employee with subtypes Secretary and Engineer:

• Separate: employee(ID, name), secretary(ID, typing_speed), engineer(ID, degree)
• Single: employee(ID, name, type, typing_speed, degree)

Q7. Given an ER diagram with entities A, B and a ternary relationship R, how many tables are needed?

Answer

Minimum: 3 tables (one for each entity).

If the ternary relationship has no attributes and appropriate cardinalities, the FKs can be placed in existing entity tables. But typically, a ternary relationship needs its own table (a junction table with three foreign keys) for proper normalization.

The general answer depends on the cardinalities, but at minimum, each entity set = 1 table.

Q8. Create tables for: A Person has multiple addresses. Each address has street, city, state.

Answer

sql
CREATE TABLE person (
    person_id INTEGER PRIMARY KEY,
    name      VARCHAR(50)
);

-- Multi-valued attribute → separate table
CREATE TABLE person_address (
    person_id INTEGER,
    street    VARCHAR(100),
    city      VARCHAR(50),
    state     VARCHAR(20),
    PRIMARY KEY (person_id, street, city, state),
    FOREIGN KEY (person_id) REFERENCES person(person_id)
);

🔗 Cross-References

• Next Topic: 14 - Functional Dependencies
• Previous Topic: 12 - ER Model
• Related: BSCS2003 (MAD 1) — Creating database schemas from requirements
• Related: 16 - Normalization (ensuring the mapped schema is well-designed)
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 7 (ER Model), Chapter 8 (Relational Database Design) Join Discord Previous12 - ER ModelNext14 - Functional Dependencies