Consider a relational table R that is in 3NF, but not in BCNF. Which one of…
2020
Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE ?
- A.
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute.
- B.
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is not a proper subset of any key.
- C.
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is a proper subset of some key.
- D.
A cell in R holds a set instead of an atomic value.
Attempted by 395 students.
Show answer & explanation
Correct answer: A
Key definitions:
BCNF: For every nontrivial functional dependency X→A, X must be a superkey.
3NF: For every nontrivial functional dependency X→A, either X is a superkey or A is a prime attribute (an attribute that is part of some candidate key).
Reasoning:
A relation that is in 3NF but not in BCNF must have at least one nontrivial functional dependency X→A where X is not a superkey (otherwise BCNF would hold).
Because the relation is in 3NF, that same dependency must satisfy the 3NF condition, so A must be a prime attribute. Otherwise the dependency would violate 3NF.
Therefore the correct characterization is: there exists a nontrivial functional dependency whose left-hand side is not a superkey and whose right-hand side is a prime attribute.
Example:
Let the relation have attributes A, B, C with a candidate key {A, B}.
If there is a dependency A→B, then A is not a superkey, B is a prime attribute (part of the candidate key), so the relation satisfies 3NF but violates BCNF.
Conclusion: The true statement is that there exists a nontrivial functional dependency whose left side is not a superkey and whose right side is a prime attribute.