AI Glossary / Data engineering and architecture / Intermediate
Third normal form (3NF)
Definition
Third normal form, or 3NF, is a relational database design condition defined through functional dependencies. For every nontrivial dependency X → A, either X must be a superkey or A must be a prime attribute, meaning it belongs to some candidate key. This general definition includes all candidate keys. A familiar introductory explanation is that nonprime facts should depend on keys without problematic partial or transitive dependencies.
What it means
A table can repeat a fact about one entity inside records describing another entity. For example, an employee record may contain a department's name even though the department identifier determines that name. The employee identifier then determines the department name indirectly through the department identifier. Separating the department fact can reduce inconsistent updates. However, the formal definition is more precise than the slogan “no transitive dependencies,” because not every chain of dependencies violates 3NF.
How it works
Establish the relation's functional dependencies and candidate keys. Examine each nontrivial dependency using the superkey-or-prime-attribute condition. When a dependency violates it, consider a decomposition that separates the affected fact while preserving the intended relationships. Check lossless reconstruction and dependency preservation as distinct properties. Standard 3NF synthesis can construct a decomposition with both properties from a suitable dependency model. After logical design, implement keys, references, nullability, and other rules in the chosen database.
Three practical examples
- Staff records: maintain department descriptions in a department relation rather than repeating the current description in every staff record.
- Product catalogs: separate a category's descriptive facts from individual products when the category identifier determines those facts.
- AI experiment management: keep a dataset version's provenance facts with that version instead of copying them into every model run that uses it.
Think of it this way
Think of keeping one authoritative description card for a department and referring to it from employee cards. A department rename then has one intended place to change. The analogy is limited because 3NF is a formal property of dependencies and keys, and historical snapshots or independently meaningful repeated descriptions can require a different model.
Worked example
Consider Staff(StaffID,DeptID,DeptName), with StaffID → DeptID and DeptID → DeptName. Assume StaffID is the only candidate key and many staff members may belong to one department. DeptID is not a superkey of Staff, and DeptName is nonprime, so DeptID → DeptName violates 3NF. Decompose into Staff(StaffID,DeptID) and Department(DeptID,DeptName). If S1 and S2 both belong to D4, their staff rows reference D4 while its current name appears in one department row. The shared DeptID determines the Department attributes, supporting lossless reconstruction under the stated rules. If the original name instead records the department name at hiring, the current-name dependency may be the wrong assumption; time and version identity must be modeled first.
Illustrative case study
Fictional example: Clover Services finds three spellings of one department name across employee records. Its team confirms that the field is intended to represent the current official name, then separates department data and corrects references. It also retains a separate history where historical names matter. This invented case demonstrates dependency-led design rather than automatic cleanup based on repeated text. No real organizational result or quantified benefit is asserted.
Common misconceptions and mistakes
Do not test only the chosen primary key when alternative candidate keys exist. Do not assume a surrogate ID makes every other dependency acceptable. Also, 3NF is weaker than BCNF: the prime-attribute allowance can permit a dependency whose determinant is not a superkey. A 3NF design can still contain redundancy and does not necessarily satisfy fourth normal form. Finally, normalizing individual components is insufficient if their decomposition loses associations or makes necessary dependencies impractical to enforce.
Business use
Third normal form provides a useful baseline for shared operational data where facts must remain consistent as they change. It can clarify ownership and reduce update, insertion, and deletion anomalies. For AI projects, stable fact locations make provenance and version relationships easier to maintain. It is not a universal performance target or a ban on analytical denormalization. If downstream tables repeat facts for a specific workload, document their derivation, refresh rules, and authoritative sources.
People also ask
Is 3NF the same as BCNF?
No. BCNF requires the determinant of every nontrivial functional dependency to be a superkey; 3NF also allows a prime right-hand attribute.
Does every transitive implication violate 3NF?
No. Apply the formal condition to the actual dependencies and candidate keys.
Does a 3NF schema guarantee true data?
No. It organizes dependencies; factual accuracy and other integrity rules still need attention.
Check your understanding
In Staff(StaffID,DeptID,DeptName), DeptID determines DeptName but does not identify an entire staff row, and DeptName belongs to no candidate key. Does that dependency satisfy 3NF?
Answer: No. Its determinant is not a superkey and its dependent attribute is not prime.
Related terms
- Second normal form (2NF)
- Functional dependency
- Transitive Dependency
- Prime Attribute
- Candidate key
- Superkey
- second normal form
- BCNF
- lossless decomposition
- dependency preservation
- relational synthesis
Learn next
take a small relation, list its dependencies, and calculate every candidate key. Evaluate each nontrivial dependency using the formal 3NF test. Propose a decomposition and explain separately its normal-form status, reconstruction property, and ability to enforce the original dependencies without joining the entire dataset.
More in Data engineering and architecture
- Terminology model - A terminology model is a structured description of the concepts used in a subject area, their meanings, characteristics and relationships.
- Test data management - Test data management is the practice of preparing, supplying, maintaining and controlling the data used to test software.
- Theta-join - A theta-join is a relational join that combines tuples from two relations when they satisfy a specified comparison condition, conventionally called theta.
- Thread pool - A thread pool is a managed collection of worker threads that execute submitted tasks, usually reusing threads across multiple tasks.
- Three-schema approach - The three-schema approach separates a database's description into external, conceptual and internal levels.
- Three-tiered architecture - Three-tiered architecture is an application architecture that separates presentation, application logic, and data management into distinct tiers.
Sources
Sources: University of Otago COSC344, Lecture 9: Normalisation, https://www.cs.otago.ac.nz/cosc344/lectures/L9_2022.pdf ; Grant Weddell, University of Waterloo, Schema Refinement, https://cs.uwaterloo.ca/~gweddell/cs348/normalization-handout.pdf ; University of Otago COSC344, Lecture 8: Functional Dependencies, https://www.cs.otago.ac.nz/cosc344/lectures/L8_2022.pdf . Primary instructor passages and excerpts consulted for the general 3NF definition, dependency reasoning, and synthesis properties. The worked schema is original and explicitly distinguishes current facts from historical snapshots.
Previous: Theta-join Next: Thompson sampling