Normal Forms (1NF / 2NF / 3NF / BCNF)

Normalization eliminates redundancy and anomalies from database design.

1NF — First Normal Form

Rule: Every cell must have a single atomic value — no repeating groups.

BEFORE 1NF (Violation):

StudentID | Name  | Courses         -- multi-valued cell!
1         | Alice | Math, Physics

AFTER 1NF:

StudentID | Name  | Course
1         | Alice | Math
1         | Alice | Physics   -- one value per cell

Primary key = (StudentID + Course) composite key

2NF — Second Normal Form

Rule: Must be in 1NF + every non-key column must depend on the WHOLE primary key (not just part of it). 2NF only matters when you have a composite primary key.

Partial dependency = non-key column depends on only PART of composite key.

BEFORE 2NF (PK = StudentID + Course):

StudentID | Course  | Name  | CourseCredits
1         | Math    | Alice | 4    -- Name depends ONLY on StudentID (partial dep)
1         | Physics | Alice | 3    -- Credits depend ONLY on Course (partial dep)

AFTER 2NF:

Students (StudentID, Name)
Courses (Course, CourseCredits)
Enrollments (StudentID, Course)  -- junction table

3NF — Third Normal Form

Rule: Must be in 2NF + no transitive dependencies. Non-key columns must depend ONLY on the primary key.

Transitive dependency: PK → Column A → Column B (Column B only indirectly depends on PK).

BEFORE 3NF:

StudentID | Name  | DeptID | DeptHead    -- DeptHead depends on DeptID, not StudentID
1         | Alice | D1     | Dr.Smith
2         | Bob   | D1     | Dr.Smith    -- repeated!

AFTER 3NF:

Students (StudentID, Name, DeptID)
Departments (DeptID, DeptHead)   -- DeptHead moved here

BCNF — Boyce-Codd Normal Form

Rule: For every functional dependency X → Y, X must be a superkey — no exceptions.

3NF: X is superkey OR Y is prime attribute. BCNF: X is superkey. FULL STOP.

Example: Schedule (Student, Course, Teacher)

FDs:
(Student, Course) → Teacher   -- CK → non-prime (OK in both)
Teacher → Course              -- Teacher NOT a superkey → BCNF violation!

BCNF Decomposition:

TeacherInfo (Teacher, Course)   -- Teacher is superkey here
Enrollment (Student, Teacher)   -- (Student, Teacher) is superkey

Summary of Normal Forms

Normal Form	Rule	Removes
1NF	Atomic values, no repeating groups, has PK	Multi-valued cells
2NF	1NF + no partial dependencies	Redundancy from composite keys
3NF	2NF + no transitive dependencies	Non-key to non-key redundancy
BCNF	Every determinant X is a superkey	All remaining redundancy

Memory Hook: 1NF = "Every cell is ONE thing". 2NF = "Non-key needs the WHOLE key". 3NF = "Non-key needs ONLY the key". BCNF = "Determinant must always be a superkey".

3NF vs BCNF Tradeoff

	3NF	BCNF
Redundancy removed	Most	All
Dependency preservation	Always	Sometimes lost
Lossless decomposition	Yes	Yes
Strictness	Less strict	Stricter