0

I have the following question:

Consider relation R(A, B, C, D, E, F, H) with the following functional dependencies:

A --> D, AE --> H, DF --> BC, E --> C, H --> E

Consider three relational schema R1(A, D), R2(E, C), and R3(A, B, E, F, H). They form a decomposition for R.

(a) Do the original functional dependencies apply in R1, R2, and R3?
(b) Is this decomposition in 3NF? Explain your answer.

My attempt:

(a) The original functional dependencies apply in R1, R2, and R3 as long as the relation contains the attributes in the functional dependencies.

(b) No. Keys in R3 = {AEF, AFH}. From {AF}+ = {ABCDF} in R, in R3 {AF}+ = {ABF}. Hence we can form a functional dependency AF --> B, and the LHS of this functional dependency does not contain a key. The RHS also does not contain only key attributes.

The solution provided did not address (a) directly, and stated that the decomposition is in 3NF because the original FDs do not violate 3NF. Would like to know what I did wrongly here. Thank you!

rigidweight
  • 15
  • 1
  • 3
  • Does this answer your question? [What is the minimal proof that a database relation is not in BCNF?](https://stackoverflow.com/questions/53386095/what-is-the-minimal-proof-that-a-database-relation-is-not-in-bcnf) – philipxy Mar 08 '22 at 03:17
  • Re that set of FDs: "These are all the FDs that hold"?--Not possible. "These are all the non-trivial FDs that hold"?--Not possible. *All the FDs implied by those by Armstrong's axioms hold.* All of *those* with appropriate attributes hold in a component. "These are some FDs that hold"?--You can't determine CKs, NFs or decompositions yourself, you need to also know which *don't* hold. Find out what a *cover* is & what the exact conditions are to apply a particular definition/rule/algorithm. They don't say that the FDs form a cover or that the decomposition is lossless; probably they meant to. – philipxy Mar 08 '22 at 03:37
  • I see, just to clarify you mean that the given FDs as well as the derived ones (from Armstrong's axioms) will hold, so to decompose the original relation R (using 3NF or BCNF or any other normalization method) we will have to take that into account? The decomposition provided in the question seems to be randomly generated and we are to assess if it is in 3NF; in this case can I conclude it is not in 3NF because of this closure {AF}+ = {ABF}? – rigidweight Mar 08 '22 at 06:00
  • What you say I mean is correct as long as what you mean exactly by "take that into account" is sound but what I meant was, don't forget that an FD implied by the given FDs might hold in a component--even if not implied by the given FDs that hold in it. My main point is, *quote & use definitions/theorems/algorithms exactly*. PS Your answers aren't clear. (a) is too ters, use enough wordsr. (b) I cannot follow, you don't give clear reasoning or the algorithm or its i/o contract or i & o. I don't get "form a FD". "key"--CK? superkey? I don't follow the reasoning for their (b) solution. – philipxy Mar 08 '22 at 08:20

1 Answers1

0

In the following I assume that the dependencies given are a cover of the dependencies of R.

(a) Do the original functional dependencies apply in R1, R2, and R3?

When one decomposes a relation, not necessarily the dependencies of a cover applies to the decomposed relations. This happens when a decomposed relation does non contains all the attributes of a dependency. For instance, in your example, DF -> BC does not hold in any of R1, R2, R3, since the attributes DFBC are not all present in a single relation (we know that functional dependencies are meaningful only inside a relation).

This not necessarily means that the decomposition suffers from a “loss of dependency”, since the definition is more complex: A decomposition of a relation schema R with attributes A and cover of dependencies F preserves the dependencies if and only if the union of the projections of the dependencies of F over the decomposed relation is a cover of F.

In Ullman, Principles of Database Systems, Computer Science Press, 1983, an algorithm is shown to compute the closure of the union of the projection of a set of dependency over a decomposition. In your particular case, by applying that algorithm we can find that the dependency DF -> BC is actually lost.

(b) Is this decomposition in 3NF?

Here you answer is correct, since the third decomposed relation is not in 3NF. As you have correctly pointed out, the candidate keys for this relation are {AEF, AFH}, while in the relation the dependency AF -> B hold, and this is a dependency that violates the 3NF since AF is not a superkey and B is not a prime attribute.

Renzo
  • 26,848
  • 5
  • 49
  • 61