3NF and BCNF

Third normal form: nothing but the key

Student(StudentID, StudentName, DepartmentID, DepartmentName), key StudentID:

The key is one column, so the table is in 2NF. But:

• StudentID → DepartmentID
• DepartmentID → DepartmentName

DepartmentName depends on the key only through DepartmentID: a transitive dependency. "Physics" repeats for every Physics student, and renaming the department means updating all of them. Split it:

• Student(StudentID, StudentName, DepartmentID)
• Department(DepartmentID, DepartmentName)

Formally, a table is in 3NF when for every non-trivial FD X → A, either X is a superkey or A is prime (part of some candidate key). "No transitive dependencies" is the friendly shortcut. It's right for simple tables, but the formal rule matters when a table has several overlapping candidate keys, as the next example shows.

Boyce–Codd normal form: every determinant is a key

BCNF drops the "or A is prime" escape: for every non-trivial FD X → Y, X must be a superkey.

Teaching(Student, Course, Instructor), with these rules:

• (Student, Course) → Instructor: a student takes each course with one instructor.
• Instructor → Course: each instructor teaches exactly one course.

The candidate keys are (Student, Course) and (Student, Instructor).

Instructor → Course has a determinant that isn't a superkey (many students share an instructor), so the table . But is prime (it's in a candidate key), so the table . The cost: which course an instructor teaches is repeated for every one of their students.