Normal Forms: 1NF, 2NF, 3NF and BCNF

Removing redundancy step by step

Poor designs suffer anomalies: update (changing a department's phone in many rows), insertion (cannot record a new department until it has a student) and deletion (deleting the last student loses the department's details). Normalisation removes them by decomposing relations. 1NF: all attribute values are atomic (no repeating groups or lists in a cell). 2NF: 1NF and no partial dependency, meaning no non-prime attribute (one not in any candidate key) depends on only part of a composite candidate key. 3NF: 2NF and no transitive dependency of a non-prime attribute on a key; formally, for every non-trivial FD X → A, either X is a super key or A is a prime attribute. BCNF (Boyce-Codd): for every non-trivial FD X → A, X must be a super key, with no exception for prime attributes. BCNF is stricter than 3NF; every BCNF relation is in 3NF, but not the reverse. Higher forms (4NF for multi-valued dependencies, 5NF for join dependencies) exist but are rarely needed in practice.

Normalising an enrolment table

Each step removes one kind of dependency.

UNF   ENROL(roll_no, name, dept, dept_phone, courses = {DBMS:A, OS:B})

1NF   ENROL(roll_no, course_id, name, dept, dept_phone, grade)
      key (roll_no, course_id)
      FDs: roll_no -> name, dept      dept -> dept_phone      (roll_no, course_id) -> grade

2NF   remove partial dependencies on part of the key:
      STUDENT(roll_no, name, dept, dept_phone)     ENROLMENT(roll_no, course_id, grade)

3NF   remove transitive dependency roll_no -> dept -> dept_phone:
      STUDENT(roll_no, name, dept)   DEPARTMENT(dept, dept_phone)   ENROLMENT(roll_no, course_id, grade)

BCNF  every determinant is now a key in its relation -> already in BCNF

Keeping one master copy

Normalisation is like keeping each phone number in one address book instead of writing it on every letter you send. Change it once and every reference is up to date.

त्वरित जाँच: A relation has key (roll_no, course_id) and the FD roll_no → name. Which normal form does it violate?

• 1NF
• 2NF
• Only BCNF
• None