Insertion, update and deletion anomalies

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A school stores each student's house and house captain in one Students table. A new house has no students yet, so its captain cannot be recorded. Which anomaly is this?

A Insertion anomaly

B Deletion anomaly

2. A teacher's room number is repeated on 30 rows of a Results table. Only 29 rows are changed when she moves rooms. What problem does this cause?

A A missing primary key

B Inconsistent copies of the same fact

3. The only order for a product called 'Lunch Box' is deleted from a single Orders table. The product's name and price are lost as well. Which anomaly is this?

A Deletion anomaly

B Insertion anomaly

4. Which statement best describes why an update anomaly happens?

A The fact is stored once, so it can only be changed in one place

B The same fact is stored in many rows, so every copy must be changed

5. You cannot add a new subject to a table until a student enrols in it, because the student columns cannot be left empty. Which is correct?

A This is an insertion anomaly caused by redundant data in one table

B This is an update anomaly caused by a foreign key

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 (9, 'Ava', 'Year 7', 'Red');		AThis update leaves inconsistent copies. The teacher's name is stored in many rows, so updating one row creates an update anomaly. The fix is to store the teacher once in a separate table.
2UPDATE Orders SET house = 'Blue' WHERE orderId = 4;		BINSERT INTO Orders (productId, name, price) VALUES (11, 'Ruler', 2);
3Delete anomaly: removing the only row that mentions a product also erases the product's details.		CIn a single big table, repeating the student's details in every new row is wasteful and risks mistakes. This is where an insert anomaly can arise.
4Insert anomaly: a new product cannot be stored until someone orders it, because the table needs order details too.		DDELETE FROM Orders WHERE orderId = 7;
5Fix a teacher's name in one row only, so Maths shows 'Ms Lee' in one row and 'Ms Li' in another.		EChanging the house in only one row leaves other rows for the same student unchanged. This causes an update anomaly with inconsistent copies.
6DELETE FROM Orders WHERE studentId = 3; -- just to remove their old orders		FDeleting these rows also removes the only copy of the student's details, causing a delete anomaly. The fix is to keep student details in their own Students table.

Definitions

data anomaly

Fill in the Blanks

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

INSERT INTO Enrolment (StudentID, CourseCode) VALUES (____, 'C10');

UPDATE Enrolment SET CourseName = 'Databases 2' _____ CourseCode = 'C10';

______ FROM Enrolment WHERE StudentID = 12;

SELECT ________ CourseName FROM Enrolment WHERE CourseCode = 'C10';

UPDATE ______ SET CourseName = 'Databases 2' WHERE CourseCode = 'C10';

Spot the Error

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

Question 1

A DELETE ClassList

B WHERE studentId = 7;

C -- Loses Mr Ford's phone too

Question 2

A INSERT INTO ClassList

B (teacher, teacherPhone)

C VALUES ('Ms Wu', '0433 777');

D -- Works: studentId is the key

Question 3

A UPDATE ClassList

B SET teacherPhone = '0499 111'

C WHERE studentId = 3;

D SELECT * FROM ClassList;

Question 4

A INSERT INTO ClassList

B (studentId, name, teacher)

C VALUES (9, 'Kai', 'Ms Leigh');

Question 5

A SELCT DISTINCT teacher,

B teacherPhone

C FROM ClassList

D WHERE teacher = 'Ms Lee';

Answer Key

Insertion, update and deletion anomalies

Section A: Quick Questions

A — Insertion anomaly

B — Inconsistent copies of the same fact

A — Deletion anomaly

B — The same fact is stored in many rows, so every copy must be changed

A — This is an insertion anomaly caused by redundant data in one table

Section B: Match the Pairs

INSERT INTO Orders VALUES (9, 'Ava', 'Year 7', 'Red'); → In a single big table, repeating the student's details in every new row is wasteful and risks mistakes. This is where an insert anomaly can arise.

UPDATE Orders SET house = 'Blue' WHERE orderId = 4; → Changing the house in only one row leaves other rows for the same student unchanged. This causes an update anomaly with inconsistent copies.

Delete anomaly: removing the only row that mentions a product also erases the product's details. → DELETE FROM Orders WHERE orderId = 7;

Insert anomaly: a new product cannot be stored until someone orders it, because the table needs order details too. → INSERT INTO Orders (productId, name, price) VALUES (11, 'Ruler', 2);

Fix a teacher's name in one row only, so Maths shows 'Ms Lee' in one row and 'Ms Li' in another. → This update leaves inconsistent copies. The teacher's name is stored in many rows, so updating one row creates an update anomaly. The fix is to store the teacher once in a separate table.

DELETE FROM Orders WHERE studentId = 3; -- just to remove their old orders → Deleting these rows also removes the only copy of the student's details, causing a delete anomaly. The fix is to keep student details in their own Students table.

Section C: Definitions

data anomaly — A problem caused by redundant data in one table: adding, changing or deleting a record leads to missing data, inconsistent copies or accidental loss of other facts.

Section D: Fill in the Blanks

INSERT INTO Enrolment (StudentID, CourseCode)
VALUES (NULL, 'C10');

UPDATE Enrolment
SET CourseName = 'Databases 2'
WHERE CourseCode = 'C10';

DELETE FROM Enrolment
WHERE StudentID = 12;

SELECT DISTINCT CourseName
FROM Enrolment
WHERE CourseCode = 'C10';

UPDATE Course
SET CourseName = 'Databases 2'
WHERE CourseCode = 'C10';

Section E: Spot the Error

A — A DELETE statement needs FROM before the table name. It should be DELETE FROM ClassList. This deletes the student's row, which also removes the teacher's phone number stored only in that row (a delete anomaly).

D — The new teacher has no student, so the studentId primary key would be empty. The insert fails. This is an insert anomaly: a fact about a teacher can't be stored without an unrelated student record.

C — Ms Lee's phone number is repeated in many rows. Filtering by one studentId updates only one copy and leaves the others with the old number (inconsistent copies). The WHERE should be teacher = 'Ms Lee' so every copy changes.

C — Because the teacher's name is repeated in every row, it must be retyped each time. Typing 'Ms Leigh' creates a different version of the same teacher and gives inconsistent copies. It should match the existing 'Ms Lee'.

A — SELECT is misspelled as SELCT. It should be SELECT DISTINCT teacher, so the query lists each teacher and phone pair once. Two different phone numbers for Ms Lee would reveal inconsistent copies.