normal forms

Question

Q)Which of the following is false??

1)Relation R in BCNF with atleast one simple candidate key is also in 4NF

2)Relation R in BCNF with all candidate key simple is also in 4NF

3)Relation R in 3NF with only one compound candidate key is also in BCNF

4)Relation R in 3NF with atleast one simple candidate key is also in 4NF

Explain with example....

 

 

Answer 1

1)Relation R in BCNF with atleast one simple candidate key is also in 4NF
     if multivalue attribute exist in the table then every candidate key will be compound key 
     so here there is one simple candidate key no multivalue attribute present so surely its in 4NF also.

2)Relation R in BCNF with all candidate key simple is also in 4NF
    same reason as above.

3)Relation R in 3NF with only one compound candidate key is also in BCNF
   
  if relation R is in 3NF and atmost one compound CK then it is also in BCNF
 if more than one compound Ckey will present then prime to prime will be possible which will     violate the BCNF property but with one compound CK there will be no such possiblity  

e.g. R(ABCDE) {AB->C C->D D->E E>A} 
       CK{AB EB DB CB} Here  C->D D->E E>A violates the BCNF

4)Relation R in 3NF with atleast one simple candidate key is also in 4NF

   There is no MVD in the relation 
  But there can be possiblity of two compound candidate key then it will not be in BCNF

 R(ABCDE) {AB->C C->BD D->E E>A} 
 CK{AB C EB DB } Here D->E E>A violates the BCNF so 4NF will also not satisfied.

option 4 is false.
 

Comments

For option 3)   R  in 3NF with one compund key can also violate BCNF. Consider ex.

R(A,B,C)   with AB->C and C->B

so one candidate key:  AB

R is in 3NF with C->B violating BCNF.

---

here you have 2 keys AB and AC and not 1, as the 3rd option specifies.

---

 if more than one compound Ckey will present then prime to prime will be possible which will     violate the BCNF property but with one compound CK there will be no such possiblity   

Explain this in  bit detail? Iam not able to get this 

---

For option 3)

Consider a 3NF that is not a BCNF:
This is only possible when a Non Super Key -> Prime attribute

Since every CK is a SK, we can say that this implies:
Non CK (X) -> Prime Attribute (Z)

Say Z is a Prime Attribute because it is a part of CK AZ.
(If Z itself was the entire CK, then even X would be a Super Key and it would not violate BCNF.)

So it is in 3NF, Not BCNF (as assumed) and we are only aware of 1 compound CK AZ (that made Z a Prime Attribute).

Now, since X -> Z therefore, AX -> AZ -> Everything. That means AX is also a CK. That means there has to be at least one more Compound CK for this to be in 3NF and Not BCNF.