ToolSphere

BCNF & 3NF Normalization Checker

Computer science

Test relations against 2NF, 3NF, and BCNF, find candidate keys and minimal cover, and synthesize 3NF or decompose BCNF with lossless join verification.

Test database relations against 2NF, 3NF, and BCNF requirements, find candidate keys, compute minimal covers, and synthesize 3NF or decompose into BCNF with lossless-join and dependency-preservation verification.

Highest Normal Form:3NFR(C, J, S)
2NF Satisfied3NF SatisfiedBCNF Violated

Keys: {CJ} {CS}

Decompositions: 3NF has 1 relations (lossless, dependency-preserving) · BCNF has 2 relations (lossless, dependencies lost).

List schema attributes separated by commas or spaces. If blank, attributes are inferred from dependencies.

Enter one dependency per line (or semicolon-separated), using -> or →. Single letters like AB -> C or named attributes like StudentId -> Dept are supported.

1. Candidate Keys & Attribute Classification

Candidate Keys{CJ}, {CS}Primary key: CJ
Prime AttributesC, J, SAttributes part of at least one candidate key
Non-Prime AttributesNone (all attributes are prime)Attributes not in any candidate key

2. Normal Form Compliance & Violations

Second Normal Form (2NF)Satisfied

Requirement: Every non-prime attribute must be fully functionally dependent on every candidate key (no partial dependencies on proper subsets of candidate keys).

Third Normal Form (3NF)Satisfied

Requirement: For every non-trivial dependency X → A, either X is a superkey OR attribute A is prime (no transitive dependencies of non-prime attributes).

Boyce-Codd Normal Form (BCNF)Violated

Requirement: For every non-trivial dependency X → A, determinant X must be a superkey. Eliminates all functional dependency redundancy.

  • J → S: Determinant J is not a superkey of relation R(C, J, S).

3. Minimal Cover (Canonical Cover) Fc

Resulting Minimal Cover

CS → J ; J → S

Derivation Steps

1. Singleton Right-Hand Sides

Split compound right-hand sides into individual attribute dependencies and eliminate trivial dependencies.

CS → J, J → S

2. Eliminate Extraneous Left-Hand Side Attributes

For each compound determinant X → A, test whether any attribute B in X can be dropped such that (X \ {B})⁺ still determines A.

CS → J, J → S

3. Eliminate Redundant Dependencies

Remove any dependency X → A whose attribute A can already be determined by the remaining dependencies under (X)⁺.

CS → J, J → S

4. Group Dependencies by Determinant

Combine dependencies sharing the exact same left-hand side into canonical form X → Y₁Y₂...

CS → J, J → S

4. 3NF Decomposition (Bernstein's Synthesis Algorithm)

Lossless Join: GuaranteedDependency Preserved: Guaranteed
R1

R1(C, J, S)

Keys: CJ, CS
Dependencies: CS → J; J → S

Lossless join: At least one synthesized relation contains a candidate key of the original relation R, guaranteeing a lossless join.

Dependency preservation: Every functional dependency in the minimal cover is embedded entirely within a synthesized relation, guaranteeing dependency preservation.

5. BCNF Decomposition (Decomposition Algorithm)

Lossless Join: GuaranteedDependency Preserved: No
R1

R1(J, S)

Keys: J
Dependencies: J → S
R2

R2(C, J)

Keys: CJ
Decomposition Trace:
  1. Decomposed R(C, J, S) via violating dependency J → S (where determinant J is not a superkey) into R₁(J, S) and R₂(C, J).

Lossless join: Each binary decomposition step splits on a functional dependency X → Y where (S₁ ∩ S₂) = X and X → S₁, guaranteeing a lossless join by Heath’s theorem.

Dependency preservation: The dependency CS → J cannot be enforced on any single decomposed relation without an expensive cross-relation join.

On this browser only

Give feedback

Did this tool help?

Tell us what worked, what didn’t, or what you’d like next.

Opens Google Forms in a new tab with this tool’s name filled in. No sign-in or email required. Feedback is optional.

How to use BCNF & 3NF Normalization Checker

  1. 1Select a textbook example or type your relation schema attributes and functional dependencies.
  2. 2Read the highest satisfied normal form and candidate keys directly in the top summary banner.
  3. 3Check the 2NF, 3NF, and BCNF breakdown to see every violating functional dependency and its cause.
  4. 4Inspect the step-by-step minimal cover (canonical cover) reduction.
  5. 5Review the 3NF synthesis relations and BCNF decomposition tables with lossless-join and dependency-preservation verification.

About BCNF & 3NF Normalization Checker

Database normalization for relational schema design

Database normalization organizes table schemas to eliminate update anomalies, reduce data duplication, and preserve functional dependencies. In US university database courses such as Berkeley CS 186, Stanford CS 145, and CMU 15-445, normalization questions follow a strict progression: computing attribute closures, determining all candidate keys, identifying prime versus non-prime attributes, testing normal form compliance (1NF through BCNF), and decomposing schemas when violations occur.

This normalization checker automates every step of that pipeline. Given a relation schema and a set of functional dependencies, it finds all minimal candidate keys, computes the minimal cover (canonical cover), tests each normal form requirement, and produces both a 3NF synthesis and a BCNF recursive decomposition with detailed proofs of lossless joins and dependency preservation.

Understanding normal form requirements: 1NF to BCNF

Relational normal forms build hierarchically on attribute dependencies:

  1. First Normal Form (1NF) requires that all attribute values are atomic and relations have no repeating groups. This tool assumes atomic values from the schema definition.
  2. Second Normal Form (2NF) forbids partial dependencies. A partial dependency occurs when a proper subset of a candidate key determines a non-prime attribute. If any part of a compound key determines a column that is not part of any key, the relation violates 2NF.
  3. Third Normal Form (3NF) forbids transitive dependencies of non-prime attributes on candidate keys. Formally, for every non-trivial functional dependency X → A, either X is a superkey or A is a prime attribute (part of some candidate key).
  4. Boyce-Codd Normal Form (BCNF) eliminates all redundancy based on functional dependencies. For every non-trivial functional dependency X → A, the determinant X must be a superkey. Unlike 3NF, BCNF does not allow exceptions for prime attributes on the right-hand side.

Minimal cover (canonical cover) algorithm

Before decomposing a relation into 3NF, the functional dependencies must be reduced to a minimal cover (also known as a canonical cover, Fc). A minimal cover contains no redundant attributes or extraneous dependencies:

  1. Singleton right-hand sides: Every multi-attribute dependency X → Y is split into individual dependencies X → A for each attribute A in Y (excluding trivial dependencies where A is in X).
  2. Extraneous left-hand side attributes: For each dependency X → A with two or more attributes on the left, we test whether dropping attribute B leaves a determinant (X \ {B}) whose closure still determines A under the current dependencies. If so, B is extraneous and removed.
  3. Redundant dependencies: Dependencies are tested sequentially. If removing X → A leaves a remaining set of dependencies whose closure of X still reaches A, X → A is redundant and eliminated.
  4. Grouped canonical form: Dependencies sharing identical determinants are merged into X → Y₁Y₂...

Worked example 1: Course, Student, and Teacher (3NF vs BCNF)

Consider the classic database textbook relation R(C, S, J) representing Courses (C), Students (S), and Teachers/Instructors (J), with the following dependencies:

  • CS → J (A student in a course has exactly one teacher)
  • J → S (Each teacher teaches exactly one student)

Running this schema through the normalization library yields:

  • Candidate keys: CJ and CS. Both are minimal determinants whose attribute closures under the dependencies reach all three attributes {C, J, S}.
  • Prime attributes: C, J, and S. Because every attribute belongs to at least one candidate key, there are zero non-prime attributes.
  • Normal form testing: Because there are no non-prime attributes, partial dependencies (2NF) and transitive dependencies on non-primes (3NF) cannot exist. Relation R is in 2NF and 3NF.
  • BCNF violation: In the dependency J → S, the determinant J has closure {J, S}, which does not reach C. Because J is not a superkey, J → S violates BCNF.
  • 3NF synthesis: Bernstein's synthesis creates relation R1(C, J, S). The decomposition contains one relation, guarantees a lossless join, and preserves all dependencies.
  • BCNF decomposition: Decomposing on J → S produces R1(J, S) with key J, and R2(C, J) with key CJ. By Heath's theorem, this decomposition guarantees a lossless join. However, dependency preservation fails: the dependency CS → J cannot be checked in R1 or R2 alone without performing an expensive join between them.

Worked example 2: Employee Projects (2NF partial dependency)

Consider an employee assignment schema R(EmpId, ProjId, Hours, EmpName, ProjName) with dependencies:

  • EmpId, ProjId → Hours
  • EmpId → EmpName
  • ProjId → ProjName

The library computes:

  • Candidate key: {EmpId, ProjId}.
  • Prime attributes: EmpId, ProjId. Non-prime attributes: EmpName, Hours, ProjName.
  • 2NF violation: EmpId → EmpName and ProjId → ProjName have determinants that are proper subsets of the candidate key {EmpId, ProjId} determining non-prime attributes. The relation is only in 1NF.
  • 3NF and BCNF decomposition: Decomposes into three tables: R1(EmpId, Hours, ProjId) with key {EmpId, ProjId}, R2(EmpId, EmpName) with key EmpId, and R3(ProjId, ProjName) with key ProjId. Both decompositions are lossless and preserve all original functional dependencies.

3NF synthesis vs BCNF decomposition trade-offs

In production database architecture, database engineers frequently choose 3NF over BCNF when functional dependencies span multiple attributes. While BCNF eliminates all redundancy, it cannot guarantee dependency preservation. Enforcing a lost dependency in a BCNF schema requires database triggers or application-level locks on table joins during every INSERT and UPDATE. In contrast, 3NF synthesis guarantees that every functional dependency can be verified as a simple primary key or UNIQUE constraint within a single table.

Guides and tutorials

Frequently asked questions

What is the difference between 3NF and BCNF?

In 3NF, a dependency X → A is allowed if X is a superkey OR A is a prime attribute (part of some candidate key). BCNF is stricter: determinant X must always be a superkey, with no exception for prime right-hand attributes. Because of this exception, every relation has a 3NF decomposition that is both lossless and dependency-preserving, whereas some relations cannot achieve BCNF without losing dependencies.

How does the tool find all candidate keys?

The tool identifies attributes that never appear on any right-hand side, since those must belong to every candidate key. It then systematically tests subsets of the remaining attributes by computing their attribute closure X⁺ under the functional dependencies. Any subset whose closure contains all relation attributes and contains no smaller superkey is recorded as a minimal candidate key.

What makes a decomposition lossless-join?

A decomposition of relation R into R1 and R2 is lossless if joining R1 and R2 on their common attributes (R1 ∩ R2) recreates the exact original relation without spurious tuples. By Heath's theorem, this is guaranteed whenever the common attributes functionally determine either R1 or R2. 3NF synthesis guarantees a lossless join by ensuring at least one synthesized relation contains a candidate key of R.

Why can BCNF decomposition lose functional dependencies?

When a violating dependency X → A is used to split a relation, other dependencies whose attributes are split between the new tables may no longer fit inside any single table. In the classic R(C, S, J) example with CS → J and J → S, BCNF decomposes into R1(J, S) and R2(C, J). The dependency CS → J cannot be checked in either table alone without computing a relational join.

What is a minimal cover (canonical cover)?

A minimal cover Fc is an equivalent set of functional dependencies in standard form with no extraneous attributes on the left-hand side and no redundant dependencies. Generating a minimal cover is the required first step of Bernstein's 3NF synthesis algorithm to prevent creating unnecessary relations.

How does 3NF synthesis handle candidate keys?

After synthesizing a relation for each dependency in the minimal cover, Bernstein's algorithm checks whether any synthesized table contains an entire candidate key of the original relation R. If none does, it explicitly adds a table containing one candidate key (such as the primary key). This extra table guarantees that the overall decomposition satisfies the lossless-join property.

Can a relation satisfy 3NF if it violates 2NF?

No. The normal forms are strictly hierarchical: every relation in 3NF is also in 2NF, and every relation in 2NF is in 1NF. A partial dependency on a candidate key violates 2NF, which automatically prevents the relation from satisfying 3NF or BCNF.

What input formats are accepted for functional dependencies?

You can write dependencies with ->, →, or =>. Single letters can be written as shorthand (such as AB -> C), or full attribute names can be separated by commas (such as StudentId, CourseId -> Grade). Separate multiple dependencies with newlines or semicolons.

Can I check multi-valued dependencies (4NF) with this tool?

This tool focuses on functional dependencies and tests up through Boyce-Codd Normal Form (BCNF). Fourth Normal Form (4NF) and Fifth Normal Form (5NF) involve multi-valued dependencies and join dependencies, which are not represented by standard functional dependencies.

Have a suggestion for BCNF & 3NF Normalization Checker?Share your feedback ↗