Search code examples
databasedatabase-normalizationfunctional-dependenciesbcnf

Database Normalization BCNF decomposition


I have a relation schema M with the attributes {B, C, D, E, R} and the following set of functional dependencies:

  1. B, D, E -> R
  2. B, D, R -> E
  3. B, C, D -> R
  4. C, D -> E

I want to perform a BCNF decomposition for this schema. The key of this relation schema is {B, C, D}.

I followed the BCNF decomposition algorithm, and in the first step, I identified the functional dependency B, D, E -> R. According to the algorithm, I should create two components: R1 {B, D, E, R} and R2 {B, C, D, E}.

Now, my question is about removing the attribute R from the original relation schema. R appears on both the left and right sides of functional dependencies B, D, R -> E and B, C, D -> R. However, B, C, D -> R is already in BCNF, and if I remove both of these dependencies, I'm left with only C, D -> E.

I haven't come across examples that cover this specific scenario. What could I have done wrong in this situation?


Solution

  • Let's follow the algorithm that you have shown in a comment.

    The FD B,D,E -> R violates the BCNF, since the only candidate key of the schema is B,D,C, so we split the schema in two subschemas:

    R1(B,D,E,R)  (all the attributes of the FD)
    R2(B,D,C,E)  (the attributes of the relation without the determinate, R)
    

    The closure of the projection of the FDs on R1 is:

    B,D,E -> R
    B,D,R -> E
    

    so the schema is in BCNF, since the only two candidate keys are (B,D,E) and (B,D,R) and in both FD the determinant is a candidate key.

    The closure of the projection of the FDs on R2 is:

    C,D -> E 
    

    but, since (C,D) is not a candidate key for R2 (the candidate key is (B,C,D)), we have to split this schema in two schemas:

    R3(C,D,E), with closure of the projected FDs C,D -> E
    R4(B,C,D), with no non-trivial dependencies
    

    so both are in BCNF.

    So the final, correct, decomposition is R1, R3, R4, and such decomposition preserves both data and dependencies.