Database Normalization BCNF decomposition

Question

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?

Answer 1

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.