← QCE Digital Solutions Unit 2

Unnormalised data and why normalise

Organising tables to reduce repeated data and prevent anomalies by splitting data into linked tables, using the normal forms 1NF to 3NF.

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

Understanding Unnormalised data and why normalise in QCE Digital Solutions Unit 2

What it does: Ava's name and house are repeated on every row. This redundancy is the main problem with one big table, and normalising to 1NF, 2NF and 3NF removes it. After splitting a big table, a foreign key links the new tables back together. The data is stored once but can still be joined when needed. Repeated columns for the same kind of value are a repeating group. Normalisation moves them into a separate linked table. Repeating the same customer details on every order wastes space and risks inconsistent copies. You apply the normal forms in order: first 1NF, then 2NF, then 3NF.

Common mistakes: There is no comma after the studentId column definition. Columns in CREATE TABLE are separated by commas, so it should be ' studentId INT PRIMARY KEY,'. Columns like subject1 and subject2 are a repeating group, which breaks 1NF. Normalised data has one row per subject, so the column should be ' subjectId INT,'.

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 Unnormalised data and why normalise 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

Which best describes the purpose of normalisation?

Organising tables to reduce repeated data and prevent anomaliesMaking a table as wide as possible so everything is in one place

Matches

Students(studentId, name, subject1, subject2, subject3)

↓

Repeating group: subject1, subject2 and subject3 repeat one kind of data in a record, so this table is not in 1NF. Move subjects into their own table with one subject per row.

Applying

Meanings

Term

normalisation

Meaning

Organising tables to reduce repeated data and prevent anomalies by splitting data into linked tables, using the normal forms 1NF to 3NF.

Practice

SELECT students.firstName, results.mark

Testing

Gaps

-- Remove repeating groups:

Check Answer

Spots

1CREATE TABLE students (

2 studentId INT 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. Which best describes the purpose of normalisation?

A. Making a table as wide as possible so everything is in one place

B. Organising tables to reduce repeated data and prevent anomalies ✓

Normalisation splits data into linked tables so each fact is stored once, which reduces redundancy and prevents anomalies.

2. A student's row has the columns Subject1, Subject2 and Subject3 holding different subjects. What is this?

A. A repeating group, which should be moved into its own table ✓

B. A properly normalised design, because every subject has a column

Repeated columns for the same kind of value are a repeating group. Normalisation moves them into a separate linked table.

3. A single orders table stores the customer's name and address again on every order. What problem is this?

A. Redundancy: the same data is stored many times ✓

B. Good practice: it keeps all customer details close to each order

Repeating the same customer details on every order wastes space and risks inconsistent copies.

4. After normalising, how are the new tables connected?

A. By a foreign key that matches a primary key in another table ✓

B. By copying all the fields from one table into the other

Normalised tables are linked with foreign keys that reference primary keys. Copying fields back would bring the redundancy back.

5. Which list shows the normal forms in the order you apply them?

A. 3NF, 2NF, 1NF

B. 1NF, 2NF, 3NF ✓

You apply the normal forms in order: first 1NF, then 2NF, then 3NF.

Match the Pairs

Orders(orderId, student, item1, item2, item3) → An unnormalised table with a repeating group: item1, item2 and item3 repeat the same kind of data in one record

Students(studentId, name, house) + Orders(orderId, studentId, item) → A normalised design: data split into two linked tables so each fact is stored once and joined by a key

Normalising is organising tables to reduce redundancy by splitting data and linking the new tables with keys → FOREIGN KEY (studentId) REFERENCES Students(studentId)

Students(studentId, name, subject1, subject2, subject3) → Repeating group: subject1, subject2 and subject3 repeat one kind of data in a record, so this table is not in 1NF. Move subjects into their own table with one subject per row.

Students(studentId, name, house, houseColour) → Houses and house colours are stored with every student, so the colour is repeated and an update anomaly can happen. Put house and houseColour in a separate Houses table.

Key Term

normalisation

Organising tables to reduce repeated data and prevent anomalies by splitting data into linked tables, using the normal forms 1NF to 3NF.

Fill in the Blanks

1. -- Remove repeating groups: 1NF

Hint: Type the abbreviation of the normal form that removes repeating groups.

2. -- Remove partial dependencies: 2NF

Hint: Type the abbreviation of the normal form that fixes fields depending on only part of a composite key.

3. CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, Name VARCHAR(50));

Hint: Type the two-word constraint that uniquely identifies each record in the new table.

4. CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)

Hint: Type the two-word constraint that links this table to the split-out table.

5. SELECT Orders.OrderID, Customer.Name FROM Orders JOIN Customer ON Orders.CustomerID = Customer.CustomerID;

Hint: Type the keyword that introduces the condition matching the foreign key to the primary key when rejoining split tables.

Spot the Error

Question 1

A CREATE TABLE students (

B studentId INT PRIMARY KEY

C firstName VARCHAR(50)

D );

Line B — There is no comma after the studentId column definition. Columns in CREATE TABLE are separated by commas, so it should be ' studentId INT PRIMARY KEY,'.

Question 2

A CREATE TABLE results (

B studentId INT,

C subject1 INT, subject2 INT,

D mark INT

E );

Line C — Columns like subject1 and subject2 are a repeating group, which breaks 1NF. Normalised data has one row per subject, so the column should be ' subjectId INT,'.

Question 3

A UPDATE subjects

B SET teacher == 'Ms Lee'

C WHERE subjectId = 1;

Line B — SQL uses a single = in a SET clause. Because the teacher is stored once in subjects, this one update fixes it everywhere. The line should be "SET teacher = 'Ms Lee'".

Question 4

A CREATE TABLE orders (

B orderId INT FOREIGN KEY,

C studentId INT,

D FOREIGN KEY (studentId)

E REFERENCES students(id)

F );

Line B — orderId uniquely identifies each order, so it is the primary key. The foreign key is studentId, which links to the students table. The line should be ' orderId INT PRIMARY KEY,'.

Question 5

A SELECT firstName, mark

B FROM students s

C JOIN results r

D ON s.studentId = r.subjectId;

Line D — Tables split by normalisation are linked by matching the primary key to its foreign key. students.studentId must equal results.studentId, not results.subjectId.

Student with a class code? Join your class →