← QCE Digital Solutions Unit 2

Third normal form (3NF) (Data and Programming Techniques)

Third normal form: the table is in 2NF and no non-key field depends on another non-key field (no transitive dependency), e.g. suburb depends on postcode, so move it to its own table.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 102

Understanding Third normal form (3NF) (Data and Programming Techniques) in QCE Digital Solutions Unit 2

What it does: Suburb has been moved out of Students into Postcodes, with Postcode kept in Students as a foreign key. This removes the transitive dependency. 3NF builds on 2NF and then removes transitive dependencies, where a non-key field depends on another non-key field. Normal forms build on each other: 1NF, then 2NF, then 3NF. Removing the repeated suburb data avoids update anomalies, because one change fixes everything. houseCaptain is determined by house, not by studentId. Move house and houseCaptain to a House table to reach 3NF.

Common mistakes: suburb depends on pcode, which is a non-key field, so this is a transitive dependency. suburb must be removed from Cust and stored in the Postcode table. pcode is the field that decides the suburb, so it must be the PRIMARY KEY of the new Postcode table. Customer can then refer to it with a foreign key.

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) (Data and Programming Techniques) to a class →

This module's key terms are also in a printable crossword (Modules 234–285) 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 has no transitive dependency.

TrueFalse

Matches

Students(StudentID, Name, Postcode, Suburb) This table is in 3NF because it has a primary key.

↓

Having a primary key does not make a table 3NF. Suburb still depends on Postcode, a non-key field, so the transitive dependency remains.

Applying

Meanings

Term

3NF

Meaning

Third normal form: the table is in 2NF and no non-key field depends on another non-key field (no transitive dependency), e.g. suburb depends on postcode, so move it to its own table.

Practice

SELECT results.studentId, subjects.name, subjects.teacher

Testing

Gaps

postcode CHAR(4) ,

Check Answer

Spots

1Cust(id, name, pcode, suburb)

2Postcode(pcode, suburb)

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 has no transitive dependency.

A. True ✓

B. False

3NF builds on 2NF and then removes transitive dependencies, where a non-key field depends on another non-key field.

2. Customer(customerId, name, postcode, suburb). Suburb depends on postcode. Which describes the problem?

A. A non-key field (suburb) depends on the whole primary key

B. A non-key field (suburb) depends on another non-key field (postcode) ✓

Suburb is determined by postcode, not directly by customerId. This is a transitive dependency, so the table is not in 3NF.

3. Which fix brings Customer(customerId, name, postcode, suburb) into 3NF?

A. Move postcode and suburb into a new Postcode table and keep postcode in Customer as a foreign key ✓

B. Delete the suburb values from the Customer table and keep the table as it is

Move the transitive fields to a new table. Postcode becomes the primary key there and stays in Customer as a foreign key to link them.

4. Which is a transitive dependency?

A. customerId -> name

B. customerId -> postcode -> suburb ✓

A transitive dependency is a chain: the key determines a non-key field, which then determines another non-key field. A key giving a name is a normal dependency.

5. A table is in 3NF but has not yet been checked for 2NF. Is that possible?

A. No, 3NF requires the table to already be in 2NF ✓

B. Yes, 3NF can be reached without 2NF

Normal forms build on each other: 1NF, then 2NF, then 3NF.

Match the Pairs

Students(StudentID, Name, Postcode, Suburb) → Not in 3NF: Suburb depends on Postcode, which is a non-key field, so there is a transitive dependency.

Postcodes(Postcode, Suburb) → A new table that holds the suburb for each postcode, so Suburb depends only on its own key.

3NF removes a non-key field that depends on another non-key field by moving it to a new table. → Students(StudentID, Name, Postcode) Postcodes(Postcode, Suburb)

A transitive dependency is a chain where the key determines a non-key field, which then determines another non-key field. → StudentID -> Postcode -> Suburb

Students(StudentID, Name, Postcode, Suburb) This table is in 3NF because it has a primary key. → Having a primary key does not make a table 3NF. Suburb still depends on Postcode, a non-key field, so the transitive dependency remains.

Students(StudentID, Name, Suburb) Postcodes(Postcode, Suburb) → Postcode was left out of Students, so there is no foreign key linking the two tables. Students cannot be matched to their postcode.

Key Term

3NF

Third normal form: the table is in 2NF and no non-key field depends on another non-key field (no transitive dependency), e.g. suburb depends on postcode, so move it to its own table.

Fill in the Blanks

1. CREATE TABLE Postcode ( postcode CHAR(4) PRIMARY KEY, suburb VARCHAR(30));

Hint: Type the two-word constraint that makes postcode the unique identifier of this new table.

2. CREATE TABLE Student (postcode CHAR(4), FOREIGN KEY (postcode) REFERENCES Postcode(postcode));

Hint: Type the two-word keyword that marks a field as a link to another table's primary key.

3. studentId -> postcode -> suburb

Hint: Type the non-key field that depends on postcode, which makes this a transitive dependency.

4. SELECT Student.name, Postcode.suburb FROM Student JOIN Postcode ON Student.postcode = Postcode.postcode;

Hint: Type the primary key of the new table, written with its table name in front.

5. ALTER TABLE Student DROP COLUMN suburb;

Hint: Type the name of the table that still holds the suburb field after it has been moved to a new table.

Spot the Error

Question 1

A Cust(id, name, pcode, suburb)

B Postcode(pcode, suburb)

C PK Cust: id

D FK: Cust.pcode -> Postcode

Line A — suburb depends on pcode, which is a non-key field, so this is a transitive dependency. suburb must be removed from Cust and stored in the Postcode table.

Question 2

A CREATE TABLE Postcode (

B pcode CHAR(4) NOT NULL,

C suburb VARCHAR(30)

D );

Line B — pcode is the field that decides the suburb, so it must be the PRIMARY KEY of the new Postcode table. Customer can then refer to it with a foreign key.

Question 3

A Cust(id, name, pcode, suburb)

B suburb -> pcode

C Transitive: id->pcode->suburb

Line B — The dependency runs from postcode to suburb, because the postcode decides the suburb. The line should read pcode -> suburb. This non-key to non-key dependency breaks 3NF.

Question 4

A Step 1: table is in 2NF

B Step 2: find non-key fields

C Step 3: does one set another?

D Step 4: delete the field

E Step 5: link tables with a FK

Line D — The dependent field must be moved to a new table, not deleted. Deleting it would lose data. The determining field becomes the key of the new table.

Question 5

A SELECT c.name, p.suburb

B FROM Customer c

C JOIN Postcode p

D ON c.name = p.pcode;

Line D — After splitting for 3NF, the join must match the foreign key to the primary key: c.pcode = p.pcode. Matching name to pcode gives meaningless results.

Student with a class code? Join your class →