3NF
2NF plus no transitive dependence of a non-key attribute on the key.
Level: intermediate
What is it?
Third Normal Form (3NF) requires 2NF and constrains nontrivial functional dependencies so that the determinant is a superkey or the dependent attribute is prime, under the formal definition. Introductory lessons often describe this as removing transitive dependencies of non-key attributes on a key.
Why does it matter?
Transitive dependencies can repeat facts that belong to another entity. Separating those facts reduces anomalies while allowing the original view to be reconstructed using joins.
Mental model
A key should determine attributes that describe the row's entity. If key→department_id and department_id→department_name, storing department_name with each employee repeats a department fact that can change independently.
How it works
- A functional dependency X→Y means rows with equal X values must have equal Y values.
- 3NF's formal definition must be checked across candidate keys, prime attributes, and all relevant functional dependencies.
- A simple transitive dependency of a non-key attribute on the key is a common 3NF violation pattern.
- A 3NF synthesis can preserve dependencies under the standard assumptions, while BCNF decomposition may not preserve all dependencies.
- Normalization aims for lossless and maintainable design, not the maximum possible number of tables.
Steps
- List candidate keys and known functional dependencies.
- Look for non-key facts determined by another non-key determinant.
- Move that fact and determinant into a separate relation with a key, keeping a reference where needed.
- Check that the decomposition is lossless and preserves required constraints.
Real-world usage
- Removing duplicated department, supplier, or category descriptions from transactional tables.
- Explaining why 3NF and BCNF differ.
Common mistakes
- Defining 3NF only by table appearance rather than functional dependencies.
- Assuming every determinant must be a superkey; that is the stricter BCNF rule.
- Decomposing without checking if joins reconstruct the original facts.
Interview questions
What does 3NF try to prevent?
It rules out certain dependencies that make non-key facts depend transitively on a candidate key, subject to the formal 3NF definition.
How is BCNF stricter than 3NF?
BCNF requires every determinant of a nontrivial functional dependency to be a superkey, while 3NF allows a dependency when its right-hand side is prime.