1NF, 2NF, 3NF, BCNF: each one closes a specific loophole
The normal forms are not arbitrary levels — each one forbids a specific, named kind of functional dependency that causes anomalies, and each is strictly stricter than the last.
After this lesson
You should be able to
- Check whether a relation is in 1NF, 2NF, and 3NF, given its functional dependencies.
- Explain what a lossless-join decomposition guarantees, and why it matters.
1NF: no repeating groups, one value per cell
First Normal Form requires every attribute to hold a single, indivisible value — no comma-separated list of phone numbers crammed into one column, no array-like cell. A Student row with phone_numbers = "9876543210,9123456789" violates 1NF; fixing it means either a separate phone_numbers table or, if the domain genuinely limits to two numbers, two named columns.
2NF and 3NF: closing the loophole one FD at a time
Second Normal Form applies specifically when a relation has a composite primary key, and requires every non-key attribute to depend on the whole key, not just part of it. In Enrolls(roll_number, course_id, course_title), the primary key is (roll_number, course_id), but course_title depends only on course_id — a partial dependency, violating 2NF, and exactly the kind of dependency that caused this lesson's own update anomaly example.
Third Normal Form goes further, forbidding a transitive dependency: a non-key attribute depending on another non-key attribute instead of the key directly. Student(roll_number, branch, hod_name) has roll_number to branch and branch to hod_name — hod_name depends on branch, not directly on roll_number, so it transitively depends on the key through branch. This violates 3NF, and the fix is the same one this unit already used: make Department its own relation.
BCNF: closing the last loophole 3NF leaves open
Boyce-Codd Normal Form requires that for every functional dependency X to Y in the relation, X must be a candidate key — a stricter rule than 3NF, which allows a narrow exception 3NF does not close. Achieving BCNF sometimes requires a decomposition that loses the ability to enforce a particular FD directly through a key, which is why 3NF remains an accepted practical compromise in some designs.
A decomposition is lossless-join when joining the resulting smaller relations back together, using their shared attributes, reproduces exactly the original relation — no extra spurious rows, no missing rows. This is the correctness guarantee mentioned in the previous lesson: a decomposition that is not lossless-join can actively corrupt query results by inventing combinations of facts that never existed together, which is why every decomposition in this course is checked against this property before being accepted.
Try it yourself
Given Employee(emp_id, dept_id, dept_location) with the functional dependencies emp_id to dept_id and dept_id to dept_location, identify which normal form this relation violates and why.
Need a hint?
dept_location depends on dept_id, which is not the primary key — dept_id itself depends on emp_id, so this is a chain, not a direct dependency on the key.
Check the worked solution
This violates 3NF: dept_location depends transitively on emp_id through dept_id (emp_id to dept_id to dept_location), rather than directly on the primary key emp_id. The fix is decomposition: Employee(emp_id, dept_id) and Department(dept_id, dept_location), removing the transitive chain entirely.
Quick check
Why does a lossless-join decomposition matter, even though it can sometimes make enforcing a functional dependency harder?
Why this lesson exists
Syllabus mapping
FIRST, Second, Third Normal forms, BCNF, Lossless join Decomposition
Maps to course outcome CO3.