Struggling with functional dependency practice questions

Question

I'm going over some practice questions.

F={ B→E, C→B, CD→E, ABE→C}

I tried to make a canonical cover -

Fc ={ B→E, C→B, D→E, AB→C}

Then finding candidate keys -

ADB, ADC

Is R in BCNF? I thought it isn't in BCNF if I do not do lossless and dependency preserving decomposition, so I decomposed so:

R1=(B,E) BCNF
R2=(C,B) BCNF
R3=(D,E) BCNF
R4=(A,B,C) BCNF
R5=(A,D,B) NF3

Alongside every decomposition I wrote what state I think it's in. Lastly I need to decompose R so that all relations are in BCNF, so that would mean decomposing R5 so that it's in BCNF, but I don't how to do that since I can't find D or A using functional dependencies.

Answer 1

Assuming that you have a relation R(A, B, C, D, E) and a cover of its functional dependencies F = { B→E, C→B, CD→E, ABE→C }, in the canonical cover the dependency D→E should be eliminated (it cannot be derived in any way). In CD→E the attribute E is extraneous, since C+ = { CBE }, and the dependency C→Eis redundant, since we have already C→B and B→E.

So a canonical cover is:

Fc={B→E, C→B, AB→C}

Since D does not appear in any dependency of Fc, it must be present in any candidate key (and you have correctly identified the only candidate keys, {ABD, ADC}).

So we have that the relation is not in BCNF (each dependency in the canonical cover has a determinant which is not a superkey).

You can decompose the relation in several ways in BNCF, depending on which order you consider the dependencies, but those decompositions are not dependency preserving. For instance, if you decompose R in:

R1 {A, C, D}
R2 {B, E}
R3 {B, C}

the dependency AB→C is lost. In particular, in this case does not exists a decomposition in BCNF such that all the original dependencies are preserved.

A 3NF decomposition that maintain both data and dependencies is the following:

R1 {A, B, C}
R2 {B, E}
R4 {A, C, D}