Update, insertion and deletion anomalies
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
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?
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?
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?
4. Which statement best describes what causes an anomaly?
5. Teacher 'Ms Lee' is typed in 30 rows. One row says 'Ms Lea' by mistake. What problem has appeared?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1UPDATE Orders SET teacher = 'Ms Lee' WHERE orderId = 5; | AUpdate anomaly: only one row is changed, so the same teacher now appears under two different names in the table | |
| 2DELETE FROM Orders WHERE orderId = 9; | BWrong: 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. | |
| 3An insertion anomaly stops you adding a new product until a customer has ordered it, because product details live in the orders table | CUPDATE Orders SET price = 5 WHERE orderId = 1; | |
| 4An update anomaly happens when a repeated value must be changed on many rows but only some rows are changed | DINSERT INTO Orders (productName, price) VALUES ('Lamp', 20); | |
| 5A 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.' | EWrong: the house details are lost too, which is a deletion anomaly. Fix by keeping houses in their own Houses table. | |
| 6A single table holds students and their house. A designer says: 'Deleting the last student in a house removes only that student.' | FDeletion anomaly: removing the only order for a product also wipes out the product's details |
C
Definitions
anomaly
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
UPDATE BigOrders SET teacher = 'Ms Kim'
_____ orderId = 1;
2.
INSERT INTO BigResults
VALUES (____, 'Art', 'Ms Ng');
3.
______ FROM BigResults
WHERE studentId = 5;
4.
SELECT ________ teacher FROM BigResults
WHERE subject = 'Maths';
5.
UPDATE Subjects ___ teacher = 'Ms Kim'
WHERE subjectId = 1;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
UPDATE big_orders
B
SET price = 6
C
WHERE product == 'Pencil';
Question 2
A
INSERT INTO big_orders
B
(product, price, studentId)
C
VALUE ('Ruler', 2, NULL);
Question 3
A
-- Deletion anomaly example
B
DELETE FROM big_orders
C
WHERE orderId = 7;
D
SELECT * big_orders;
Question 4
A
SELECT product, price
B
FORM big_orders
C
WHERE product = 'Pencil';
Question 5
A
UPDATE big_orders
B
SET house = Blue
C
WHERE studentId = 2;
Answer Key
Update, insertion and deletion anomalies
Section A: Quick Questions
Q1
A — Update anomaly
Q2
B — Insertion anomaly
Q3
A — Deletion anomaly
Q4
A — Redundant data stored in a table
Q5
B — Inconsistent data caused by redundancy
Section B: Match the Pairs
Q1
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
Q2
DELETE FROM Orders WHERE orderId = 9; → Deletion anomaly: removing the only order for a product also wipes out the product's details
Q3
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);
Q4
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;
Q5
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.
Q6
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.
Section C: Definitions
Q1
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.Section D: Fill in the Blanks
Q1
UPDATE BigOrders SET teacher = 'Ms Kim'
WHERE orderId = 1;Q2
INSERT INTO BigResults
VALUES (NULL, 'Art', 'Ms Ng');Q3
DELETE FROM BigResults
WHERE studentId = 5;Q4
SELECT DISTINCT teacher FROM BigResults
WHERE subject = 'Maths';Q5
UPDATE Subjects SET teacher = 'Ms Kim'
WHERE subjectId = 1;Section E: Spot the Error
Q1
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';
Q2
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);
Q3
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;
Q4
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
Q5
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'