Third normal form (3NF) is a property of a relational schema: for every nontrivial functional dependency X → A, either X is a superkey or A is a prime attribute. A prime attribute belongs to at least one candidate key. This formal test is more precise than the familiar shorthand “no transitive dependencies.”
What the terms in the definition mean
- Functional dependency (X → A): In every valid state of the relation, any two rows that agree on attributes X must also agree on A.
- Nontrivial dependency: The right-hand attribute is not already part of the left-hand set; for example, X → A is trivial if A is in X.
- Superkey: A set of attributes that functionally determines every attribute in the relation.
- Candidate key: A minimal superkey—removing any attribute from it means it no longer determines the whole relation.
- Prime attribute: An attribute that appears in at least one candidate key. An attribute in no candidate key is nonprime.
For a dependency with several attributes on the right, such as X → {A, B}, evaluate the condition separately for A and B.
As an Amazon Associate I earn from qualifying purchases.
How to test a relation for 3NF
- Write down the meaningful functional dependencies implied by the application’s rules. Dependencies describe constraints on all valid data, not just patterns that happen to appear in a few current rows.
- Find every candidate key for the relation. Do not assume the chosen primary key is the only candidate key.
- For each nontrivial dependency X → A, check whether X is a superkey. If it is, that dependency satisfies the 3NF condition.
- If X is not a superkey, check whether A is prime. If it is not, the dependency violates 3NF.
- Repeat for every attribute on every dependency’s right-hand side. The relation is in 3NF only if all dependencies pass.
Example: a transitive dependency that violates 3NF
Consider relation R(A, B, C) with dependencies A → B and B → C. Suppose A is a key and C is nonprime. The dependency A → B passes because its determinant is a key. But B → C fails: B is not a superkey, and C is not prime. Thus A determines C through B, creating the familiar transitive-dependency case. The University of Wollongong uses this pattern to illustrate a 3NF violation (lecture materials).
Free tools Windows power users keep installed
One-click scans. No signup required.
Why “no transitive dependencies” is only a shorthand
The shorthand is helpful for common designs where a key determines non-key attributes and one non-key attribute determines another. However, the formal rule also accounts for relations with overlapping candidate keys. In those cases, a dependency can have a determinant that is not a superkey and still satisfy 3NF because its right-hand attribute is prime. The prime-attribute clause is why the full definition is needed.
#1 Best Overall
How 3NF differs from BCNF
Boyce–Codd normal form (BCNF) is stricter. For every nontrivial functional dependency, BCNF requires the determinant to be a superkey. Unlike 3NF, it does not allow a non-superkey determinant merely because the right-hand attribute is prime.
For example, consider LOCATION(city, street, zipcode) with dependencies (city, street) → zipcode and zipcode → city. The candidate keys are (city, street) and (zipcode, street), so both city and zipcode are prime. The dependency zipcode → city passes 3NF because city is prime, even though zipcode alone is not a superkey. It therefore violates BCNF.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Why use 3NF
Normalization organizes data around its functional dependencies to reduce repeated facts and the anomalies that repetition can cause. 3NF is often a practical compromise: decomposing a relation can reduce redundancy, while a 3NF synthesis can provide a lossless-join decomposition that preserves dependencies. BCNF may remove more redundancy, but a BCNF decomposition can fail to preserve every dependency, potentially making some constraints harder to enforce without joining relations. More relations can also mean more joins and more complex queries. The right design depends on the application’s actual dependencies and operational needs.
Recommended Free Tools
Quick Recap
Rank #3
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
