ER Diagrams to Tables
Drawing and reading ER diagrams: entities, relationship sets, cardinality and participation, generalisation, and how an ER model becomes tables.
- Identify entities, attributes, relationship sets and keys in an ER diagram
- Distinguish cardinality ratios (1:1, 1:N, M:N) and total vs partial participation
- Map an ER diagram to relational tables including FK placement rules
Every DBMS exam paper opens with "Draw the ER diagram for X" — and every software design interview closes with "how would you model this?" The skill is identical: decide what is a thing, what is a fact about a thing, and where the relationship lives.
ER building blocks:
- Entity set: a noun (Student, Course). Weak entity set: no key of its own, identified via an owner entity (Dependent of Employee) — double rectangle, partial key with dashed underline.
- Attributes: simple/derived (age from DOB)/multivalued (ovals double)/composite. Key attribute underlined.
- Relationship set: a verb (Enrolls, Works_For). Degree = number of participating entity sets.
Cardinality:
- 1:1 — one A relates to at most one B (Head manages Department)
- 1:N — one A relates to many B (Department has Employees)
- M:N — many A relate to many B (Student Enrolls Course)
Participation: total (every A must participate — double line) vs partial (optional — single line).
Keys in relationships: for 1:1 the relationship can borrow a key from either side; for 1:N the key of the N-side entity moves into the 1-side table as FK; for M:N a new junction table with PK = (keyA, keyB).
ER → relational mapping rules:
- Each strong entity set → one table; attributes become columns; key = primary key.
- 1:1 relationship → merge tables or add FK + UNIQUE on the participating side.
- 1:N → add FK on the N-side.
- M:N → new relation with composite PK of both participant keys (plus relationship attributes).
- Multivalued attribute → separate table.
- Weak entity → table with owner FK; PK = (owner PK, partial key).
- Generalisation (ISA) → single table with type discriminator, or one table per subclass (design trade-off).
Relational model vocabulary: relation = table, tuple = row, attribute = column, domain = allowed values, degree = columns, cardinality = rows.
| Where the FK goes | |
|---|---|
| 1:1 | Either side (UNIQUE constraint) or merged table |
| 1:N | N-side table gets FK to 1-side |
| M:N | New junction table, composite PK |
Total participation = NOT NULL FK; partial = FK may be NULL.
Forgetting derived/multivalued attributes, and placing the FK on the wrong side of a 1:N.
- 1
Strong entities
Student(roll PK, name), Course(cid PK, title), Department(dname PK), Employee(eid PK, name).
- 2
M:N Enrolls
Junction Enrolls(roll, cid, sem) PK = (roll, cid), FKs to Student and Course.
- 3
1:N offers
Add FK dname to Course (a course belongs to one department); Course stays its own table.
- 4
1:1 manages
Add FK eid to Department with UNIQUE — one employee manages at most one department.
- 5
Check
Every relationship covered; no attribute left homeless; FKs point to the 1-side.
Solution
Five base tables + one junction table; FKs placed by cardinality rules.
Dependent (dname) of Employee with partial key dname. Write the table and its PK.
Hint: Owner key + partial key together.
Putting a FK in both tables for a 1:N relationship
Only the N-side needs the FK; the other direction is implied.
Using name as the primary key of Student
Names are not unique — prefer roll/enrollment id; examiners penalise non-unique PKs.
What is a weak entity set? How is it represented in the relational model?
An entity set without a key of its own, identified via its owner entity; mapped with owner FK + partial key forming the composite PK.
Source: Frequent 4-mark question.
- ER = entities, attributes, relationships with cardinality and participation.
- Mapping: 1:N → FK on N; M:N → junction table; weak → owner FK + partial key.
- Relational vocabulary: relation/tuple/attribute, keys, domain.
