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

VIOLATION OF 3NF IS NON SUPER KEY → NON PRIME
             3NF SUPPORTS FD'S OF TYPE:
              SUPER KEY → X (BCNF)
               PRIME → PRIME (NOT BCNF)

---

Aisa ek question to GATE 2025 vi deserve karta he 😍

---

3NF VIOLATION

NON KEY --> NON KEY

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

Detailed Video Solution with Complete Analysis: 3NF But Not BCNF - GATE 2020 Question  

When can we say that a Relation $R$ is NOT in a particular Normal Form??

---

NOT in BCNF:

There exists a nontrivial functional dependency $X \rightarrow A$, where $X$ is not a superkey.

---

NOT in 3NF:

There exists a nontrivial functional dependency $X \rightarrow A$, where $X$ is not a superkey and $A$ is a non-prime attribute.

---

If R is in 3NF But NOT in BCNF then:

There exists a nontrivial functional dependency $X \rightarrow A$, where $X$ is not a superkey and $A$ is a prime attribute.

---

NOT in 2NF:

There exists a nontrivial functional dependency $X \rightarrow A$, where $X$ is a proper subset of some candidate key and $A$ is a non-prime attribute.

---

NOT in 1NF:

A cell in $R$ holds a set instead of an atomic value.

---

In $3NF$ but Not in $BCNF$ $\rightarrow $ Option $A$

Option $B$ $\rightarrow $ Not in $3NF$

Option $C$ $=$ Not in $2NF$

Option $D$ $\rightarrow$ Not in $1NF$

Detailed Video Solution with Complete Analysis: https://youtu.be/vx8X9iiaOaw?feature=shared 

Comments

If R is in 3NF But NOT in BCNF then:

There exists a nontrivial functional dependency , where  is not a superkey and  is a prime attribute. 

But , here we are confirming that other fd's not violating the 3NF form , like in the other fd there might be a situation that  non superkey determines non prime attribute ? 

Answer 3

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 4

Simple:-
If its not in BCNF the X is not superkey
so according to 3NF non prime attribute should not depend on non prime attribute. Since in X->A , X is not super key so its non prime attribute, So if A is non prime then non prime depends on non prime So A should be prime attribute

Answer 5

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.

Answer 6

SE The only condition for That R in 3NF but Not in BCNF in X-Y is X in not super key( then only it is not in BCNF) and  then Y must be prime otherwise it voilates the condition of 3NF...  

Option (A) is Correct