Converting an ER Model to a Relational Schema
Press Next → or use ← → arrow keys
The Story — Sketch to Builder's Worksheet
Entities become tables, attributes become columns, keys become primary keys, and relationships become foreign keys. Master that one sentence and the rest is detail.
The Big Picture — Conceptual → Logical
| ER Construct | Relational Equivalent |
|---|---|
| Strong entity | A table |
| Weak entity | Table with the owner's key included |
| Simple attribute | A column |
| Composite attribute | One column per leaf component |
| Multivalued attribute | A separate table |
| Derived attribute | Usually not stored |
| Key attribute | Primary key |
| 1:1 / 1:N relationship | Foreign key (no new table) |
| M:N relationship | Junction table |
The Seven-Step Mapping Algorithm
Create all entity tables first (steps 1–2), then relationships (steps 3–5, 7) can reference tables that already exist. Attributes get flattened along the way (step 6). Build the boxes before you draw the arrows.
Strong Entities Become Tables
CREATE TABLE STUDENT (
Roll_No INTEGER PRIMARY KEY, -- key attribute
Name TEXT,
Age INTEGER
);Composite, Derived & Multivalued Attributes
Name → First_Name, Last_NameAddress → House_No, City, PIN
Age from DOB · Total = Qty × PriceExperience = today − Join_Date
EMP_PHONE(Emp_ID, Phone_No)
-- Composite flattened + multivalued lifted into its own table
CREATE TABLE EMPLOYEE ( Emp_ID PRIMARY KEY, First_Name, Last_Name, House_No, City, PIN );
CREATE TABLE EMP_PHONE ( Emp_ID REFERENCES EMPLOYEE(Emp_ID), Phone_No,
PRIMARY KEY (Emp_ID, Phone_No) );
-- Derived: keep DOB, compute Age at query time
SELECT Roll_No, Name, FLOOR(DATEDIFF(CURRENT_DATE, DOB)/365.25) AS Age FROM STUDENT;Why a Multivalued Attribute Needs Its Own Table
Storing "9876…, 9123…" in a single Phone_No cell violates first normal form and breaks searching and updates. Lift it into a child table keyed by the owner instead.
Mapping 1:1 Relationships
No new table. Put one entity's primary key into the other as a foreign key — prefer the side with total participation to minimise NULLs.
CREATE TABLE PERSON ( Person_Id PRIMARY KEY, Name );
CREATE TABLE PASSPORT ( Pass_No PRIMARY KEY, Issue_Dt,
Person_Id REFERENCES PERSON(Person_Id) UNIQUE ); -- 1:1 → UNIQUE FKMapping 1:N Relationships
CREATE TABLE CUSTOMER ( Cust_No PRIMARY KEY, Name, City );
CREATE TABLE LOAN (
Loan_No PRIMARY KEY,
Amount,
Cust_No REFERENCES CUSTOMER(Cust_No) -- FK on the "many" side, repeats freely
);The one customer would need many loan numbers stuffed into a single cell — impossible. The foreign key always goes on the many side, where it can repeat.
Mapping M:N Relationships — Junction Table
CREATE TABLE ENROLLS (
Roll_No REFERENCES STUDENT(Roll_No),
Course_ID REFERENCES COURSE(Course_ID),
Grade, -- relationship attribute lives here
PRIMARY KEY (Roll_No, Course_ID) -- composite key blocks duplicate enrolments
);Mapping Weak Entities
"Payment 2" is ambiguous across loans — only "Payment 2 of Loan L1" is unique. The weak entity borrows the owner's key.
Payment_No repeats across loans, but the pairs (L1, 1) and (L2, 1) stay distinct. Omit the owner's key and identical partial keys collide.
Mapping n-ary Relationships
A relationship of degree 3 or more always becomes its own table, holding every participant's primary key as a foreign key, plus any descriptive attribute.
| Ternary Relationship | Resulting Table |
|---|---|
| DOCTOR – PATIENT – DRUG (PRESCRIBES) | PRESCRIBES(Doctor_ID FK, Patient_ID FK, Drug_ID FK, Dose) |
| SUPPLIER – PROJECT – PART (SUPPLIES) | SUPPLIES(Sup_ID FK, Proj_ID FK, Part_ID FK, Qty) |
An n-ary table is a junction table with three (or more) foreign keys. The combination of participant keys forms the primary key, and attributes like Dose or Qty belong to the whole triple.
A Complete University 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)Three Common Mistakes to Avoid
Ask: is it a strong entity, weak entity, multivalued attribute, M:N, or n-ary relationship? If yes → new table. Everything else is just a foreign key or a column.
Quick Reference — Every Construct
| ER Construct | Mapping Rule | New Table? |
|---|---|---|
| Strong entity | Table with key attribute as PK | Yes |
| Weak entity | PK = owner PK + partial key | Yes |
| Composite attr. | One column per leaf | No |
| Derived attr. | Omitted — computed on demand | No |
| Multivalued attr. | Child table keyed by owner | Yes |
| 1:1 relationship | FK on either side (prefer total) | No |
| 1:N relationship | FK on the many side | No |
| M:N relationship | Junction table with both PKs | Yes |
| n-ary relationship | Table with all participants' PKs | Yes |
Golden Rules of Mapping — 1 to 3
Golden Rules of Mapping — 4 to 6
Boxes first, arrows second. Give every table a key, put the FK on the many side, and give M:N and n-ary their own tables — do that and any ER diagram converts cleanly.
From Diagram to Database
You can now take any ER diagram and produce a correct set of tables — strong and weak entities, all three relationship types, n-ary relationships, and every attribute flavour. Next comes making those tables well-formed: functional dependencies and normalization (1NF → BCNF).
Entities → tables, attributes → columns, keys → primary keys, relationships → foreign keys — and only M:N, n-ary, weak entities and multivalued attributes earn a table of their own.
🔄 End of tutorial · Press ← to review, or click Restart