← QCE Digital Solutions Unit 2

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.

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

Understanding Insertion anomaly in QCE Digital Solutions Unit 2

What it does: The student fields are filled with NULL just to store the new course, which shows the new-course-with-no-student insertion anomaly. Mixing course facts and student facts in one table means a course row cannot exist without a student row. Splitting them into separate tables fixes this. Dummy rows create false records. A count of students would include 'TBA', which makes the data incorrect. The same anomaly can work the other way. A new fact can't be stored cleanly without unrelated data, so NULLs are forced in. Normalisation separates facts about different entities, so each can be inserted independently.

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

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 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 school stores students and their courses in ONE table. A new course, Robotics, has no students yet. Which statement describes the insertion anomaly?

Robotics cannot be stored without adding a fake or empty student rowRobotics is stored twice, so the copies can disagree

Matches

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.

Applying

Meanings

Term

insertion anomaly

Meaning

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.

Practice

INSERT INTO subjects (subjectId, name, teacher)

Testing

Gaps

VALUES (, NULL, 'DS11', 'Robotics');

Check Answer

Spots

2(sid, code, title)

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

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

An insertion anomaly means a new fact (the course) can't be saved until unrelated data (a student) exists. Duplicated copies disagreeing is an update anomaly.

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

The second option is a deletion anomaly. The first is an insertion anomaly because the course cannot be added by itself.

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

With no student yet, the student fields must be left as NULL. These forced nulls are a sign of an insertion anomaly.

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 ✓

Mixing course facts and student facts in one table means a course row cannot exist without a student row. Splitting them into separate tables fixes this.

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

Dummy rows create false records. A count of students would include 'TBA', which makes the data incorrect.

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.

Key Term

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.

Fill in the Blanks

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

Hint: Type the keyword for a missing value that is forced into the student column because no student exists yet.

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

Hint: Type the constraint that stops a new course being stored without a student.

3. SELECT * FROM enrol WHERE studentId IS NULL;

Hint: Type the condition that finds rows where the student data is missing, which are the leftover rows from adding a course alone.

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

Hint: Type the keyword that supplies the data for a new row, leaving the student fields empty.

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

Hint: Type the name of a separate table that stores only course facts, which removes the insertion anomaly.

Spot the Error

Question 1

A INSERT INTO Enrol

B (sid, code, title)

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

D -- sid is NULL: no student yet

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

Question 2

A INSERT INTO Enrol

B (sid, code, title)

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

D -- 0 is a dummy student row

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

Question 3

A -- Course exists, no enrolment

B SELECT title, FROM Course

C WHERE code = 'ART9';

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

Question 4

A INSERT INTO Enrol

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

C -- No student row is needed

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

Question 5

A IF course is new THEN

B sid == NULL

C INSERT code, title, sid

D END IF

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

Student with a class code? Join your class →