I have a relation schema M with the attributes {B, C, D, E, R} and the following set of functional dependencies:
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?
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.