edited by
19,523 views
45 votes
45 votes

Which of the following is TRUE?

  1. Every relation in $\text{3NF}$ is also in $\text{BCNF}$
  2. A relation $\text{R}$ is in $\text{3NF}$ if every non-prime attribute of $\text{R}$ is fully functionally dependent on every key of $R$
  3. Every relation in $\text{BCNF}$ is also in $\text{3NF}$
  4. No relation can be in both $\text{BCNF}$ and $\text{3NF}$
edited by

5 Answers

Best answer
38 votes
38 votes

(C) Every relation in $\text{BCNF}$ is also in $\text{3NF}.$ Straight from definition of $\text{BCNF}.$

More Read: https://gatecse.in/demystifying-database-normalization/

edited by
22 votes
22 votes

$\text{1. Every relation in 3NF is also in BCNF}$

For a $3NF$ relation to be in $BCNF$, that has to has satisfied this condition-

$\text{for each dependency }$ $X \rightarrow Y$, $\text{X should be the super key.}$

So, this statement is wrong

 $\text{2. A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent}$ $\text{on every key of R}$

This is a straightforward definition of $2NF$. If a relation is in $1NF$ and in that relation each & every non-prime attributes has to be fully functionally dependent on each key of the relation.

& for a $2NF$ relation to be in $3NF$, that has to satisfy the condition which states, no non-prime attributes should be transitively dependent on candidate key or we can say that, no non-prime attribute should be determined by anything but a super key

Let's take an example:

$R(ABCDE)$ and $F = \{BC \rightarrow ADE, A \rightarrow BCDE, D \rightarrow E\}$

The candidate keys will be = $\{ A, BC\}$

Prime Attribute = $A,B,C$

Non-Prime Attribute = $D,E$

Here, all the non-prime attributes$(D,E)$ are fully functionally dependent on each key of $R$

But here, $E$(non-prime attribute) can be determined by $D$ and $D$ is not the candidate key. So, $E$ is transitively dependent on the candidate keys of $R$ and moreover, $D$ is not the super key also.

$\therefore$ This relation is not in $3NF$. For making it to $3NF$ we have to break the relation into $2$ parts: $R_1(ABCD), F=\{ \{A \rightarrow BCD\}, BC \rightarrow AD\}$ & $R_2(DE), F = \{D \rightarrow E\}$

This option is also wrong.

$\text{3. Every relation in BCNF is also in 3NF}$

Obviously, For a relation to be in $BCNF$, it has to be in $3NF$ first.

$\color{Green}{\therefore \text{This statement is Correct}}$

$\text{4. No relation can be in both BCNF and 3NF}$

Yes, there can be some relation which is in $3NF$ and as well as $BCNF$

and if a relation is in $BCNF$ then it is also in $3NF$

So, this statement is also wrong.

Correct option is $C)$

edited by
7 votes
7 votes

What is partial Dependency ?
Most aspirants think that its related to 2NF but its an independent topic separate from 2NF.
 

Navanthe

Consider a relation R(ABC) AB→C and A→C
Here A→ C is making the FD AB→ C as partial .

Consider a relation R(ABCD) AB<-->CD  C-->D
There is no partial dependency 

Partial dependency is not restricted to only non prime or prime attributes and neither to non trivial FD’s or to trivial FD’s.


Now coming to the question specially option ii) ,its saying every non prime is fully functionally dependent on all CKs.
Consider the reltion R(ABCD)  AB→ CD C→ D
Here the candidate key is AB and C and D are non prime attributes,which are fully functionally depdendent on all CKs.
Still we can get a partial dependency (AB → C → D and C-/→ AB ) → (AB→ D)
So it doesnt hold true

image

As per this image BCNF is a subset of 3NF which means its more stricter.Hence a relation in BCNF is always in 3NF. SK→ Non prime is the intersection point of FDs for both of them 

Correct answer will be option C.

0 votes
0 votes

option C

here, in option B, it satisfies the requirement of 2NF. To be in 3NF no transitive dependency should ne there. Relation can have NP->NP FD satisfying statement B, but that will violate 3NF condition.

Answer:

Related questions

9 votes
9 votes
4 answers
2
gatecse asked Sep 29, 2014
2,662 views
Given the sequence of terms, $\text{AD CG FK JP}$, the next term is$\text{OV}$$\text{OW}$$\text{PV}$$\text{PW}$
13 votes
13 votes
1 answer
3
gatecse asked Sep 29, 2014
2,795 views
Which one of the following options is the closest in meaning to the word given below?Mitigate DiminishDivulgeDedicateDenote
10 votes
10 votes
2 answers
4
gatecse asked Sep 29, 2014
2,938 views
Choose the most appropriate alternative from the options given below to complete the following sentence:Despite several _________ the mission succeeded in its attempt to ...