← QCE Digital Solutions Unit 2

Third normal form (3NF) (Prototype Data Solutions)

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

Part of: QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 23

Understanding Third normal form (3NF) (Prototype Data Solutions) in QCE Digital Solutions Unit 2

What it does: houseCaptain depends on houseId, which is a non-key field, and only reaches studentId through it. That is a transitive dependency. The house fields are removed from Student and placed in a new House table, with houseId as the primary key and the foreign key in Student. That is the definition of 3NF. It builds on 2NF and also removes dependencies between non-key fields. In 3NF every non-key field must depend on the key, the whole key and nothing but the key. 2NF only removes partial dependencies on part of a composite key. Transitive dependencies are removed at 3NF. The foreign key keeps the link between the tables, so the author details can still be found without being repeated.

Common mistakes: The primary key must identify each student. houseName is a house detail, not a student detail. Every field must depend on the key, so the key should be studentId. suburb depends on postcode, which is a non-key field. That is a transitive dependency, so the table breaks 3NF. The suburb belongs in a separate Postcode table, so this line should be name TEXT.

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) (Prototype Data Solutions) to a class →

This module's key terms are also in a printable crossword (Modules 305–336) and word search (Modules 234–409).

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.

FalseTrue

Matches

Student(studentId, name, houseId, houseCaptain)

↓

houseCaptain depends on houseId (a non-key field), not on studentId. This is a transitive dependency, so 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 (no transitive dependency). Every field depends on the key, the whole key and nothing but the key.

Practice

-- HouseHead depends on HouseID, not StudentID

Testing

Gaps

houseId INTEGER ,

Check Answer

Spots

1CREATE TABLE Student (

2 houseName TEXT PRIMARY KEY,

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

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 also removes dependencies between non-key fields.

2. Students(StudentID, Name, HomeroomID, HomeroomTeacher). HomeroomTeacher depends on HomeroomID. Which describes this?

A. Transitive dependency, so it breaks 3NF ✓

B. Every field depends on the key, so it meets 3NF

HomeroomTeacher depends on HomeroomID, which is a non-key field. That is a transitive dependency. Move the teacher into a Homeroom table.

3. What is a transitive dependency?

A. A non-key field depends on the primary key

B. A non-key field depends on another non-key field ✓

A field depending on the primary key is what we want. A transitive dependency goes key to non-key field to another non-key field.

4. Which phrase sums up 3NF?

A. The key, the whole key and nothing but the key ✓

B. The key and anything else that is handy

In 3NF every non-key field must depend on the key, the whole key and nothing but the key.

5. Orders(OrderID, ProductID, ProductName, Price). ProductName depends on ProductID. How do you fix this to reach 3NF?

A. Add more rows to the Orders table

B. Move ProductName and Price into a Products table ✓

Fields that depend on a non-key field (ProductID) move to their own table. ProductID stays in Orders as a foreign key.

Match the Pairs

3NF → A table in 2NF where no non-key field depends on another non-key field

Student(studentId, name, houseId) → 3NF: every non-key field depends on the key, and house details are held in a separate House table

A transitive dependency is when a non-key field depends on another non-key field, not directly on the primary key → studentId -> houseId -> houseCaptain

To reach 3NF, move the fields that depend on a non-key field into their own table, linked by a foreign key → House(houseId, houseName, houseCaptain)

Student(studentId, name, houseId, houseCaptain) → houseCaptain depends on houseId (a non-key field), not on studentId. This is a transitive dependency, so the table is not in 3NF.

Product(productId, name, categoryId, categoryName) → categoryName depends on categoryId, which is a non-key field. Storing it here repeats the category name and breaks 3NF.

Key Term

3NF

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

Fill in the Blanks

1. CREATE TABLE House ( houseId INTEGER PRIMARY KEY, houseHead TEXT);

Hint: Type the keyword pair that makes houseId the key of the new table, so houseHead depends only on the key.

2. CREATE TABLE Student ( studentId INTEGER PRIMARY KEY, houseId INT REFERENCES House(houseId));

Hint: Type the keyword that links this column to the key of the House table.

3. SELECT Student.firstName, House.houseHead FROM Student JOIN House ON Student.houseId = House.houseId;

Hint: Type the table-qualified primary key that the foreign key must equal in the join.

4. ALTER TABLE Student DROP COLUMN houseHead;

Hint: Type the keywords that remove a field which depends on another non-key field (a transitive dependency).

5. Student(studentId, firstName, houseId) House(houseId, houseHead)

Hint: Type the key field of House that houseHead depends on, so every field depends on its table's key.

Spot the Error

Question 1

A CREATE TABLE Student (

B houseName TEXT PRIMARY KEY,

C name TEXT,

D houseId INT

E );

Line B — The primary key must identify each student. houseName is a house detail, not a student detail. Every field must depend on the key, so the key should be studentId.

Question 2

A CREATE TABLE Customer (

B custId INT PRIMARY KEY,

C suburb TEXT,

D postcode TEXT

E );

Line C — suburb depends on postcode, which is a non-key field. That is a transitive dependency, so the table breaks 3NF. The suburb belongs in a separate Postcode table, so this line should be name TEXT.

Question 3

A SELECT c.name, p.suburb

B FROM Customer c

C JOIN Customer p

D ON c.postcode = p.postcode;

Line C — After moving to 3NF, suburb is stored in the Postcode table. The query must join Postcode p, not Customer again.

Question 4

A CREATE TABLE Student (

B studentId INT PRIMARY KEY,

C houseId INT,

D FOREIGN KEY (houseName)

E REFERENCES House(houseId)

F );

Line D — The Student table only has a houseId column. houseName was moved to the House table in 3NF, so the foreign key must be (houseId).

Question 5

A CREATE TABLE Product (

B productId INT PRIMARY KEY,

C categoryName TEXT,

D price REAL

E );

Line C — categoryName depends on the category, not on productId, so it is a transitive dependency. Keep only the categoryId foreign key here and store the name in a Category table.

Student with a class code? Join your class →