← QCE Digital Solutions Unit 2

Deletion anomaly

Deleting a row also wipes out a separate fact stored only in that row, e.g. deleting the last student enrolled in a course also erases the course details.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 46

Understanding Deletion anomaly in QCE Digital Solutions Unit 2

What it does: Because course facts share a table with student facts, one row holds both. Deleting the last student's row removes the course facts. The course teacher was stored only in Maya's row, so deleting the row also deletes the course details. That is a deletion anomaly. A deletion anomaly happens when removing one fact accidentally removes another. The second definition describes an insertion anomaly.

Common mistakes: REMOVE is not an SQL keyword. Rows are deleted with DELETE FROM. Student 7 may be the last student in a course, so deleting the row could also erase the course details (a deletion anomaly). SQL uses a single = to compare values in a WHERE clause, not ==. If Sam is the only IT101 student, this delete also wipes out the IT101 course details (a deletion anomaly).

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 Deletion anomaly to a class →

This module's key terms are also in a printable crossword (Modules 144–185) and word search (Modules 144–228).

Take this worksheet further — free with Cloodit

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

Learning

Duo

A table stores StudentID, StudentName, CourseCode and CourseTeacher in one row per enrolment. Maya is the only student in course DIG101. Which describes what happens if her row is deleted?

Only Maya's enrolment is removed and nothing else is lostThe teacher for DIG101 is lost too, which is a deletion anomaly

Matches

Deleting the last student in Robotics is safe because the Robotics course details will stay in the table.

↓

In a single unnormalised table the Robotics details exist only in that student's row, so deleting the row also erases the course name and teacher.

Applying

Meanings

Term

deletion anomaly

Meaning

Deleting a row also wipes out a separate fact stored only in that row, e.g. deleting the last student enrolled in a course also erases the course details.

Practice

-- Student 7 is the only student in DS101

Testing

Gaps

FROM Enrolment WHERE studentId = 7;

Check Answer

Spots

1REMOVE FROM Enrolment

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 table stores StudentID, StudentName, CourseCode and CourseTeacher in one row per enrolment. Maya is the only student in course DIG101. Which describes what happens if her row is deleted?

A. The teacher for DIG101 is lost too, which is a deletion anomaly ✓

B. Only Maya's enrolment is removed and nothing else is lost

The course teacher was stored only in Maya's row, so deleting the row also deletes the course details. That is a deletion anomaly.

2. Which statement best defines a deletion anomaly?

A. Adding a row fails because some other data is missing

B. Deleting a row also loses a separate fact that was stored only in that row ✓

A deletion anomaly happens when removing one fact accidentally removes another. The second definition describes an insertion anomaly.

3. Which delete would cause a deletion anomaly in the single Enrolment table (StudentID, CourseCode, CourseName)?

A. DELETE FROM Enrolment WHERE StudentID = 7; when student 7 is the last one in the course ✓

B. DELETE FROM Enrolment WHERE StudentID = 7; when 20 other students are in the same course

If other students are enrolled, the course details still exist in other rows. The anomaly only occurs when the deleted row is the last one holding those details.

4. A school stores course details in a Course table and enrolments in a separate Enrolment table. What happens when the last student leaves a course?

A. The course details stay in the Course table ✓

B. The course details are lost with the student's enrolment

Separating course data into its own table means deleting an enrolment row no longer removes the course. This avoids the deletion anomaly.

5. What is the best fix for a deletion anomaly caused by course details sitting in a student enrolment table?

A. Never delete any rows from the table

B. Move the course details into their own table and link it with a foreign key ✓

Normalising so each fact is stored in one place stops one deletion from wiping out another fact. Refusing to delete rows is not a real fix.

Match the Pairs

DELETE FROM Enrolment WHERE StudentID = 7; → Removes the row for student 7. If that was the only row holding a course's details, those details are lost too (a deletion anomaly).

deletion anomaly → Deleting a row also wipes out a separate fact that was stored only in that row.

Course details (CourseName, Teacher) are stored in the same table as student enrolments, so they are only kept while a student is enrolled. → Enrolment(StudentID, StudentName, CourseID, CourseName, Teacher)

Normalising fixes the deletion anomaly by moving course details into their own table, so deleting an enrolment keeps the course. → Course(CourseID, CourseName, Teacher) Enrolment(StudentID, CourseID)

Deleting the last student in Robotics is safe because the Robotics course details will stay in the table. → In a single unnormalised table the Robotics details exist only in that student's row, so deleting the row also erases the course name and teacher.

A deletion anomaly happens when you add a new course and it cannot be saved until a student enrols. → This describes an insertion anomaly, not a deletion anomaly. A deletion anomaly happens when deleting a row loses a separate fact.

Key Term

deletion anomaly

Deleting a row also wipes out a separate fact stored only in that row, e.g. deleting the last student enrolled in a course also erases the course details.

Fill in the Blanks

1. DELETE FROM Enrolment WHERE studentId = 7;

Hint: Type the SQL keyword that removes rows from a table.

2. DELETE FROM Enrolment WHERE studentId = 7;

Hint: Type the keyword that limits which rows are removed, so only one student's row goes.

3. DELETE FROM Enrolment WHERE studentId = 7; SELECT courseName FROM Enrolment WHERE courseCode = 'IT101';

Hint: Type the column that holds the course detail, which is now lost if student 7 was the last one enrolled.

4. DELETE FROM Enrolment WHERE studentId = 7;

Hint: Type the comparison operator that matches one exact student ID.

5. -- Course details are in their own table DELETE FROM Enrolment WHERE studentId = 7; SELECT * FROM Course;

Hint: Type the table name that keeps the course details separate, so they survive the deletion.

Spot the Error

Question 1

A REMOVE FROM Enrolment

B WHERE studentId = 7;

C SELECT * FROM Enrolment

Line A — REMOVE is not an SQL keyword. Rows are deleted with DELETE FROM. Student 7 may be the last student in a course, so deleting the row could also erase the course details (a deletion anomaly).

Question 2

A -- Table: Enrolment

B -- Row: 7, Sam, IT101, Coding

C DELETE FROM Enrolment

D WHERE studentId == 7;

Line D — SQL uses a single = to compare values in a WHERE clause, not ==. If Sam is the only IT101 student, this delete also wipes out the IT101 course details (a deletion anomaly).

Question 3

A DELETE FROM Enrolment

B WHERE studentId = 7;

C SELECT * IN Enrolment

D WHERE courseCode = 'IT101';

Line C — A SELECT needs FROM to name the table, not IN. It should be SELECT * FROM Enrolment. The check would show no IT101 rows left, so the course details are lost.

Question 4

A DELETE FROM Enrolment

B WHERE studentId = 7;

C SELECT courseName teacher

D FROM Enrolment;

Line C — Columns in a SELECT list must be separated by a comma: SELECT courseName, teacher. After the delete, this query finds nothing for the course. That shows the deletion anomaly.

Question 5

A CREATE TABLE Course (

B code CHAR(5) PRIMARY KEY

C courseName VARCHAR(30)

D );

Line B — A comma is missing at the end of the code column definition, so the next column is a syntax error. It should end with a comma. Storing course details in their own Course table avoids the deletion anomaly.

Student with a class code? Join your class →