← QCE Digital Solutions Unit 2

Modifying a database to 3NF

Third normal form: move fields that depend on a non-key field into a new table with its own primary key, then link the tables with a foreign key. Check no data is lost.

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

Understanding Modifying a database to 3NF in QCE Digital Solutions Unit 2

What it does: Joining on the foreign key and primary key rebuilds the original data, so you can compare it with the old table to confirm nothing was lost. houseName depends on houseId, a non-key field. That is a transitive dependency. Move it to a House table and keep houseId in Students as a foreign key. Copy the data first, then drop the old columns. Dropping them first loses data. 2NF only removes partial dependencies. 3NF also needs the transitive dependency removed. To reach 3NF you split the dependent fields into a new table, and that table needs its own primary key (teacherId).

Common mistakes: The fields that depend on a non-key field must go into a NEW table. The classes table already exists, so the new table needs its own name, such as rooms. Without DISTINCT, every product row copies its supplier, so the new table gets repeated suppliers. Use SELECT DISTINCT supplier so each supplier is stored once.

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 Modifying a database to 3NF 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 Students table has studentId (PK), name, houseId and houseName. houseName depends on houseId, not on studentId. What should you do to reach 3NF?
Move houseName into a new House table with houseId as its primary keyLeave houseName where it is because it is in the same row as studentId
Matches
CREATE TABLE teachers (teacherName VARCHAR(50));
↓
The new table has no primary key. Every table made by splitting for 3NF needs its own primary key, such as teacherId INT PRIMARY KEY.
Applying
Meanings
Term
3NF
Meaning
Third normal form: move fields that depend on a non-key field into a new table with its own primary key, then link the tables with a foreign key. Check no data is lost.
Practice
CREATE TABLE teachers (
Testing
Gaps
teacherId INT ,
Check Answer
Spots
1CREATE TABLE classes (
2 roomId INTEGER PRIMARY KEY,

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

The 'Moray Eel' badge — Lurking Debugger, 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 Students table has studentId (PK), name, houseId and houseName. houseName depends on houseId, not on studentId. What should you do to reach 3NF?
A. Move houseName into a new House table with houseId as its primary key ✓
B. Leave houseName where it is because it is in the same row as studentId
houseName depends on houseId, a non-key field. That is a transitive dependency. Move it to a House table and keep houseId in Students as a foreign key.
2. After splitting a table to reach 3NF, how do you link the new table back to the original?
A. Delete the key from the original table so the tables are separate
B. Keep the new table's primary key in the original table as a foreign key ✓
The foreign key in the original table matches the primary key of the new table. This is how a join can rebuild the full data.
3. Which problem does 3NF fix?
A. A non-key field that depends on another non-key field ✓
B. A table that has more than one row of data
3NF removes transitive dependencies, where a non-key field depends on another non-key field instead of the primary key.
4. Orders(orderId, productId, productName, price). productName and price depend on productId. Which design is in 3NF?
A. Orders(orderId, productName) and Products(price)
B. Orders(orderId, productId) and Products(productId, productName, price) ✓
productId becomes the primary key of the new Products table. Orders keeps productId as a foreign key. The second design loses the link between the tables.
5. When you move fields into a new table, what must the new table have?
A. Its own primary key ✓
B. No primary key because it is only a copy
Every table needs a primary key to identify each row. For the moved fields, this is the field they depended on, such as houseId.

Match the Pairs

CREATE TABLE teachers (teacherId INT PRIMARY KEY, teacherName VARCHAR(50)); → Creates a new table for fields that depend on a non-key field, with its own primary key.
ALTER TABLE subjects ADD FOREIGN KEY (teacherId) REFERENCES teachers(teacherId); → Links the original table to the new table so the data can be joined back together.
Check no data is lost after splitting: joining the new tables should give back every original row. → SELECT * FROM subjects INNER JOIN teachers ON subjects.teacherId = teachers.teacherId;
Fields that depend on a non-key field (teacherName depends on teacherId, not subjectId) are moved into a new table. → INSERT INTO teachers SELECT DISTINCT teacherId, teacherName FROM subjects;
CREATE TABLE teachers (teacherName VARCHAR(50)); → The new table has no primary key. Every table made by splitting for 3NF needs its own primary key, such as teacherId INT PRIMARY KEY.
ALTER TABLE subjects DROP COLUMN teacherName; -- before copying it to teachers → The field was dropped before its data was copied into the new table, so the teacher names are lost forever.

Key Term

3NF
Third normal form: move fields that depend on a non-key field into a new table with its own primary key, then link the tables with a foreign key. Check no data is lost.

Fill in the Blanks

1. CREATE TABLE teacher ( teacherId INT PRIMARY KEY, room VARCHAR(10));
Hint: Type the constraint that gives the new table its own primary key.
2. ALTER TABLE student ADD FOREIGN KEY (teacherId) REFERENCES teacher(teacherId);
Hint: Type the keyword that links the foreign key to the primary key in the new table.
3. INSERT INTO teacher(teacherId, room) SELECT DISTINCT teacherId, room FROM student_old;
Hint: Type the keywords that copy each unique teacher into the new table only once.
4. SELECT s.name, t.room FROM student s JOIN teacher t ON s.teacherId = t.teacherId;
Hint: Type the keyword that combines the two tables, to check no data is lost.
5. ALTER TABLE student DROP COLUMN room;
Hint: Type the keywords that remove the moved field from the original table.

Spot the Error

Question 1
A CREATE TABLE classes (
B roomId INTEGER PRIMARY KEY,
C building TEXT
D );
Line A — The fields that depend on a non-key field must go into a NEW table. The classes table already exists, so the new table needs its own name, such as rooms.
Question 2
A INSERT INTO suppliers (name)
B SELECT supplier
C FROM products;
Line B — Without DISTINCT, every product row copies its supplier, so the new table gets repeated suppliers. Use SELECT DISTINCT supplier so each supplier is stored once.
Question 3
A ALTER TABLE products
B ADD supplierName TEXT
C REFERENCES suppliers(id);
Line B — The link column must be a foreign key that holds the new table's primary key value. It should be supplierId INTEGER, not the supplier's name as text.
Question 4
A CREATE TABLE products (
B pId INTEGER PRIMARY KEY,
C supId INTEGER UNIQUE,
D FOREIGN KEY (supId)
E REFERENCES suppliers(id)
F );
Line C — A foreign key must allow repeated values, because many products can share one supplier. UNIQUE stops that. The line should be just supId INTEGER,.
Question 5
A SELECT COUNT(*)
B FROM products
C WHERE supplierId IS NOT NULL;
Line C — To check no data was lost, look for products that did not get linked to a supplier. That means IS NULL, and the count should be 0. IS NOT NULL counts the linked rows, which proves nothing.

Student with a class code? Join your class →