Insertion anomaly

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A school stores students and their courses in ONE table. A new course, Robotics, has no students yet. Which statement describes the insertion anomaly?

A Robotics is stored twice, so the copies can disagree

B Robotics cannot be stored without adding a fake or empty student row

2. Which situation is an example of an insertion anomaly?

A You cannot add a new course to the Enrolment table until a student enrols in it

B You delete the last student in a course and the course details disappear

3. In a single Enrolment table, a new course is added before any student enrols. What must go in the student columns?

A NULL values (forced nulls)

B The course name copied into each column

4. True or false: An insertion anomaly happens because one table mixes facts about two different things, such as courses and students.

A False

B True

5. Which is a risk of using a dummy student row like 'TBA' to store a new course?

A The table now holds fake data that can corrupt counts and reports

B The database automatically removes the course

Match the Pairs

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

Left column		Right column
1INSERT INTO Enrolment (CourseName, StudentID) VALUES ('Robotics', NULL);		AA dummy student ID of 0 is used just to store the course. This creates a fake record and is an insertion anomaly symptom, not a fix.
2Enrolment(StudentID, StudentName, CourseName, Teacher)		BAdds a new course to a single table by forcing a NULL into the student column because no student has enrolled yet
3A new course with no students yet cannot be recorded without adding unrelated student data		COne table mixing student and course facts, so a new course cannot be stored until a student exists
4Splitting the data into separate tables removes the insertion anomaly		DINSERT INTO Course (CourseName, Teacher) VALUES ('Cyber Security', 'Ms Lee');
5INSERT INTO Enrolment (StudentID, CourseName) VALUES (0, 'Robotics');		EThis is wrong because in a single combined table a course row needs student data, so it cannot be stored alone without NULLs or dummy values.
6A new course can be added to the Enrolment table at any time without students, so no insertion anomaly exists.		FINSERT INTO Enrolment VALUES (NULL, NULL, 'Cyber Security', 'Ms Lee');

Definitions

insertion anomaly

Fill in the Blanks

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

INSERT INTO enrol VALUES (____, NULL, 'DS11', 'Robotics');

CREATE TABLE enrol ( studentId INT ________, courseCode TEXT);

SELECT * FROM enrol WHERE studentId _______;

INSERT INTO enrol (courseCode, courseName) ______ ('DS12', 'Databases');

INSERT INTO ______ (courseCode, courseName) VALUES ('DS12', 'Databases');

Spot the Error

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

Question 1

A INSERT INTO Enrol

B (sid, code, title)

C VALUES ('DIG11', 'Art');

D -- sid is NULL: no student yet

Question 2

A INSERT INTO Enrol

B (sid, code, title)

C VALUES (0, 'ART9', Art);

D -- 0 is a dummy student row

Question 3

A -- Course exists, no enrolment

B SELECT title, FROM Course

C WHERE code = 'ART9';

Question 4

A INSERT INTO Enrol

B VALUES ('DIG11', 'Art');

C -- No student row is needed

Question 5

A IF course is new THEN

B sid == NULL

C INSERT code, title, sid

D END IF

Answer Key

Insertion anomaly

Section A: Quick Questions

B — Robotics cannot be stored without adding a fake or empty student row

A — You cannot add a new course to the Enrolment table until a student enrols in it

A — NULL values (forced nulls)

B — True

A — The table now holds fake data that can corrupt counts and reports

Section B: Match the Pairs

INSERT INTO Enrolment (CourseName, StudentID) VALUES ('Robotics', NULL); → Adds a new course to a single table by forcing a NULL into the student column because no student has enrolled yet

Enrolment(StudentID, StudentName, CourseName, Teacher) → One table mixing student and course facts, so a new course cannot be stored until a student exists

A new course with no students yet cannot be recorded without adding unrelated student data → INSERT INTO Enrolment VALUES (NULL, NULL, 'Cyber Security', 'Ms Lee');

Splitting the data into separate tables removes the insertion anomaly → INSERT INTO Course (CourseName, Teacher) VALUES ('Cyber Security', 'Ms Lee');

INSERT INTO Enrolment (StudentID, CourseName) VALUES (0, 'Robotics'); → A dummy student ID of 0 is used just to store the course. This creates a fake record and is an insertion anomaly symptom, not a fix.

A new course can be added to the Enrolment table at any time without students, so no insertion anomaly exists. → This is wrong because in a single combined table a course row needs student data, so it cannot be stored alone without NULLs or dummy values.

Section C: Definitions

insertion anomaly — A problem where a new fact, like a new course, cannot be stored until unrelated data, like a student, exists, forcing null values or dummy rows in a single table.

Section D: Fill in the Blanks

INSERT INTO enrol
VALUES (NULL, NULL, 'DS11', 'Robotics');

CREATE TABLE enrol (
  studentId INT NOT NULL,
  courseCode TEXT);

SELECT * FROM enrol
WHERE studentId IS NULL;

INSERT INTO enrol (courseCode, courseName)
VALUES ('DS12', 'Databases');

INSERT INTO course (courseCode, courseName)
VALUES ('DS12', 'Databases');

Section E: Spot the Error

C — A new course with no student forces a NULL into sid, but a value is still needed for every column. The line should be VALUES (NULL, 'DIG11', 'Art'); so the count matches (sid, code, title).

C — Text values need quotes. It should be 'Art'. The dummy student 0 is another sign of an insertion anomaly: the course cannot be saved without unrelated data.

B — A comma after the last selected column is a syntax error. It should be SELECT title FROM Course. Once courses have their own table, they can exist with no student.

A — After the fix, a new course goes in the Course table. Inserting into Enrol would force a student value or a NULL, which is the insertion anomaly. It should be INSERT INTO Course.

B — Pseudocode assigns with the arrow ←, while == compares. A new course with no student forces sid to be set to NULL, so it should be sid ← NULL.