relational-databasedatabase-normalizationbcnf

Decompose relation into BCNF form

Suppose we have

R(ABCDE) and functional dependencies: {AB -> C, B -> C, C -> D}, convert this into BCNF.

I see that the candidate key for R is ABE so this is clearly not in BCNF already.

To decompose, I created these relations:

R1(ABC)
R2(BC)
R3(CD)
R4(ABE)

Does this work?

Solution

  • If the functional dependencies given are a cover of the functional dependencies holding on R, applying the so-called “analysis” algorithm produces the following decomposition:

    R2 (ABE)
R3 (CD)
R4 (BC)

    We start by considering a minimal cover of the dependencies. This is:

    B → C
C → D

    (since in AB → C we can note that A is estraneous, since we have already that B → C).

    So, since B → C violates the BCNF, we decompose R in two relations:

    R1 (BCD), with candidate key B
R2 (ABE), with candidate key ABE

    In the second relation there are no non-trivial functional dependency, so we leave it as is, while in R1 the only candidate key is B, so C → D violates the BCNF and we decompose it in:

    R3 (CD)
R4 (BC)

    so the final decomposition is R2, R3 and R4. Note that this decomposition preserves the functional dependencies (not always this is possible with this algorithm).