Converting an ER Diagram to Tables

The Relational Model · 30 min

Core CS · DBMS

Five entity types, four relationships, seven tables

Turning an ER diagram into a relational schema is seven rules applied in a fixed order. You will run six of them on one small college diagram, see why the seventh never fires, and watch where each primary key comes from.

Convert one diagram, one rule at a time →
Three of the four relationships in this diagram never become tables at all. A 1:1 and a 1:N are one foreign-key column each. Only a relationship with many on both sides needs a table of its own.

01 The idea

What actually becomes a table

An ER diagram holds four kinds of thing: entity types, relationships, attributes, and the cardinality and participation markings on the lines. The conversion is a sorting exercise. Some of those become tables, some become columns, and one of them becomes nothing at all.

Entity types always become tables, strong or weak. Relationships are the interesting case, and the deciding fact is the cardinality. A foreign-key column holds exactly one value, so a column can only ever record “at most one on that side”. That is enough for a 1:1 and enough for a 1:N, which is why neither of them needs a table. It is not enough for M:N, so M:N gets a table whose rows are the pairs. Attributes mostly become columns, except that a multivalued attribute cannot fit in one, so it gets a table too.

The part interviewers actually press on is not which tables you built. It is the primary key of each one, because that is where the diagram’s meaning ends up. Every rule below names a key, and every key in this lesson is checked against the rows you can see.

Entity types always become tables. A relationship becomes a table only when neither side is limited to one; otherwise it collapses into a foreign-key column. A multivalued attribute becomes a table too, because a column holds one value. Everything else is deciding the primary key.
Strong entity typeAn entity type with a key attribute of its own, so one of its rows is identified without looking at any other table. STUDENT, COURSE, INSTRUCTOR and LIBRARY_CARD are all strong here. Being one half of a 1:1 does not make an entity type weak.
Weak entity typeAn entity type with no key of its own. It has a partial key, unique only among the entities belonging to one owner: instalment number 1 exists for every student who pays. Its full identity is the owner’s key plus the partial key.
ParticipationWhether every entity of a type has to take part in a relationship. Total means every one does, partial means some do not. This is the fact that decides which side of a 1:1 receives the foreign key, and it is the marking students skip.

02 Worked example

One college diagram, written out

Here is the diagram this whole lesson uses, written in text so nothing is hidden in a drawing. Five entity types, four relationships, and one attribute of each awkward kind: a composite one, a derived one and a multivalued one. Read the last line first; it is the notation key.

-- entity typesSTUDENT       sid*, name(first, last), dob, /age, {phone}COURSE        cid*, title, creditsINSTRUCTOR    empid*, inameLIBRARY_CARD  card_no*, issued_onINSTALMENT    inst_no~, amount, paid_on        -- weak, owner STUDENT -- relationships                    sides  participationTAKES      STUDENT --- LIBRARY_CARD   1:1   card total, student partialPAYS       STUDENT --- INSTALMENT     1:N   identifying; instalment totalTEACHES    INSTRUCTOR --- COURSE      1:N   every course has one instructorENROLS     STUDENT --- COURSE         M:N   attribute: marks -- *  key attribute   ~  partial key   /  derived   {}  multivalued   ()  composite

Three lines carry most of the difficulty. Line 2 has all three awkward attributes on one entity type: name is composite and arrives as two columns, /age is derived and arrives as no column at all, and {phone} is multivalued and ends up needing a table. Line 6 is the weak entity type, marked by ~ on its partial key. Line 9 is where participation does real work: cardinality decides which table receives a column everywhere else in this diagram, and TAKES is the one line where the participation marking decides it instead.

1 · The diagramfive entity types, four relationships, one multivalued attribute
→
2 · Entity types firstfive tables; sid, cid, empid and card_no become primary keys
→
3 · The weak one borrowsinst_no reads 1, 2, 1 down three rows, so the key is (sid, inst_no)
→
4 · Count the sidesTAKES and TEACHES become one column each; ENROLS must become a table
→
5 · Multivalued lastphone cannot be a column, so it becomes a table of four rows

Node 4 is where the conversion is won or lost, which is why it is marked. Nothing about TAKES, TEACHES and ENROLS looks different on the page: three lines joining two boxes each. What separates them is the cardinality written on the line, and that single marking decides whether you write a column or build a table. Get it wrong in one direction and you have a table nobody needed; get it wrong in the other and your schema cannot record what the diagram said.

Count the result before you build it. Five entity types give five tables. One M:N relationship gives one more. One multivalued attribute gives one more. 7 tables, and three of the four relationships produced a foreign-key column instead of a table: the 1:1, and both of the 1:N lines. The rest of the lesson is the rule behind each of those seven, and the key each one ends up with.

03 Mechanics

The seven rules, in the order you apply them

The order matters, because rules 2 to 7 all borrow primary keys that rule 1 has to have created first. Work down the list and every foreign key you need already exists.

Before the table, the three words the fourth column is written in, because interviewers use them precisely and marks are lost here. A superkey is any set of columns whose values are unique in every state the table is allowed to reach. A candidate key is a superkey with nothing removable: take any one column out and uniqueness breaks. The primary key is whichever candidate key you picked. So “a candidate key is a key that could be the primary key” is not the definition; minimality is the whole content of the word.

RuleIn the diagramWhat you createPrimary key becomesOn our diagram
1 Strong entity type E One table with every simple attribute of E. A composite attribute contributes its components and then disappears; a derived attribute contributes nothing; a multivalued attribute waits for rule 6. The key attribute E already has student(sid), course(cid), instructor(empid), library_card(card_no)
2 Weak entity type W with owner E One table with W’s own attributes plus the primary key of E as a foreign key, normally ON DELETE CASCADE because W has no meaning without its owner. The owner’s primary key together with W’s partial key instalment(sid, inst_no)
3 Binary 1:1 between S and T No new table. The primary key of one side becomes a UNIQUE column in the other, with the relationship’s own attributes beside it. Put it on the side whose participation is total, so it can be NOT NULL. If both sides are total you may instead merge the two into one table. Unchanged on both sides. The new column is a second candidate key of the table that received it. library_card.sid UNIQUE NOT NULL; card_no stays the key
4 Binary 1:N, with S on the 1 side No new table. The primary key of S becomes a plain foreign-key column in T’s table, with the relationship’s own attributes beside it. No UNIQUE here: that column is meant to repeat. Unchanged. T keeps the key it already had. course.empid; cid stays the key
5 Binary M:N A new table holding both primary keys as foreign keys, plus the relationship’s own attributes. The two foreign keys together, and nothing else enrols(sid, cid), with marks outside the key
6 Multivalued attribute A of E A new table holding the primary key of E and one column for A, one row per value. E’s key together with A student_phone(sid, phone)
7 n-ary relationship over n entity types A new table holding all n primary keys as foreign keys, plus its own attributes. Same shape as rule 5 with more columns. All n foreign keys together, unless one participating side has cardinality 1, in which case that side’s key is left out of the key none in this diagram

Rule 5 is the one to be able to argue, because it is the one people try to shortcut. Suppose you tried to record ENROLS with a column instead of a table. Put cid inside student and you have said each student takes at most one course. Put sid inside course and you have said each course has at most one student. Neither is what the diagram says, and there is no third column to try. The only shape that records many on both sides is one row per pair, and once the rows are pairs, the pair is what identifies a row. That is why the primary key is composite, and why marks sits in the table but outside the key: marks belongs to the pair, it does not help identify it.

Here is the whole diagram as DDL. Read it against the rule numbers in the comments rather than top to bottom.

CREATE TABLE student (                          -- rule 1 · strong entity  sid TEXT PRIMARY KEY, dob DATE NOT NULL,      -- age is derived: not stored  first_name TEXT NOT NULL, last_name TEXT NOT NULL);  -- composite name, flattenedCREATE TABLE instructor (empid TEXT PRIMARY KEY, iname TEXT NOT NULL);CREATE TABLE library_card (                     -- rule 1, then rule 3  card_no TEXT PRIMARY KEY, issued_on DATE,  sid TEXT UNIQUE NOT NULL REFERENCES student(sid));  -- 1:1 · the total sideCREATE TABLE instalment (                       -- rule 2 · weak entity  sid TEXT REFERENCES student(sid) ON DELETE CASCADE,  inst_no INT, amount NUMERIC NOT NULL, paid_on DATE,  PRIMARY KEY (sid, inst_no));                  -- owner key + partial keyCREATE TABLE course (                           -- rule 1, then rule 4  cid TEXT PRIMARY KEY, title TEXT NOT NULL, credits INT NOT NULL,  empid TEXT NOT NULL REFERENCES instructor(empid));  -- 1:N · the N sideCREATE TABLE enrols (                           -- rule 5 · M:N gets a table  sid TEXT REFERENCES student(sid),  cid TEXT REFERENCES course(cid),  marks INT, PRIMARY KEY (sid, cid));            -- both foreign keys togetherCREATE TABLE student_phone (                    -- rule 6 · multivalued  sid TEXT REFERENCES student(sid), phone TEXT,  PRIMARY KEY (sid, phone));-- rule 7 · an n-ary relationship would add one more table here, holding--          all n foreign keys; the key is all n, minus any side limited to one.

Line 7 is rule 3 and the only place participation shows up as syntax. UNIQUE is what makes it 1:1 rather than 1:N, and NOT NULL is legal only because every card belongs to a student. It also gives library_card a second candidate key: card_no and sid are each unique on their own, and either could have been the primary key. Line 14 is rule 4 and deliberately has no UNIQUE, because one instructor here teaches two of the three courses and that column is supposed to repeat. Line 18 is rule 5, and line 21 is rule 6; in both, a foreign key is part of the primary key, which is normal and not a mistake.

One vendor difference worth naming, because it bites exactly here. A UNIQUE column accepts many NULLs in the SQL standard, PostgreSQL, MySQL and Oracle. SQL Server treats NULLs as equal in a unique constraint and accepts only one NULL row, so a nullable UNIQUE foreign key fails on the second row that has no partner. That is a second reason to put the 1:1 foreign key on the side that is never empty.

Every rule ends with a sentence about the primary key, and that sentence is the answer an interviewer is listening for. Anyone can say that M:N needs a junction table. Saying that its key is both foreign keys together, because neither is unique alone and dropping either one breaks uniqueness, is the same breath and a different mark.

05 Cheat sheet

The seven rules on one card

Learn it as construct, table, key. The third column is the one that gets asked about, and the one people leave out.

ER constructWhat you buildPrimary key
Strong entity typea table of its simple attributesits own key attribute
Weak entity typea table, plus the owner’s key as a foreign keyowner's key + partial key
Binary 1:1no table; a UNIQUE foreign-key column on the total sideboth keys unchanged
Binary 1:Nno table; a foreign-key column on the N sidethe N side keeps its own key
Binary M:Na table of both keys plus the relationship’s attributesthe two foreign keys together
Multivalued attributea table of the owner’s key plus one value per rowowner's key + the value
n-ary relationshipa table of all n keys plus its own attributesall n keys, minus any side with cardinality 1
Composite in, components outA composite attribute contributes its leaves as columns and then vanishes. name(first, last) becomes first_name and last_name, and there is no column called name anywhere in the schema. Nest a composite inside a composite and you still only write the leaves.
Derived attributes are not stored/age never becomes a column, because it is a function of dob, which you already have. Compute it when you read: age(dob) in PostgreSQL, TIMESTAMPDIFF(YEAR, dob, CURDATE()) in MySQL. A stored age is wrong the day after you store it.
Count the tables before you write themOne per entity type, one per M:N or n-ary relationship, one per multivalued attribute. Here that is 5 + 1 + 1 = 7. If your answer has a table for a 1:1 or a 1:N, you built a table for a relationship that only needed a column.

06 Where & why

Where these rules are the DDL you type

The seven rules are not a diagram exercise. Each one lands as a clause you can type into a database that is running right now, and each of these systems takes one of them slightly differently. Interviewers ask about exactly those differences.

PostgreSQL
Every rule is one clause

PRIMARY KEY (sid, cid) is rule 5 and creates a single unique index on the pair, not one per column, so the column order in that key decides which lookups it can serve. ON DELETE CASCADE is rule 2 written down: delete a student and the instalments go with her, which is what “weak” means. A foreign key here may point at any column carrying a unique constraint, not only at a primary key, which is why the second candidate key rule 3 creates is a legitimate target.

MySQL · InnoDB
The composite key is also the physical order

InnoDB stores the table clustered on its primary key and every secondary index carries that primary key as its row pointer. So the composite key from rule 5 is not just a constraint: enrols is physically ordered by sid then cid, which makes “all courses for one student” a range scan and “all students on one course” an index lookup you have to add. InnoDB also creates an index on a foreign-key column if you have not, so rule 4 quietly costs you one index per relationship.

SQL Server
Where option A of the 1:1 breaks

Putting the 1:1 foreign key on the partial side means a nullable UNIQUE column. SQL Server treats two NULLs as equal for a unique constraint and allows only one NULL row, so the second student with no library card fails to insert. The fix is a filtered unique index, WHERE sid IS NOT NULL. It is a syntax difference, but it turns rule 3’s participation guidance from a preference into a portability problem.

SQLite
The rules hold, the enforcement does not

Foreign keys are ignored until the connection runs PRAGMA foreign_keys = ON, so all six references in this schema are documentation by default. SQLite also allows NULLs in most PRIMARY KEY columns, a long-standing documented deviation, with INTEGER PRIMARY KEY the exception. So declare the parts of a composite key NOT NULL yourself, or rule 2 and rule 5 give you keys that do not actually identify anything.

The diagram is not the schema, and the gap between them is where the keys get decided. That is why this conversion is asked in interviews at all: a table you can draw proves nothing, and a primary key you can justify against the rows proves you understood the cardinality.

07 Interview questions

What they actually ask

This usually arrives as a whiteboard task rather than a question: here is a diagram, give me the tables. The follow-ups are always about keys, so answer each table with its key attached before anyone has to ask.

Walk me through converting an ER diagram into tables.
Seven rules in a fixed order. Strong entity types first: one table each, primary key is the key attribute the entity already has. Then weak entity types, whose key is the owner’s primary key plus the partial key. Then relationships by cardinality: 1:1 and 1:N become a foreign-key column and no new table, while M:N and n-ary become a table whose primary key is the foreign keys together. Multivalued attributes go last, one table each, keyed on the owner’s key plus the value.
Which relationships get their own table and which do not?
M:N and n-ary do. 1:1 and 1:N do not. The reason is that a foreign-key column holds exactly one value, so it can only ever say “at most one on that side”. Put cid inside student and you have recorded one course per student, which is not what an M:N line says. The only shape that records many on both sides is one row per pair.
What is the primary key of the table an M:N relationship produces?
The two foreign keys together, and nothing else. On our enrols rows sid repeats and cid repeats, so neither is unique alone, and the four pairs are distinct, so the pair is; drop either column and uniqueness breaks, which is what makes the pair minimal rather than merely unique. The relationship’s own attribute marks sits in the table but outside the key. If the same student may enrol in the same course twice, the pair stops being unique and the key needs a third column such as the semester.
A weak entity type: what table do you build, and what is its key?
One table with the weak entity’s own attributes plus the owner’s primary key as a foreign key, and the primary key is the owner’s key together with the partial key. Our inst_no reads 1 and 2 for Asha and 1 again for Ravi, so it identifies nothing on its own; (sid, inst_no) does. That foreign key is normally ON DELETE CASCADE, because a weak entity has no meaning once its owner is gone.
Which side of a 1:1 relationship should hold the foreign key?
The side whose participation is total, so the column can be NOT NULL and no row is left with an empty cell. Every library card belongs to a student but not every student takes a card, so sid goes into library_card rather than card_no into student. If both sides are total you may also merge the two entity types into one table; if both are partial, either placement leaves NULLs and you choose the one that leaves fewer.
Why can a multivalued attribute not just be a column?
Because a column holds one value. Put two phone numbers in it and you have a list inside a cell, which cannot be indexed, joined or constrained, and which breaks first normal form. The fix is a table of the owner’s key plus one value per row, keyed on both columns together. Asha with two numbers becomes two rows in student_phone and stays one row in student.
What happens to composite and derived attributes?
A composite attribute contributes its component parts as columns and then disappears: name(first, last) becomes first_name and last_name, and no column is called name. A derived attribute is not stored at all, because it is a function of data you already hold; age comes from dob at query time. A stored age is wrong the day after you store it.
Where do the attributes of a relationship go?
Into whichever table now represents that relationship. For M:N and n-ary it is the new table, so marks lives in enrols, outside the key. For 1:N it is the table on the N side, beside the foreign key you just put there. For 1:1 it is whichever of the two tables received the foreign key. There is never a separate table just for a relationship’s attributes.
Superkey, candidate key, primary key: what is the difference?
A superkey is any set of columns whose values are unique in every state the table is allowed to reach. A candidate key is a superkey with nothing removable: take one column out and uniqueness breaks. The primary key is whichever candidate key you chose to declare. On enrols, {sid, cid, marks} is a superkey but not a candidate key because marks comes out freely, {sid, cid} is a candidate key, and it is the one we declared.
Can this conversion ever leave you with a table that has no primary key?
No. Every one of the seven rules ends by naming the key of the table it created, so if you finish holding a table you cannot key, you skipped a cardinality or a participation marking on the diagram. The usual cause is treating an M:N relationship as though one side were limited to one, which produces a table of pairs with no reason for the pairs to be distinct.
Do teams ship exactly this schema in practice?
The structure usually yes, the keys often not. The common deviation is adding a generated id to the table an M:N produced, because some frameworks and ORMs want a single-column key on every table. That is workable only if you also declare UNIQUE (sid, cid), because the composite primary key was giving you that guarantee for free and a surrogate key throws it away. The other deviation is deliberate denormalisation for read speed, which is a decision taken later and is not part of the conversion.

08 Practice problems

Six to work through

All six extend the same college diagram. For each one, write the tables before you write the keys, then check every key against rows you actually wrote down.

Add a hostel

Easy
The college adds a HOSTEL entity type with hostel_id, hname and warden. Every student lives in exactly one hostel, and one hostel houses many students. Write the tables that are created, the tables that change, and the primary key of every table you touched.
Follow-up
The diagram carries a participation marking on each side of this line. Exactly one of them changes something you can write in the DDL and the other changes nothing at all; say which is which, and what you would have had to write for the other one.
Show the hint
Decide which table receives the foreign key first, then ask what you would need to add to stop that column ever being empty.

Six arrows, two tables with none

Easy
The finished schema has seven tables and six foreign keys. Name every foreign key: the table it sits in, the table it points at, and which of the seven rules created it. Then name the two tables that hold no foreign key at all and say what those two have in common.
Follow-up
Four of the six foreign keys are also part of their own table’s primary key, which looks wrong the first time you see it and is completely normal. Say which four, and which rule produced each.
Show the hint
Go rule by rule rather than table by table; every foreign key in this schema was created by exactly one of the seven.

Both sides total, then not

Medium
Suppose a library card becomes compulsory: every student must hold one and every card must belong to a student, so merging STUDENT and LIBRARY_CARD into a single table is now legal. Write that merged table. Then a year later the college allows a student to hold two cards; state exactly what you must do to the merged design and to the two-table design to absorb that, and count the tables and foreign keys each one ends up with.
Follow-up
One of the two designs absorbs the change with a single ALTER TABLE and the other cannot be altered into shape at all. The reason is not about empty cells.
Show the hint
Write down what the primary key of each design is being asked to identify after the change, and check whether it still can.

A third participant

Medium
Replace TEACHES with a ternary relationship over INSTRUCTOR, COURSE and a new SEMESTER entity type keyed on sem_id: an instructor teaches a course in a semester. Write the table it produces, give its primary key, and say what happens to the empid column that rule 4 had put on course.
Follow-up
The primary key here depends on a cardinality claim nobody has made yet. Give the two different keys you get from “a course has one instructor in a given semester” and from “a course may have several instructors in the same semester”, and one pair of rows that is legal under one reading and rejected under the other.
Show the hint
Write four candidate rows over (empid, cid, sem_id) before you decide anything; the key is whatever the two readings disagree about.

Read the schema backwards

Medium
You are handed only the seven tables and their constraints, with no diagram. For each table, say which ER construct produced it. Then name one thing about the original diagram that the schema does not record anywhere.
Follow-up
Two of the seven tables are ambiguous in different ways. For one of them, a single constraint is the only thing separating a 1:1 diagram from a 1:N one. For the other, two genuinely different diagrams produce exactly the same table and nothing in the schema separates them at all. Find both.
Show the hint
Look at the table whose primary key is every column it has, and ask what else could have produced exactly that table.

What the schema cannot promise

Hard
Total participation and NOT NULL are not the same thing. For each of the four relationships in the diagram, write the participation constraint on both sides as the diagram states it, then say whether the converted schema enforces it, enforces it only by accident, or cannot enforce it at all. For every side it cannot enforce, name the smallest thing you could add to the database that would.
Follow-up
There is a clean split: one kind of side is always enforceable with a single keyword and the other kind is never enforceable in plain DDL on any vendor. State that split as a rule, then find the one side in this diagram that came out enforced without anybody thinking about participation at all.
Show the hint
For each relationship, ask which table ended up holding the foreign key, then ask what stops a row in the other table from being pointed at by nobody.