-1

Given these functional dependencies for 

R: {A,B,C,D,E,F}

AC->EF
E->CD
C->ADEF
BDF->ACD

I got this as the canonical cover:

E->C
C->ADEF
BF->C

And then broke it down to Boyce Codd Normal Form:

Relation 1: {C,A,D,E,F} 
Relation 2: {B,F,C}

I figured that this is lossless and dependency preserving? But is this true, since from the original functional dependencies BDF->ACD is no longer in any of my relations. But if I go from my calculated canonical cover then all my functional dependencies are preserved.

So that question is: Is this decomposition to BCNF dependency preserving?

Loqgar
  • 11
  • 1
  • 1
    BCNF is not in general dependency-preserving https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form#Achievability_of_BCNF – AntC Apr 19 '19 at 23:13
  • What exactly is your question? (Or questions--but please ask one per post unless they're very much related.) If you just want to know whether there might be no BCNF FD-preserving decomposition, ask that. Except it's trivially researched yet nevertheless a faq, so don't. Re "is this right": Show the steps of your work following your reference/textbook, with justification--we want to check your work but not redo it & we need your choices when an algorithm allows them & otherwise we can't tell you where you went wrong & right. See hits googling 'stackexchange homework'. – philipxy Apr 20 '19 at 01:12
  • Do you mean the original set of FDs is a cover for the relation? If so, say that. Otherwise, you can't find a canonical cover *for the relation* & CKs & decompositions. Also presumably you mean "a" canonical cover, since you didn't show that it's the only one. PS See why you should "Show the steps of your work following your reference/textbook, with justification"? If given FDs aren't a relation cover then you can't justify CKs & a decomposition. It's not just about us checking your work, it's about you making appropriate effort in asking. PS "go from my calculated canonical cover" is unclear. – philipxy Apr 20 '19 at 01:27
  • You added a question. It might be clear--if it's actually the question you mean. But you did not clarify what you already wrote. Adding to something unclear does not make it clear. Please act on my comments. You do not need to "Show the steps of your work following your reference/textbook, with justification" if you tell us to assume the result. But you need to show your reasoning that uses what is assumed. You almost do that here, but you're not clear. PS From your textbook, what do "FD cover" & "preserves FDs" mean? Where do you get stuck finding whether the definition applies? – philipxy Apr 20 '19 at 09:49

1 Answers1

1

A decomposition preserves the dependencies if and only if the union of the projection of the dependencies on the decomposed relations is a cover of the dependencies of the relation.

So, to know if a decomposition preserves or not the dependencies it is not sufficient to check if the dependencies of a particular cover have been preserved or not (for instance by looking if some decomposed relation has all the attributes of the dependency). For instance, in a relation R(ABC) with a cover F = {A→B, B→C, C→A} one could think that in the decomposition R1(AB) and R2(BC) the dependency C→A is not preserved. But if you project F on AB you obtain A→B, B→A, projecting it on BC you obtain B→C, C→B, so from their union you can derive also C→A.

The check is not simple, even if there exists polynomial algorithms that perform this task (for instance, one is described in J. Ullman, Principles of Database Systems, Computer Science Press, 1983).

Assuming the dependencies that you have given form a cover of the dependencies of the relation, the canonical cover that you have found is incorrect. In fact BF -> C cannot be derived from the original dependencies.

For this reason, your decomposition is not correct, since R2(BCF) is not in BCNF (actually, it is not in 2NF).

One possible canonical cover of R is the following:

BDF → C
C → A
C → E
C → F
E → C
E → D

Following the analysis algorithm, there are two possible decompositions in BCNF (according to the dependencies chosen for elimination). One is:

R1 = (ACDEF)
R2 = (BC)

while the other is:

R1 = (ACDEF)
R3 = (BE)

(note that BC and BE are candidate keys of the original relation, together with BDF).

A cover of the dependencies in R1 is:

C → A
C → E
C → F 
E → C
E → D

while both in R2 and R3 no non-trivial dependencies hold.

From this, we can conclude that both decompositions do not preserve the dependencies; for instance the following dependency (and all those derived from it) cannot be obtained:

BDF → C
Renzo
  • 26,848
  • 5
  • 49
  • 61
  • Only your last paragraphs address what they seem to be trying to ask, re a canonical cover's FDs being preserved but not those of an original cover. (It only matters that it was another cover, not that it was canonical.) The rest should be a PS. It would help to explain "projection of a FD on a relation". But their description of & reasoning for the peculiarity/contradiction they noticed is not clear. So the question should not be answered. The asker should clarify. Eg because people will guess at something that is not what the question asked, eg this answer. See my comments on the question. – philipxy Apr 20 '19 at 10:10
  • After your edit: 1. To show preservation it is sufficient to find a cover whose FDs are in components. Your writing is not clear in "it is not correct to check if the dependencies of a cover have been preserved or not". Phrasings with "not" in natural language frequently do not make clear what is being negated--you recently ran afoul of this elsewhere. If "a cover" means "a particular cover" then you are correct but the fact is that if there is some cover with FDs in components then FDs are preserved. 2. Please don't say "..." followed by "to be more explicit". Just rewrite to be clear. – philipxy Apr 20 '19 at 10:25
  • In an old Saturday Night Live skit with Ed Asner his character is the retiring manager of a mainland US nuclear power plant who ambiguously tells his co-workers "You can't put too much water in a nuclear reactor." From the beach in Hawaii he observes a day's-end glow just above the horizon ... in the east. – philipxy Apr 20 '19 at 10:50