ER to Relational Mapping

Definition

ER-to-Relational Mapping is the systematic process of translating a completed ER/EER diagram into a set of relational tables (schemas), following a well-defined set of rules for each ER construct. This is the bridge from conceptual design (this whole chapter) to the physical relational database (Chapter 4's relation/table concepts).

Worked Example — Mapping Rules Applied to the College Database

1. Strong entity set → table. Student(Roll_No, Name, DOB, Email) — each attribute becomes a column; the entity's key becomes the table's primary key.
2. Weak entity set → table with composite key. Dependent(Employee_ID, Dependent_Name, Relationship, DOB) — the owner's key (EmployeeID) is embedded as a foreign key AND combined with the partial key (DependentName) to form the composite primary key.
3. 1:N relationship → foreign key. For Department Offers Course, no separate table is needed — simply add Dept_Code as a foreign key column inside Course.
4. M:N relationship → junction table. For Student Enrolls_In Course, create Enrollment(Roll_No, Course_Code, Grade, Enrollment_Date), with Roll_No and Course_Code as foreign keys, together forming the composite primary key (this table also naturally absorbs any relationship attributes, like Grade).
5. Multi-valued attribute → separate table. Student_Phone(Roll_No, Phone_Number), with Roll_No as a foreign key, and (RollNo, PhoneNumber) as the composite key.
6. Composite attribute → flattened columns. Address becomes four ordinary columns (Street, City, State, Pincode) directly inside Student, with no separate table.
7. Generalization/Specialization → one of several strategies, commonly: (a) one table per subclass PLUS one table for the superclass, linked by a shared key (most flexible, used above for Person/Student/Employee); or (b) one combined table for the whole hierarchy with nullable subclass-specific columns; or (c) one table per subclass only, duplicating superclass attributes into each (used when the hierarchy is total and disjoint).
8. Aggregation → reference the junction table's key. Monitored_By from Faculty to the Student-EnrollsIn-Course aggregate is implemented by adding a `FacultyID foreign key column directly into the Enrollment` table (since Enrollment already represents that aggregated relationship as a row).

Edge Cases

• Derived attributes (6.10) are generally NOT given a column at all — they are recomputed via queries/views, not mapped to storage.
• Choosing between the three generalization-mapping strategies (rule 7) is a genuine trade-off: separate superclass+subclass tables avoid NULLs and support partial/overlapping hierarchies cleanly but require JOINs to reconstruct a full Person; one combined table avoids joins but wastes space with NULLs for non-applicable subclass columns and struggles with overlapping membership.
• A 1:1 relationship can be merged into a single table (if both sides always have total participation with each other) instead of using a foreign key across two tables — an additional flexibility not available for 1:N or M:N.
• Every table produced by this process must ultimately satisfy the relational integrity rules from Chapter 4 (entity integrity: primary key not null/unique; referential integrity: foreign keys must reference existing values) — ER-to-relational mapping is the design bridge, but the resulting rules are the relational model's own.

Key Takeaways / Interview Q&A

Q: Does every relationship in an ER diagram require its own separate table? A: No — only M:N relationships (and optionally ternary+ relationships) require a dedicated junction table; 1:1 and 1:N relationships can usually be captured with a simple foreign key on one side.

Q: How does a weak entity's table differ from an ordinary entity's table? A: A weak entity's table must include the owner's primary key as a foreign key, and its own primary key is composite (owner key + partial key) rather than a key from its own attributes alone.

Q: Name the three common strategies for mapping a generalization/specialization hierarchy to tables. A: (1) Superclass table + one table per subclass linked by shared key; (2) one single combined table with nullable subclass columns; (3) one table per subclass only, each duplicating the superclass's attributes.