Search code examples
databasefunctional-dependenciesbcnf

Determining BCNF violations


So I have a relation schema with FD's that look like this:

R(A,B,C,D): AB -> C, B -> D, CD -> A, AD -> B

Now I'm trying to find all the BCNF violations and then decompose the tables. I computed the left hand side of all the FD's and found this:

AB+ = {A, B, C, D}
B+ = {B, D} <- violation
CD+ = {C, D, A, B}
AD+ = {A, D, B, C}

So I decompose the table to look like this:

R1 (B, D)
R2 (A, B, C)

The only problem is that I'm not sure if this is all I have to do when it comes to decomposing the tables or if I have to do more. I'm mainly confused about the AB, CD, and AD parts.


Solution

  • In your example, B → D is in effect the only dependency that violates the BCNF, since in all the other depedencies the left hand side is a key (actually all the keys of the relation are (A D), (A B), (B C) and (C D)).

    So, you can decompose by splitting the original relation R in R1, containing B+, that is BD, and R2, containing R - B+ + B, that is ABC, as you have correctly found.

    Then one should apply again this process if in any of the decomposed relation there is some dependency that violates the BCNF. But this is not the case, since the only dependency in R1 is B → D, with B the only key, and with the dependencies AB → C and BC → A in R2, that has keys AB and BC.

    At this point can terminate the process since R1 and R2 are both in BCNF. But we should note also that this decomposition does not preserve the dependencies, since CD → A and AD → B have been lost.