This is the written version of ER to relational mapping, taken from the lesson itself. The simulations, drag-and-drop activities and quizzes only work in the interactive lesson.
Every box, ellipse and diamond in an ER diagram maps to a specific relational structure. We’ll walk through all eight mapping rules and build a real schema together — with live simulations you can play with. No sign-in, nothing collected.
The big picture
One pipeline, eight rules
Mapping isn’t guesswork. You read the ER diagram, apply a deterministic set of rules to each construct, and out comes a clean set of relational tables.
ER diagram
Entities, attributes and relationships, drawn in Chen’s notation.
8 mapping rules
A fixed recipe: each construct has exactly one correct relational form.
Relational tables
Clean tables with primary keys and foreign keys, ready for SQL.
Interactive · the main event
The 8 Rules Explorer
Tap a rule to see the ER construct on the left turn into the exact relational table(s) on the right. These eight cover everything you’ll meet in a diagram.
Strong entity → Table
Each strong entity becomes a table. Every simple attribute becomes a column, and the key attribute becomes the PRIMARY KEY.
ER diagram
StudentId FirstName LastName
Relational tables
💡 Entity name → table · key attribute → PRIMARY KEY.
Interactive · relationships
The Cardinality Studio
The trickiest part of mapping is relationships. Flip between 1:1, 1:N and M:N and watch where the foreign key lands — and when a whole new junction table appears.
One department employs many employees, but each employee belongs to one department. Add the department’s key as a foreign key on the many-side — no extra table required.
🔗 One FK is added to the many-side
Resulting schema
🔗 dept_id INT FK→DEPARTMENT
Interactive · attributes
Build the table, attribute by attribute
Attributes come in flavours — simple, composite, multivalued and derived — and each maps differently. Classify each one and watch the STUDENT schema assemble itself.
For each attribute of STUDENT, choose how it maps into the schema.
Name (First, Last)composite
Schema so far
Interactive · worked example
Map a university enrolment diagram
Put the rules together on a real example. Step through entities, a composite address, a 1:N link and an M:N enrolment to reach the complete four-table schema.
Three strong entities — STUDENT, MODULE and DEPARTMENT — each become a table with its key as PRIMARY KEY.
Step 1 of 4
Watch out
Four classic mistakes
Most mapping errors come down to these. Here’s the wrong move and the fix for each.
❌ Wrong
Storing a derived "age" column that goes stale every birthday.
✅ Right
Store date_of_birth and compute age with DATEDIFF() when needed.
One "address VARCHAR(200)" column for a composite Address.
Flatten into street_name, city and post_code — each queryable.
Putting both FKs of an M:N inside one of the entity tables.
Always create a junction table holding both foreign keys.
Adding the FK on the "1" side of a 1:N relationship.
The FK always lives on the many-side of a 1:N.
Interactive · put it together
Mapping Detective
Read each scenario and pick the correct mapping. This is exactly the reasoning you’ll use designing real schemas.
A CUSTOMER can place many ORDERs; each ORDER is placed by exactly one CUSTOMER.
How do you map it?
At a glance
The 8 rules, side by side
One line per rule — the construct and what it becomes.
Strong entity → Table
Entity name → table · key attribute → PRIMARY KEY.
Composite attribute → Flatten
Break the composite into one column per sub-attribute.
Multivalued attribute → New table
Each repeating value gets its own row in a new table.
1:N relationship → FK on the N-side
The FK always goes on the MANY side.
M:N relationship → Junction table
M:N always becomes a third, junction table.
1:1 relationship → FK choice
FK on the mandatory side — or merge if always together.
Weak entity → Composite PK
Partial key + owner key → composite primary key.
Derived attribute → Do NOT store
If it can be calculated, don’t store it.
Check yourself
Five quick questions
See how much of the lesson stuck.
In a 1:N relationship, the foreign key goes on the “many” side.
