← QCE Digital Solutions Unit 3

Update, insertion and deletion anomalies

A fault caused by redundant data: an update anomaly leaves copies that disagree, an insertion anomaly blocks adding data, and a deletion anomaly loses other data when a record is removed.

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

Understanding Update, insertion and deletion anomalies in QCE Digital Solutions Unit 3

What it does: The row cannot be stored properly without an order or student value, so the new product cannot be added by itself. This is an insertion anomaly. The address is stored in many rows. Changing just one leaves copies that disagree, which is an update anomaly. Anomalies come from the same data being repeated. Splitting tables and using keys helps prevent them. Storing each fact once, in its own table linked by a foreign key, removes the redundancy that causes anomalies. When two kinds of data share one table, deleting a row can remove facts about the other kind. This is a deletion anomaly.

Common mistakes: SQL uses a single = to compare values, not ==. The WHERE must match every Pencil row, because a price copied on many rows can cause an update anomaly if only some are changed. It should be WHERE product = 'Pencil';. The keyword is VALUES, not VALUE. The NULL studentId shows an insertion anomaly: a new product can only be stored in this table by faking an order. It should be VALUES ('Ruler', 2, NULL);.

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 Update, insertion and deletion anomalies 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 shop table repeats a customer's address on every order row. The customer moves and only one row is changed. Which anomaly is this?

Update anomalyDeletion anomaly

Matches

A table stores each teacher's phone number on every class row. The number changes, and one row is edited. The designer says: 'The data is now consistent.'

↓

Wrong: the other rows still hold the old number, so the copies disagree. This is an update anomaly. Fix by storing the phone number once in a Teachers table.

Applying

Meanings

Term

anomaly

Meaning

A fault caused by redundant data: an update anomaly leaves copies that disagree, an insertion anomaly blocks adding data, and a deletion anomaly loses other data when a record is removed.

Practice

UPDATE products SET category = 'Stationary' WHERE productId = 1;

Testing

Gaps

orderId = 1;

Check Answer

Spots

2SET price = 6

3WHERE product == 'Pencil';

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 shop table repeats a customer's address on every order row. The customer moves and only one row is changed. Which anomaly is this?

A. Update anomaly ✓

B. Deletion anomaly

The address is stored in many rows. Changing just one leaves copies that disagree, which is an update anomaly.

2. A single table holds students and their subjects. You can't add a new subject until a student enrols in it. Which anomaly is this?

A. Update anomaly

B. Insertion anomaly ✓

Adding the subject is blocked because there is no student row to hold it. That is an insertion anomaly.

3. An orders table also stores product details. Deleting the only order for a Backpack also erases the Backpack's name and price. Which anomaly is this?

A. Deletion anomaly ✓

B. Insertion anomaly

Removing one record lost other data that was stored with it. That is a deletion anomaly.

4. Which statement best describes what causes an anomaly?

A. Redundant data stored in a table ✓

B. Using a primary key in a table

Anomalies come from the same data being repeated. Splitting tables and using keys helps prevent them.

5. Teacher 'Ms Lee' is typed in 30 rows. One row says 'Ms Lea' by mistake. What problem has appeared?

A. A missing primary key

B. Inconsistent data caused by redundancy ✓

Repeated copies of the same fact can disagree. This inconsistency is the risk of redundant data.

Match the Pairs

UPDATE Orders SET teacher = 'Ms Lee' WHERE orderId = 5; → Update anomaly: only one row is changed, so the same teacher now appears under two different names in the table

DELETE FROM Orders WHERE orderId = 9; → Deletion anomaly: removing the only order for a product also wipes out the product's details

An insertion anomaly stops you adding a new product until a customer has ordered it, because product details live in the orders table → INSERT INTO Orders (productName, price) VALUES ('Lamp', 20);

An update anomaly happens when a repeated value must be changed on many rows but only some rows are changed → UPDATE Orders SET price = 5 WHERE orderId = 1;

A table stores each teacher's phone number on every class row. The number changes, and one row is edited. The designer says: 'The data is now consistent.' → Wrong: the other rows still hold the old number, so the copies disagree. This is an update anomaly. Fix by storing the phone number once in a Teachers table.

A single table holds students and their house. A designer says: 'Deleting the last student in a house removes only that student.' → Wrong: the house details are lost too, which is a deletion anomaly. Fix by keeping houses in their own Houses table.

Key Term

anomaly

A fault caused by redundant data: an update anomaly leaves copies that disagree, an insertion anomaly blocks adding data, and a deletion anomaly loses other data when a record is removed.

Fill in the Blanks

1. UPDATE BigOrders SET teacher = 'Ms Kim' WHERE orderId = 1;

Hint: Type the keyword that limits the change to one row, which leaves other copies of the teacher unchanged and causes an update anomaly.

2. INSERT INTO BigResults VALUES (NULL, 'Art', 'Ms Ng');

Hint: Type the value that means no student exists yet. A new subject can only be added with an empty student, which is an insertion anomaly.

3. DELETE FROM BigResults WHERE studentId = 5;

Hint: Type the command that removes a record. Removing the last student of a subject can also wipe out the teacher details, which is a deletion anomaly.

4. SELECT DISTINCT teacher FROM BigResults WHERE subject = 'Maths';

Hint: Type the keyword that removes repeated values from the results. More than one teacher for Maths shows that the copies disagree.

5. UPDATE Subjects SET teacher = 'Ms Kim' WHERE subjectId = 1;

Hint: Type the keyword that introduces the new column value. With the teacher stored once, one change fixes it and avoids an update anomaly.

Spot the Error

Question 1

A UPDATE big_orders

B SET price = 6

C WHERE product == 'Pencil';

Line C — SQL uses a single = to compare values, not ==. The WHERE must match every Pencil row, because a price copied on many rows can cause an update anomaly if only some are changed. It should be WHERE product = 'Pencil';

Question 2

A INSERT INTO big_orders

B (product, price, studentId)

C VALUE ('Ruler', 2, NULL);

Line C — The keyword is VALUES, not VALUE. The NULL studentId shows an insertion anomaly: a new product can only be stored in this table by faking an order. It should be VALUES ('Ruler', 2, NULL);

Question 3

A -- Deletion anomaly example

B DELETE FROM big_orders

C WHERE orderId = 7;

D SELECT * big_orders;

Line D — A SELECT needs the FROM keyword before the table name. This query checks what was lost after the delete: if order 7 was the only row for an item, its price disappears too (deletion anomaly). It should be SELECT * FROM big_orders;

Question 4

A SELECT product, price

B FORM big_orders

C WHERE product = 'Pencil';

Line B — FORM is a typo for FROM. This query lists every copy of the Pencil price so you can check that they agree. If they differ, that is an update anomaly. It should be FROM big_orders

Question 5

A UPDATE big_orders

B SET house = Blue

C WHERE studentId = 2;

Line B — Text values must be in quotes, so Blue needs single quotes. A student's house is repeated on each of their order rows, so every row must be changed or an update anomaly appears. It should be SET house = 'Blue'

Student with a class code? Join your class →