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.