ER to Relational Mapping
Scenario
You've drawn the ER diagram for a university database: Students, Courses, an Enrolls relationship (M:N with a Grade attribute), and a Dependent weak entity tied to a Faculty entity. Now you have to turn this into actual SQL tables. Which concepts become tables? Where do foreign keys go? What happens to the M:N relationship?
What is the systematic set of rules for translating every ER construct into a relational table?
Mental model
Translating an ER diagram to tables is like turning an architect's blueprint into a building — each room type in the blueprint (entity, relationship, weak entity) has a fixed construction rule, and you apply each rule mechanically.
The ER diagram is the what; the relational schema is the how. Each construction rule handles one ER construct. Strong entities become tables with their attributes as columns. M:N relationships become their own junction tables. Weak entities merge their identifying relationship's foreign key into their primary key. The rules are deterministic — given the same ER diagram, every competent designer produces the same relational schema.
Explanation
The ER-to-relational mapping is a set of seven standard steps, each handling a specific ER construct. Mastering these steps means you can mechanically convert any ER diagram into a correct relational schema — which is exactly what an interview or OA question tests.
Step 1 — Strong entities: Each strong entity type becomes a table. Its simple attributes become columns. The key attribute becomes the primary key. Composite attributes are flattened: Address (Street, City, Pin) becomes three columns — Street, City, Pin — unless you choose to store it as a single string. Derived attributes are typically not stored (Age is not a column; DateOfBirth is). For example, a Student entity with StudentID (key), Name, and DateOfBirth becomes a Student table: Student(StudentID PK, Name, DateOfBirth).
Step 2 — Multivalued attributes: Each multivalued attribute becomes a separate table. The new table has the entity's primary key as a foreign key plus the multivalued attribute. A Student with multiple PhoneNumbers becomes: PhoneNumbers(StudentID FK, PhoneNumber) with primary key (StudentID, PhoneNumber). This is because a relational table cell must hold a single atomic value (1NF); you cannot store a list in one cell.
Step 3 — Weak entities: A weak entity becomes a table that includes, as part of its primary key, the primary key of the identifying (owner) entity as a foreign key. A Dependent of Employee becomes: Dependent(EmployeeID FK, DependentName, Relationship) with primary key (EmployeeID, DependentName). The foreign key is part of the composite PK because Dependent can only be uniquely identified together with its owning Employee.
Step 4 — 1:1 relationships: The relationship is implemented by placing the primary key of one entity as a foreign key in the other entity's table. Choose the entity with total participation to receive the foreign key — this avoids NULL values. A Person has exactly one Passport (total participation on Passport side): add PersonID as a foreign key in the Passport table.
Step 5 — 1:N relationships: The relationship is implemented by placing the primary key of the '1' side as a foreign key in the 'N' side's table. A Department (1) has many Employees (N): add DepartmentID as a foreign key in the Employee table. No separate junction table needed.
Step 6 — M:N relationships: A new junction table must be created. Its primary key is the combination of both entities' primary keys. Any relationship attributes become columns in this junction table. Student Enrolls Course (M:N, with Grade attribute): create Enrolls(StudentID FK, CourseID FK, Grade) with primary key (StudentID, CourseID).
Step 7 — N-ary relationships: For ternary (or higher) relationships, create a new table whose primary key is the combination of all participating entities' primary keys, plus any relationship attributes. The principle is the same as Step 6, extended to three or more participants.
Key points
- Strong entity → table: Each strong entity becomes a table; its key attribute becomes the primary key. Composite attributes are flattened; derived attributes are usually omitted.
- Multivalued attribute → separate table: Cannot store a list in one cell (1NF). Create a new table with (entity PK, attribute value) as the composite PK.
- Weak entity → table with composite PK: Table includes the owner entity's PK as a foreign key that forms part of the composite primary key. Ensures identification only within the owner's context.
- 1:1 relationship → foreign key: Add the PK of one side as a FK in the other's table. Place the FK on the total-participation side to avoid NULLs.
- 1:N relationship → foreign key on the N side: Add the PK of the '1' entity as a FK in the 'N' entity's table. No junction table needed.
- M:N relationship → junction table: Create a new table whose PK is the combination of both entities' PKs. Relationship attributes become columns in this junction table.
Common mistakes
- Creating a junction table for a 1:N relationship: A 1:N relationship only needs a foreign key on the N side — no junction table. Adding an unnecessary junction table introduces extra joins and complexity. Only M:N relationships require a junction table.
- Forgetting to include relationship attributes in the junction table: When an M:N relationship has its own attributes (e.g., Grade on Enrolls), those attributes must go into the junction table — not into either entity's table. Grade depends on both the student and the course, so it belongs where both are present.
- Storing derived attributes as columns: Derived attributes (Age, Duration, TotalPrice) are usually not stored — they can be computed from stored base attributes at query time. Storing them risks inconsistency (the stored Age may not match DateOfBirth). Always store the base fact.
- Placing the foreign key on the wrong side in a 1:N: In a 1:N (Department has many Employees), the FK (DepartmentID) goes in the Employee table — the N side — not in the Department table. Putting it in Department would require storing an array of EmployeeIDs, violating 1NF.
Glossary
- ER-to-relational mapping
- The precise, step-by-step process of translating a conceptual database blueprint into the actual tables and columns a real database will use.
- strong entity type
- A primary object in your database (like a 'User' or 'Product') that can exist completely on its own without needing another object to exist first.
- primary key
- A unique identifier (like an ID number) assigned to each row in a database table to ensure every single record can be distinctly found.
Recall questions
- How do you map a many-to-many (M:N) relationship to the relational model?
- Why does a weak entity's table have a composite primary key?
- Where does the foreign key go in a 1:N relationship, and why?
- What happens to multivalued attributes during ER-to-relational mapping?
Questions & answers
Map the following ER design to relational tables: Student (StudentID, Name) — Enrolls — Course (CourseID, Title); Enrolls has a Grade attribute; one student can enroll in many courses and vice versa.
Student(StudentID PK, Name), Course(CourseID PK, Title), Enrolls(StudentID FK, CourseID FK, Grade) with PK = (StudentID, CourseID). The Enrolls junction table captures the M:N relationship and holds the relationship attribute Grade.
Approach: Identify the relationship type first (M:N → junction table). Then check for relationship attributes (Grade → column in junction). Name the junction table after the relationship verb. This 3-table result is the canonical answer.
An Employee entity has a multivalued attribute WorksIn (an employee can work in multiple departments). How does this map to relational tables?
Employee(EmployeeID PK, Name, ...). WorksIn(EmployeeID FK, DepartmentID, ...) with PK = (EmployeeID, DepartmentID). The multivalued attribute becomes a separate table because 1NF prohibits storing a list in a single column.
Approach: Explain that 1NF is the underlying reason — each cell must be atomic. The separate table also allows WorksIn to carry attributes (e.g., a StartDate per department) if needed in the future — a design advantage.