ToolSphere
Back to all articles
student

BCNF vs 3NF: Normalization Examples

2026-11-109 min read

Try it: free BCNF & 3NF Normalization Checker

Find keys, minimal cover and test 2NF, 3NF and BCNF

Open →

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 shorthand R(C, S, J)
  • Functional Dependencies:
    1. CS → J (A student enrolled in a course has exactly one assigned instructor)
    2. 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:

  1. Singleton RHS: Both dependencies already have single-attribute right-hand sides: CS → J and J → S.
  2. 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.
  3. 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.
  4. Grouped Minimal Cover:
    • CS → J
    • J → 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: determinant CS is a candidate key (superkey). Passes 3NF.
    • J → S: determinant J is not a superkey (J⁺ = { J, S } ≠ { C, S, J }). However, the right-hand attribute S is a prime attribute (it is part of candidate key CS). Passes 3NF.
  • BCNF: VIOLATED! BCNF requires that every determinant must be a superkey without exception:
    • J → S: determinant J is not a superkey. Because BCNF does not allow the "Y is prime" exception, J → S violates 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:

  1. From CS → J: create relation R₁(C, S, J).
  2. From J → S: create relation R₂(J, S).
  3. Candidate key check: R₁(C, S, J) already contains candidate keys CS and CJ, so no standalone key relation is needed.
  4. Subsumption: R₂(J, S) is a proper subset of R₁(C, S, J), so R₂ is absorbed into R₁.

Final 3NF Schema: Single relation R(C, S, J) with candidate keys CS and CJ.

  • Lossless Join: Yes.
  • Dependency Preservation: Yes (both CS → J and J → S remain intact).

BCNF Decomposition

BCNF decomposes the table along the violating dependency J → S:

  1. Split R(C, S, J) using J → S:
    • R₁(J, S) containing the attributes of the dependency { J, S }.
    • R₂(C, J) containing the determinant and the remaining attributes { C, J }.
  2. Check normal form of decomposed tables:
    • In R₁(J, S): candidate key is J. Determinant J is a superkey. In BCNF.
    • In R₂(C, J): candidate key is CJ. No non-trivial dependencies exist. In BCNF.

Final BCNF Schema: R₁(J, S) and R₂(C, J).

  • Lossless Join: Guaranteed by Heath's theorem because R₁ ∩ R₂ = { J }, and J is a candidate key of R₁.
  • Dependency Preservation: LOST! The original dependency CS → J cannot be checked within R₁ or R₂ alone. Attributes C and S reside in separate tables. To enforce CS → 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:
    1. EmpId, ProjId → Hours (An employee logs hours on a specific project)
    2. EmpId → EmpName (An employee ID determines the employee name)
    3. 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:

  1. EmpId → EmpName:
    • Determinant EmpId is a proper subset of candidate key { EmpId, ProjId }.
    • Dependent attribute EmpName is non-prime.
    • This is a partial dependency. It violates 2NF.
  2. ProjId → ProjName:
    • Determinant ProjId is a proper subset of candidate key { EmpId, ProjId }.
    • Dependent attribute ProjName is non-prime.
    • This is also a partial dependency. It violates 2NF.
  3. 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

  1. Singleton RHS: All right-hand sides are single attributes (Hours, EmpName, ProjName).
  2. Extraneous LHS Attributes: In { EmpId, ProjId } → Hours, removing EmpId leaves ProjId⁺ = { ProjId, ProjName } (no Hours). Removing ProjId leaves EmpId⁺ = { EmpId, EmpName } (no Hours). Neither is extraneous.
  3. Redundant Dependencies: None of the three dependencies can be derived from the remaining two.
  4. Grouped Minimal Cover:
    • EmpId, ProjId → Hours
    • EmpId → EmpName
    • ProjId → ProjName

Step 4: Decompositions

3NF Synthesis (Bernstein's Algorithm)

Synthesize one relation per minimal cover dependency:

  1. R₁(EmpId, EmpName) with candidate key EmpId (from EmpId → EmpName).
  2. R₂(ProjId, ProjName) with candidate key ProjId (from ProjId → ProjName).
  3. R₃(EmpId, ProjId, Hours) with candidate key { EmpId, ProjId } (from EmpId, 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:

  1. Decompose on violating dependency EmpId → EmpName:
    • R₁(EmpId, EmpName)
    • R_rest(EmpId, ProjId, Hours, ProjName)
  2. In R_rest, ProjId → ProjName violates BCNF (determinant ProjId is 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₂, and R₃ on foreign keys EmpId and ProjId reconstructs 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
  1. 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.
  2. Choose 3NF when: BCNF decomposition loses a functional dependency (like Example 1). If verifying that dependency on every INSERT or UPDATE would 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, but EmpId → EmpName). Partial dependencies violate 2NF.
  • Transitive Dependency: A non-prime attribute depends on another non-key attribute (e.g., key A → B and non-key B → 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.

Looking for other tools?

Explore ToolSphere Homepage →