Database Normalisation — Walkthrough Examples

📚 Example 1: The School Library

The librarian keeps everything in one giant table. Every time a student borrows books, she adds a row:

Breaks a rule Repeated data

Table: Loans (the giant messy one 😱)
StudentID	StudentName	FormGroup	FormTeacher	BooksBorrowed
001	Amy	10B	Ms Lopez	Dracula, Frankenstein
002	Ben	10A	Mr Khan	Dracula
003	Cara	10B	Ms Lopez	Frankenstein, Holes, Wonder

Let's fix it — one normal form at a time. 💪

Step 1 Get to 1NF — one box, one value

🕵️ Spot the problem: the BooksBorrowed column has whole lists inside single cells. Amy's cell holds two books!

🤔 Think first: how would YOU fix it? Then click to reveal.

Give each borrowed book its own row. Yes, we repeat the student info for now — we'll deal with that in the next steps!

⬇️

✅ Now in 1NF

Table: Loans
🔑 StudentID	🔑 Book	StudentName	FormGroup	FormTeacher
001	Dracula	Amy	10B	Ms Lopez
001	Frankenstein	Amy	10B	Ms Lopez
002	Dracula	Ben	10A	Mr Khan
003	Frankenstein	Cara	10B	Ms Lopez
003	Holes	Cara	10B	Ms Lopez
003	Wonder	Cara	10B	Ms Lopez

Every cell holds one value ✅. The key is now StudentID + Book together (each pairing appears once).

⚠️ But look at all that pink repeated data! Amy's details are typed once per book. 1NF is only level one — onwards!

Step 2 Get to 2NF — facts about the WHOLE key

🕵️ Spot the problem: our key is StudentID + Book. Ask of every other column: "do you need BOTH parts of the key, or just one?"

🙋 StudentName — depends only on StudentID. Partial! ❌
🙋 FormGroup — depends only on StudentID. Partial! ❌
🙋 FormTeacher — depends only on StudentID. Partial! ❌

🤔 Think first: what should we do with those columns? Then click to reveal.

Move all the student facts into their own Students table, and leave the Loans table to record just who borrowed what.

⬇️

✅ Now in 2NF — two tables

Table: Loans
🔑🔗 StudentID	🔑 Book
001	Dracula
001	Frankenstein
002	Dracula
003	Frankenstein
003	Holes
003	Wonder

➡️

Table: Students
🔑 StudentID	StudentName	FormGroup	FormTeacher
001	Amy	10B	Ms Lopez
002	Ben	10A	Mr Khan
003	Cara	10B	Ms Lopez

Amy's name now appears once, no matter how many books she borrows. The 🔗 StudentID in Loans links back to the Students table.

⚠️ Almost there… but "Ms Lopez" still appears twice. Why? Because FormTeacher is really a fact about the FormGroup, not about the student. That's a job for 3NF!

Step 3 Get to 3NF — nothing but the key

🕵️ Spot the problem: in the Students table, FormTeacher depends on FormGroup (an ordinary column) — not on StudentID (the key). Every 10B student automatically has Ms Lopez.

🤔 Think first: how do we fix it? Then click to reveal.

Give form groups their own table. Students keep just the FormGroup code as a link.

⬇️

✅ Now in 3NF — three tables, fully tidy! 🎉

Table: Loans
🔑🔗 StudentID	🔑 Book
001	Dracula
001	Frankenstein
002	Dracula
003	Frankenstein
003	Holes
003	Wonder

➡️

Table: Students
🔑 StudentID	StudentName	🔗 FormGroup
001	Amy	10B
002	Ben	10A
003	Cara	10B

➡️

Table: FormGroups
🔑 FormGroup	FormTeacher
10A	Mr Khan
10B	Ms Lopez

Every fact lives in exactly one place:

📕 Who borrowed what → Loans
🙋 Who each student is → Students
🏫 Who teaches each form → FormGroups

🍕 Example 2: The Pizza Shop (quicker run-through)

Try to spot each problem before opening the answer!

Table: PizzaOrders (messy!)
OrderID	CustomerName	CustomerPhone	PizzasOrdered
P100	Dev	555-1234	Margherita, Pepperoni
P101	Ella	555-9876	Hawaiian
P102	Dev	555-1234	Pepperoni

🥇 1NF check: what's wrong? (click to reveal)

PizzasOrdered holds lists! Split them so each pizza on each order gets its own row. The key becomes OrderID + Pizza.

🔑 OrderID	🔑 Pizza	CustomerName	CustomerPhone
P100	Margherita	Dev	555-1234
P100	Pepperoni	Dev	555-1234
P101	Hawaiian	Ella	555-9876
P102	Pepperoni	Dev	555-1234

🥈 2NF check: which columns depend on only PART of the key? (click to reveal)

CustomerName and CustomerPhone depend only on OrderID — not on which pizza. Move order facts to an Orders table:

OrderLines
🔑🔗 OrderID	🔑 Pizza
P100	Margherita
P100	Pepperoni
P101	Hawaiian
P102	Pepperoni

➡️

Orders
🔑 OrderID	CustomerName	CustomerPhone
P100	Dev	555-1234
P101	Ella	555-9876
P102	Dev	555-1234

🥉 3NF check: is any column describing another ordinary column? (click to reveal)

CustomerPhone depends on CustomerName (well — on the customer!), not on the OrderID. Dev's phone number is stored twice. Give customers their own table:

Orders
🔑 OrderID	🔗 CustomerID
P100	C1
P101	C2
P102	C1

➡️

Customers
🔑 CustomerID	CustomerName	CustomerPhone
C1	Dev	555-1234
C2	Ella	555-9876

🎉 Done! Three linked tables (OrderLines ➡️ Orders ➡️ Customers), every fact stored once.

🧭 Your normalisation checklist

Use these three questions on ANY table, in order:

Step	Ask yourself…	If YES…
🥇 1NF	Is there a list squashed into one cell?	Split it into separate rows
🥈 2NF	Does any column depend on only part of a two-part key?	Move it to its own table
🥉 3NF	Does any column describe another ordinary column instead of the key?	Move it to its own table

🧠 "The key, the whole key, and nothing but the key!"