2

I've been given the relation and functional dependencies

enter image description here

And am looking to justify what form it is in, and then to transform it into BCNF.

Now I proposed that it was in 3NF, as the second FD is a transitive dependency with a key attribute as its RHS. This second FD also violates BCNF, because C is not a superkey for R.

However - I am unsure how to go about decomposing into BCNF.

If I decompose into;

enter image description here

This voids the first FD, and effectively makes (A,C) the new key - so it doesn't seem correct! Can this relation be converted to BCNF?

davidhood2
  • 1,367
  • 17
  • 47

1 Answers1

2

Can this relation be converted to BCNF?

Every relation can be converted in BCNF, by applying the “analysis algorithm”, that can be found on any good book on databases.

Note that the relation has two keys, AB and AC, so that all attributes are primes (and for this reason the relation is automatically in 3NF).

You must start by finding all the dependencies that violates the BCNF, in this case only C → B, since C is not a superkey.

Then you decompose the relation in two relations, one contaning C and all the attributes determinates by it (in this case only B), and the other one including all the other attributes plus C.

So the decomposition is actually: 

R1(B, C), with key C, with the only (non-trivial) dependency C → B
R2(A, C), with key AC, without (non-trivial) dependencies

Then the decomposition must be repeated for every relation that has some dependency that violates the BCNF, but in this case there is no such relation, because both R1 and R2 are in BCNF.

Finally note that the decomposition does not preserve the dependencies. In fact the dependency AB → C is not preserved in the decomposition.

Renzo
  • 26,848
  • 5
  • 49
  • 61