Modification Anomalies

Modification Anomalies and Normalization

Insert Anomaly

Unnormalized Table (Before Insert)

Student_ID	Student_Name	Course	Instructor
101	Alice	DBMS	Dr. Smith
102	Bob	OOP	Dr. Johnson

Issue: Insert Anomaly

Suppose a new course, “Networking”, is introduced but no students have enrolled yet. Due to the table structure, we cannot add the course without associating it with at least one student.

Normalization Solution

We split the data into separate tables:

Students Table:

Student_ID	Student_Name
101	Alice
102	Bob

Courses Table:

Course_ID	Course_Name
1	DBMS
2	OOP
3	Networking

Enrollments Table:

Student_ID	Course_ID
101	1
102	2

Now, we can insert new courses without requiring student enrollment.

---

Delete Anomaly

Unnormalized Table (Before Deletion)

Student_ID	Student_Name	Course	Instructor
101	Alice	DBMS	Dr. Smith
102	Bob	OOP	Dr. Johnson
103	Charlie	DBMS	Dr. Smith

Issue: Delete Anomaly

If Alice and Charlie drop DBMS, we lose all records of Dr. Smith teaching the course, even though DBMS and Dr. Smith should still exist.

Normalization Solution

We introduce separate tables:

Instructors Table:

Instructor_ID	Instructor_Name
1	Dr. Smith
2	Dr. Johnson

Courses Table:

Course_ID	Course_Name	Instructor_ID
1	DBMS	1
2	OOP	2

Deleting students from the enrollment table no longer removes information about courses or instructors.

---

Update Anomaly

Unnormalized Table (Before Update)

Student_ID	Student_Name	Course	Instructor
101	Alice	DBMS	Dr. Smith
102	Bob	OOP	Dr. Johnson
103	Charlie	DBMS	Dr. Smith

Issue: Update Anomaly

If Dr. Smith changes their name to Dr. Adams, we must update multiple rows. If we miss one, we create inconsistent data.

Normalization Solution

We store instructors separately:

Instructors Table:

Instructor_ID	Instructor_Name
1	Dr. Adams
2	Dr. Johnson

Courses Table:

Course_ID	Course_Name	Instructor_ID
1	DBMS	1
2	OOP	2

Updating Dr. Smith’s name in one place reflects the change across all references.