S5 ICT | Database Normalisation (1NF, 2NF, 3NF): Tutor Prep Guide (45-minute flow, script, common errors)

2026-11-15

Tutor lesson-prep guides · Secondary ICT

Students can recite the three normal forms, then meet a 500-row order table and cannot say which column has to move. This lesson turns three names into three questions asked column by column.

Objectives — By the end of the lesson students can: (1) state that 1NF requires one atomic value per cell and split two phone numbers into two rows; (2) spot a partial dependency under a composite key and explain that item name depends on item ID alone; (3) spot a transitive dependency and move customer name out of the orders table; (4) split a 500-row order table into customers with 20 rows, items with 50, orders with 120 and order lines with 500, joined back by foreign keys.

01 Prerequisites (3-minute check)

If any of these fails, fix it before the lesson starts.

Skill	Quick check	Pass mark
Primary key	Which column identifies one row of an order table	Order ID
Functional dependency	Once the item ID is fixed, can the item name still change	No, the item ID determines it
Percentages	Only 20 customer names appear in 500 rows, so what share is duplicated	96 per cent, that is 480 rows
Composite key	What does order ID plus item ID together mean	Only both together identify a row

02 Lesson flow (45 minutes)

Time	Stage	Tutor does	Student does
0-6 min	Entry check	Ask about the primary key, functional dependency and the duplication share; keep answers on the right of the board	Spoken answers, working on paper
6-16 min	Act one: three anomalies, one cause	A 500-row order table with 20 customers, each name appearing 25 times; walk through an address change, a cancelled order and a new customer	Students describe each anomaly
16-26 min	Act two: 1NF and 2NF	Two phone numbers in one cell need two rows; under a composite key the item name depends on item ID alone, so items move out	Students name the column that breaks 2NF
26-36 min	Act three: 3NF and foreign keys	Order ID determines customer ID, which determines customer name; that is transitive, so customers move out	Students name the four tables and two foreign keys
36-42 min	Question types and a split exercise	The same table at 800 rows, checked with the same three questions	Students hand in a table diagram
42-45 min	Exit check	Three spoken questions; note which act each error comes from	All three correct

03 Teaching script (read it out)

Scene 1: Act one: three anomalies, one cause

Picture a table where each row is one order line: order ID, customer ID, customer name, customer address, item ID, item name, unit price, quantity. Only 20 customers appear across 500 rows, so each name is written about 25 times and 480 rows carry a duplicate, which is 96 per cent. The first problem is an update anomaly: when a customer moves, you must change 25 rows, and one missed row leaves two addresses for one person. The second is a delete anomaly: cancel their last order and deleting the row deletes the customer too. The third is an insert anomaly: a new customer with no order yet has nowhere to live. All three come from one cause, the same fact stored many times.

If a customer changes address, how many rows must you change, and what happens if you miss one?

Scene 2: Act two: 1NF is one value per cell, 2NF is do not depend on half a key

First normal form is the easy one: one value per cell. Two phone numbers in one cell must become two rows or a separate phone table. Second normal form asks you to look at a composite key. The order lines table is keyed by order ID plus item ID, yet the item name and unit price are decided by item ID alone, with the order playing no part. That is a partial dependency: a non-key column depending on only part of the key. The fix is to move item ID, item name and unit price into an items table, so 50 products are stored 50 times rather than 500.

Customer name depends on customer ID rather than on order ID, so is that a 2NF problem?

Scene 3: Act three: 3NF is do not depend through a second hop

After 2NF the orders table holds order ID, customer ID, customer name and customer address. There is a chain: order ID determines customer ID, and customer ID determines customer name. Customer name is not decided directly by the key but one hop away, which is a transitive dependency and breaks third normal form. The fix is a customers table holding customer ID, name, phone and address. That leaves four tables: customers 20 rows, items 50, orders 120, order lines 500. The order lines table carries two foreign keys, order ID and item ID, and joining the tables returns the original 500 rows with nothing lost.

Is the customer name in the orders table decided directly by order ID, or one hop away?

04 Question types and worked steps

Type	Example	Steps	Answer
Judge 1NF	A phone cell holding 9123 4567 and 6234 5678	One atomic value per cell, so multi-values split into rows or a new table	Two phone rows, or a customer phone table
Judge 2NF	Key is order ID plus item ID, and the row holds item name and price	Item name depends on item ID alone, a partial dependency	An items table with item ID, name and price, 50 rows
Judge 3NF	The orders table holds customer name and address	The key decides customer ID, which decides the name, a transitive dependency	A customers table with ID, name and address, 20 rows
Write the foreign keys	What columns must order lines keep so the tables join back	Two foreign keys plus the quantity	Order ID, item ID and quantity

Sanity check (teach this): Three checks: (1) join the four tables back and the result must still be 500 rows, so one row short means a missing foreign key; (2) ask of every non-key column how many rows a change would touch, and anything above one means duplication remains; (3) more tables is not better, four is right here, while seven means six joins per query and no time left in the exam.

05 Independent practice (3 questions)

Question	Answer	What to watch
500 order lines and 20 customers: how many times is a customer name duplicated	480 times, that is 500 minus 20	Check the subtraction rather than a multiplication
Before normalisation an address change touches 25 rows; after it, how many	One row, in the customers table	Check that removing duplication removes the repeated edit
120 orders and 500 order lines: what is the mean number of lines per order	4.17 lines	500 divided by 120, and a decimal is acceptable

06 Common mistakes and what to say

Mistake	Student writes	Say this
Splitting into as many tables as possible	Seven tables, so every query needs six joins	Normalisation exists to remove anomalies, not to maximise table count. Stop when 3NF leaves no anomaly, because splitting further only makes queries harder.
Confusing 2NF with 3NF	Calling customer name a 2NF problem	Customer name depends on customer ID, not on part of the composite key, so it is a 3NF transitive dependency. Ask which column it depends on first.
Dropping the foreign key	Splitting order lines out but keeping no order ID	After splitting, always ask which column joins the tables back. Without a foreign key the 500 rows become fragments that cannot be rejoined.
Reading 1NF as no blanks	Treating an empty phone cell as a 1NF breach	1NF is about one atomic value per cell, so multi-values need rows. A blank is a missing value, which is a different problem.

07 Homework (paper, section and question numbers)

S5 ICT Practice Paper 1, database section — Section C, questions 9 to 12

08 Exit check (last 5 minutes)

Question	Answer	Decision
500 rows and 20 customers: how many duplicate names	480	Wrong: reteach act one, counting the duplicates
A composite-key table holding an item name breaks which normal form	2NF, a partial dependency	Wrong: reteach act two, depending on half the key
The orders table holding a customer name breaks which normal form	3NF, a transitive dependency	Wrong: reteach act three, the key decides A and A decides B

09 Tutor FAQ

Which year level is this for?

Hong Kong S5 ICT asks students to design related tables and justify normalisation. The S4 work on primary keys, field types and simple queries is the prerequisite, so revise the idea that one column determines another before this lesson.

Is 45 minutes enough?

Yes, provided act three gets ten minutes. Students stall on telling 2NF from 3NF, so keep both questions on the board: does it depend on part of the key, and does it depend through a second hop?

Must students memorise the definitions?

No. The exam asks them to judge and to split tables, so three actions are enough: one value per cell, no depending on half a key, no depending through a second hop.

Find a tutor — free Tutor Registration →

A TutorZone lesson-prep guide. Written by the TutorZone team; questions and lesson flow are original and are not taken from any school or publisher material.