Boyce-Codd Normal Form (BCNF) in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding BCNF Definition

Which of the following best describes the condition for a relation to be in Boyce-Codd Normal Form (BCNF)?

A Every non-prime attribute is fully functionally dependent on the primary key.

B Every attribute is functionally dependent on the primary key only.

C There are no transitive dependencies in the relation.

D Every determinant is a candidate key.

💡 Hint

Recall that BCNF is a stricter form of 3NF focusing on determinants.

BCNF requires that for every functional dependency X → Y, X must be a candidate key. This means every determinant is a candidate key.

📋 Factual

intermediate

BCNF vs 3NF Differences

Which statement correctly distinguishes BCNF from Third Normal Form (3NF)?

A 3NF allows dependencies where the determinant is not a candidate key if the dependent is a prime attribute, but BCNF does not.

B BCNF allows some dependencies where the determinant is not a candidate key, but 3NF does not.

C 3NF requires every determinant to be a candidate key, but BCNF allows exceptions.

D BCNF and 3NF have the exact same requirements for functional dependencies.

💡 Hint

Think about the exceptions allowed in 3NF but not in BCNF.

3NF allows a functional dependency where the determinant is not a candidate key if the dependent attribute is part of some candidate key (prime attribute). BCNF does not allow this exception.

🚀 Application

advanced

Identifying BCNF Violations

Given a relation R(A, B, C) with functional dependencies: A → B and B → C, which of the following is true about R?

A R is in BCNF because A is a candidate key.

B R is in BCNF because all dependencies have candidate keys as determinants.

C R is not in BCNF because B → C violates BCNF as B is not a candidate key.

D R is not in BCNF because A → B violates BCNF.

💡 Hint

Check which attributes are candidate keys and which dependencies violate BCNF.

A is a candidate key since A → B and B → C imply A → C by transitivity. However, B → C violates BCNF because B is not a candidate key.

🔍 Analysis

advanced

Decomposition to BCNF

You have a relation R(X, Y, Z) with dependencies X → Y and Y → X. Which of the following decompositions will result in relations in BCNF?

A Decompose into R1(X, Y) and R2(Y, Z).

B Decompose into R1(X, Z) and R2(Y, Z).

C No decomposition needed; R is already in BCNF.

D Decompose into R1(X, Y, Z) only.

💡 Hint

Consider which attributes form candidate keys and how dependencies affect BCNF.

X ↔ Y (X → Y and Y → X), but candidate keys of R are {X,Z} and {Y,Z} since no FDs involve Z. Thus X → Y violates BCNF (X not candidate key). Decomposing into R1(X, Y) (CKs: X, Y) and R2(Y, Z) (no non-trivial FDs) achieves BCNF.

❓ Reasoning

expert

BCNF and Candidate Keys Count

Consider a relation R with attributes {A, B, C, D} and functional dependencies: A → B, B → C, and C → A. How many candidate keys does R have if it is in BCNF?

A One candidate key only.

B Three candidate keys.

C Two candidate keys.

D Four candidate keys.

💡 Hint

Analyze the cycle of dependencies and what attributes determine all others.

The dependencies form a cycle among A, B, and C (A→B→C→A so A↔B↔C). D is not functionally dependent on others (no {A,B,C}+ → D), so candidate keys are {A,D}, {B,D}, {C,D} — three candidate keys.