Insert, update and delete anomalies

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1INSERT INTO orders VALUES (11, 'Ava', 'Pencil', 'Stationery', 1.5);		AWrong: repeated (redundant) data is the cause of anomalies. It wastes space and leads to insert, update and delete anomalies.
2UPDATE orders SET price = 2 WHERE product = 'Pencil';		BAn 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.
3A delete anomaly: removing the only order for a product also wipes out the product's details, so that information is lost.		CWrong 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.
4An update anomaly: the same fact is stored in many rows, so changing it in only some rows leaves inconsistent data.		DRisk of an update anomaly: the price is repeated on many rows, so if any row is missed the data becomes inconsistent.
5A student says: 'Repeating a customer's name in every order row is fine because it never causes any problems.'		EDELETE FROM orders WHERE orderId = 7;
6A student says: 'An insert anomaly happens when you change a price and miss a row.'		FUPDATE orders SET house = 'Blue' WHERE orderId = 3;

Definitions

anomaly

Fill in the Blanks

Complete each line of code by filling in the missing parts.

INSERT INTO BigTable (Teacher, Room) VALUES ('Ms Park', 'C2'); -- Cannot add Ms Park alone: ______ anomaly

______ BigTable SET Room = 'B7' WHERE Teacher = 'Ms Lee';

___________ BigTable (Teacher, Room) VALUES ('Ms Park', 'C2'); -- Insert anomaly: no student to link to

UPDATE BigTable SET Room = 'B7' WHERE StudentID = 1; -- Other rows still show B4: ______ anomaly

DELETE FROM BigTable _____ StudentID = 4; -- Mr Ford's only row goes: delete anomaly

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A -- Price repeated in rows

B UPDATE FROM BigOrders

C SET price = 5.00

D WHERE product = 'Pen';

Question 2

A -- New product, no order yet

B INSERT Products

C VALUES (9, 'Ruler', 2.5);

Question 3

A -- Delete only Ruler order

B DELETE FROM BigOrders

C WHERE orderId == 7;

Question 4

A SELCT DISTINCT teacher, room

B FROM BigClasses

C WHERE teacher = 'Ms Lee';

Question 5

A -- Ms Lee moves to room 5

B UPDATE BigClasses

C SET room TO 5

D WHERE teacher = 'Ms Lee';

Answer Key

Insert, update and delete anomalies

Section A: Quick Questions

A — Insert anomaly

A — Update anomaly: the data is now inconsistent

B — Delete anomaly

A — Redundant (repeated) data stored in one table

B — Adding, changing or deleting a record leaves data inconsistent or lost

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

INSERT INTO BigTable (Teacher, Room)
VALUES ('Ms Park', 'C2');
-- Cannot add Ms Park alone: insert anomaly

UPDATE BigTable
SET Room = 'B7'
WHERE Teacher = 'Ms Lee';

INSERT INTO BigTable (Teacher, Room)
VALUES ('Ms Park', 'C2');
-- Insert anomaly: no student to link to

UPDATE BigTable SET Room = 'B7'
WHERE StudentID = 1;
-- Other rows still show B4: update anomaly

DELETE FROM BigTable
WHERE StudentID = 4;
-- Mr Ford's only row goes: delete anomaly

Section E: Spot the Error

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.

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.

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.

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.

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.