COMPUTER SCIENCE / DATABASE DESIGN
Database Normalization
From messy, repeating data to clean, dependency-safe tables (1NF › BCNF)
Normalization is the process of organizing tables so that each fact is stored in exactly one place. Skip it,
and a database quietly accumulates anomalies: updating one row leaves a stale copy of the same fact
somewhere else, deleting a row erases information that had nothing to do with what you meant to delete,
and inserting new data forces you to invent placeholder values just to satisfy the schema. Each "normal
form" below is a stricter rule that removes one specific category of these problems — and each one
assumes the table already satisfies the rule before it.
UNF 1NF 2NF 3NF BCNF
Raw / repeating groups Atomic columns No partial dependency No transitive dependency Every determinant is a key
Each normal form is a stricter constraint building on the one before it.
Starting Point: An Unnormalized Table
Consider a table tracking course enrollments that stores everything in one place, including repeating
course values for a student and a derived field.
UNNORMALIZED
StudentID StudentName Courses InstructorOffice
S1 Ayesha Khan CS101, CS102 Room 204
S2 Bilal Ahmed CS101 Room 204
Problem: the Courses column holds multiple values in one cell, so a single field cannot be queried or updated atomically.
1NF — First Normal Form
Rule: every column holds a single, atomic value, and each row is uniquely identifiable — no repeating
groups or comma-packed lists.
Database Normalization: 1NF Through BCNF Page 1
1NF
StudentID StudentName Course InstructorOffice
S1 Ayesha Khan CS101 Room 204
S1 Ayesha Khan CS102 Room 210
S2 Bilal Ahmed CS101 Room 204
Better, but StudentName repeats for every course, and InstructorOffice depends only on the course, not the student.
2NF — Second Normal Form
Rule: the table is in 1NF, and every non-key column depends on the entire primary key — not just part of it.
This only matters when the key is composite, as (StudentID, Course) is here. StudentName depends solely
on StudentID, so it is split into its own table.
2NF — Students
StudentID StudentName
S1 Ayesha Khan
S2 Bilal Ahmed
2NF — Enrollments
StudentID Course InstructorOffice
S1 CS101 Room 204
S1 CS102 Room 210
S2 CS101 Room 204
Removed the partial dependency, but InstructorOffice still depends on Course, not on the
(StudentID, Course) pair as a whole.
3NF — Third Normal Form
Rule: the table is in 2NF, and no non-key column depends on another non-key column (a transitive
dependency). InstructorOffice depends on Course, not on the enrollment itself, so it moves to a Courses
table.
3NF — Courses
Course InstructorOffice
CS101 Room 204
CS102 Room 210
Database Normalization: 1NF Through BCNF Page 2
3NF — Enrollments
StudentID Course
S1 CS101
S1 CS102
S2 CS101
Every fact now lives in exactly one place: student names in
Students, office locations in Courses, and only the
relationship itself in Enrollments.
BCNF — Boyce-Codd Normal Form
3NF is usually enough in practice, but it allows one edge case to slip through: a table can be in 3NF and still
have a non-trivial functional dependency whose left-hand side is not a candidate key, if that determinant
happens to also be a candidate key for something else in the same table. BCNF closes this gap with a single,
stricter rule: for every functional dependency X › Y in the table, X must be a candidate key. A classic
example is a table of (Student, Subject, Tutor) where each tutor teaches only one subject, but a subject can
have several tutors — Tutor › Subject holds even though Tutor isn't a key on its own, so the table still needs
to be split further to satisfy BCNF.
Quick reference
• 1NF: atomic columns, no repeating groups.
• 2NF: 1NF + no partial dependency on part of a composite key.
• 3NF: 2NF + no transitive dependency through another non-key column.
• BCNF: every determinant of a functional dependency is a candidate key.
• Most production schemas stop at 3NF; BCNF and beyond matter most in tables with overlapping
composite keys.
When to Denormalize
Normalization optimizes for data integrity, not read speed — a fully normalized schema often means more
joins per query. It's common, and reasonable, to selectively denormalize in read-heavy systems: caching a
computed total, duplicating a rarely-changing field to avoid a join, or flattening a reporting table. The key is
that this should be a deliberate performance trade-off made after understanding the normalized design,
not a shortcut taken to avoid designing the schema properly in the first place.
A worked reference on relational database normalization, from unnormalized data through BCNF. Typeset in Lora and Poppins.
Database Normalization: 1NF Through BCNF Page 3