GATE CSE 2012 | Question: 2

Question

Which of the following is TRUE?

• A. Every relation in $\text{3NF}$ is also in $\text{BCNF}$
• B. 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$
• C. Every relation in $\text{BCNF}$ is also in $\text{3NF}$
• D. No relation can be in both $\text{BCNF}$ and $\text{3NF}$

Comments

Please explain difference between "fully functionally dependent" and "functionally dependent"

---

Normal Form Set 

---

Detailed Video Solution: https://youtu.be/H00icXp3Bvc 

Option B is the definition of 2NF, Not of 3NF. 

Complete Normalization Playlist: Normalization Complete Playlist

Answer 1

(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/

Comments

Option B is a clear cut definition of 2NF & This is enough to become false.

---

@Anu007 I have a doubt

every non prime attribute means the example you gave there are c , d prime attributes .and every key means AB , then it says” every non prime” attribute is fully dependent on “every key” of R , then the dependeny you gave C->D cannot even exist right ?

 correct me wherever i am wrong ..

---

@KhushiRastogi

For option $B$, its actually definition of 2NF where no partial dependency exists

Note that partial dependency can still exist within prime attributes and schema is still in $2NF$

In the example, we assume

Prime attributes: $A$,$B$. 

Non-prime attributes: $C$,$D$

FDs expected to hold: $AB$ $\rightarrow$ $D$, $C$ $\rightarrow$ $D$

Acc to option $B$, $AB$ fully determines both $C$ and $D$. 

But, example has another FD $C$ $\rightarrow$ $D$ which is non-prime to non-prime. So, this schema is still not in $3NF$

Hope this clears all your doubts

Answer 2

$\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)$

Comments

Ans is (C) Every relation in BCNF is also in 3NF. Straight from definition of BCNF.

 Answer about confusion option B is:- https://gateoverflow.in/33464/3nf-true-false

---

@Subarna Das Ma'am I think there is a correction in your statement 2.

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.

It should be

 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 some key of the relation. 

---

Sambhrant Maurya

yes!!

Answer 3

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

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.

Answer 4

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.