Give an example of a relation which is in 3NF but not in BCNF

Give an example of a relation which is in 3NF but not in BCNF? How will you convert that relation to BCNF?

The correct answer and explanation is :

Example of a Relation in 3NF but not in BCNF:

Consider the relation R(A, B, C) with the following functional dependencies (FDs):

• A → B
• B → A
• C → B

Assume that the candidate keys for this relation are: {A, C} and {B, C}

Step 1: Check for 3NF

A relation is in Third Normal Form (3NF) if for every functional dependency X → Y, at least one of the following is true:

1. X → Y is a trivial FD (Y ⊆ X)
2. X is a superkey
3. Every attribute in Y is a prime attribute (i.e., part of some candidate key)

Let’s evaluate the FDs:

• A → B: A is not a superkey, but B is a prime attribute (since {B, C} is a candidate key) → ✅ Satisfies 3NF
• B → A: Same logic as above → ✅ Satisfies 3NF
• C → B: C is not a superkey and B is a prime attribute → ✅ Satisfies 3NF

✅ Thus, the relation is in 3NF

Step 2: Check for BCNF

A relation is in Boyce-Codd Normal Form (BCNF) if for every functional dependency X → Y, X is a superkey.

• A → B: A is not a superkey → ❌ Violates BCNF
• B → A: B is not a superkey → ❌ Violates BCNF
• C → B: C is not a superkey → ❌ Violates BCNF

❌ So, the relation is not in BCNF

Step 3: Convert to BCNF

To convert to BCNF, decompose R based on the violating FD A → B:

1. Create R1(A, B) with FDs: A → B and B → A
   → A is a key in R1 → R1 is in BCNF
2. Create R2(A, C)
   → From original keys, A and C together form a key → R2 is in BCNF

✅ Now both R1 and R2 are in BCNF, and the decomposition is lossless and dependency-preserving (in this case, because A ↔ B).

Summary

A relation can be in 3NF but not in BCNF when a non-superkey determines a prime attribute. BCNF requires stricter rules: only superkeys can determine other attributes. To enforce BCNF, decompose the relation using violating FDs while ensuring lossless join and, if possible, dependency preservation.