← WA Computer Science ATAR Unit 2

Normalising a table step by step to 3NF

Take a table through 1NF, 2NF, then 3NF by removing repeating groups, then partial dependencies, then transitive dependencies, giving each new table its own key.

Part of: WA Computer Science ATAR Year 11 — Data Management — Module 48

Understanding Normalising a table step by step to 3NF in WA Computer Science ATAR Unit 2

What it does: HouseName and HeadOfHouse depend on HouseID, not directly on StudentID. Moving them to a House table with HouseID as its primary key removes the transitive dependency. ProductID determined ProductName and Price, so it is the primary key of Product. It stays in OrderLine as part of the key and as a foreign key linking back to Product. 1NF removes repeating groups so every field holds one value. Phone numbers move to their own rows or table. Each form builds on the one before. A table must be in 1NF before 2NF and in 2NF before 3NF. With a single-field key, every non-key field depends on the whole key. Partial dependencies only occur with composite keys.

Common mistakes: The first step, 1NF, removes repeating groups such as Item1 and Item2. Transitive dependencies are removed later, in 3NF. The line should read '1NF: remove repeating groups'. 2NF removes partial dependencies. Here ItemName and Price depend on only part of the composite key (ItemID). Transitive dependencies are removed in 3NF. The line should read '2NF: remove partial deps'.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Normalising a table step by step to 3NF to a class →

This module's key terms are also in a printable crossword (Modules 28–122) and word search (Modules 28–266).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A table has a Phone1, Phone2, Phone3 group of columns for each student. Which normal form step removes this?
3NF: remove the transitive dependency1NF: remove the repeating group
Matches
Student(StudentID, Name, HouseID, HouseName) so the table is now in 3NF
↓
HouseName depends on HouseID, which is not the key. This transitive dependency means the table is not in 3NF.
Applying
Meanings
Term
Normalise to 3NF
Meaning
Take a table through 1NF, 2NF, then 3NF by removing repeating groups, then partial dependencies, then transitive dependencies, giving each new table its own key.
Practice
UNF: Order(OrderID, CustID, CustName, {ProductID, ProductName, Qty})
Testing
Gaps
Removing repeating groups gives
Check Answer
Spots
1UNF: Ord(ID, Item1, Item2)
21NF: remove transitive deps

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Jellyfish' badge — Flow Controller, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A table has a Phone1, Phone2, Phone3 group of columns for each student. Which normal form step removes this?
A. 1NF: remove the repeating group ✓
B. 3NF: remove the transitive dependency
1NF removes repeating groups so every field holds one value. Phone numbers move to their own rows or table.
2. A table has the composite key (OrderID, ProductID) and ProductName depends only on ProductID. Which step fixes this?
A. 1NF: remove the repeating group
B. 2NF: remove the partial dependency ✓
ProductName depends on only part of the composite key. 2NF moves ProductID and ProductName to a new Product table.
3. In a Student table, StudentID determines TeacherID and TeacherID determines TeacherName. Which problem is this?
A. A transitive dependency, fixed in 3NF ✓
B. A partial dependency, fixed in 2NF
TeacherName depends on StudentID only through TeacherID, which is a non-key field. That is transitive, so 3NF moves Teacher details to a new table.
4. What is the correct order to normalise a table?
A. 1NF, then 2NF, then 3NF ✓
B. 3NF, then 2NF, then 1NF
Each form builds on the one before. A table must be in 1NF before 2NF and in 2NF before 3NF.
5. When you split TeacherName out into a new Teacher table, what must the new table have?
A. No key, because it is only a lookup list
B. Its own primary key, such as TeacherID ✓
Every new table needs its own primary key. The original table keeps TeacherID as a foreign key to link back.

Match the Pairs

Order(OrderID, Item1, Item2, Item3) → An unnormalised table with a repeating group of item columns that must be removed to reach 1NF
OrderLine(OrderID, ProductID, ProductName, Qty) → ProductName depends only on ProductID, part of the composite key, so this has a partial dependency and fails 2NF
Step 3 to reach 3NF: remove a transitive dependency by moving the non-key determinant into its own table → Student(StudentID, Name, HouseID) House(HouseID, HouseName, HeadOfHouse)
Choosing keys for new tables: the attribute that determined the moved fields becomes the new table's primary key and stays as a foreign key in the old one → Product(ProductID, ProductName, Price) OrderLine(OrderID, ProductID, Qty)
Student(StudentID, Name, HouseID, HouseName) so the table is now in 3NF → HouseName depends on HouseID, which is not the key. This transitive dependency means the table is not in 3NF.
Normalise to 3NF: remove transitive dependencies first, then repeating groups, then partial dependencies → The steps are in the wrong order. Normalisation removes repeating groups (1NF), then partial dependencies (2NF), then transitive dependencies (3NF).

Key Term

Normalise to 3NF
Take a table through 1NF, 2NF, then 3NF by removing repeating groups, then partial dependencies, then transitive dependencies, giving each new table its own key.

Fill in the Blanks

1. Removing repeating groups gives 1NF
Hint: Type the normal form reached once repeating groups are removed.
2. Remove partial dependencies to reach 2NF
Hint: Type the normal form reached once partial dependencies on part of a composite key are removed.
3. OrderID -> CustID -> CustName is transitive
Hint: Type the kind of dependency where a non-key field depends on another non-key field, which 3NF removes.
4. OrderItem(OrderID, ProductID, Qty) PRIMARY KEY (OrderID, ProductID)
Hint: Type the second field that, with OrderID, identifies one row in the new table.
5. Customer(CustID, CustName) Order(OrderID, OrderDate, CustID)
Hint: Type the foreign key field that links each order to the new customer table.

Spot the Error

Question 1
A UNF: Ord(ID, Item1, Item2)
B 1NF: remove transitive deps
C OrderItem(OrderID, Item, Qty)
D PK: OrderID + Item
Line B — The first step, 1NF, removes repeating groups such as Item1 and Item2. Transitive dependencies are removed later, in 3NF. The line should read '1NF: remove repeating groups'.
Question 2
A Key: (OrderID, ItemID)
B ItemID -> ItemName, Price
C 2NF: remove transitive deps
D Item(ItemID, ItemName, Price)
Line C — 2NF removes partial dependencies. Here ItemName and Price depend on only part of the composite key (ItemID). Transitive dependencies are removed in 3NF. The line should read '2NF: remove partial deps'.
Question 3
A Stu(SID, Name, TutID, TutName)
B SID -> TutID -> TutName
C TutID is a non-key field
D New: Tutor(SID, TutName)
Line D — TutName depends on TutID, a non-key field, so it moves to a new table. The key of the new table must be the determinant TutID, not SID. The line should be 'New: Tutor(TutID, TutName)'.
Question 4
A Student(SID, Name, TutID)
B Tutor(TutID, TutName)
C ERD: Tutor many --- 1 Student
D FK: Student.TutID -> Tutor
Line C — One tutor looks after many students, and each student has one tutor. The 'one' side is Tutor and the 'many' side is Student. The line should read 'ERD: Tutor 1 --- many Student'.
Question 5
A Student(SID, Name, Phones)
B Problem: Name holds a list
C 1NF: one value per field
Line B — The field that holds a list of values is Phones, which breaks 1NF. Name holds a single value. The line should read 'Problem: Phones holds a list'.

Student with a class code? Join your class →