Redirected
edited by
2,691 views
8 votes
8 votes

Which of the following statement false of relation $R$ is in $3NF$ but not $BCNF$?

  1. Relation $R$ must consist atleast two over-lapped candidate keys.
  2. Relation $R$ must consist proper subset of candidate key determines proper subset of some other candidate key.
  3. Relation $R$ must consist atmost one compound candidate key and other candidate keys simple candidate key.
  4. Relation $R$ must consist atleast two compound candidate keys.

Please Explain every Option.

edited by

1 Answer

Best answer
18 votes
18 votes

Answer C.

We know that if a relation $R$ is in 3NF but Not in BCNF, then Relation $R$ must consist proper subset of a candidate key which determines proper subset of some other candidate key. So, Using this, we can derive Option A and D.

First, To make proper subset of a candidate key we need a Compound key (Can't make a proper subset of a simple candidate key as it will be $\phi$) and to make proper subset of some other candidate key we need another Compound key. Hence, We need Two compound keys at least.  Try it.

Option C says "Relation R must consist atmost one compound candidate key and other candidate keys simple candidate key."
Which we can easily discard as in this cse we won't have that One type of Non-trivial FD which is allowed in 3NF but not in BCNF. Try it.

Now, coming to Option $A$, This is what we need to observe here.

We know that if a relation $R$ is in 3NF but Not in BCNF then there must be some Non-trivial FD of the form $A \rightarrow B$ where $A$ is a proper subset of a cand key (let this cand key be $AY$) and $B$ is the proper subset of some other cand key. Hence, Let this other cand key be $BX$. Since $A$ determines $B$ hence we can make another cand key from here as $AX$. So, now We have Two cand keys as $AX$ and $AY$ which are overlapping candidate keys (because of $A$)....Hence Option A "Relation R must consist atleast two over-lapped candidate keys." is correct.

NOW you may get a doubt that will we always have at least three compound cand keys like we got here ($AY, BX,AX$)...Then answer is No. Why?

Say, In the first place Two cand keys in $R$ were $MP$ and $MQ$ and We have Non-trivial FD $P \rightarrow Q$...So, Now even if we replace $Q$ by $P$ in the second compound cand key, we get $MP$ which is already there. So, We can say that we will at least get two such compound keys for sure if a relation is in 3NF but not in BCNF.

Take example cases yourself and find intuition about these facts.

selected by

Related questions

2 votes
2 votes
1 answer
1
1 votes
1 votes
0 answers
2
Balaji Jegan asked Oct 23, 2018
301 views
1 votes
1 votes
1 answer
3
cynicthnkr asked Jul 10, 2018
718 views
Given a relation R(ABCD) with functional dependencies:$AB \to CD\\ BC \to D$Normal form is?1) 1 NF2) 2 NF3) 3 NF4) BCNF
1 votes
1 votes
1 answer
4
ankitgupta.1729 asked Dec 16, 2017
1,048 views