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. X is not a superkey and A is a non-prime attribute. B. R has a nontrivial functional dependency X -> A, where X is not a superkey and A is a non-prime attribute. C. R has a nontrivial functional dependency X -> A, where X is not a superkey and A is a prime attribute and X is a proper subset of any key. D. A cell in R holds a set instead of an atomic value.

GATE 2020 · Databases · Database Normalization · medium

Answer: C. R has a nontrivial functional dependency X -> A, where X is not a superkey and A is a prime attribute and X is a proper subset of any key.

1. Analyze what 3NF allows that BCNF forbids: Since R is in 3NF but NOT BCNF, there must exist a non-trivial FD X->A where X is NOT a superkey (BCNF violation). For this same FD to not violate 3NF, A must be a prime attribute.
2. Evaluate each option: Option A mentions A is non-prime — if A were non-prime and X is not a superkey, this would violate 3NF, contradicting our assumption. So A is FALSE. Option B also says A is non-prime — same issue, FALSE. Option C says X is not a superkey, A is prime, and X is a proper subset of any key — this is consistent with 3NF (A is prime) while violating BCNF (X not superkey). TRUE. Option D about non-atomic values is about 1NF, unrelated to 3NF vs BCNF.
3. Verify with an example: Classic example: R(A, B, C) with FDs {AB->C, C->B}. Keys: {AB} and {AC}. C->B: C is not a superkey, but B is prime (part of key AB). So R is in 3NF but not BCNF. Here X = C (proper subset of key AC? No, C is itself a key AC minus A). Actually X = C and key = AC, so C is a proper subset of key AC. A (the attribute) = B which is prime. This matches option C.