I'm trying to understand decomposition to BCNF. I have read many examples, yet I still do not understand few things. I followed this answer to try and solve following problem:
Attributes are customer name(A), address(B), phone(C), id(D) and accounts have a number(E), type(F) and balance(G).
What functional dependencies hold if customers have one and only one id, name, address and phone number and accounts have one number, type and balance and are owned by one and only one customer? Give a BCNF decomposition using these dependencies of R(ABCDEFG)
What I have come to so far:
To first obtain the FDs specified in the question:
D -> ABC // If we agree on same customer ID, then we agree on the name, address and phone #
E -> DFG // If we agree Account number, then we agree on customer ID, account balance and account type
The only candidate key we have is: {E} as all attributes can be obtained with this attribute.
Since there are no extraneous left-hand side attributes and no redundant FD, I have come to following relational tables:
R1={D, A, B, C}
R2={E, D, F, G}
Where the keys in these two relations are marked in bold
Now to check for BCNF we check if any of these relations (R1,R2) violate the conditions of BCNF (i.e. for every functional dependency X->Y the left hand side (X) has to be a superkey) .
Now we can see that E -> DFG has a left hand side that is a super key. However D -> ABC does not have a left hand side that is a super key. So that FD violates BCNF. But I don't know how to proceed into decomposing into BCNF.
When you check for satisfaction of the BNCF of the decomposed relations, you must check the functional dependencies separately for each relation.
So, in R1={D, A, B, C} the only (candidate) key is D (as you have noted), with all the non-trivial dependencies that have only D as left part; in R2={E, D, F, G} the only (candidate) key is E with all the non-trivial dependencies that have only E as left part. So in both relations there is no (non-trivial) dependency that violates the BCNF, and so the decomposition is correct and nothing else must be done.