Mapping the ER and EER Model to the Relational Model | Relational Model and Relational Algebra | 2114112 Database Management System

Database Design · Logical Mapping

ER & EER to Relational Mapping

The complete 9-step algorithm for translating conceptual ER/EER diagrams into actual relational tables, columns, and keys.

Logical database design (also called data model mapping) is the phase where your conceptual ER/EER blueprints become real relational schemas. Every entity, relationship, and attribute in your diagram must be systematically converted into tables, columns, primary keys, and foreign keys.

📐

Conceptual

ER / EER Diagram

→

⚙️

Process

9-Step Algorithm

→

🗄️

Logical

Relational Schema

→

💾

Physical

Database Tables

Part 1 — Mapping the Basic ER Model (Steps 1–7)

01

Mapping Regular (Strong) EntitiesOne table per regular entity type

• Action: For every regular (strong) entity type in your ER diagram, create a new relational table.
• Columns: Include all simple attributes. For composite attributes (e.g., "Name" → First, Middle, Last), include only the individual simple components — not the composite itself.
• Primary Key: Choose one of the entity's key attributes to become the table's Primary Key.

-- ER Entity: EMPLOYEE {Emp_ID, First_Name, Last_Name, Salary} -- "Name" was composite → broken into First_Name, Last_Name EMPLOYEE(Emp_ID, First_Name, Last_Name, Salary)

02

Mapping Weak EntitiesRequires owner's PK as a Foreign Key

• Action: Create a separate table for the weak entity.
• Foreign Key: Because a weak entity cannot exist without its owner, include the owner entity's Primary Key as a Foreign Key in this new table.
• Primary Key: The composite of the owner's PK + the weak entity's own partial key.

ℹ️

A weak entity (e.g., DEPENDENT of an EMPLOYEE) has no key of its own. Its identity is partially borrowed from its owner. The partial key (e.g., Dep_Name) only becomes unique in combination with the owner's PK.

-- Weak entity: DEPENDENT {Dep_Name, Birthdate} owned by EMPLOYEE DEPENDENT(Emp_ID, Dep_Name, Birthdate) -- PK = {Emp_ID + Dep_Name} (composite) FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID)

03

Mapping Binary 1:1 RelationshipsThree strategies available

One-to-one relationships offer three mapping strategies. Choose based on participation constraints:

Option A — Foreign Key ★ Most Common

Add the PK of one table into the other as a Foreign Key. Prefer the table with total participation as the one receiving the FK. Include relationship attributes here too.

Option B — Merged Relation

If both entities have total participation (they always exist 1-to-1 together), merge all attributes into a single table. Simplest possible outcome.

Option C — Cross-Reference

Create a third bridging table holding both PKs as FKs. Generally avoided for 1:1 — adds an unnecessary extra table.

-- Option A: EMPLOYEE manages DEPARTMENT (1:1) -- DEPARTMENT has total participation → add FK there DEPARTMENT(Dept_ID, Dept_Name, Manager_Emp_ID, Manager_Start_Date) FOREIGN KEY (Manager_Emp_ID) REFERENCES EMPLOYEE(Emp_ID)

04

Mapping Binary 1:N RelationshipsForeign Key on the "Many" side

• Rule: Always use the Foreign Key approach — no new table needed.
• Placement: Take the PK of the entity on the "1" side and place it as a Foreign Key inside the table on the "N" (many) side.
• Include any attributes of the relationship itself in the "N" side table too.

💡

Memory tip: The FK always travels to the many side. Think — many employees belong to one department, so Department_ID goes into the Employee table (not the other way around).

-- DEPARTMENT (1) ──has── EMPLOYEE (N) EMPLOYEE(Emp_ID, Name, Salary, Dept_ID) FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID) -- Dept_ID (FK) placed on the N-side (EMPLOYEE), not on DEPARTMENT

05

Mapping Binary M:N RelationshipsAlways requires a new bridging table

• Rule: A many-to-many relationship cannot be represented with a simple Foreign Key. You must create a new bridging (junction) table.
• Columns: The new table holds the PKs of both participating entities as Foreign Keys.
• Primary Key: The composite of those two Foreign Keys. Include any relationship attributes as extra columns.

⚠️

Never try to squeeze an M:N relationship into one of the existing entity tables using a FK — it will cause massive data redundancy and integrity violations.

-- EMPLOYEE (M) ──works_on── PROJECT (N) with attribute Hours WORKS_ON(Emp_ID, Project_ID, Hours) FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID) FOREIGN KEY (Project_ID) REFERENCES PROJECT(Project_ID)

06

Mapping Multivalued AttributesAlways requires a new table

• Problem: Relational tables cannot store a list of values in a single cell (no atomic violation allowed).
• Solution: Create a new table for each multivalued attribute.
• Columns: One column for the attribute value + the parent entity's PK as a Foreign Key.
• Primary Key: Composite of the attribute value + the Foreign Key.

-- EMPLOYEE has multivalued attribute: Phone_Number EMP_PHONE(Emp_ID, Phone_Number) FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID) -- Each phone number gets its own row — no lists in a single cell

07

Mapping N-ary Relationships (Degree > 2)New table with n Foreign Keys

• Action: Similar to M:N — create a new table representing the relationship.
• Columns: Include the Primary Keys of all participating entities as Foreign Keys.
• Primary Key: Usually the composite of all the Foreign Keys combined.

-- Ternary: EMPLOYEE supplies PART to PROJECT SUPPLY(Emp_ID, Part_ID, Project_ID, Quantity) FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID) FOREIGN KEY (Part_ID) REFERENCES PART(Part_ID) FOREIGN KEY (Project_ID) REFERENCES PROJECT(Project_ID)

Part 2 — Mapping Enhanced ER (EER) Constructs (Steps 8–9)

08

Mapping Specialization / Generalization (Subclasses)Four options depending on disjoint vs. overlapping, total vs. partial

When a superclass (e.g., EMPLOYEE) has subclasses (e.g., SECRETARY, ENGINEER), choose one of four mapping options:

Option 8A — Superclass + Subclass Tables ★ Most Flexible

Create a table for the superclass and separate tables for each subclass. Each subclass table holds its own attributes plus the superclass PK (which doubles as both PK and FK).

Option 8B — Subclass Tables Only

Drop the superclass table entirely. Each subclass table includes all inherited superclass attributes. Best when specialization is total (every entity must belong to a subclass).

Option 8C — Single Table + Type Column

One giant table for all. Add a Type column (e.g., Job_Type) to indicate the subclass. Subclass-specific columns are NULL when not applicable. Best for disjoint specialization.

Option 8D — Single Table + Boolean Flags

One table with multiple Boolean flag columns (e.g., Is_Secretary, Is_Engineer). Used when subclasses overlap (an entity can belong to multiple subclasses simultaneously).

-- Option 8A: EMPLOYEE superclass + ENGINEER subclass EMPLOYEE(Emp_ID, Name, Salary) ENGINEER(Emp_ID, Eng_Type) -- Emp_ID = PK and FK FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID) -- Option 8C: Single table with type discriminator EMPLOYEE(Emp_ID, Name, Salary, Job_Type, Typing_Speed, Eng_Type) -- Job_Type = 'Secretary' | 'Engineer' | ... -- Eng_Type is NULL for non-engineers; Typing_Speed NULL for non-secretaries

📌

Choosing the right option: 8A is the safest general choice. 8B suits total specialization. 8C is clean for disjoint subclasses with few NULLs. 8D is reserved for overlapping subclasses.

09

Mapping Union Types (Categories)Requires an artificial surrogate key

• Problem: A category (union type) groups entirely different entity types that have different PKs — e.g., OWNER can be a PERSON (SSN), a BANK (Bank_Code), or a COMPANY (Company_ID). No single natural key works across all three.
• Solution: Create a new table for the category and generate a brand-new artificial surrogate key (e.g., Owner_ID).
• Link back: Add this surrogate key as a Foreign Key into each of the member entity tables (PERSON, BANK, COMPANY).

-- Category: OWNER = PERSON ∪ BANK ∪ COMPANY -- Surrogate key Owner_ID added to each member OWNER(Owner_ID) -- category table PERSON(SSN, Name, Address, Owner_ID) -- FK back to OWNER BANK(Bank_Code, Bank_Name, Owner_ID) -- FK back to OWNER COMPANY(Company_ID, Company_Name, Owner_ID) -- FK back to OWNER VEHICLE(Vehicle_ID, Make, Model, Owner_ID) -- references category FOREIGN KEY (Owner_ID) REFERENCES OWNER(Owner_ID)

Quick Reference — Conceptual to Relational Mapping

ER / EER Concept	→	Relational Model Construct	Step
Regular (Strong) Entity	→	New Table Table	1
Simple Attribute	→	Column (Attribute)	1
Composite Attribute	→	Set of simple component columns (composite itself dropped)	1
Key Attribute	→	Primary Key PK	1
Weak Entity	→	New Table with composite PK (owner PK + partial key) FK	2
1:1 Relationship	→	Foreign Key on total-participation side FK or merged table Merge	3
1:N Relationship	→	Foreign Key on the "Many" (N) side FK	4
M:N Relationship	→	New bridging table with two Foreign Keys Table FK×2	5
Multivalued Attribute	→	New table with (attribute value + parent PK as FK) Table	6
N-ary Relationship	→	New table with n Foreign Keys Table FK×n	7
Specialization / Generalization	→	4 options: superclass+subclass tables, subclass-only, single table+type col, single table+flags	8
Union Type (Category)	→	New category table + surrogate key propagated as FK to all member tables Surrogate Key	9

ER & EER → Relational Mapping — 9 systematic steps from conceptual diagram to production-ready relational schema.