DBMS Normalization Explained: 1NF to BCNF With Real Examples

Every DBMS course teaches normalization the same broken way: a new example for every normal form, so you never see why each one exists — just a rule to memorize. Here's the same table, taken through all four, so you can watch what's actually being fixed at each step.

The starting table

A college is storing student enrollments like this:

Looks reasonable. It's actually broken in four different ways, and each normal form fixes exactly one.

1NF — every cell holds one value

First normal form just says: no repeating groups, no multi-valued cells. If somewhere a CourseID column held "C101, C102" for one row instead of two separate rows, that violates 1NF. Our table above is already in 1NF — every cell is atomic. This is usually the one step nobody gets wrong, because it's the one that would look obviously weird in a spreadsheet.

2NF — no partial dependency on part of a composite key

The primary key here is (StudentID, CourseID) together — you need both to identify a row uniquely. 2NF asks: does any non-key column depend on only part of that key?

StudentName depends only on StudentID (Aisha is Aisha regardless of which course). CourseName, Instructor, and InstructorPhone depend only on CourseID. Neither depends on the full composite key — that's a partial dependency, and it's a 2NF violation.

Fix: split into three tables.

Student(StudentID, StudentName)
Course(CourseID, CourseName, Instructor, InstructorPhone)
Enrollment(StudentID, CourseID)

Now every non-key column in each table depends on that table's whole key.

3NF — no transitive dependency

Look at the Course table now: CourseID → Instructor → InstructorPhone. The phone number depends on the instructor, and the instructor depends on the course — so the phone number depends on CourseID only transitively, through Instructor. That's a 3NF violation: a non-key column depending on another non-key column instead of directly on the key.

Fix: split Instructor out.

Course(CourseID, CourseName, Instructor)
Instructor(Instructor, InstructorPhone)

This is also the practical reason 3NF matters: without it, updating one instructor's phone number means finding and editing it in every course row they teach. Miss one, and your data disagrees with itself.

BCNF — every determinant is a candidate key

BCNF (Boyce-Codd Normal Form) is 3NF's stricter sibling. 3NF allows one edge case BCNF doesn't: a non-key attribute determining part of the key. It shows up with overlapping composite keys — for example, if (StudentID, CourseID) determines the row, but CourseID alone determines Instructor, and in some data set Instructor also determines which CourseID they're allowed to teach (a 1:1 instructor-to-course constraint), you'd have a determinant (Instructor) that isn't itself a full candidate key. Most tables that satisfy 3NF also satisfy BCNF — the gap only opens with these overlapping-key situations, which is why BCNF questions in a viva usually come as "give an example where 3NF holds but BCNF doesn't," not as a redesign exercise.

The one-line version, for your viva

• 1NF: atomic values, no repeating groups.
• 2NF: no non-key column depends on only part of a composite key.
• 3NF: no non-key column depends on another non-key column.
• BCNF: every determinant must be a candidate key — 3NF with the loophole closed.

If an examiner asks "why normalize at all," the answer is always the same: to kill update, insert, and delete anomalies — the situations where fixing one fact means editing it in five places, or where you can't add a course until a student enrolls in it, or where deleting the last student in a course accidentally deletes the course's own information too.

---

Sumantrak is an AI mentor built for Indian engineering, diploma, BCA and BSc CS/IT students — DBMS, OS, and the rest of your syllabus, tuned to your actual branch. Free to start →