1.3Converting an ER diagram to tables
The idea
To turn an ER diagram into tables apply a short rule set: M:N needs its own table, 1:N puts a foreign key on the N side, 1:1 puts it on either side.
Think of it like this: Moving house: a many-to-many friendship needs a separate address book; a one-to-many just needs a label on each box.
Why it exists
It is the most common ER question: "what is the minimum number of tables?"
How it works
- Start with one table per strong entity set.
- M:N relationship: add a table with both keys (together they are its key).
- 1:N: add the 1-side key as a foreign key in the N-side table, no new table.
- 1:1: foreign key on either side (better on the total side); if both sides are total merge into one table.
- Add one table for each multivalued attribute and include the owner key in each weak entity table.
Worked example: Two entities with a 1:1 relationship and both sides total: how many tables?
One. Both sets always occur together, so merge them into a single table.
Now play with it
- →Change the cardinality below and watch the table count and the foreign key location change.
- →Make a 1:1 relationship total on both sides.
Put the foreign key a in B (the N side). No extra table is needed. The N side is total, so the foreign key is NOT NULL.
| Case | Tables | Foreign key goes |
|---|---|---|
| M:N | 3 | in the relationship table (both keys form its key) |
| 1:N | 2 | in the N side |
| 1:1, one side total | 2 | in the total side |
| 1:1, both total | 1 (merge) | not needed |
| Multivalued attribute | +1 per attribute | in the new table |
| n-ary relationship | +1 | all participating keys |
Minimum tables = entities + (number of M:N relationships) − (merged 1:1 total relationships).
Check yourself
Entities A and B are in an M:N relationship. The minimum number of tables is
Exam notes (the dense version)
Each strong entity gets a table. M:N relationship → its own table whose key is both keys. 1:N → put the foreign key on the N side, no new table. 1:1 → foreign key on either side (best on the total side); if both sides are total you can merge into one table. A multivalued attribute needs its own table. A weak entity table includes the owner key.
Previous year questions: ER Model, Relational Model & Keys (9)
PYQs are grouped by topic, so this is the full set for ER Model, Relational Model & Keys; try the ones that match this subtopic. Open all 9 on the topic page.