normal form

Question

A 3NF table which does not have multiple overlapping candidate keys is said to be in

a)1NF

b)2NF

c)4NF

d)BCNF

Comments

Ans will be BCNF

Given condition: a relation is already in 3NF and  multiple CKs exist and they must be non overlapping.

Now, lets analyze:

If a table is in 3NF then all the FDs X->Y must be either

case (1) : X is SK (satisfies BCNF property) or  

case (2) : Y is  Prime_Attrib (we have to analyze this case where X- non_key, Y- prime_Attribute.)

Now, If such case exist i.e. Non_key -> Prime_Attrib then there must be overlapping CK (which violates given condition). So if a table is in 3NF and multiple non overlapping CKs are present then case (2) i.e       Non_key->Prime_Attrib.    It means table becomes BCNF. Lets understand this point by an example:

R( ABCDEF) - 3NF and FDs are:

{ ABC->DEF,   D->ABCEF    , E->B  }

// I have deliberately put FD "E->B" to avoid BCNF form. So if i do so then my CK becomes overlapping. ie

CK= { D, ABC, ACE} - violates initial condition. So FDs like E->B can't exist in 3NF, multiple non overlapping CKs.

So Correct Ans- Option D (BCNF)

---

@stanchion

Nice Explanation

---

i didn't get how can we inference BCNF from, "A 3NF table which does not have multiple overlapping candidate keys is said to be in" .

someone, please explain it.