← QCE Digital Solutions Unit 3

Reducing redundancy by splitting a table

Splitting a table so repeated details move into their own table, linked back with a foreign key, so each fact is stored only once and redundancy is reduced.

Part of: QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 101

Understanding Reducing redundancy by splitting a table in QCE Digital Solutions Unit 3

What it does: Teacher details live in the subjects table only, so one query on that table shows each fact stored once. The foreign key equals the primary key in the ON condition, so the split tables are linked back together without storing repeated data. Links use keys. Matching text can be misspelt or duplicated, while a primary key identifies exactly one row. Nothing is lost. A query can join the tables on the key whenever the full picture is needed. Splitting a table starts by moving the repeated details into their own table with a primary key. This is the redundancy problem that splitting a table fixes.

Common mistakes: The new teachers table needs a primary key so other tables can link back to each teacher. Line 2 should be ' id INTEGER PRIMARY KEY,'. teacherId links each subject to a row in teachers, so it must be a FOREIGN KEY. The subjects table already has its own primary key (id). Line 4 should be ' FOREIGN KEY (teacherId)'.

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 Reducing redundancy by splitting a table to a class →

This module's key terms are also in a printable crossword (Modules 15–119) and word search (Modules 15–176).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A Orders table repeats a customer's name and address on every order they place. What is the best fix?
Move the customer details into a Customers table and link each order with a foreign keyKeep the details in Orders and copy them again each time to be safe
Matches
One big table: results(studentId, firstName, lastName, subjectName, teacher, mark)
↓
Student and subject details are repeated on every result row, so the same fact is stored many times. Move them into students and subjects tables and keep only the keys in results.
Applying
Meanings
Term
normalisation
Meaning
Splitting a table so repeated details move into their own table, linked back with a foreign key, so each fact is stored only once and redundancy is reduced.
Practice
SELECT results.studentId, subjects.name, results.mark
Testing
Gaps
houseId INTEGER ,
Check Answer
Spots
1CREATE TABLE teachers (
2 id INTEGER,

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

The 'Space Turtle' badge — Speed in Zero-G, 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 Orders table repeats a customer's name and address on every order they place. What is the best fix?
A. Keep the details in Orders and copy them again each time to be safe
B. Move the customer details into a Customers table and link each order with a foreign key ✓
Splitting the table stores each customer's details once. Orders then link back to them with a foreign key.
2. Which statement describes the goal of splitting a table?
A. Each fact is stored in one place only ✓
B. Each fact is stored in as many places as possible
Reducing redundancy means a fact such as a teacher's name is stored once, not repeated on many rows.
3. A Students table repeats the house captain's name on every student row. Where should that detail go?
A. In a separate Houses table, linked by a houseId foreign key ✓
B. In the Students table, typed again on every row
The captain belongs to the house, not to each student. It is stored once in Houses and students link to it.
4. After splitting a table, how does a row in the new table get connected back to the original one?
A. A foreign key in the original table holds the primary key value of the new table ✓
B. The original table copies all the new table's fields into itself
A foreign key stores only the key value, for example customerId. That avoids copying the other details.
5. A shop's Sales table repeats the product name and price on every sale. Which design reduces redundancy?
A. Sales(saleId, productName, productPrice, quantity) with the details repeated
B. Sales(saleId, productId, quantity) and Products(productId, name, price) ✓
Products holds each name and price once. Sales only stores the productId to point to it.

Match the Pairs

CREATE TABLE houses (house TEXT PRIMARY KEY, headOfHouse TEXT); → Creates a separate table so each house and its head are stored once, instead of being repeated on every student row.
students(studentId, firstName, house) -> houses(house) → The house field in students is a foreign key that links each student back to the one house row that holds the details.
One fact in one place: the teacher name is stored once in subjects, not repeated on every results row. → SELECT name, teacher FROM subjects;
After splitting, a join uses the foreign key to bring the linked details back together when needed. → SELECT students.firstName, results.mark FROM students INNER JOIN results ON students.studentId = results.studentId;
One big table: results(studentId, firstName, lastName, subjectName, teacher, mark) → Student and subject details are repeated on every result row, so the same fact is stored many times. Move them into students and subjects tables and keep only the keys in results.
results(studentId, subjectId, mark, teacher) -- teacher copied into results as well as subjects → Copying the teacher into results instead of linking by subjectId stores the same fact in two places. Keep teacher only in subjects and link with the foreign key subjectId.

Key Term

normalisation
Splitting a table so repeated details move into their own table, linked back with a foreign key, so each fact is stored only once and redundancy is reduced.

Fill in the Blanks

1. CREATE TABLE houses ( houseId INTEGER PRIMARY KEY, houseName TEXT);
Hint: Type the two-word keyword that makes houseId identify each house row uniquely, so each house is stored once.
2. CREATE TABLE students (studentId INTEGER, houseId INT REFERENCES houses(houseId));
Hint: Type the keyword that links this column back to the key in the houses table.
3. INSERT INTO houses VALUES (1, 'Red'); INSERT INTO students VALUES (1, 'Ava', 1);
Hint: Type the key number of the Red house, so the student row links to it instead of repeating the house name.
4. UPDATE houses SET houseName = 'Crimson' WHERE houseId = 1;
Hint: Type the primary key column that picks out the one row to change, because the fact is stored in only one place.
5. SELECT students.firstName, houses.houseName FROM students JOIN houses ON students.houseId = houses.houseId;
Hint: Type the table-qualified primary key that the foreign key in students must equal to bring the house details back.

Spot the Error

Question 1
A CREATE TABLE teachers (
B id INTEGER,
C name TEXT
D );
Line B — The new teachers table needs a primary key so other tables can link back to each teacher. Line 2 should be ' id INTEGER PRIMARY KEY,'.
Question 2
A CREATE TABLE subjects (
B id INTEGER PRIMARY KEY,
C teacherId INTEGER,
D PRIMARY KEY (teacherId)
E REFERENCES teachers(id)
F );
Line D — teacherId links each subject to a row in teachers, so it must be a FOREIGN KEY. The subjects table already has its own primary key (id). Line 4 should be ' FOREIGN KEY (teacherId)'.
Question 3
A SELECT s.name, t.name
B FROM subjects s
C JOIN teachers
D ON s.teacherId = t.id;
Line C — The query uses the alias t (t.name and t.id), but line 3 never defines it. Line 3 should be 'JOIN teachers t'.
Question 4
A INSERT INTO teachers VALUES
B (1, 'Ms Lee');
C INSERT INTO subjects VALUES
D (1, 'Maths', 'Ms Lee');
Line D — After the split, subjects stores only the teacher's key, not the name. Copying 'Ms Lee' brings the repeated detail back. Line 4 should be ' (1, 'Maths', 1);'.
Question 5
A -- Ms Lee changes her name
B UPDATE subjects
C SET name = 'Ms Lee-Ray'
D WHERE id = 1;
Line B — The teacher's name is stored once, in the teachers table, so that is where it is updated. subjects has no name column for teachers. Line 2 should be 'UPDATE teachers'.

Student with a class code? Join your class →