The big idea: Normalising is not guesswork. It is the same four steps on any scenario.
List the data, make it atomic, remove partial dependencies, remove transitive ones. Follow them in order and 3NF falls out.
Do it on paper first: Write out a few sample rows with real values before touching tables.
Dependencies are much easier to see in actual data than in a list of column names.
Free preview
This is the free notes preview
You're reading the free notes. Aimnova Pro unlocks the full study experience — and you can try it with your first topic free to keep:
- FlashcardsLock in vocabulary and key terms with spaced repetition.
- Practice questionsAnswer exam-style questions and get instant AI marking.
- Mock exams & past-paper vaultSit full mocks and see exactly how examiners award marks.
- Personalised study planA daily plan built around your exam date and weak areas.
The four steps run end to end on a school scenario. Follow the same route on any other.
Interactive diagram
Explore the labelled diagram, charts and maps for this topic in full study mode.
1 — List every piece of data
Read the scenario and write down every fact it mentions, in one wide table with a few sample rows.
Do not organise yet. You are only making sure nothing is missing.
2 — Reach 1NF
Split any cell holding a list into separate rows, and remove repeating groups of columns.
Then decide the primary key. If no single column is unique, it becomes composite — which is where step 3 starts to matter.
3 — Reach 2NF
Take each non-key column and ask: does it depend on the whole key, or just part of it?
Anything depending on part moves out, into a table keyed on that part. Skip this step entirely if the key is a single column.
4 — Reach 3NF
In every table, ask whether any non-key column determines another non-key column.
If so, both move to a new table, and the determining column stays behind as a foreign key.
Then check it backwards: Take a fact you know the scenario needs — "which room does Mr Pinto use" — and follow the keys to find it.
If you cannot get there, a link is missing. If you find it twice, you are not in 3NF yet.
Feeling unprepared for exams?
Get a clear study plan, practice with real questions, and know exactly where you stand before exam day. No more guessing.
The scenario: The hospital records which doctor saw which patient, on what date, in which ward, and which consultant heads that ward.
One wide table, and a few sample rows to reason over.
The unnormalised table
PatientID, PatientName, DoctorID, DoctorName, VisitDate, WardID, WardName, ConsultantName.
A patient seen three times appears on three rows, with their name and ward written out each time.
1NF and the key
Every cell already holds one value. No single column identifies a row: the same patient can be seen twice, and the same doctor sees many patients.
The key becomes PatientID, DoctorID and VisitDate together.
2NF — the partial dependencies
PatientName depends on PatientID alone; DoctorName on DoctorID alone. Both are parts of the key, so both move out.
Left behind: a Visit table of PatientID, DoctorID, VisitDate and WardID.
3NF — the transitive dependency
WardName and ConsultantName depend on WardID, which is not a key of the Visit table.
So a Ward table takes WardID, WardName and ConsultantName, and Visit keeps WardID as a foreign key.
The finished design: Patient (PatientID, PatientName) · Doctor (DoctorID, DoctorName) · Ward (WardID, WardName, ConsultantName) · Visit (PatientID, DoctorID, VisitDate, WardID).
Every fact now appears once. Change a ward's consultant and you change one row.
How this is tested — you must show the tables at each stage, with the keys marked. It comes up two ways:
Paper 1 Section A
- Construct a 3NF design from a scenario, 5-8 marks
- Show the tables and identify every key
- Justify a move you made
Paper 1 Section B — case study
- Normalise the case study's data
- Explain what problem each step removed
The classic trap: Jumping straight to a set of tables. Marks are awarded for the stages — showing 1NF, then 2NF, then 3NF — so a correct final answer with no working can still lose most of them.
A shop records: OrderID, OrderDate, CustomerID, CustomerName, ProductID, ProductName, UnitPrice, Quantity. Construct a design in 3NF.
Model answer plan
See the answer broken into clear points, with guidance on how each point earns marks.