Normalization (1NF, 2NF, 3NF)

Why normalize?

When the same fact is stored in many places, three kinds of anomalies appear:

• Update anomaly — change Rao’s office in one row but forget another → contradictory data.
• Insert anomaly — you can’t record a new course until a student enrolls in it.
• Delete anomaly — delete the only student in a course and you lose the course’s details too.

Normalization reorganises tables so that every fact is stored once.

Functional dependencies

A → B (“A functionally determines B”) means: if two rows have the same A, they must have the same B. Examples:

• StudentID → Name
• CourseID → CourseName, Instructor
• Instructor → Office

Normal forms are rules about which dependencies are allowed.

The normal forms

First Normal Form (1NF)

• Every cell holds one atomic value (no lists, no repeating groups).
• Rows are uniquely identified by a primary key.

Second Normal Form (2NF)

• In 1NF, and
• no partial dependency: no non-key column depends on only part of a composite primary key.

Third Normal Form (3NF)

• In 2NF, and
• no transitive dependency: no non-key column depends on another non-key column.

A handy summary of 3NF: every non-key attribute must depend on “the key, the whole key, and nothing but the key.”

Worked example

Unnormalised:

StudentID	Name	Courses
101	Asha	C1 DBMS Rao B-12 ; C2 OS Iyer A-03

1NF — one value per cell, key = (StudentID, CourseID):

StudentID	Name	CourseID	Course	Instructor	Office
101	Asha	C1	DBMS	Rao	B-12
101	Asha	C2	OS	Iyer	A-03
102	Ravi	C1	DBMS	Rao	B-12

Partial dependencies: StudentID → Name, CourseID → Course, Instructor, Office.

2NF — split by what each column depends on:

• Students(StudentID, Name)
• Enrollments(StudentID, CourseID)
• Courses(CourseID, Course, Instructor, Office)

Transitive dependency in Courses: CourseID → Instructor → Office.

3NF:

• Students(StudentID, Name)
• Enrollments(StudentID, CourseID)
• Courses(CourseID, Course, Instructor)
• Instructors(Instructor, Office)

Every fact now appears exactly once, and a JOIN brings the original view back:

SELECT s.StudentID, s.Name, c.CourseID, c.Course, c.Instructor, i.Office
FROM Students s
JOIN Enrollments e ON e.StudentID = s.StudentID
JOIN Courses c     ON c.CourseID  = e.CourseID
JOIN Instructors i ON i.Instructor = c.Instructor;

Beyond 3NF

• BCNF (Boyce–Codd): for every dependency X → Y, X must be a superkey. Slightly stricter than 3NF.
• 4NF: no multivalued dependencies; 5NF: no join dependencies.

Denormalization

Normalized designs need more joins, which can be slow for read-heavy analytics. Data warehouses sometimes denormalize on purpose (star schemas) — a conscious trade of redundancy for speed.

Common mistakes

• Thinking 1NF is only about “no duplicate rows” — atomic values matter too.
• Splitting tables without keeping the keys needed to join them back (a lossy decomposition).
• Over-normalizing small lookup data where the extra joins aren’t worth it.