Database normalization organizes table columns and relationships to eliminate redundant data and prevent update, insertion, and deletion anomalies. In college database courses (like CS 186, CS 145, or relational database theory) and software engineering interviews, students frequently encounter a classic dilemma: what is the real difference between Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF)?
While both forms eliminate unwanted functional dependencies, they handle overlapping candidate keys differently. 3NF always guarantees a lossless join and dependency preservation, but it tolerates minor redundancy when a determinant is not a superkey. BCNF eliminates all redundancy caused by functional dependencies, but decomposing into BCNF can sometimes lose critical dependencies.
This guide breaks down 2NF, 3NF, and BCNF rules, computes candidate keys and minimal covers step by step, and walks through two complete textbook examples—including the classic case where 3NF and BCNF produce entirely different table structures.
Normal Form Rules at a Glance
Every normal form builds strictly on the requirements of the form before it:
| Normal Form | Core Requirement | What It Eliminates |
|---|---|---|
| 1NF | Every column value must be atomic (single-valued, no repeating groups or nested arrays). | Repeating groups, multi-valued attributes, nested lists. |
| 2NF | In 1NF + no partial dependencies: no non-prime attribute may depend on a proper subset of any candidate key. | Redundancy from composite primary keys where an attribute depends on only part of the key. |
| 3NF | In 2NF + no transitive dependencies: for every non-trivial X → Y, either X is a superkey OR Y is prime (each attribute in Y belongs to at least one candidate key). | Transitive dependencies where non-key attributes determine other non-key attributes. |
| BCNF | For every non-trivial dependency X → Y, X must be a superkey. | All redundancy caused by functional dependencies, including overlapping candidate keys. |
Essential Definitions
To evaluate these rules accurately, keep four formal definitions clear:
- Superkey: Any set of attributes whose closure contains every attribute in the relation.
- Candidate Key: A minimal superkey. If you remove any attribute from a candidate key, its closure no longer contains the entire relation.
- Prime Attribute: An attribute that belongs to at least one candidate key.
- Non-Prime Attribute: An attribute that does not belong to any candidate key.
Example 1: Where 3NF and BCNF Differ
The textbook classic (from Silberschatz, Korth, and Sudarshan's Database System Concepts) models course tutoring and instructor assignments:
- Schema:
CourseEnrollment(Course, Student, Instructor)or shorthandR(C, S, J) - Functional Dependencies:
CS → J(A student enrolled in a course has exactly one assigned instructor)J → S(Each instructor tutors only one student)
Relation: R(C, S, J)
Dependencies:
CS → J
J → S
Step 1: Attribute Closures and Candidate Keys
To find the candidate keys, compute the attribute closure X⁺ for every combination:
| Attribute Set X | Closure X⁺ | Step-by-Step Expansion | Minimal Superkey? |
|---|---|---|---|
| C | { C } | No dependency has LHS ⊆ { C }. | No |
| S | { S } | No dependency has LHS ⊆ { S }. | No |
| J | { J, S } | J → S adds S. |
No |
| CS | { C, S, J } | CS → J adds J. Reaches all attributes. |
Yes: Candidate Key |
| CJ | { C, J, S } | J → S adds S. Reaches all attributes. |
Yes: Candidate Key |
| JS | { J, S } | J → S adds S. Does not reach C. |
No |
Both CS and CJ determine all attributes { C, S, J }. Neither is a subset of the other, and neither single attribute { C }, { S }, or { J } reaches the full schema. Therefore, the candidate keys are CS and CJ.
- Prime Attributes:
C,S,J(every attribute belongs to at least one candidate key: C is in both, S is in CS, and J is in CJ). - Non-Prime Attributes: None (empty set).
Step 2: Minimal Cover (Canonical Cover)
Before decomposing or synthesizing relations, compute the minimal cover Fc:
- Singleton RHS: Both dependencies already have single-attribute right-hand sides:
CS → JandJ → S. - Extraneous LHS Attributes: In
CS → J, test whether C or S can be eliminated:- Without C: check if
S⁺determines J under{CS → J, J → S}.S⁺ = { S }(does not reach J). C is not extraneous. - Without S: check if
C⁺determines J.C⁺ = { C }(does not reach J). S is not extraneous. - In
J → S, J is a single attribute, so no extraneous attributes exist.
- Without C: check if
- Redundant Dependencies:
- Remove
CS → J: closure(CS)⁺under{ J → S }is{ C, S }, which does not contain J. Not redundant. - Remove
J → S: closure(J)⁺under{ CS → J }is{ J }, which does not contain S. Not redundant.
- Remove
- Grouped Minimal Cover:
CS → JJ → S
Step 3: Normal Form Testing
Now evaluate each normal form against the minimal cover:
- 1NF: Satisfied (attributes are atomic).
- 2NF: Satisfied. 2NF states that no non-prime attribute may depend on a proper subset of a candidate key. Because all attributes are prime, there are no non-prime attributes to violate 2NF.
- 3NF: Satisfied! For each non-trivial dependency X → Y, test whether X is a superkey OR Y is prime:
CS → J: determinantCSis a candidate key (superkey). Passes 3NF.J → S: determinantJis not a superkey (J⁺ = { J, S } ≠ { C, S, J }). However, the right-hand attribute S is a prime attribute (it is part of candidate keyCS). Passes 3NF.
- BCNF: VIOLATED! BCNF requires that every determinant must be a superkey without exception:
J → S: determinantJis not a superkey. Because BCNF does not allow the "Y is prime" exception,J → Sviolates BCNF.
Highest Normal Form for R(C, S, J): 3NF.
Step 4: Decompositions Compared
3NF Synthesis (Bernstein's Algorithm)
3NF synthesis creates a relation for each functional dependency in the minimal cover:
- From
CS → J: create relationR₁(C, S, J). - From
J → S: create relationR₂(J, S). - Candidate key check:
R₁(C, S, J)already contains candidate keysCSandCJ, so no standalone key relation is needed. - Subsumption:
R₂(J, S)is a proper subset ofR₁(C, S, J), soR₂is absorbed intoR₁.
Final 3NF Schema: Single relation R(C, S, J) with candidate keys CS and CJ.
- Lossless Join: Yes.
- Dependency Preservation: Yes (both
CS → JandJ → Sremain intact).
BCNF Decomposition
BCNF decomposes the table along the violating dependency J → S:
- Split
R(C, S, J)usingJ → S:R₁(J, S)containing the attributes of the dependency{ J, S }.R₂(C, J)containing the determinant and the remaining attributes{ C, J }.
- Check normal form of decomposed tables:
- In
R₁(J, S): candidate key isJ. DeterminantJis a superkey. In BCNF. - In
R₂(C, J): candidate key isCJ. No non-trivial dependencies exist. In BCNF.
- In
Final BCNF Schema: R₁(J, S) and R₂(C, J).
- Lossless Join: Guaranteed by Heath's theorem because
R₁ ∩ R₂ = { J }, andJis a candidate key ofR₁. - Dependency Preservation: LOST! The original dependency
CS → Jcannot be checked withinR₁orR₂alone. Attributes C and S reside in separate tables. To enforceCS → J, an application or database trigger must perform an expensive join:R₁ ⋈ R₂.
3NF Design:
Table: CourseEnrollment(Course, Student, Instructor)
Keys: (Course, Student) and (Course, Instructor)
Pros: Preserves CS → J and J → S without joins.
Cons: Redundancy if an instructor is repeated across courses with the same student.
BCNF Design:
Table 1: InstructorStudent(Instructor, Student) [Key: Instructor]
Table 2: CourseInstructor(Course, Instructor) [Key: Course, Instructor]
Pros: No data redundancy for instructor assignments.
Cons: Loses the dependency CS → J; cannot prevent a student from having
two instructors in the same course without a cross-table join check.
Example 2: From 1NF to 2NF, 3NF, and BCNF
The second example (adapted from Elmasri & Navathe's Fundamentals of Database Systems, chapter 15) illustrates composite candidate keys and partial dependencies:
- Schema:
StaffAssignment(EmpId, ProjId, Hours, EmpName, ProjName) - Functional Dependencies:
EmpId, ProjId → Hours(An employee logs hours on a specific project)EmpId → EmpName(An employee ID determines the employee name)ProjId → ProjName(A project ID determines the project name)
Relation: StaffAssignment(EmpId, ProjId, Hours, EmpName, ProjName)
Dependencies:
EmpId, ProjId → Hours
EmpId → EmpName
ProjId → ProjName
Step 1: Candidate Keys and Attribute Classification
Compute attribute closures:
(EmpId)⁺ = { EmpId, EmpName }(ProjId)⁺ = { ProjId, ProjName }(EmpId, ProjId)⁺ = { EmpId, ProjId, Hours, EmpName, ProjName }
Candidate key: { EmpId, ProjId } (composite key).
- Prime Attributes:
EmpId,ProjId(belong to the candidate key). - Non-Prime Attributes:
Hours,EmpName,ProjName.
Step 2: Normal Form Violations
Examine each dependency against the normal forms:
EmpId → EmpName:- Determinant
EmpIdis a proper subset of candidate key{ EmpId, ProjId }. - Dependent attribute
EmpNameis non-prime. - This is a partial dependency. It violates 2NF.
- Determinant
ProjId → ProjName:- Determinant
ProjIdis a proper subset of candidate key{ EmpId, ProjId }. - Dependent attribute
ProjNameis non-prime. - This is also a partial dependency. It violates 2NF.
- Determinant
EmpId, ProjId → Hours:- Determinant is the entire candidate key. Satisfies 2NF, 3NF, and BCNF.
Because partial dependencies exist, the relation is only in 1NF. It violates 2NF, 3NF, and BCNF.
Step 3: Minimal Cover
- Singleton RHS: All right-hand sides are single attributes (
Hours,EmpName,ProjName). - Extraneous LHS Attributes: In
{ EmpId, ProjId } → Hours, removingEmpIdleavesProjId⁺ = { ProjId, ProjName }(noHours). RemovingProjIdleavesEmpId⁺ = { EmpId, EmpName }(noHours). Neither is extraneous. - Redundant Dependencies: None of the three dependencies can be derived from the remaining two.
- Grouped Minimal Cover:
EmpId, ProjId → HoursEmpId → EmpNameProjId → ProjName
Step 4: Decompositions
3NF Synthesis (Bernstein's Algorithm)
Synthesize one relation per minimal cover dependency:
R₁(EmpId, EmpName)with candidate keyEmpId(fromEmpId → EmpName).R₂(ProjId, ProjName)with candidate keyProjId(fromProjId → ProjName).R₃(EmpId, ProjId, Hours)with candidate key{ EmpId, ProjId }(fromEmpId, ProjId → Hours).
Because R₃ contains the original candidate key { EmpId, ProjId }, no extra key relation is required. No relation is a subset of another.
BCNF Decomposition
BCNF decomposition resolves violations sequentially:
- Decompose on violating dependency
EmpId → EmpName:R₁(EmpId, EmpName)R_rest(EmpId, ProjId, Hours, ProjName)
- In
R_rest,ProjId → ProjNameviolates BCNF (determinantProjIdis not a superkey). Decompose:R₂(ProjId, ProjName)R₃(EmpId, ProjId, Hours)
Both 3NF synthesis and BCNF decomposition yield the exact same three tables:
| Table | Columns | Candidate Key | Highest Form | Dependencies Preserved |
|---|---|---|---|---|
| R₁ (Employee) | EmpId, EmpName |
EmpId |
BCNF | EmpId → EmpName |
| R₂ (Project) | ProjId, ProjName |
ProjId |
BCNF | ProjId → ProjName |
| R₃ (Assignment) | EmpId, ProjId, Hours |
{ EmpId, ProjId } |
BCNF | EmpId, ProjId → Hours |
- Lossless Join: Yes. Joining
R₁,R₂, andR₃on foreign keysEmpIdandProjIdreconstructs the original relation without spurious tuples. - Dependency Preservation: Yes. All dependencies are enforced within individual tables.
When Should You Choose 3NF over BCNF?
In database coursework and production engineering, choosing between 3NF and BCNF comes down to a clear architectural trade-off:
Do all BCNF violations have
lossless + preserving splits?
|
+--------------+--------------+
| |
Yes No
| |
Decompose to Does your system
BCNF require enforcing
(Zero FD redundancy) the lost dependency?
|
+--------------+--------------+
| |
Yes No
| |
Keep in 3NF Decompose to
(Preserves all FDs BCNF + enforce via
without SQL joins) trigger/materialized view
- Choose BCNF when: Every decomposed table is dependency-preserving (like Example 2). There is no downside: you achieve zero functional redundancy and preserve all constraints.
- Choose 3NF when: BCNF decomposition loses a functional dependency (like Example 1). If verifying that dependency on every
INSERTorUPDATEwould require an expensive join across two tables, keeping the relation in 3NF guarantees constraint enforcement at O(1) single-table speed.
Frequently Asked Questions
1. What is the main difference between 3NF and BCNF?
3NF allows a functional dependency X → Y where X is not a superkey, provided that every attribute in Y is a prime attribute (part of some candidate key). BCNF removes this exception: every determinant X of a non-trivial dependency must be a superkey, regardless of whether Y is prime.
2. Why does 3NF have the prime attribute exception?
The prime attribute exception in 3NF guarantees that any relation can be decomposed into a set of smaller relations that are simultaneously lossless and dependency-preserving using Bernstein's synthesis algorithm. BCNF cannot always guarantee dependency preservation.
3. Can a table be in BCNF but not in 3NF?
No. BCNF is strictly stronger than 3NF. Any relation that satisfies BCNF automatically satisfies 3NF, 2NF, and 1NF. If a table violates 3NF, it also violates BCNF.
4. What is a partial dependency compared to a transitive dependency?
- Partial Dependency: A non-prime attribute depends on only part of a composite candidate key (e.g.,
{EmpId, ProjId}is the key, butEmpId → EmpName). Partial dependencies violate 2NF. - Transitive Dependency: A non-prime attribute depends on another non-key attribute (e.g., key
A → Band non-keyB → C). Transitive dependencies violate 3NF.
5. What is a minimal cover (canonical cover)?
A minimal cover Fc is a simplified, equivalent set of functional dependencies containing no redundant dependencies, no compound right-hand sides, and no extraneous attributes on the left-hand side. Computing the minimal cover is the required first step before running 3NF synthesis.
6. Does BCNF decomposition always guarantee a lossless join?
Yes. BCNF decomposition algorithms recursively split relations on a violating dependency X → Y into R₁(X ∪ Y) and R₂(R \ Y ∪ X). Because R₁ ∩ R₂ = X and X → (X ∪ Y), Heath's theorem guarantees that the natural join R₁ ⋈ R₂ is always lossless.
7. Why does BCNF decomposition sometimes lose functional dependencies?
When two attributes involved in a functional dependency (such as CS → J) end up in different decomposed tables (such as R₁(J, S) and R₂(C, J)), the database engine cannot enforce the dependency within a single table constraint. Enforcing it requires cross-table joins.
8. How does Bernstein's algorithm guarantee dependency preservation in 3NF?
Bernstein's 3NF synthesis algorithm builds a relation directly around every functional dependency in the minimal cover. Because every dependency is assigned its own relation, no dependency can ever be separated or lost across tables.
9. Which normal form is preferred in industry practice?
Most operational (OLTP) relational databases target 3NF or BCNF. If a schema can achieve BCNF without losing dependencies, engineers use BCNF. If BCNF requires cross-table constraints that hurt write throughput, teams settle on 3NF. In reporting and data warehouses (OLAP), schemas are deliberately denormalized into star or snowflake schemas to speed up analytical queries.
10. How can I test my own schemas and verify 2NF, 3NF, and BCNF?
You can use the free BCNF and 3NF Normalization Checker on ToolSphere. Enter your relation attributes and functional dependencies to automatically compute attribute closures, find all candidate keys, generate the minimal cover, and inspect step-by-step 3NF synthesis and BCNF decomposition trees.
Use the Candidate Key Finder to investigate candidate keys before comparing normal forms. For a related walkthrough, read Attribute Closure and Candidate Keys: Example.