← QCE Digital Solutions Unit 2

Insert, update and delete anomalies

A problem caused by repeated (redundant) data: adding, changing or deleting a record leaves data inconsistent or lost. The three types are insert, update and delete anomalies.

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

Understanding Insert, update and delete anomalies in QCE Digital Solutions Unit 2

What it does: If the product details are stored only in the order row, deleting that order deletes the only copy of the product data, which is a delete anomaly. Anomalies happen when the same facts are repeated across rows or mixed together in one table. Splitting the data into related tables removes the repeats. An anomaly is about data becoming inconsistent or lost after a change, not about speed. The phone number appears on many rows, so every row must be updated to stay consistent. Missing one causes an update anomaly. Storing each fact once, in its own table linked by keys, means one change updates it everywhere. This is what normalisation does.

Common mistakes: UPDATE does not use FROM. It should be UPDATE BigOrders. In a big table the price is stored on many rows, so an update must change every one or it causes an update anomaly. INSERT must be followed by INTO, so the line should be INSERT INTO Products. Storing products in their own table avoids the insert anomaly, where a product cannot be added until someone orders it.

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 Insert, update and delete anomalies 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 single table stores each student's name along with their teacher's name and room. A new teacher is hired but has no students yet, and cannot be added until a student is enrolled. Which anomaly is this?
Insert anomalyDelete anomaly
Matches
A student says: 'Repeating a customer's name in every order row is fine because it never causes any problems.'
↓
Wrong: repeated (redundant) data is the cause of anomalies. It wastes space and leads to insert, update and delete anomalies.
Applying
Meanings
Term
anomaly
Meaning
A problem caused by repeated (redundant) data: adding, changing or deleting a record leaves data inconsistent or lost. The three types are insert, update and delete anomalies.
Practice
UPDATE subjects SET teacher = 'Ms Kim' WHERE name = 'Maths';
Testing
Gaps
-- Cannot add Ms Park alone: anomaly
Check Answer
Spots
1-- Price repeated in rows
2UPDATE FROM BigOrders

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

The 'Bear' badge — Forest Guardian, 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 single table stores each student's name along with their teacher's name and room. A new teacher is hired but has no students yet, and cannot be added until a student is enrolled. Which anomaly is this?
A. Insert anomaly ✓
B. Delete anomaly
The teacher's details can't be stored without a student row existing. Being unable to add data on its own is an insert anomaly.
2. A teacher's room changes from B12 to B15. The room is stored on 30 student rows, and only 29 are updated. What problem is this?
A. Update anomaly: the data is now inconsistent ✓
B. Insert anomaly: the data is now inconsistent
Repeated data must be changed in many rows. Missing one row leaves two different rooms for the same teacher, which is an update anomaly.
3. The only row that records the Drama teacher is deleted when the last Drama student leaves. The teacher's details are lost too. Which anomaly is this?
A. Update anomaly
B. Delete anomaly ✓
Deleting one record also removed other facts stored in it. Losing data this way is a delete anomaly.
4. What is the root cause of insert, update and delete anomalies?
A. Redundant (repeated) data stored in one table ✓
B. Using SQL instead of a spreadsheet
Anomalies happen when the same facts are repeated across rows or mixed together in one table. Splitting the data into related tables removes the repeats.
5. Which statement describes an anomaly correctly?
A. A query that runs slowly on a large table
B. Adding, changing or deleting a record leaves data inconsistent or lost ✓
An anomaly is about data becoming inconsistent or lost after a change, not about speed.

Match the Pairs

INSERT INTO orders VALUES (11, 'Ava', 'Pencil', 'Stationery', 1.5); → An insert anomaly can occur here: in one big table the product details are only stored when someone orders it, so a new product can't be added without an order.
UPDATE orders SET price = 2 WHERE product = 'Pencil'; → Risk of an update anomaly: the price is repeated on many rows, so if any row is missed the data becomes inconsistent.
A delete anomaly: removing the only order for a product also wipes out the product's details, so that information is lost. → DELETE FROM orders WHERE orderId = 7;
An update anomaly: the same fact is stored in many rows, so changing it in only some rows leaves inconsistent data. → UPDATE orders SET house = 'Blue' WHERE orderId = 3;
A student says: 'Repeating a customer's name in every order row is fine because it never causes any problems.' → Wrong: repeated (redundant) data is the cause of anomalies. It wastes space and leads to insert, update and delete anomalies.
A student says: 'An insert anomaly happens when you change a price and miss a row.' → Wrong type: missing a row when changing data is an update anomaly. An insert anomaly is when new data can't be added without other unrelated data.

Key Term

anomaly
A problem caused by repeated (redundant) data: adding, changing or deleting a record leaves data inconsistent or lost. The three types are insert, update and delete anomalies.

Fill in the Blanks

1. INSERT INTO BigTable (Teacher, Room) VALUES ('Ms Park', 'C2'); -- Cannot add Ms Park alone: insert anomaly
Hint: Type the kind of anomaly that stops you adding a new fact because other data is missing.
2. UPDATE BigTable SET Room = 'B7' WHERE Teacher = 'Ms Lee';
Hint: Type the SQL keyword that changes existing records. Changing repeated data in many rows is where update anomalies come from.
3. INSERT INTO BigTable (Teacher, Room) VALUES ('Ms Park', 'C2'); -- Insert anomaly: no student to link to
Hint: Type the SQL keywords that add a new record to a table.
4. UPDATE BigTable SET Room = 'B7' WHERE StudentID = 1; -- Other rows still show B4: update anomaly
Hint: Type the kind of anomaly that happens when only some copies of repeated data are changed.
5. DELETE FROM BigTable WHERE StudentID = 4; -- Mr Ford's only row goes: delete anomaly
Hint: Type the clause keyword that picks which row is deleted. Deleting that row also loses the teacher's details.

Spot the Error

Question 1
A -- Price repeated in rows
B UPDATE FROM BigOrders
C SET price = 5.00
D WHERE product = 'Pen';
Line B — UPDATE does not use FROM. It should be UPDATE BigOrders. In a big table the price is stored on many rows, so an update must change every one or it causes an update anomaly.
Question 2
A -- New product, no order yet
B INSERT Products
C VALUES (9, 'Ruler', 2.5);
Line B — INSERT must be followed by INTO, so the line should be INSERT INTO Products. Storing products in their own table avoids the insert anomaly, where a product cannot be added until someone orders it.
Question 3
A -- Delete only Ruler order
B DELETE FROM BigOrders
C WHERE orderId == 7;
Line C — SQL uses a single = for comparison, so it should be WHERE orderId = 7;. Deleting the only order for Ruler from a big table also deletes the Ruler details, which is a delete anomaly.
Question 4
A SELCT DISTINCT teacher, room
B FROM BigClasses
C WHERE teacher = 'Ms Lee';
Line A — SELECT is misspelt. It should be SELECT DISTINCT teacher, room. This query checks whether one teacher has more than one room recorded, which shows an update anomaly has left the data inconsistent.
Question 5
A -- Ms Lee moves to room 5
B UPDATE BigClasses
C SET room TO 5
D WHERE teacher = 'Ms Lee';
Line C — SET assigns a value with =, so the line should be SET room = 5. Because the room is repeated on many rows, a missed row would leave inconsistent data, which is an update anomaly.

Student with a class code? Join your class →