GATE CSE 2020 | Question: 36

Question

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 \rightarrow A$, where $X$ is not a superkey and $A$ is a prime attribute.
• B. $R$ has a nontrivial functional dependency $X \rightarrow 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 \rightarrow 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.

Comments

https://www.youtube.com/watch?v=AqRZ04rjiNY&list=PLIPZ2_p3RNHhJjQGnZB2jORa0JxLdMp4X

---

(1NF) No atomic values – Option D wrong.

(2NF) X is proper subset of key – Option C wrong

(3NF) X is not superkey and A is non-prime attribute – Option B wrong

 

Hence Answer–A.

---

Learn Complete Analysis Here:

3NF But Not BCNF - GATE 2020 Question

What makes a relation 3NF But Not BCNF??

Answer 1

In $\text{3NF}$  where functional dependency is of type $X  \rightarrow Y$

$X$ can be the super key or $Y$ can be the prime attribute

Whereas in $\text{BCNF}$ where functional dependency is of type $X \rightarrow Y$

$X$ should be super key $\text{(BCNF}$ is more strict compared to $\text{3NF)}$

• Option (C) says it has a partial dependency $($not even $\text{2NF)}$.
• Option (D) multiple values in a cell. i.e not atomice $($not even $\text{1NF)}$.
• Option (B)  says $X$ is not a super key and $Y$ is not a prime attribute. Therefore not $\text{3NF}$.

Ans (A): Says $X$ is not a super key but $Y$ is a prime attribute. Satisfies one of the conditions of the $\text{3NF}$ formal definition. As $X$ is not a Super Key it is not in $\text{BCNF}$.

Answer 2

OPTION A. SINCE X IS NOT A SUPER KEY BUT A  IS PRIME. ( ANY ONE CONDITION (1) X is a Super key (2) A is prime attribute ) .here option A satisfy the condition.

Answer 3

If ( X  ->  Y ) is trivial function means Y is subset of X (X ∩ Y = not-null),
example (sid+sname) -> sid,  here  X must be SuperKey.

But in question is say (X -> Y) is Non-trivial means X ∩ Y = null , means X is must be not SuperKey.

Given is in 3NF and not in BCNF.

3NF => means (L.H.S = CandidateKey or SuperKey)  or  (R.H.S = Primary Attribute)

BCNF => means  (L.H.S = CandidateKey or SuperKey)  and  (R.H.S = non Primary Attribute) .

We already know X(LHS) is not superKey , hence to fulfill it is in 3NF and not in BCNF Y(RHS) must be a Prime attribute .Since it also clear that its not in BCNF.

 

Option A is correct.