Comprehensive Guide to BCNF and 3NF in Database Normalization

What is BCNF (Boyce-Codd Normal Form)?

Definition: A relation R is in BCNF if for every FD X → Y in F⁺, either:

X → Y is trivial (Y ⊆ X), OR

X is a superkey.

Intuition: Only keys are allowed to determine attributes.

Example: If A→B and A is NOT a superkey → violates BCNF.

Why it matters: Most exam questions test BCNF violation detection.

What causes a BCNF violation?

Any FD X → Y where X is NOT a superkey.

Intuition: Left side doesn't uniquely identify tuples → redundancy.

Example: In R(A,B,C), FD B→C violates BCNF if key is A.

Why it matters: You MUST identify violations before decomposing.

What is the BCNF Decomposition Algorithm?

Definition: If X→Y violates BCNF, decompose R into:

- R1 = X ∪ Y

- R2 = R − (Y − X)

Continue until all relations are in BCNF.

Intuition: Split the table so that the violating dependency becomes correct.

Example: A→B in ABCDEF → split into AB and ACDEF.

Why it matters: Directly tested via multi-step decomposition questions.

Why is BCNF decomposition always lossless?

Because we decompose on X→Y, and X is in both R1 and R2.

Intuition: Shared key = no spurious tuples.

Example: R(ABC), FD A→B → AB and AC share A.

Why it matters: Proof-type questions ask why BCNF ensures lossless joins.

Why is BCNF not always dependency-preserving?

Some FDs may require joining tables to enforce.

Intuition: Sometimes you lose ability to check an FD locally.

Example: Decompose CSZ into (Z,S) and (C,Z) → FD CS→Z not enforceable without join.

Why it matters: Exam often asks for tradeoff BCNF vs 3NF.

What is an example of BCNF decomposition?

For FDs A→BFL, M→T, AM→S (AM is key), decompose into ABFL and AMTS.

Intuition: A and M are NOT keys, so violations.

Example: Decompose → ABFL, AMTS → then MT, AMS.

Why it matters: Shows multi-step BCNF decomposition with composite keys.

What is 3NF (Third Normal Form)?

A relation is in 3NF if for every FD X→Y, either X→Y is trivial, X is a superkey, or each attribute in Y−X is part of SOME key.

Intuition: Relaxed version of BCNF to preserve dependencies.

Example: Z→S is allowed in 3NF if S is part of a key.

Why it matters: Exam asks comparing BCNF vs 3NF.

When do we prefer 3NF over BCNF?

When BCNF decomposition breaks dependency preservation.

Intuition: 3NF keeps important FDs enforceable locally.

Example: CSZ example — 3NF avoids splitting into CZ + ZS.

Why it matters: Critical essay-style question.

What is a prime attribute?

An attribute that belongs to at least one candidate key.

Intuition: "Key attribute."

Example: In key AB, attributes A and B are prime.

Why it matters: Used to check 3NF's third condition.

What is the relationship between BCNF and 3NF?

BCNF ⊂ 3NF; all BCNF relations are in 3NF, not vice versa.

Intuition: BCNF is stricter; 3NF is more forgiving.

Example: Z→S allowed in 3NF but not BCNF.

Why it matters: Exam loves trick questions about this relationship.

What is a canonical cover?

A minimal set of FDs equivalent to the original FDs.

Intuition: Remove redundancy and simplify FDs.

Example: A→BC becomes A→B and A→C.

Why it matters: Used in 3NF synthesis algorithm.

What are the steps to compute a canonical cover?

Split RHS so each FD has one attribute, minimize LHS by removing extra attributes, remove redundant FDs, combine FDs with the same LHS.

Intuition: Clean up the FD set.

Example: AB→C, A→B → AB→C redundant since A→C implied.

Why it matters: Essential for synthesis algorithm questions.

What is a redundant FD?

An FD implied by other FDs, so it can be removed.

Intuition: Already determined indirectly.

Example: A→C is redundant if A→B and B→C exist.

Why it matters: Used in canonical cover reduction.

What is minimal LHS in canonical cover?

If X→Y holds, check if any proper subset of X also determines Y.

Intuition: Remove unnecessary determinants.

Example: AB→C, but A→C holds → remove B.

Why it matters: Exam often requires checking minimality.

What is the 3NF Synthesis Algorithm?

Method to decompose into 3NF while preserving all dependencies.

Intuition: Build tables directly from FDs.

Example: FD A→BC and CD→E produce relations ABC and CDE.

Why it matters: A big multi-step exam problem.

What are the steps of the 3NF Synthesis Algorithm?

Compute canonical cover F′, create relation R(X∪Y) for each FD, remove relations that are subsets of others, add a relation if none contains a key.

Intuition: Turn each FD into its own table.

Example: From A→BC and B→D, create ABC and BD.

Why it matters: Exam-grade question: "Apply 3NF synthesis to this schema."

Why does 3NF synthesis always preserve dependencies?

Each FD X→Y in the canonical cover becomes its own table containing X and Y.

Intuition: All FDs stay local.

Example: FD A→B creates table AB → enforceable directly.

Why it matters: Contrast with BCNF which may lose dependencies.

When is an extra relation added in 3NF synthesis?

If none of the produced relations contains a key for R.

Intuition: Need at least one table that identifies tuples.

Example: Add AD to preserve key AD.

Why it matters: Common trick in exam questions.

Why can some synthesized tables be removed?

If one relation is a subset of another, it is redundant.

Intuition: Bigger relation contains all info of smaller.

Example: AB ⊆ ABC → remove AB.

Why it matters: Saves unnecessary output relations.

What is a lossless join in 3NF synthesis?

Lossless join is guaranteed if you add a relation containing a key.

Intuition: Key acts as a glue between tables.

Example: Without AD in example, join was lossy; adding AD fixed it.

Why it matters: Exam often shows lossy join if student forgets to add a key table.