← QCE Digital Solutions Unit 2

Normalising a table to 3NF step by step

Work through 1NF, 2NF, then 3NF, splitting data into new tables linked by foreign keys so no data is lost and anomalies are removed.

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

Understanding Normalising a table to 3NF step by step in QCE Digital Solutions Unit 2

What it does: HouseID FK links each student to a row in the House table, so nothing is lost when House details move out. The price is stored once in Product, so one UPDATE fixes it everywhere and no update anomaly occurs. Each normal form builds on the one before it. A table must be in 1NF before you test for 2NF, and in 2NF before you test for 3NF. This is a partial dependency. Create a new table for the field that depends on part of the key, then link it with a foreign key. No data is lost. Normalising is lossless. Joining the tables on foreign key = primary key rebuilds the original data, which checks you did not lose anything.

Common mistakes: After 3NF the Line table links to Product through its key. It must hold ProdID, the foreign key, not ProdName. Otherwise the link to Product is not a key and the tables cannot be joined reliably. Repeating groups are removed in 1NF. Third normal form removes transitive dependencies, where a non-key field depends on another non-key field.

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 to 3NF step by step 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 stores StudentID, StudentName, Subject1, Subject2 and Subject3. Which normal form does it break?

3NF: StudentName depends on a non-key field1NF: the repeating subject columns hold a repeating group

Matches

Order(OrderID PK, Item1, Item2, Item3)

↓

Repeating groups (Item1, Item2, Item3) break 1NF. Fix: one row per item, using a composite key of OrderID and ProductID.

Applying

Meanings

Term

1NF, 2NF, 3NF

Meaning

Work through 1NF, 2NF, then 3NF, splitting data into new tables linked by foreign keys so no data is lost and anomalies are removed.

Practice

-- 3NF schema from one big orders table

Testing

Gaps

CustID INT , Name TEXT);

Check Answer

Spots

3Orders(OrderID PK, CustID FK)

4Line(OrderID, ProdName, Qty)

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 stores StudentID, StudentName, Subject1, Subject2 and Subject3. Which normal form does it break?

A. 3NF: StudentName depends on a non-key field

B. 1NF: the repeating subject columns hold a repeating group ✓

1NF needs atomic values with no repeating groups. Move each subject into its own row, or into a separate table, so each field holds one value.

2. You normalise a table in order. Which order is correct?

A. 3NF, then 2NF, then 1NF

B. 1NF, then 2NF, then 3NF ✓

Each normal form builds on the one before it. A table must be in 1NF before you test for 2NF, and in 2NF before you test for 3NF.

3. Table OrderLine has the composite key (OrderID, ProductID). ProductName depends only on ProductID. What do you do to reach 2NF?

A. Move ProductID and ProductName into a new Product table and keep ProductID in OrderLine as a foreign key ✓

B. Delete the ProductName field from the database

This is a partial dependency. Create a new table for the field that depends on part of the key, then link it with a foreign key. No data is lost.

4. In a Student table, StudentID is the primary key. HouseName depends on HouseID, and HouseID is a non-key field. Which fix reaches 3NF?

A. Create a House table (HouseID, HouseName) and keep HouseID in Student as a foreign key ✓

B. Make HouseName the primary key of the Student table

This is a transitive dependency: StudentID determines HouseID, and HouseID determines HouseName. Move the dependent field to its own table and link with a foreign key.

5. After splitting a table during normalisation, how do you keep the data connected?

A. Put the primary key of the new table into the original table as a foreign key ✓

B. Copy all the fields into both tables so nothing is missed

Foreign keys link the new tables to the original, so you can rejoin the data and lose nothing. Copying whole fields would bring back repeated data.

Match the Pairs

OrderLine(OrderID PK, ProductID PK, Qty) → 2NF result: Qty depends on the whole composite key, so it stays here while product details move to their own table.

Product(ProductID PK, ProductName, Price) → 2NF result: product details depend only on ProductID, so each is stored once instead of in every order row.

After splitting out a new table, the old table keeps the new table's key as a foreign key so the data can be linked back together. → Student(StudentID PK, Name, HouseID FK)

Checking for anomalies after normalising: a product's price change now needs only one row to be edited. → UPDATE Product SET Price = 5 WHERE ProductID = 2;

Order(OrderID PK, Item1, Item2, Item3) → Repeating groups (Item1, Item2, Item3) break 1NF. Fix: one row per item, using a composite key of OrderID and ProductID.

Student(StudentID PK, Name, House, Captain) → Captain depends on House, not on StudentID (a transitive dependency), so this breaks 3NF. Move House and Captain to a House table linked by a HouseID foreign key.

Key Term

1NF, 2NF, 3NF

Work through 1NF, 2NF, then 3NF, splitting data into new tables linked by foreign keys so no data is lost and anomalies are removed.

Fill in the Blanks

1. CREATE TABLE Customer ( CustID INT PRIMARY KEY, Name TEXT);

Hint: Type the keywords that make CustID uniquely identify each customer row in the new table.

2. CREATE TABLE Orders (OrderID INT, CustID INT, FOREIGN KEY (CustID) REFERENCES Customer(CustID));

Hint: Type the keywords that link CustID in this table to the Customer table so no data is lost.

3. OrderLine(OrderID, ProductID, Qty) Product(ProductID, ProductName, Price)

Hint: Type the field that ProductName and Price depend on, which becomes the primary key of the new 2NF table.

4. Suburb(PostCode, SuburbName) Customer(CustID, Name, PostCode)

Hint: Type the foreign key field that replaces the suburb name in Customer after the 3NF split.

5. UPDATE Product SET Price = 12.50 WHERE ProductID = 3;

Hint: Type the primary key column that lets the price be changed once in a single row, so there is no update anomaly.

Spot the Error

Question 1

A Customer(CustID PK, Name)

B Product(ProdID PK, Name, Price)

C Orders(OrderID PK, CustID FK)

D Line(OrderID, ProdName, Qty)

Line D — After 3NF the Line table links to Product through its key. It must hold ProdID, the foreign key, not ProdName. Otherwise the link to Product is not a key and the tables cannot be joined reliably.

Question 2

A 1NF: one value per field

B 2NF: remove partial dependency

C 3NF: remove repeating groups

D Link tables with foreign keys

Line C — Repeating groups are removed in 1NF. Third normal form removes transitive dependencies, where a non-key field depends on another non-key field.

Question 3

A Key: (StudentID, SubjectID)

B Teacher depends on both parts

C That is a partial dependency

D Move Teacher to Subject table

Line B — Teacher depends only on SubjectID, which is part of the key. That is why it is a partial dependency that 2NF removes. It does not depend on both parts of the key.

Question 4

A Student(ID PK, Name, HouseID)

B House(HouseID PK, Captain)

C HouseID is a PK in Student

D No data lost: join on HouseID

Line C — HouseID is the primary key of House. In Student it is a foreign key that links each student to their house. It is not the primary key of Student.

Question 5

A CREATE TABLE Student (

B ID INT PRIMARY KEY,

C HouseID INT

D );

Line C — The new link needs a foreign key. HouseID must say REFERENCES House so it points at the House table. Without it the tables are not linked and referential integrity is lost.

Student with a class code? Join your class →