-1

I'm reading a textbook containing the following question:

Given the following relation R {A,B,C,D,E,H} and the functional 
dependencies AB->CD, BC->D, C->H, D->HB, CH->AE
does the following decomposition is dependency preserving?
R1(A,C,E,H) R2(B,D,H), R3(A,B,C), R4(B,C,D)

The answer of the textbook was that it is in fact functional dependency preserving, where I thought it wasn't because of the dependency AB->D
Reading this answer made it even more confusing, because it made it seems like
if there is a key inside one of the sub relations, the decomposition must be dependency preserving
A counter example that I couldn't dispute is
For the two rows a1 b1 c1 d1 h1 e2 and a2 b1 c2 d2 h2 e2
all the F.D of R hold, but now R3 has
a1 b1 c1 and a2 b1 c2
and R4 has b1 c1 d1 and b1 c2 d2,
joining R3 and R4 on B gives a1 b1 c2 d2 which breaks the AB->D F.D

philipxy
  • 14,867
  • 6
  • 39
  • 83
DsCpp
  • 2,259
  • 3
  • 18
  • 46
  • 2
    We have FDs `AB -> C`, represented in `R3`; and `BC -> D`, represented in `R4`. Together (law of transitivity) they imply `AB -> D`, so we don't need to represent that separately. – AntC Jan 24 '19 at 09:15
  • 2
    Re the alleged counter-example: "joining R3 and R4 on B ...". Wrong, wrong, wrong: there's no 'on' when joining relations. You must use natural join of all attributes in common. So the `R3` join `R4` gives `a1 b1 c1 d1` and `a2 b1 c2 d2`. – AntC Jan 24 '19 at 09:31
  • Thanks a lot for pointing to the flaw in my example. as to the state that if a minimal key of the original relation is in one of the sub relations, the decomposition is assured to be dependency preserving? – DsCpp Jan 24 '19 at 10:25
  • What is the reasoning for "because" in "because of the dependency AB->D"? Why do you claim "if there is a key inside one of the sub relations, the decomposition must be dependency preserving"? If you don't give your reasoning then we can't tell you where you went right & wrong. Also make your post self-contained--quote everything from another question necessary to ask & answer this question. PS That answer clearly says that you have to consider the *closure* of given FDs. PS Please: Clarify via post edits, not comments. Ask 1 question per post. – philipxy Jan 25 '19 at 03:09

1 Answers1

1

In the example the dependencies are preserved as indicated by AntC in the comments.

The condition that a candidate key of the original relation is present in a decomposed relation is not a guarantee that dependencies are preserved.

Consider for instance the relation R(A B C D E) with dependencies {A → E, BCE → A, D → C} and the decomposition R1(A B D), R2(A E), R3(C D). The relation R1 contains one of the candidate keys of the original relation ({ABD}), but in the decomposition the dependency {BCE → A} is not preserved.

The fact that one of the original keys is present in one decomposed relation could be an indication that the decomposition is lossless, but in general there is no relation between lossless decomposition and dependencies preservation (see for instance this). There is however a result that connects in some way the two properties, as shown in an answer to the question cited.

Renzo
  • 26,848
  • 5
  • 49
  • 61
  • the projection of F on Ti (The definition you provided) lives only within attributes of ti, so how, in the example above AB->D can be in any of the projections, if A,B,D doesn't live "together" in any of the decompositions? Thanks. – DsCpp Jan 24 '19 at 15:31
  • If the definition of “dependencies preservation”, the use of “≡” instead of “=” means that the dependencies obtained with the union of the projections must be *equivalent* to the set F, it must not be necessarily *equal* to F. In other words, you have to obtain a cover of the original set of dependencies, since you can have many “equivalent” set of dependencies (cover). See for instance the first part of this [answer](https://stackoverflow.com/a/54277901/2382734). – Renzo Jan 24 '19 at 15:58
  • So, besides having that my conception was wrong. the true way of determining preservation of dependencies is to build the closure of the dependencies of all the sub relations, and to see if it's equivalent? – DsCpp Jan 25 '19 at 09:15
  • @DsCpp, there is an algorithm so that it not necessary to compute all the projection of F+ (which is an exponential task). It is described in many books on databases. – Renzo Jan 25 '19 at 13:30