Boyce-Codd Normal Form (BCNF) in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is Boyce-Codd Normal Form (BCNF)?

BCNF is a type of database normalization that ensures every determinant is a candidate key. It removes redundancy caused by functional dependencies.

beginner

What is a determinant in the context of BCNF?

A determinant is an attribute or a set of attributes on which some other attribute is functionally dependent.

intermediate

How does BCNF differ from Third Normal Form (3NF)?

BCNF is stricter than 3NF. In BCNF, every determinant must be a candidate key, while 3NF allows some exceptions if the dependent attribute is a prime attribute.

beginner

Why is BCNF important in database design?

BCNF helps eliminate redundancy and update anomalies by ensuring that all functional dependencies are based on candidate keys.

intermediate

Give an example of a functional dependency that violates BCNF.

If in a table, attribute A determines B but A is not a candidate key, then this violates BCNF. For example, if {StudentID, Course} is the key but Course determines Instructor, then Course → Instructor violates BCNF.

Which of the following must be true for a relation to be in BCNF?

A No transitive dependencies exist

B Every attribute is dependent on the primary key

C Every determinant is a candidate key

D Every attribute is atomic

BCNF requires that every determinant in a functional dependency is a candidate key.

BCNF is a stricter form of which normal form?

A First Normal Form (1NF)

B Third Normal Form (3NF)

C Fourth Normal Form (4NF)

D Second Normal Form (2NF)

BCNF is a stricter version of 3NF, removing some exceptions allowed in 3NF.

If a functional dependency exists where the determinant is not a candidate key, what does this imply?

A The relation violates BCNF

B The relation is in 1NF

C The relation is in BCNF

D The relation is in 2NF

BCNF requires determinants to be candidate keys; otherwise, it is violated.

Which problem does BCNF help to avoid in databases?

A Data redundancy and update anomalies

B Slow query performance

C Lack of indexing

D Data encryption issues

BCNF reduces redundancy and prevents update anomalies by enforcing strict dependency rules.

In BCNF, what is a candidate key?

A An attribute that is always numeric

B An attribute that can be null

C An attribute that depends on another attribute

D An attribute that uniquely identifies a tuple

A candidate key uniquely identifies each record in a table.

Explain the main rule that a relation must follow to be in Boyce-Codd Normal Form (BCNF).

Think about what attributes determine others and their role as keys.

Describe why BCNF is considered stricter than Third Normal Form (3NF) and give an example of a violation.

Focus on the role of determinants and 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