0

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.

Kim
  • 69
  • 1
  • 12
  • Hi. You don't seem to be using terms or justifying correctly. Give a reference to the presentation you are following. Dozens of published academic textbook pdfs are free online. ("obtained" is not a defined or clear term re CKs. The FDs that hold when some hold are all the ones given by Armstrong's axioms, including trivial ones. What do "extraneous left-hand side attributes" & "redundant FD" mean--are they part of the presentation in your reference?--why did you "come to" those schemas? Each component has its own FDs & CKs. In BCNF *nontrivial* FDs must be on superkeys. Etc.) – philipxy Feb 25 '18 at 18:21
  • @philipxy It's already specified in the post: https://stackoverflow.com/a/34350122/9139313 – Kim Feb 25 '18 at 18:51
  • Thanks, sorry I missed that link. Regardless, SO posts should be self-contained. OK, that explains some of your phrasing & conclusions. Still you really need to learn & use terms & processes correctly. – philipxy Feb 25 '18 at 19:06

1 Answers1

0

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.

Renzo
  • 26,848
  • 5
  • 49
  • 61
  • I don't quite understand. Do you compare the left hand side of a FD to the candidate key or a relation? Because from the other post it says that the left hand side has to be a superkey. Does that not refer to a candidate key? – Kim Feb 25 '18 at 14:00
  • A candidate key is a set of attributes that determine all the others, and a superkey is a superset of candidate key (non strict superset). So in R1 all the possibile (non-trivial) dependencies are D-> A, D->B, D->C, D->AB, D->BC, etc., D -> ABC. In all these cases D is the candidate key, so it is a superkey, so the dependencies do not violates the BCNF. What is not clear in this case? – Renzo Feb 25 '18 at 14:38
  • Alright to clarify, there has to exist a relation for each functional dependency such that the left hand side of a functional dependency is a superkey of that relation? – Kim Feb 25 '18 at 14:52
  • No, a decomposition is done according to an algorithm (for instance for BCNF there is the analysis algorithm) and in a decomposed relation there can be several functional depedencies of the original set of dependencies. For instance the analysis algorithm treats only problematic dependencies (i.e. that violate a normal form). – Renzo Feb 25 '18 at 15:25