Search code examples
database-normalization

How can i normalize this to BCNF?

The relation is 

 Student(StdNo,StdName,appointNo,timing,advisor)

and the functional dependancies are:

   StdNo->StdName
   StdNo,appointNo->Timing,advisor
   Timing->appointNo

By normalising to 2NF I get:

   R1(stdNo,appointNo,Timing,advisor)
   R2(stdNo,StdName)

I think this is already in 3NF. But I am having trouble reducing to BCNF.

For BCNF, i think the R1 relation violates BCNF as Timing-> appointNo and Timing is not a super key. How can i reduce to BCNF?

Solution

  • You are correct in considering that the decomposition in R1, R2 is in 3NF but not in BCNF, for the reason you have specified (timing → appointNo violates the normal form).

    So you should decompose R2 in R3(timing, appointNo) and R4(StdNo, advisor, timing), and the final decomposition is R1, R3, R4.

    Note that with this decomposition the dependency StdNo,appointNo → timing,advisor is not preserved.