Deletion anomaly

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

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

Left column		Right column
1DELETE FROM Enrolment WHERE StudentID = 7;		AThis describes an insertion anomaly, not a deletion anomaly. A deletion anomaly happens when deleting a row loses a separate fact.
2deletion anomaly		BCourse(CourseID, CourseName, Teacher) Enrolment(StudentID, CourseID)
3Course details (CourseName, Teacher) are stored in the same table as student enrolments, so they are only kept while a student is enrolled.		CDeleting a row also wipes out a separate fact that was stored only in that row.
4Normalising fixes the deletion anomaly by moving course details into their own table, so deleting an enrolment keeps the course.		DIn 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.
5Deleting the last student in Robotics is safe because the Robotics course details will stay in the table.		EEnrolment(StudentID, StudentName, CourseID, CourseName, Teacher)
6A deletion anomaly happens when you add a new course and it cannot be saved until a student enrols.		FRemoves the row for student 7. If that was the only row holding a course's details, those details are lost too (a deletion anomaly).

Definitions

deletion anomaly

Fill in the Blanks

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

______ FROM Enrolment WHERE studentId = 7;

DELETE FROM Enrolment _____ studentId = 7;

DELETE FROM Enrolment WHERE studentId = 7; SELECT __________ FROM Enrolment WHERE courseCode = 'IT101';

DELETE FROM Enrolment WHERE studentId _ 7;

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

Spot the Error

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

Question 1

A REMOVE FROM Enrolment

B WHERE studentId = 7;

C SELECT * FROM Enrolment

Question 2

A -- Table: Enrolment

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

C DELETE FROM Enrolment

D WHERE studentId == 7;

Question 3

A DELETE FROM Enrolment

B WHERE studentId = 7;

C SELECT * IN Enrolment

D WHERE courseCode = 'IT101';

Question 4

A DELETE FROM Enrolment

B WHERE studentId = 7;

C SELECT courseName teacher

D FROM Enrolment;

Question 5

A CREATE TABLE Course (

B code CHAR(5) PRIMARY KEY

C courseName VARCHAR(30)

D );

Answer Key

Deletion anomaly

Section A: Quick Questions

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

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

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

A — The course details stay in the Course table

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

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

Section C: Definitions

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.

Section D: Fill in the Blanks

DELETE FROM Enrolment WHERE studentId = 7;

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

DELETE FROM Enrolment WHERE studentId = 7;

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

Section E: Spot the Error

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

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

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.

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.

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.