Normalization: 1NF to BCNF
Why tables get split into smaller ones, and what each normal form fixes. One messy table is taken from 1NF through BCNF with the exact anomaly each step removes.
Database normalization organises tables so each fact is stored once: 1NF requires atomic values, 2NF removes dependence on part of a composite key, 3NF removes dependence between non-key columns, and BCNF requires every determinant to be a candidate key. Interviewers expect the 3NF versus BCNF difference and the update, insert and delete anomalies normalization prevents.
Normalization is organising a table so each fact is stored once. 1NF means atomic values, 2NF removes dependence on part of a key, 3NF removes dependence between non-key columns, and BCNF requires every determinant to be a candidate key. The goal is to avoid update, insert and delete anomalies.
- A table is in first normal form (1NF) when every cell holds a single value and there are no repeating groups such as phone1, phone2 and phone3 columns.
- A table in 1NF with a single-column key is automatically in second normal form (2NF), because no partial dependency on part of the key is possible.
- Third normal form (3NF) removes transitive dependencies, where a non-key column depends on the key only through another non-key column.
- 3NF allows a dependency X -> A when A is part of a candidate key, while BCNF requires X itself to be a candidate key.
- BCNF decomposition is lossless but can lose the ability to enforce some dependencies without a join, which is why many practical designs stop at 3NF.
01The problem: one big table
Imagine a single table for a college:
Enrollment(student_id, student_name, course_id, course_title, instructor, instructor_phone)
If a student takes five courses, the student's name is stored five times. If an instructor teaches 200 students, the phone number is stored 200 times. That causes three kinds of trouble.
Update anomaly: the instructor changes phone number and you must update 200 rows. Miss one and the data disagrees with itself.
Insert anomaly: you cannot record a new course with an instructor until a student enrols, because the key needs a student_id.
Delete anomaly: delete the last student from a course and you also lose the fact that the course and instructor exist.
Normalization is the process of splitting the table so each fact lives in exactly one place.
02Functional dependencies, the tool behind it
A functional dependency A -> B says that two rows with the same A must have the same B. student_id -> student_name holds, because a student has one name. course_id -> course_title, instructor holds if each course has one title and one instructor. instructor -> instructor_phone holds if phone numbers are per instructor.
The key of the table is a set of columns that determines all the others. Here (student_id, course_id) determines everything, so it is the candidate key. Every normal form is a rule about how the other dependencies relate to that key.
031NF: atomic values, no repeating groups
A table is in first normal form when every cell holds a single value and there are no repeating groups. A column phones containing "98765, 91234" breaks 1NF. So does having columns phone1, phone2, phone3.
The fix is a separate row per value, or a separate table StudentPhone(student_id, phone). The reason is practical. You cannot search, index or constrain a value hidden inside a comma-separated string without parsing it.
042NF: no partial dependency on the key
This only matters when the key has more than one column. Our key is (student_id, course_id).
student_name depends only on student_id. course_title depends only on course_id. Those are partial dependencies, and they are why names and titles repeat.
Split them out:
Student(student_id, student_name)Course(course_id, course_title, instructor, instructor_phone)Enrollment(student_id, course_id)
Now each student name is stored once. A table with a single-column key that is in 1NF is automatically in 2NF.
053NF: no dependency between non-key columns
Look at Course. The key is course_id, but instructor_phone depends on instructor, and instructor depends on course_id. So the phone depends on the key only through another non-key column. This is a transitive dependency. It reintroduces the update anomaly: two courses by the same instructor store the phone twice.
Split it:
Course(course_id, course_title, instructor_id)Instructor(instructor_id, name, phone)
A common memory line is that every non-key attribute must depend on "the key, the whole key, and nothing but the key".
06BCNF: every determinant is a candidate key
Take a table Teaching(student, subject, instructor) with these rules: a student has one instructor per subject, and each instructor teaches only one subject. So (student, subject) -> instructor and also instructor -> subject.
Candidate keys are (student, subject) and (student, instructor). The dependency instructor -> subject has a left side, instructor, that is not a candidate key. This table is in 3NF, because subject is part of a candidate key, but it is not in BCNF. The anomaly: if an instructor changes the subject they teach, you must update every student's row.
The fix is to split into Instructor(instructor, subject) and Assignment(student, instructor). BCNF decomposition is lossless, meaning you can join the parts back to get the original, but it can lose the ability to enforce some dependencies without a join. That trade-off is why many practical designs stop at 3NF.
07When to denormalize, and interview questions
Normalised schemas are good for writes and correctness. Reporting systems and read-heavy services sometimes copy a column into a second table (denormalisation) so a hot query avoids a join. The price is that you must now keep the copies consistent. Do it deliberately, for a measured problem, not by default.
Q: What are the three anomalies?
A: Update, insert and delete anomalies, all caused by storing one fact in several places or mixing two facts in one row.
Q: Difference between 3NF and BCNF?
A: 3NF allows a dependency X -> A if A is part of a candidate key. BCNF requires X itself to be a candidate key.
Q: Is a table with a single-column primary key always in 2NF?
A: Yes, if it is in 1NF, since no partial dependency on part of the key is possible.
Q: Does normalisation always improve performance?
A: No. It improves integrity and write cost, but it can add joins to reads.