Chapter 12 of 41

Converting ER Diagrams to Tables

On this page

An ER diagram is a drawing. A database stores tables. Sooner or later every design has to be converted from the diagram into a set of table definitions (relation schemas) that you can actually create with SQL. This step is called ER-to-relational mapping, and it follows a small set of rules.

The good news: the rules are mechanical. Once you know how each ER construct (strong entity, attribute type, weak entity, 1:1, 1:N, M:N, recursive and n-ary relationships, specialization, aggregation) becomes tables, you can convert any diagram. GATE and university exams also like to ask "what is the minimum number of tables needed?" — we'll cover that too.

This chapter uses the ER ideas from ER Model, Types of Attributes, Relationships, Cardinality & Participation, Weak Entities and Generalization, Specialization & Aggregation. We'll use a few words that the next chapters define properly (primary key, foreign key); for now, a primary key is the column(s) that uniquely identify a row, and a foreign key is a column that must match a primary key value in another table.


The Running Example: a University

Here is the ER design we will convert. Read it carefully — every rule below refers back to it.

Entities
  DEPARTMENT (dept_id, dept_name)
  PROFESSOR  (prof_id, name)
  STUDENT    (roll_no, name, dob, address{street, city, pincode}, {phone}, age*)
                composite: address    multivalued: phone    derived (*): age
  COURSE     (course_id, title, credits)
  DEPENDENT  (dep_name, relation)          weak entity, owner = PROFESSOR,
                                           partial key = dep_name

Relationships
  Works_in      PROFESSOR  N : 1  DEPARTMENT   professor side total
  Heads         PROFESSOR  1 : 1  DEPARTMENT   department side total
  Offers        DEPARTMENT 1 : N  COURSE       course side total
  Enrolls       STUDENT    M : N  COURSE       attribute: grade
  Prerequisite  COURSE     M : N  COURSE       recursive (course, prereq)
  Has_dependent PROFESSOR  1 : N  DEPENDENT    identifying relationship

Every CREATE TABLE shown below was run on MySQL 8.4 in a practice database named dbms10u_db (you'll see that name inside some error messages), and every output is real. In a real script, create tables in dependency order: department, then professor, then the tables that reference them.


Rule 1: Strong Entity → Table

Each strong entity becomes a table. Its simple attributes become columns, and its key attribute becomes the primary key.

CREATE TABLE department (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);

Each row of the table is one entity; the table as a whole is the entity set.


Rule 2: Composite, Multivalued and Derived Attributes

Composite attribute → flatten it

A relational column must hold a single, atomic value (we'll see why in Relational Model). So a composite attribute like address{street, city, pincode} is replaced by its simple components. The composite name address itself disappears.

Multivalued attribute → separate table

A student can have several phone numbers. We can't store a list in one column, and we can't add columns phone1, phone2, phone3 (how many is enough? and searching becomes painful). So a multivalued attribute gets its own table containing:

  • the key of the owning entity (as a foreign key), and

  • the attribute value,

with the combination of the two as the primary key (the same student cannot list the same phone twice).

Derived attribute → don't store it

age can be computed from dob. Storing it would mean the stored value goes stale every birthday. Leave it out and compute it in queries (or a view).

CREATE TABLE student (
  roll_no INT PRIMARY KEY,
  name    VARCHAR(50) NOT NULL,
  dob     DATE,
  street  VARCHAR(100),      -- address{street, city, pincode} flattened
  city    VARCHAR(50),
  pincode CHAR(6)
);                           -- age (derived) is NOT stored

CREATE TABLE student_phone (
  roll_no INT,
  phone   VARCHAR(15),
  PRIMARY KEY (roll_no, phone),
  FOREIGN KEY (roll_no) REFERENCES student(roll_no) ON DELETE CASCADE
);

After inserting two students (Asha has two phones, Bilal one):

SELECT s.roll_no, s.name, p.phone
FROM student s JOIN student_phone p ON s.roll_no = p.roll_no;
+---------+-------+------------+
| roll_no | name  | phone      |
+---------+-------+------------+
|       1 | Asha  | 9876500001 |
|       1 | Asha  | 9876500002 |
|       2 | Bilal | 9123400003 |
+---------+-------+------------+

And the derived attribute, computed on demand (here as of 2 November 2026; in practice you'd use CURDATE()):

SELECT roll_no, name, TIMESTAMPDIFF(YEAR, dob, '2026-11-02') AS age FROM student;
+---------+-------+------+
| roll_no | name  | age  |
+---------+-------+------+
|       1 | Asha  |   22 |
|       2 | Bilal |   22 |
+---------+-------+------+

Rule 3: Weak Entity → Table with the Owner's Key

A weak entity has no key of its own; it is identified by its owner's key plus its partial key (see Weak Entities). So its table contains:

  • the owner's primary key as a foreign key,

  • its own attributes,

  • a primary key = owner's key + partial key.

The identifying relationship (Has_dependent) does not get a separate table — it is already captured by the foreign key. And because a weak entity cannot exist without its owner, ON DELETE CASCADE is the natural choice.

CREATE TABLE dependent (
  prof_id  INT,
  dep_name VARCHAR(50),
  relation VARCHAR(20),
  PRIMARY KEY (prof_id, dep_name),
  FOREIGN KEY (prof_id) REFERENCES professor(prof_id) ON DELETE CASCADE
);

Two professors can each have a dependent called "Kiran" — that is exactly why dep_name alone can't be the key:

+---------+----------+----------+
| prof_id | dep_name | relation |
+---------+----------+----------+
|     101 | Kiran    | Son      |
|     101 | Lata     | Spouse   |
|     102 | Kiran    | Daughter |
+---------+----------+----------+

Deleting professor 102 removes their dependents automatically:

DELETE FROM professor WHERE prof_id = 102;
SELECT * FROM dependent;
+---------+----------+----------+
| prof_id | dep_name | relation |
+---------+----------+----------+
|     101 | Kiran    | Son      |
|     101 | Lata     | Spouse   |
+---------+----------+----------+

Rule 4: 1:N Relationship → Foreign Key on the "N" Side

In Offers, one department offers many courses, but each course belongs to one department. So put the department's key into the course table.

Why the N side? Because each course row needs to store exactly one department — a single value fits in a single column. If you tried the opposite (store courses inside the department row), one department row would need many course values, which is a multivalued attribute again.

  • If the N side has total participation (every course must be offered by some department), make the foreign key NOT NULL.

  • If the relationship has attributes, they go into the same N-side table.

CREATE TABLE course (
  course_id VARCHAR(10) PRIMARY KEY,
  title     VARCHAR(50) NOT NULL,
  credits   INT,
  dept_id   INT NOT NULL,                     -- Offers (total on course side)
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

CREATE TABLE professor (
  prof_id INT PRIMARY KEY,
  name    VARCHAR(50) NOT NULL,
  dept_id INT NOT NULL,                       -- Works_in (N:1, total)
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

No separate table is needed for a 1:N relationship. (You may create one, but it is never required.)


Rule 5: 1:1 Relationship → Foreign Key on Either Side (prefer the total side)

For a 1:1 relationship you can put the foreign key in either table, but add a UNIQUE constraint — otherwise it silently becomes 1:N.

Which side? Prefer the side with total participation. Every department has a head, but most professors are not heads. If we stored heads_dept in professor, the column would be NULL for nearly every row. Storing head_id in department means the column is always filled.

ALTER TABLE department
  ADD COLUMN head_id INT UNIQUE,              -- UNIQUE keeps it 1:1
  ADD FOREIGN KEY (head_id) REFERENCES professor(prof_id);

A practical catch: circular foreign keys

The ER design says the department side is total, so ideally head_id would be NOT NULL. But look at what happens: a professor needs an existing department (professor.dept_id is NOT NULL), and a department would need an existing professor. Neither row can be inserted first:

INSERT INTO professor VALUES (101, 'Dr. Rao', 1);   -- department 1 doesn't exist yet
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbms10u_db`.`professor`, CONSTRAINT `professor_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`))

The SQL standard solves this with deferrable constraints (checked at commit time), which PostgreSQL and Oracle support but MySQL does not. So in MySQL we leave head_id nullable and insert in three steps:

INSERT INTO department (dept_id, dept_name) VALUES (1, 'Computer Science');
INSERT INTO professor VALUES (101, 'Dr. Rao', 1), (102, 'Dr. Fernandes', 1);
UPDATE department SET head_id = 101 WHERE dept_id = 1;
+---------+------------------+---------+
| dept_id | dept_name        | head_id |
+---------+------------------+---------+
|       1 | Computer Science |     101 |
+---------+------------------+---------+

Now Dr. Rao heads Computer Science. The UNIQUE on head_id really enforces "a professor heads at most one department" — he cannot also be made head of a new Mathematics department:

INSERT INTO department VALUES (2, 'Mathematics', 101);
ERROR 1062 (23000): Duplicate entry '101' for key 'department.head_id'

This is a good example of a design rule that the diagram states but the table definition can't fully enforce.

Special case: total on both sides

If both sides of a 1:1 relationship are total (every A has exactly one B and every B has exactly one A), the two entities are always paired, so they can be merged into a single table. This matters for minimum-table questions.


Rule 6: M:N Relationship → Separate Table

A student takes many courses and a course has many students. Neither side can hold a single foreign key, so the relationship gets its own table (often called a junction or bridge table) containing:

  • the primary keys of both entities, each as a foreign key,

  • the relationship's own attributes (grade),

  • a primary key made of both foreign keys together.

CREATE TABLE enrolls (
  roll_no   INT,
  course_id VARCHAR(10),
  grade     CHAR(2),
  PRIMARY KEY (roll_no, course_id),
  FOREIGN KEY (roll_no)   REFERENCES student(roll_no),
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Why is grade here and not in student or course? Because a grade belongs to a pair: Asha's grade in DBMS. It is a property of the relationship, not of either entity.


Rule 7: Recursive (Unary) Relationship

A recursive relationship connects an entity set to itself, so the key appears twice with different role names.

  • 1:N recursive (e.g. employee manages employees): add a self-referencing foreign key column in the same table. The SQL tutorial's sample employees table does exactly this with manager_id REFERENCES employees(emp_id).

  • M:N recursive (a course can have many prerequisites and be a prerequisite of many): create a separate table with two foreign keys to the same table.

CREATE TABLE prerequisite (
  course_id VARCHAR(10),
  prereq_id VARCHAR(10),
  PRIMARY KEY (course_id, prereq_id),
  FOREIGN KEY (course_id) REFERENCES course(course_id),
  FOREIGN KEY (prereq_id) REFERENCES course(course_id)
);
SELECT c.course_id, c.title, p.prereq_id, pc.title AS prereq_title
FROM prerequisite p
JOIN course c  ON c.course_id  = p.course_id
JOIN course pc ON pc.course_id = p.prereq_id;
+-----------+---------------------+-----------+------------------+
| course_id | title               | prereq_id | prereq_title     |
+-----------+---------------------+-----------+------------------+
| CS201     | DBMS                | CS101     | Programming in C |
| CS301     | Distributed Systems | CS201     | DBMS             |
+-----------+---------------------+-----------+------------------+

Rule 8: N-ary (Ternary) Relationship → Separate Table

A relationship among three or more entities always gets its own table, with a foreign key to each participating entity.

Example: Supplies(SUPPLIER, PART, PROJECT) — "supplier S supplies part P to project J".

supplies(supplier_id, part_id, project_id, quantity)

The primary key is usually all three foreign keys. If the ER diagram has a "1" on one side (say, for a given part and project there is only one supplier), that entity's key can be dropped from the primary key: PRIMARY KEY (part_id, project_id).


Rule 9: Specialization / Generalization

Take EMPLOYEE (emp_id, name, salary) with subclasses ENGINEER (skill) and MANAGER (team_size), where the specialization is overlapping and partial (one employee can be both; some are neither). There are three standard ways to map it.

Method A: one table for the superclass + one table per subclass

Each subclass table holds the superclass key (as both primary key and foreign key) plus its local attributes.

CREATE TABLE employee (emp_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, salary DECIMAL(10,2));
CREATE TABLE engineer (emp_id INT PRIMARY KEY, skill VARCHAR(30),
  FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE);
CREATE TABLE manager  (emp_id INT PRIMARY KEY, team_size INT,
  FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE);

Divya is an engineer, Farhan is both, Gopal is neither. Rebuilding the full picture needs joins:

SELECT e.emp_id, e.name, e.salary, g.skill, m.team_size
FROM employee e
LEFT JOIN engineer g ON e.emp_id = g.emp_id
LEFT JOIN manager  m ON e.emp_id = m.emp_id;
+--------+--------+-----------+---------+-----------+
| emp_id | name   | salary    | skill   | team_size |
+--------+--------+-----------+---------+-----------+
|      1 | Divya  |  90000.00 | Backend |      NULL |
|      2 | Farhan | 120000.00 | ML      |         8 |
|      3 | Gopal  |  40000.00 | NULL    |      NULL |
+--------+--------+-----------+---------+-----------+

This works for every combination of constraints (disjoint/overlapping, total/partial), which is why it's the default answer.

Method B: one table per subclass only (no superclass table)

engineer(emp_id, name, salary, skill)
manager (emp_id, name, salary, team_size)

Use this only when the specialization is total (otherwise Gopal, who is in no subclass, has nowhere to live) and preferably disjoint (otherwise Farhan's name and salary are stored twice and can go out of sync).

Method C: one single table with a type flag

CREATE TABLE employee_single (
  emp_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, salary DECIMAL(10,2),
  is_engineer BOOLEAN NOT NULL DEFAULT FALSE, skill VARCHAR(30),
  is_manager  BOOLEAN NOT NULL DEFAULT FALSE, team_size INT
);
+--------+--------+-----------+-------------+---------+------------+-----------+
| emp_id | name   | salary    | is_engineer | skill   | is_manager | team_size |
+--------+--------+-----------+-------------+---------+------------+-----------+
|      1 | Divya  |  90000.00 |           1 | Backend |          0 |      NULL |
|      2 | Farhan | 120000.00 |           1 | ML      |          1 |         8 |
|      3 | Gopal  |  40000.00 |           0 | NULL    |          0 |      NULL |
+--------+--------+-----------+-------------+---------+------------+-----------+

(MySQL's BOOLEAN is a synonym for TINYINT(1), so it shows 1/0.) For a disjoint specialization, a single emp_type column replaces the flags. No joins are needed, but there are many NULLs, and you can't declare "skill is required for engineers" with a simple NOT NULL.

Method

Tables

Works when

Pros

Cons

A: superclass + subclasses

1 + k

Always

No redundancy, few NULLs

Joins to see a full entity

B: subclasses only

k

Total (and ideally disjoint)

No join for subclass queries

Queries over "all employees" need UNION; overlap causes duplication

C: single table

1

Always (best for few subclass attributes)

Simplest, no joins

Many NULLs; weaker constraints


Rule 10: Aggregation

Recall the example from the previous chapter: STUDENT Works_on PROJECT, and each (student, project) assignment is Monitored by one PROFESSOR.

  1. Map Works_on as usual (M:N → its own table).

  2. The aggregated relationship's key is the key of the Works_on table: (roll_no, project_id).

  3. Map Monitors like any relationship, with a foreign key that points to works_on (not separately to student and project).

CREATE TABLE works_on (
  roll_no INT, project_id INT, hours INT,
  PRIMARY KEY (roll_no, project_id),
  FOREIGN KEY (roll_no)    REFERENCES student(roll_no),
  FOREIGN KEY (project_id) REFERENCES project(project_id)
);

CREATE TABLE monitors (                 -- one monitor per assignment
  roll_no INT, project_id INT,
  prof_id INT NOT NULL,
  PRIMARY KEY (roll_no, project_id),
  FOREIGN KEY (roll_no, project_id) REFERENCES works_on(roll_no, project_id),
  FOREIGN KEY (prof_id) REFERENCES professor(prof_id)
);

Since each assignment has exactly one monitor, monitors could even be merged into works_on as a prof_id column (the "1:N → foreign key" idea again). Keeping it separate is required if monitoring is many-to-many.

Now the database itself refuses to record monitoring for an assignment that doesn't exist. Student 2 does not work on project 7:

INSERT INTO monitors VALUES (2, 7, 101);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbms10u_db`.`monitors`, CONSTRAINT `monitors_ibfk_1` FOREIGN KEY (`roll_no`, `project_id`) REFERENCES `works_on` (`roll_no`, `project_id`))

That is exactly the constraint a plain ternary relationship would have lost.


All the Rules in One Table

ER construct

Becomes

Strong entity

Table; key attribute → primary key

Composite attribute

Its simple components as columns

Multivalued attribute

New table (owner key, value); PK = both

Derived attribute

Not stored (compute in query/view)

Weak entity

Table with owner's key as FK; PK = owner key + partial key; identifying relationship needs no table

1:1 relationship

FK (+ UNIQUE) on one side, preferably the total side; merge into one table if total on both sides

1:N relationship

FK on the N side (NOT NULL if total); relationship attributes go there too

M:N relationship

New table with both keys; PK = both

Recursive relationship

1:N → self-referencing FK; M:N → new table with two FKs to the same table

N-ary relationship

New table with FKs to all participants

Specialization

Superclass + subclass tables (always works), subclass-only, or single table

Aggregation

Relationship on the aggregate references the aggregated relationship's table


Minimum Number of Tables

Exams often give an ER diagram and ask for the minimum number of tables. "Minimum" means: merge every relationship into an entity table whenever the rules allow it. For two strong entities E1 and E2 joined by a binary relationship R (no multivalued attributes):

Cardinality of R

Participation

Minimum tables

Why

M:N

any

3

R must have its own table

1:N

any

2

R merges into the N side

1:1

partial on both sides, or total on one side

2

R merges into one side (the total side, if any)

1:1

total on both sides

1

E1, R and E2 merge into one table

Then add +1 for each multivalued attribute. A weak entity needs one table of its own; its identifying relationship needs none.

Worked example

Count the tables for our university design:

Construct

Table?

Running count

DEPARTMENT, PROFESSOR, STUDENT, COURSE (strong entities)

4 tables

4

phone (multivalued)

student_phone

5

DEPENDENT (weak) + Has_dependent

dependent only

6

Works_in (N:1), Offers (1:N), Heads (1:1)

merged as FKs

6

Enrolls (M:N)

enrolls

7

Prerequisite (recursive M:N)

prerequisite

8

Minimum = 8 tables.

Practice question

Entities E1(a1, a2, {a3}), E2(b1, b2), E3(c1, c2) with keys a1, b1, c1; {a3} is multivalued. R1 is 1:N from E1 to E2, R2 is M:N between E2 and E3. Minimum tables?

Show answer

5: E1, E1_a3 (for the multivalued attribute), E2 (holding a1 as FK for R1), E3, and R2(b1, c1).


Common Mistakes

  • Putting the 1:N foreign key on the "1" side. It always goes on the N side.

  • Forgetting UNIQUE on a 1:1 foreign key. Without it, the table allows 1:N.

  • Making only one column the key of an M:N table. PRIMARY KEY (roll_no) on enrolls would allow each student only one course.

  • Using just the partial key for a weak entity. dep_name alone is not unique; the owner's key is part of the primary key.

  • Storing derived attributes (age, total, count). They go stale.

  • Creating a table for an identifying relationship. The weak entity's foreign key already represents it.

  • Choosing "subclass tables only" for a partial specialization. Entities in no subclass are lost.


Interview and Exam Questions

How is an M:N relationship represented in tables?

As a separate table containing the primary keys of both entities as foreign keys (plus any relationship attributes). Its primary key is the combination of the two foreign keys.

Where does the foreign key go for a 1:N relationship and why?

On the N side, because each row on the N side relates to exactly one row on the 1 side, so a single column suffices. On the 1 side you would need to store many values in one row.

How is a multivalued attribute mapped?

To a separate table with the owner entity's key and the attribute value; the primary key is the combination of both.

What is the primary key of a weak entity's table?

The primary key of the owner (identifying) entity plus the weak entity's partial key (discriminator).

When can two entities in a 1:1 relationship be merged into one table?

When both sides have total participation, so every row of one always pairs with exactly one row of the other.

Give three ways to map a specialization and when each is suitable.

(1) Superclass table + one table per subclass keyed by the superclass key — works always. (2) Only subclass tables with inherited attributes — only for total (ideally disjoint) specialization. (3) Single table with a type column/flags — simple, no joins, but many NULLs.


Summary

  • Strong entities become tables; composite attributes are flattened, multivalued attributes get their own table, derived attributes are not stored.

  • Weak entities take the owner's key into their primary key, usually with ON DELETE CASCADE.

  • 1:N → foreign key on the N side; 1:1 → foreign key + UNIQUE on one side (preferably total); M:N and n-ary → their own table.

  • Specialization has three mappings; the superclass-plus-subclasses method always works.

  • Aggregation maps to a table that references the aggregated relationship's table.

  • For minimum tables: M:N = 3, 1:N = 2, 1:1 = 2 (or 1 if total on both sides), plus one per multivalued attribute.

Now that we have tables, the next chapter, Relational Model, looks at what a "table" means formally: relations, tuples, domains and their properties.