Search code examples
databaseschemanormalization3nfbcnf

BCNF Decomposition and Keys

I've been looking to decompose the following relation from its present state, into BCNF with three functional dependencies.

enter image description here

Taking the maxim

the key, the whole key, and nothing but the key

I concluded that B-->C transitive functional dependency meant it was in 2NF, and should be decomposed to remove this into

enter image description here

This also, I think, should be in BCNF. However, my question is, does the A,B --> C FD break this - because it doesn't seem to match the 'nothing but the key', aspect of the maxim above? (And the 'B' part of the A,B --> FD is not a key attribute, rather 'B' is addition to the key)

Solution

  • You should note that the three dependencies:

    A → B
A B → D
B → C

    are not a canonical cover (A B → D can be simplified to A → D, given A → B). So, the canonical cover is:

    A → B
A → D
B → C

    and since the key is A, you are correct in decomponing the relation in:

    R1<(B, C), {B → C}>
R2<(A, B, D), {A → B, A → D}>

    Note that all the dependencies satisfy the BCNF definition, since the key of R1 is B, the key of R2 is A, and each depedency has its LHS which is a key.