retagged by
756 views
1 votes
1 votes

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

https://dba.stackexchange.com/questions/81310/what-exactly-is-overlapping-candidate-key

https://gateoverflow.in/150945/normal-form

I read above two links , They went bouncer. Doubt i have is 

Relation R(ABCDEF) {ABC->DEF,   D->ABCEF    , E->B  }

D, ABC, ACE are keys for relation which is in 3NF

I understand relation is not in BCNF because E is not S.K.

But how can Overlapping of ABC & ACE stopping this relation from becoming BCNF ?

retagged by

1 Answer

0 votes
0 votes

Leave the example,try this :

SSP (Sno, Sname, Pno, Qty)

Here, Sname is considered unique for each Sno.

FD of above relation is

(Sno, Pno)                - Qty

(Sname, Pno)          - Qty

Sno                           - Sname

Sname                     - Sno

This relation has two overlapping candidate keys, because there are two composite candidate keys (Sno, Pno) and (Sname, PNo) out of which Pno is common attribute in both the candidate keys, so this is due case of overlapping of candidate keys.

Possible FD Diagram

Here, both the relations are in 3NF, because every non-key attribute is non-transitively fully functional dependent on the primary key.

In above relation, there is only one non-key attribute i.e. Qty and it is FFD and non transitively dependent on the primary key.

Sname, Sno are not non-key attributes because they can participate into the primary key as shown in FD diagram.

But, SSP relation is not in BCNF because this relation has four determinants:

(Sno, Pno)

(Sname, Pno)

(Sno)

(Sname)

Out of these four determinants two determinants (Sno, Pno) and (Sname, Pno) are unique but Sno and Sname determinants are not candidate keys.

So,we see the overlapping candidate  keys are causing trouble.... :-)

Related questions

0 votes
0 votes
0 answers
1