At the heart of normalization lies the problem of data redundancy. Redundancy occurs
when the same piece of information is stored multiple times within a database. While some
level of redundancy might occasionally be introduced for performance reasons (a concept
we'll touch upon later as denormalization), uncontrolled redundancy is generally undesirable
because it leads to a host of problems known as anomalies. These anomalies compromise
the accuracy and consistency of your data, making the database difficult to manage and
prone to errors.

Let's illustrate these anomalies using a common scenario. Imagine a single database table
designed to store information about students, their courses, and the instructors teaching
those courses:

Table: Student_Course_Instructor

StudentI StudentNa StudentMaj CourseI CourseTit InstructorNa InstructorDe
D me or D le me pt
S101 Alice CS CS101 Intro to Prof. Smith Computer
DB Sci.
S101 Alice CS MA201 Calculus I Prof. Jones Mathematic
s
S102 Bob EE CS101 Intro to Prof. Smith Computer
DB Sci.
S103 Charlie CS CS101 Intro to Prof. Smith Computer
DB Sci.

In this table, notice that StudentName and StudentMajor are repeated for Alice because
she's taking two courses. Similarly, CourseTitle, InstructorName, and
InstructorDept are repeated for CS101 and Prof. Smith for multiple students. This
repetition is data redundancy, and it sets the stage for the following problems:

6.1.1 Insertion Anomalies

An insertion anomaly occurs when you cannot insert new, valid data into the database
without also inserting redundant or artificial data, or without being able to insert a piece of
data independently.

Example Scenario:
● Adding a New Instructor: Suppose a new instructor, Prof. Green, joins the
Mathematics Department, but they haven't been assigned to teach any courses yet. If
we only have the Student_Course_Instructor table, how do we record Prof.
Green's details? We would be forced to create a dummy StudentID and CourseID
to add their information, or we couldn't add them at all until they were assigned a
course. This couples the Instructor information with Course and Student
information unnecessarily.
● Adding a New Course: Similarly, if a new course, "Data Structures," (CS301), is
introduced, but no students have enrolled yet, and no instructor is assigned, we face
the same dilemma. We can't add CS301 and its CourseTitle without associating it
with a student and instructor.

6.1.2 Deletion Anomalies

a deletion anomaly occurs when the deletion of a specific record unintentionally causes the
loss of other, seemingly unrelated, valuable information.

Example Scenario:
● Student Drops a Course: If student S102 (Bob) drops CS101, and if Bob was the
only student taking CS101, deleting Bob's record would also delete the information
about CS101 (its CourseTitle), Prof. Smith, and Prof. Smith's
Department. This information about the course and instructor is valuable and
should persist even if no students are currently enrolled in that specific course.

6.1.3 Update Anomalies

An update anomaly occurs when a single piece of data is stored redundantly in multiple
places, and updating one instance of it requires updating all other instances. Failure to
update all instances leads to inconsistent and contradictory data.

Example Scenario:
● Changing an Instructor's Department: Suppose Prof. Smith's department changes
from "Computer Sci." to "School of Engineering." In our
Student_Course_Instructor table, Prof. Smith's department is listed multiple
times (for S101, S102, S103). To maintain data accuracy, we would need to find and
update every single row where Prof. Smith appears. If we miss even one
instance, the database will contain conflicting information about Prof. Smith's
department, leading to data inconsistency.

Normalization provides a systematic methodology to address these anomalies by
decomposing large, anomaly-prone tables into smaller, well-structured tables, ensuring that
each fact is stored in exactly one place.