Insertion Anomaly

Setting the Scene

Recall the running table:

StudentCourse(StudentID, StudentName, CourseID, CourseName,
              InstructorID, InstructorName, InstructorOffice, EnrollmentDate)

Every row exists only because a specific student enrolled in a specific course. That is the problem.

The Failure Scenario

The university hires a new instructor, Dr. Fernandez (I4), assigned to Room 402, who will start teaching next semester but has not yet been assigned any course, and — critically — no student has enrolled in anything he will teach yet.

Try to insert his information:

INSERT INTO StudentCourse (StudentID, StudentName, CourseID, CourseName,
                            InstructorID, InstructorName, InstructorOffice, EnrollmentDate)
VALUES (???, ???, ???, ???, 'I4', 'Dr. Fernandez', 'Room 402', ???)

There is no StudentID, no CourseID, no EnrollmentDate to put in this row — those columns are (part of) the table's primary key, and a relational table cannot hold a row with a null/missing primary key. You are forced into one of two bad choices:

1. Refuse to record Dr. Fernandez at all until some student enrolls in a course he teaches — but that means the database simply cannot represent a true, current fact ("Dr. Fernandez works here, office 402") that the university needs today (e.g., for payroll, or an internal directory).
2. Invent fake placeholder values (StudentID = 'PENDING', CourseID = 'TBD') — which corrupts the key and pollutes every future query ("how many students are enrolled?" now needs to filter out fake rows).

This inability to insert a fact about one entity (an instructor) without also having a fact about an unrelated entity (an enrollment) is the insertion anomaly.

Why This Happens

The root cause is exactly the redundancy from 21.1: instructor facts (InstructorName, InstructorOffice) are not stored in their own table keyed by InstructorID — they are riding along inside a table whose key is (StudentID, CourseID). Any fact that logically belongs to instructors alone gets trapped behind the requirement to also supply a student and a course.

The same problem strikes a brand-new course with no students yet (CourseID = 'C5', CourseName = 'Cloud Computing', assigned to I2, but zero enrollments so far) — you cannot record the course exists until someone enrolls.

Edge Cases

• This is different from not being allowed to insert a duplicate row (a candidate-key violation) — the insertion anomaly is about being unable to insert any row representing a true fact, because the schema forces unrelated facts to travel together.
• A table that already has NULLs allowed in non-key columns does not fix this — the missing values here are in what would need to be the key, which by definition (entity integrity) cannot be null.

Key Takeaways / Interview Angle

• Q: Define insertion anomaly in one sentence. You cannot insert a fact about one entity without artificially also having data for another, unrelated entity, because both are crammed into one table.
• Q: How is it fixed? By decomposing the table so each entity (Student, Course, Instructor) has its own relation with its own key — exactly what 2NF/3NF do later in this chapter (see 21.13, 21.14): an Instructor(InstructorID, InstructorName, InstructorOffice) table lets you insert Dr. Fernandez immediately, with no course or student required.
• Q: Is this only about instructors? No — the same anomaly applies to any entity subordinate to the composite key: e.g., a new Course with no enrollments yet.