Database Normalisation — Practice Activities

🔑 Game 1: Spot the Primary Key

Click the column header that would make the best primary key. Remember: it must be unique for every row and never empty.

Table A — Gym members

MemberID	FirstName	Town
M01	Sam	Leeds
M02	Sam	York
M03	Priya	Leeds

Table B — Books

Title	Author	ISBN
Dracula	Stoker	978-01
Dracula	Stoker	978-02
Holes	Sachar	978-03

Table C — Flights today

Destination	FlightNo	Gate
Paris	BA204	12
Paris	EZ881	12
Rome	BA377	7

🧠 Game 2: Key Knowledge Quiz

Pick the right answer. You only get one try per question, so think carefully! 🤔

Q1. What is a primary key?

Q2. What is a foreign key?

Q3. In the Students table, FormID points at the FormGroups table. In the Students table, FormID is a…

Q4. Why do we normalise a database?

🕵️ Game 3: 1NF Detective

Is each table in 1NF? Remember the rule: one box, one value!

Table A

🔑 PetID	PetName	Vaccines
P1	Rex	Rabies, Flu
P2	Milo	Flu

Table B

🔑 PetID	🔑 Vaccine	DateGiven
P1	Rabies	3 May
P1	Flu	9 May
P2	Flu	9 May

Table C

🔑 TripID	Destination	StudentsGoing
T1	Museum	Amy, Ben, Cara
T2	Zoo	Ben

🔨 Game 4: Click the Broken Cell

One cell in this table breaks the 1NF rule. Click it!

🔑 BandID	BandName	Instruments
B1	The Loops	Guitar, Drums, Bass
B2	Array of Sunshine	Keyboard

🗂️ Game 5: Sort the Statements

Click a statement card, then click the normal form it belongs to. Sort all six! 🃏

"One cell held 3 phone numbers — I split them into rows" "TeacherName depended on FormGroup, not on the student key" "SnackPrice only depended on PART of a two-column key" "Every cell now holds exactly one value" "Facts must be about the WHOLE key" "Facts must be about the key and NOTHING ELSE"

🥇 1NF

🥈 2NF

🥉 3NF

👾 Game 6: Final Boss — Which Normal Form?

Look at each table and decide the highest normal form it has reached.

The Hobbies column holds "Chess, Football" in one cell.

🔑 StudentID	Name	Hobbies
001	Amy	Chess, Football
002	Ben	Chess

Key = OrderID + ItemID together. But ItemName depends only on ItemID.

🔑 OrderID	🔑 ItemID	Qty	ItemName
O1	I5	2	Pencil
O2	I5	1	Pencil

Single-column key; every other column is a fact about that key only.

🔑 StudentID	Name	DateOfBirth
001	Amy	2010-04-12
002	Ben	2010-11-03

DoctorPhone depends on DoctorName — an ordinary column, not the key. Which rule does this break?

🔑 ApptID	Patient	DoctorName	DoctorPhone
A1	Sam	Dr Wu	555-2000
A2	Priya	Dr Wu	555-2000

🎮 Practice Activities