database-query-normalization-true-false

Question

if table r has only one candidate key then which of the following is true:

if R is in 3nf,it is also in bcnf

R may not be in bcnf

Answer 1

Both are true.

1. If in 3NF, a relation will not be in BCNF if and only if

• $A \to B$, where $B$ is a prime attribute and $A$ is not a super key and $B \nsubseteq A$.

But, if there is only one candidate key, there is no possibility of a $B$ prime attribute other than the FD becoming trivial. So, the relation in 3NF must be in BCNF. 

2. R may or may not be in BCNF - Even with one candidate key, we can have partial FD (if the key is composite) and hence the relation need not be even in 2NF.

Comments

okay sir got it, Thankyou for mentioning this point.

---

take relation  R(A,B,C,D,E)

F.D-:{

           ABC→DE

           A→C

}

C.K-→ABC

is in 3NF,

but A→C is not in BCNF.

Am i correct ?@arjun sir?

---

Hey,

if A$\rightarrow$C is in set of FDs then you can not say ABC as a CK because ABC can be further minimised to AB which would be minimal form and hence can be called as CK.

So, here, CK is AB not ABC.

Answer 2

Given that table has only one C.K.,

if R is in 3NF, then for F.D. X->Y

either X is S.K (which is required for BCNF)

or Y shld have prime attributes, if this is so then then we get more than one C.Keys

Hence, R is in BCNF

Comments

actually answer is given first one.

---

what about first statement?

---

sir lets assume R(a,b,c,d)

since question given "one" candidate key.

lets assume ab as the only candidate key,then,ab⁺={a,b,c,d}

then possible fd's are ab->c,ab->d,ab->cd.

now the first option says "if in 3nf"

so if in 3nf then c->d and d->c are ruled out.

Now the real confusion arises with fd's like c->a,c->b,d->a,d->b.

if c/d could uniqely identify a or b can we say actually more than one candidate key(which violates the question) possible like,cb/ca/db/da ?

if we can say that then first option is correct,it is also in bcnf as only one fd ab->cd possible with ab as the super key.

Answer 3

Statement 1$\Rightarrow$$\mathbf{If\: R\: is\: in\: 3NF,\: It \: is\; also\; in\: BCNF}$

$\Rightarrow$If it is in 3NF,then it is no doubt in 2NF$\Rightarrow$It does not have partial dependency.

$\Rightarrow$It won't contain Partial Dependency i.e

A functional dependency X → Y is a partial dependency if some attribute A ε X can be removed from X and the dependency still holds; that is, for some A ε X, (X – {A}) → Y.

  relation Prime Attribute$\rightarrow$Non Prime Attribute.so it won't contain Prime Attribute$\rightarrow$Prime Attribute.It may contain Prime Attribute$\rightarrow$Prime Attribute.

$\Rightarrow$It won't contain Transitive dependency i.e it won't contain relation Non Key$\rightarrow$Non key.But it may contain relation Prime Attribute$\rightarrow$Prime Attribute.

Now BCNF says that if it has Functional dependency X$\rightarrow$Y,X must be a superkey.

But in Relation Prime Attribute$\rightarrow$Prime Attribute,prime Attribute is not a super key,Makes Statement1 False .

Statement 2$\Rightarrow$R may be in BCNF

$\Rightarrow$If Relation contains Prime Attribute$\rightarrow$Prime Attribute,then it is not in BCNF.

if it won't contain this relation ,it will be in BCNF,so it may/may not be in BCNF.

Comments

please read my earlier comment, "nonprime" to "prime" to prove it is not in bcnf.. This can be used for bcnf proof.in bcnf this case is not possible,in 3nf it is possible.

---

yes you can!

---

@Sourav Prime->Prime is not possible here,as only one candidate key is possible,rt?

Answer 4

for 1st question:

AB->CD  D->A     ..................(AB is candidate key)D->A in 3NF but not in BCNF

for 2nd question,

A->B   B->C ......................(A is candidate key) B->C not in BCNF.

Comments

@ARjun. Even though CB is candidate key, C->A is not in BCNF

---

it will not be in 2NF ,then why are you talking about BCNF?(prime$\rightarrow$non prime=partial dependency)

---

@sourav. Thanks.CHanging the answer.