Converting an ER Model to a Relational Schema

The Story That Explains the Mapping

The ER model is a conceptual design; the relational model is a logical design that maps almost one-to-one onto real tables. We cannot carry every ER constraint across perfectly, but a faithful, implementable schema can always be produced by following a small set of mechanical rules.

The Big Picture — What Maps to What

Before the step-by-step rules, hold this translation table in your head. Every ER construct on the left has a fixed home on the right.

The Seven-Step Mapping Algorithm

Almost every textbook follows the same seven steps, in this order. Run them top to bottom and any ER diagram becomes a complete schema.

Step 1 — Strong Entities Become Tables

For each strong entity, create a table with the same name. Every simple, single-valued attribute becomes a column, and the entity's key attribute becomes the primary key (shown underlined in the diagram, marked PK in the schema).

-- Step 1 : strong entity to table
STUDENT ( Roll_No PK, Name, Age )

Handling Composite, Derived & Multivalued Attributes

Three attribute types need special care, because relational tables only allow atomic, single-valued columns. The diagram below is the summary; the three sub-sections after it explain each case in detail, with the exact tables that get created.

A. Composite Attributes — One Column per Leaf

A composite attribute is one that can be split into smaller sub-parts. For example, Name splits into First_Name and Last_Name, and Address splits into House_No, City, and PIN. A relational column must be atomic, so we cannot keep the composite "root" as a single column.

Here is how each composite attribute of EMPLOYEE flattens into columns:

The resulting EMPLOYEE table — the composite roots have vanished, only their leaves remain as real columns:

-- Composite Name and Address are flattened into leaf columns
EMPLOYEE ( Emp_ID PK, First_Name, Last_Name, House_No, City, PIN )

B. Derived Attributes — Don't Store It, Compute It

A derived attribute is one whose value can be calculated from other data that is already stored. Age can be derived from Date_of_Birth; an order's Total can be derived from Quantity and Unit_Price. Storing a derived value is a mistake: it wastes space and quickly goes stale (your stored Age would be wrong on the next birthday).

What you actually store — notice there is no Age column:

What the user sees when they ask for it — Age is calculated fresh (here, as of 2026):

How to calculate it — the derived value is produced by a formula at read time, for example with a query or a view (no extra storage):

-- Age is NEVER stored; it is computed from DOB whenever needed
SELECT Roll_No, Name,
       FLOOR( DATEDIFF(CURRENT_DATE, DOB) / 365.25 ) AS Age
FROM   STUDENT;

-- Or expose it permanently as a VIEW (still nothing extra is stored)
CREATE VIEW STUDENT_AGE AS
SELECT Roll_No, Name,
       FLOOR( DATEDIFF(CURRENT_DATE, DOB) / 365.25 ) AS Age
FROM   STUDENT;

The same principle covers every derived attribute — store the inputs, write the formula:

C. Multivalued Attributes — Create a Separate Table

A multivalued attribute can hold many values for a single entity — an employee may have several phone numbers, a person may have many skills. A single column can hold only one value, so a multivalued attribute cannot live inside the entity's own table.

The main EMPLOYEE table keeps only single-valued data:

The new EMP_PHONE table stores one row per phone number — so E1's two phones become two rows:

-- Multivalued Phone_No is lifted out into its own table
EMPLOYEE  ( Emp_ID PK, Name )
EMP_PHONE ( Emp_ID FK, Phone_No )   -- PK = (Emp_ID, Phone_No)

Step 3 — Mapping 1:1 Relationships

A one-to-one relationship needs no new table. Put the primary key of one entity into the other as a foreign key. Prefer the side with total participation — it minimises NULLs, because that side always has a partner.

-- Step 3 : 1:1 mapped with a foreign key (+ UNIQUE keeps it 1:1)
PERSON   ( Person_Id PK, Name )
PASSPORT ( Pass_No PK, Issue_Dt, Person_Id FK UNIQUE )

Step 4 — Mapping 1:N Relationships

This is the most common case. Place the primary key of the "one" side into the "many" side as a foreign key. No new table is needed — the foreign key is the relationship.

-- Step 4 : 1:N mapped with a foreign key on the many side
CUSTOMER ( Cust_No PK, Name, City )
LOAN     ( Loan_No PK, Amount, Cust_No FK )

Step 5 — Mapping M:N Relationships

When both sides are "many", a foreign key cannot capture it. Create a brand-new junction table (also called a relationship or cross-reference table) holding the primary keys of both entities plus any descriptive attributes of the relationship.

-- Step 5 : M:N mapped to a junction table
STUDENT ( Roll_No PK, Name )
COURSE  ( Course_ID PK, Title )
ENROLLS ( Roll_No FK, Course_ID FK, Grade )  -- PK = (Roll_No, Course_ID)

Step 2 — Mapping Weak Entities

A weak entity becomes a table that includes the owner's primary key as a foreign key. Its primary key is the combination of that foreign key and the weak entity's partial key (discriminator).

-- Step 2 : weak entity borrows the owner's key
LOAN    ( Loan_No PK, Amount )
PAYMENT ( Loan_No FK, Payment_No, Pay_Date )  -- PK = (Loan_No, Payment_No)

Step 7 — Mapping n-ary Relationships

A relationship connecting three or more entities (degree > 2) always becomes its own table. Include the primary key of every participating entity as a foreign key, plus any descriptive attributes. The combination of those foreign keys is normally the primary key.

A Complete Worked Example

Let us convert a small University ER diagram end to end. It contains two strong entities (DEPARTMENT, STUDENT, COURSE), a 1:N relationship, an M:N relationship with a descriptive attribute, and a multivalued attribute.

-- Final relational schema
DEPARTMENT     ( Dept_ID PK, Dname )
STUDENT        ( Roll_No PK, Name, Dept_ID FK )
COURSE         ( Course_ID PK, Title )
ENROLLS        ( Roll_No FK, Course_ID FK, Grade )   -- PK = (Roll_No, Course_ID)
STUDENT_PHONE  ( Roll_No FK, Phone_No )            -- PK = (Roll_No, Phone_No)

Common Mistakes to Avoid

Quick Reference Cheat Sheet

Golden Rules of ER-to-Relational Mapping