← WA Computer Science ATAR Unit 2

Third normal form (3NF)

A table in 2NF where no non-key field depends on another non-key field. Every field depends on the key, the whole key and nothing but the key.

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

Understanding Third normal form (3NF) in WA Computer Science ATAR Unit 2

What it does: A non-key determinant such as Postcode breaks the 'nothing but the key' rule, so its dependent field is split out. Normalisation is a sequence: partial dependencies are removed at 2NF, then transitive dependencies at 3NF. That is the definition of 3NF. It builds on 2NF and then removes dependencies between non-key fields. Postcode determines Suburb and neither is the key. This is a transitive dependency, so Postcode and Suburb should move to their own table. 1NF is about the key, 2NF is about the whole key, and 3NF adds 'nothing but the key', so non-key fields cannot depend on each other.

Common mistakes: suburb depends on postcode, which is a non-key field. That is a transitive dependency, so suburb must not stay in Customer. Line 3 should be 'Fields: name, postcode'. suburb depends on studentId only through postcode (studentId -> postcode -> suburb). That is a transitive dependency, not a direct one. Line 3 should say suburb depends via postcode.

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 Third normal form (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 is in 3NF when it is in 2NF and no non-key field depends on another non-key field.
TrueFalse
Matches
Customer(CustomerID, Name, Postcode, Suburb) -- already in 3NF
↓
Suburb depends on Postcode, a non-key field, so this is a transitive dependency and the table is not in 3NF
Applying
Meanings
Term
3NF
Meaning
A table in 2NF where no non-key field depends on another non-key field. Every field depends on the key, the whole key and nothing but the key.
Practice
Customer(CustomerID, Name, Postcode, Suburb)
Testing
Gaps
Postcode Suburb
Check Answer
Spots
2Key: customerId
3Fields: name, postcode, suburb

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

The 'Sperm Whale' badge — Deep Sea Champion, 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 is in 3NF when it is in 2NF and no non-key field depends on another non-key field.
A. True ✓
B. False
That is the definition of 3NF. It builds on 2NF and then removes dependencies between non-key fields.
2. Customer(CustomerID, Name, Postcode, Suburb) where Postcode determines Suburb. Which statement is correct?
A. Suburb depends on CustomerID only, so the table is already in 3NF
B. Suburb depends on Postcode, a non-key field, so the table is not in 3NF ✓
Postcode determines Suburb and neither is the key. This is a transitive dependency, so Postcode and Suburb should move to their own table.
3. Which phrase sums up 3NF?
A. The key, the whole key and nothing but the key ✓
B. The key and only part of the key
1NF is about the key, 2NF is about the whole key, and 3NF adds 'nothing but the key', so non-key fields cannot depend on each other.
4. What is a transitive dependency?
A. Key → non-key field A → non-key field B ✓
B. Non-key field that depends on only part of a composite key
In a transitive dependency, B depends on the key only through A, another non-key field. Depending on part of a composite key is a partial dependency, which 2NF removes.
5. Student(StudentID, Name, HouseID, HouseColour) where HouseID determines HouseColour. How do you fix it to reach 3NF?
A. Delete the HouseColour field and do not store it anywhere
B. Move HouseID and HouseColour into a new House table and keep HouseID in Student as a foreign key ✓
Splitting into a new table removes the transitive dependency without losing data. HouseID stays in Student as a foreign key to link the tables.

Match the Pairs

StudentID → Postcode, Postcode → Suburb → A transitive dependency: Suburb depends on the key only through Postcode, a non-key field, so the table breaks 3NF
Student(StudentID, Name, Postcode) Postcode(Postcode, Suburb) → The result of removing the transitive dependency, so each non-key field depends only on its own table's key
3NF means every field depends on the key, the whole key and nothing but the key → Postcode → Suburb (determinant is not the key, so move it to a new table)
A table must already be in 2NF before it can be in 3NF → 1NF → 2NF (remove partial dependencies) → 3NF (remove transitive dependencies)
Customer(CustomerID, Name, Postcode, Suburb) -- already in 3NF → Suburb depends on Postcode, a non-key field, so this is a transitive dependency and the table is not in 3NF
Removing Suburb from Customer and deleting it, without creating a Postcode table → The Postcode to Suburb data is lost; the dependent field must move to a new table with Postcode as its primary key

Key Term

3NF
A table in 2NF where no non-key field depends on another non-key field. Every field depends on the key, the whole key and nothing but the key.

Fill in the Blanks

1. Postcode -> Suburb -- postcode determines suburb
Hint: Type the symbol that shows one field determines another.
2. CREATE TABLE Suburb ( Postcode CHAR(4) PRIMARY KEY, SuburbName VARCHAR(30));
Hint: Type the constraint that makes Postcode the key of the new table.
3. Key, the whole key and nothing but the key
Hint: Type the word that completes the 3NF rule: no other non-key fields allowed.
4. StudentID -> Postcode -> Suburb This is a transitive dependency
Hint: Type the word for a dependency that passes through a non-key field.
5. SELECT Customer.Name, Suburb.SuburbName FROM Customer JOIN Suburb ON Customer.Postcode = Suburb.Postcode;
Hint: Type the table-qualified primary key that the foreign key matches.

Spot the Error

Question 1
A Table: Customer
B Key: customerId
C Fields: name, postcode, suburb
D Moved to Postcode: suburb
Line C — suburb depends on postcode, which is a non-key field. That is a transitive dependency, so suburb must not stay in Customer. Line 3 should be 'Fields: name, postcode'.
Question 2
A studentId -> postcode
B postcode -> suburb
C suburb depends directly on key
D So the table is not in 3NF
Line C — suburb depends on studentId only through postcode (studentId -> postcode -> suburb). That is a transitive dependency, not a direct one. Line 3 should say suburb depends via postcode.
Question 3
A SELECT postcode, suburb
B FROM Customer
C WHERE suburb = 'Fremantle';
Line B — After 3NF, suburb is stored in the Postcode table, not in Customer. The query must read FROM Postcode.
Question 4
A SELECT c.name, p.suburb
B FROM Customer c, Postcode p
C WHERE c.postcode=p.suburb;
Line C — The foreign key Customer.postcode must be matched to the primary key Postcode.postcode. Comparing it with suburb gives wrong or no matches. It should be p.postcode.
Question 5
A Table is in 2NF
B Check key fields for links
C postcode -> suburb is found
D Move suburb to a new table
E Table is now in 3NF
Line B — 3NF removes dependencies between non-key fields. So you check the non-key fields for links to each other, not the key fields.

Student with a class code? Join your class →