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.

Practice

1. What is the main goal of Boyce-Codd Normal Form (BCNF) in database design?

easy

A. To ensure every functional dependency is based on a candidate key

B. To allow duplicate rows in a table

C. To combine multiple tables into one

D. To remove all foreign keys from a table

Solution

1. Step 1: Understand BCNF definition

   BCNF requires that for every functional dependency X -> Y, X must be a candidate key.

2. Step 2: Analyze options

   Only To ensure every functional dependency is based on a candidate key correctly states this goal. Other options describe unrelated or incorrect concepts.

3. Final Answer:

   To ensure every functional dependency is based on a candidate key -> Option A

4. Quick Check:

   BCNF = dependencies from candidate keys [OK]

Hint: BCNF means all dependencies come from keys [OK]

Common Mistakes:

• Thinking BCNF removes foreign keys
• Confusing BCNF with merging tables
• Believing BCNF allows duplicates

2. Which of the following functional dependencies violates BCNF in a relation?

easy

A. C -> D, where C is a candidate key

B. A -> B, where A is a candidate key

C. B -> C, where B is not a candidate key

D. D -> A, where D is a candidate key

Solution

1. Step 1: Recall BCNF condition

   Every functional dependency's left side must be a candidate key.

2. Step 2: Check each dependency

   Only B -> C has a left side (B) that is not a candidate key, violating BCNF.

3. Final Answer:

   B -> C, where B is not a candidate key -> Option C

4. Quick Check:

   Dependency from non-key violates BCNF [OK]

Hint: Check if left side of dependency is a candidate key [OK]

Common Mistakes:

• Assuming all dependencies are allowed
• Confusing candidate keys with foreign keys
• Ignoring the key status of the left side

3. Consider a relation R(A, B, C) with functional dependencies: A -> B and B -> C. Which of the following is true about R?

medium

A. R is not in BCNF because B -> C violates BCNF

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

C. R is in BCNF because B determines C

D. R is not in BCNF because A -> B violates BCNF

Solution

1. Step 1: Identify candidate keys

   A determines B, and B determines C, so A determines B and C. Thus, A is a candidate key.

2. Step 2: Check BCNF condition

   Dependency B -> C has B on left, which is not a candidate key. This violates BCNF.

3. Final Answer:

   R is not in BCNF because B -> C violates BCNF -> Option A

4. Quick Check:

   Non-key dependency breaks BCNF [OK]

Hint: Check if all left sides are candidate keys [OK]

Common Mistakes:

• Assuming A -> B means all dependencies are fine
• Ignoring transitive dependencies
• Confusing BCNF with 3NF

4. Given a relation with attributes (X, Y, Z) and dependencies: X -> Y and Y -> X, what is the problem if we say this relation is in BCNF?

medium

A. There is no problem; it is in BCNF

B. X and Y are candidate keys, so BCNF is satisfied

C. X and Y together form a candidate key, so BCNF is violated

D. X and Y are not candidate keys, so BCNF is violated

Solution

1. Step 1: Analyze dependencies

   X -> Y and Y -> X mean X and Y determine each other, but neither determines Z. Candidate keys must include Z (e.g., {X, Z}, {Y, Z}).

2. Step 2: Check BCNF condition

   X -> Y has X on left, but X is not a superkey (does not determine Z), violating BCNF. Similarly for Y -> X.

3. Final Answer:

   X and Y are not candidate keys, so BCNF is violated -> Option D

4. Quick Check:

   Determinants must be superkeys covering all attributes [OK]

Hint: Mutual dependencies don't make keys without covering all attributes [OK]

Common Mistakes:

• Thinking mutual dependencies make X and Y candidate keys
• Forgetting to verify determination of all attributes like Z
• Ignoring that both sides can be keys

5. You have a relation R(A, B, C, D) with dependencies: A, B -> C, C -> D. To convert R into BCNF, what should you do?

hard

A. Remove dependency C -> D

B. Decompose R into R1(A, B, C) and R2(C, D)

C. Add C to the candidate key

D. Combine all attributes into one table

Solution

1. Step 1: Identify violating dependency

   C -> D violates BCNF because C is not a candidate key (A,B is key).

2. Step 2: Decompose relation

   Decompose into R1(A, B, C) with key (A,B) and R2(C, D) with key C to fix violation.

3. Final Answer:

   Decompose R into R1(A, B, C) and R2(C, D) -> Option B

4. Quick Check:

   Decompose on violating dependency to achieve BCNF [OK]

Hint: Decompose on dependencies with non-key left side [OK]

Common Mistakes:

• Trying to remove dependencies instead of decomposing
• Adding attributes incorrectly to keys
• Merging tables instead of decomposing