Yes. BCNF satisfies the stronger determinant condition and therefore meets 3NF's requirements.

Can a table with a composite key be in BCNF?

Yes. The key's size is not the test; the functional dependencies are.

Does BCNF guarantee no redundancy of any kind?

No. Other dependency types and modeling choices can introduce redundancy outside the functional-dependency issues BCNF addresses.

AI Glossary / Data engineering and architecture / Intermediate

Boyce-Codd Normal Form

Definition

Boyce-Codd Normal Form, or BCNF, is a relational database normal form requiring every determinant in a nontrivial functional dependency to be a superkey. It aims to remove redundancy caused by dependencies whose determining attributes do not identify the whole row. BCNF is stronger than Third Normal Form, but achieving it can complicate enforcement of some original dependencies.

What it means

A functional dependency X → Y means that rows agreeing on X must agree on Y. It is nontrivial when Y contains an attribute outside X. A superkey is an attribute set that determines all attributes of the relation; it need not be minimal.

BCNF asks a precise question about each relevant dependency: does its left-hand side identify the entire tuple? If not, the relation contains a dependency that violates BCNF.

This is a property of the schema together with its intended constraints, not merely the visible rows in one sample. An empty or small table can accidentally satisfy dependencies that the business does not guarantee. The analysis must use valid rules for the intended data.

How it works

Identify the functional dependencies and candidate keys. For every nontrivial dependency implied by those rules, check whether the determinant is a superkey. Attribute closure is a standard way to determine what a set of attributes can identify.

When a violating dependency is found, a decomposition can separate the associated facts. Standard BCNF decomposition procedures preserve lossless reconstruction when correctly applied, but the result is not always dependency-preserving. Some rules may then require checking information across multiple relations.

Third Normal Form allows an additional case involving a prime attribute, meaning an attribute belonging to a candidate key. BCNF does not allow that exception for a non-superkey determinant. Thus a relation can be in 3NF and still fail BCNF.

BCNF addresses functional-dependency redundancy. It does not automatically resolve every issue involving multivalued or join dependencies, nor does it specify the best physical indexes or query execution plan.

Three practical examples

An employee directory separates department details when DepartmentID determines DepartmentName but does not identify an employee row.
A product catalog separates supplier facts from item assignments when the stated dependencies show that the facts have different determinants.
A database review checks a proposed schema's dependency closures before describing it as BCNF, rather than relying only on a composite primary-key declaration.

Think of it this way

Think of a register that repeats a department's address on every employee entry. The address is a fact about the department, but the register's full row is about an employee. Putting the department fact in its own appropriate record can reduce repeated updates.

The analogy is useful but incomplete. BCNF is determined by formal dependencies, including cases with overlapping candidate keys that are not obvious from a familiar register. A visually tidy table is not proof of the normal form.

Worked example

A non-superkey determinant

Consider an invented relation Asset(AssetID, SiteID, SiteName). Assume AssetID determines SiteID and SiteID determines SiteName. Several assets can belong to one site.

AssetID determines all three attributes, so it is a candidate key under these assumptions. SiteID determines SiteName, but it does not determine AssetID. Therefore SiteID → SiteName is nontrivial and its determinant is not a superkey: the relation fails BCNF.

Decompose it into AssetSite(AssetID, SiteID) and Site(SiteID, SiteName). The common SiteID determines all of Site, so the binary decomposition is lossless. Each component is in BCNF under the stated dependencies, and the two given dependencies can be checked locally.

If site S4 is renamed, its name changes once in Site rather than across every asset row. The example assumes current site names. Tracking historical names or asset movements would require a suitable temporal design and a fresh dependency analysis.

Illustrative case study

A fictional mentoring service records Learner, Topic and Mentor. Its rules say each mentor handles one topic, while each learner has one mentor per topic. Mentor determines Topic, and Learner plus Topic determines Mentor.

Mentor alone does not identify a learner assignment, so the first dependency violates BCNF. Splitting into MentorTopic and LearnerMentor removes that violation and can preserve reconstruction under the stated rules. However, the original one-mentor-per-learner-per-topic rule is no longer a constraint on either component alone.

The team evaluates how to enforce that rule before accepting the redesign. The case illustrates a real design tradeoff: reducing one form of redundancy can move an important integrity check across relations. A normal-form label does not eliminate the need to plan enforcement.

Common misconceptions and mistakes

Every determinant must be a minimal candidate key. BCNF requires a superkey; minimality is not required for the determinant.
Trivial dependencies violate BCNF when their determinant is small. The BCNF test concerns nontrivial dependencies.
A BCNF decomposition always preserves every dependency locally. Losslessness and dependency preservation are separate, and the latter is not always achievable with BCNF.

Business use

BCNF helps reduce inconsistent repeated facts and clarify where updates belong. It can make a database easier to maintain when different kinds of facts have been combined in one relation.

Before decomposing a production schema, document the dependency assumptions and identify the queries and constraints affected. Verify lossless reconstruction and decide how every important rule will be enforced after the change. Consider a dependency-preserving 3NF design when the tradeoff justifies it, with the remaining redundancy understood explicitly.

For AI-assisted schema design, require explanations of keys, closures and violating dependencies. A generated diagram or a claim that tables are normalized is insufficient. Use small counterexamples to test the reasoning, then validate migration and workload behavior separately.

Check your understanding

In R(EmployeeID, DepartmentID, DepartmentName), DepartmentID determines DepartmentName but not EmployeeID. Does that nontrivial dependency satisfy BCNF?

Answer: No. DepartmentID is not a superkey of R, so the dependency violates BCNF under the stated rules.

Related terms

Third normal form (3NF)
Functional dependency
Superkey
Candidate key
Prime Attribute
Third Normal Form
attribute closure
lossless decomposition
dependency preservation

Learn next

Read the difference between 3NF and BCNF. For a small schema, calculate each determinant's closure and explain which dependencies violate BCNF before proposing any split.

Sources

Silberschatz, Korth and Sudarshan, Relational Database Design: https://www.db-book.com/slides-dir/PDF-dir/ch7.pdf. Primary textbook treatment of normal forms and decomposition.

Database System Concepts, BCNF and dependency preservation slides: https://www.db-book.com/Previous-editions/db4/slide-dir/ch7-2.pdf. Primary explanation of the 3NF tradeoff.

Emory University, Boyce-Codd Normal Form: https://www.cs.emory.edu/~cheung/Courses/377/Syllabus/9-NormalForms/BCNF.html. Primary teaching examples. The asset and mentoring cases are original illustrations of the stated rules.

Previous: Box's M test Next: Boykov-Kolmogorov