Normalization (1NF to BCNF)

The starting point: one messy table

A college tracks enrollments in a single spreadsheet-style table. Here are three rows:

This table has all three classic anomalies. Update anomaly: if Rao moves office, you must edit every row that mentions Rao. Insert anomaly: you cannot record a new course until a student enrolls in it. Delete anomaly: if Meena drops OS201, the fact that Iyer sits in C-04 may vanish with her.

1NF: one value per cell

The courses column holds a list, so the table is not in 1NF. Split it so each row is one student in one course, and add a grade. The key becomes (student_id, course_id). The functional dependencies are now:

• student_id → student_name
• course_id → course_title, instructor
• instructor → instructor_office
• (student_id, course_id) → grade

2NF: remove partial dependencies

2NF only matters when the key is composite. student_name depends on just student_id, which is part of the key, and course_title depends on just course_id. These are partial dependencies. Move each group to a table keyed by the attribute it actually depends on:

• students(student_id, student_name)
• courses(course_id, course_title, instructor, instructor_office)
• enrollments(student_id, course_id, grade)

3NF: remove transitive dependencies

In courses, course_id → instructor → instructor_office. The office describes the instructor, not the course, so it is a transitive dependency on the key. Split it out:

CREATE TABLE students    (student_id TEXT PRIMARY KEY, student_name TEXT NOT NULL);
CREATE TABLE instructors (instructor TEXT PRIMARY KEY, office TEXT NOT NULL);
CREATE TABLE courses (
  course_id    TEXT PRIMARY KEY,
  course_title TEXT NOT NULL,
  instructor   TEXT NOT NULL REFERENCES instructors(instructor)
);
CREATE TABLE enrollments (
  student_id TEXT REFERENCES students(student_id),
  course_id  TEXT REFERENCES courses(course_id),
  grade      CHAR(2),
  PRIMARY KEY (student_id, course_id)
);

All three anomalies are gone. Rao's office lives in one row, a course can exist with no students, and deleting an enrollment deletes nothing else. A handy test for 3NF: every non-key attribute must depend on the key, the whole key, and nothing but the key.

BCNF: when 3NF is not enough

Now add a new rule: each tutor teaches exactly one subject, and each student has one tutor per subject. Table tutoring(student, subject, tutor) has the dependencies (student, subject) → tutor and tutor → subject. Both (student, subject) and (student, tutor) are candidate keys, so every attribute is part of some key. That makes the table 3NF, because 3NF allows a dependency whose right side is a prime attribute.

BCNF does not allow it. tutor → subject has a determinant, tutor, that is not a superkey. The redundancy is real: 'Rao teaches DB' is repeated for every student Rao tutors. Decompose into tutor_subject(tutor, subject) and student_tutor(student, tutor). The decomposition is lossless, but (student, subject) → tutor can no longer be checked inside one table. This is the trade-off interviewers want you to state: 3NF can always preserve dependencies, but BCNF sometimes cannot.

Common mistakes

• Calling a table 2NF-violating when its key is a single column. Partial dependencies need a composite key.
• Reading normal forms from sample data instead of from the business rules. Dependencies are constraints, not coincidences in today's rows.
• Normalizing derived values away and then recomputing them with expensive aggregates on every read. Sometimes a cached total is the right call.
• Forgetting that a normalized schema needs indexes on its foreign keys. Without them the extra joins get slow. See database indexes.

Interview questions