← WA Computer Science ATAR Unit 2

Insertion, update and deletion anomalies

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.

Part of: WA Computer Science ATAR Year 11 — Data Management — Module 43

Understanding Insertion, update and deletion anomalies in WA Computer Science ATAR Unit 2

What it does: If order 7 is the only row holding that product's name and price, deleting the order accidentally destroys the product facts as well. The table's design needs an order row to hold the product, so a product with no order has to be added with missing (NULL) order fields, or cannot be added at all. The captain's details cannot be added without a student row. Needing unrelated data before you can store a fact is an insertion anomaly. Redundant copies are the cause. Storing a fact once in its own table means one UPDATE is enough. The same fact now has conflicting values. This is the result of an update anomaly.

Common mistakes: 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). 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.

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 Insertion, update and deletion anomalies to a class →

This module's key terms are also in a printable crossword (Modules 28–122) and word search (Modules 28–266).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

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?

Deletion anomalyInsertion anomaly

Matches

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.

Applying

Meanings

Term

data anomaly

Meaning

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.

Practice

UPDATE products SET category = 'Writing' WHERE name = 'Pencil';

Testing

Gaps

VALUES (, 'C10');

Check Answer

Spots

1DELETE ClassList

2WHERE studentId = 7;

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 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

The captain's details cannot be added without a student row. Needing unrelated data before you can store a fact is an insertion 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 ✓

Redundant data means one fact is stored many times. If a copy is missed, the table holds conflicting values. This is an update anomaly.

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

Removing one record accidentally destroyed other facts (the product details). That is a deletion 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 ✓

Redundant copies are the cause. Storing a fact once in its own table means one UPDATE is enough.

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

The subject is a separate fact, but it is stored inside a student's row. You cannot insert the subject on its own, so it is an insertion anomaly.

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.

Key Term

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.

Fill in the Blanks

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

Hint: Type the keyword for a missing value, because a new course has no student enrolled yet and so no student ID to store.

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

Hint: Type the clause keyword that picks which rows change. If any rows holding this course are missed, their copies of the name become inconsistent.

3. DELETE FROM Enrolment WHERE StudentID = 12;

Hint: Type the command that removes rows. If this was the only row for a course, the course details are lost too.

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

Hint: Type the keyword that removes repeated values, so you can see whether one course code has more than one version of its name.

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

Hint: Type the name of the separate table that stores each course only once, so one update fixes the name everywhere.

Spot the Error

Question 1

A DELETE ClassList

B WHERE studentId = 7;

C -- Loses Mr Ford's phone too

Line 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).

Question 2

A INSERT INTO ClassList

B (teacher, teacherPhone)

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

D -- Works: studentId is the key

Line 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.

Question 3

A UPDATE ClassList

B SET teacherPhone = '0499 111'

C WHERE studentId = 3;

D SELECT * FROM ClassList;

Line 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.

Question 4

A INSERT INTO ClassList

B (studentId, name, teacher)

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

Line 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'.

Question 5

A SELCT DISTINCT teacher,

B teacherPhone

C FROM ClassList

D WHERE teacher = 'Ms Lee';

Line 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.

Student with a class code? Join your class →