Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE??
R has a nontrivial functional dependency X → A, where X is not a superkey and A is a prime attribute.
R has a nontrivial functional dependency X → A, where X is not a superkey and A is a nonprime attribute and X is not a proper subset of any key.
R has a nontrivial functional dependency X → A, where X is not a superkey and A is a nonprime attribute and X is a proper subset of some key.
A cell in R holds a set instead of an atomic value.
Generalization: If functional dependency X → A, Then
In 3NF: (i) Either X can be superkey or Y can be a prime attribute.
OR (ii) There should not be any transitive dependencies.
Where Transitive dependency means (in simple form) there should not be any dependencies like Non-key → Non-key
Whereas, In BCNF If Functional Dependency is X → A then X must be a superkey.
Now, check the options.
Option (D) says A cell will have more than 1 value instead of an atomic value so, It is not even 1NF.
Option (C) X → A where X is a proper subset of some key and A non-prime attribute. So, It is partial dependency. Thus, It is not even 2NF. For any FDs to be in 3NF, it must satisfy 2NF also. So, It is not in 3NF.
Option (B) X → A where neither X superkey nor A – prime attribute. So, not 3NF.
Option (A) Satisfies 3NF property but not in BCNF, because as mentioned For any FD X → A to be in BCNF, X must be superkey.
So, correct option (A)