Question

First, come up with a relation with some functional dependencies. At least one of the functional dependencies violates BCNF and at least one other of the functional dependencies makes it not dependency-preserving. Now, do lossless-join decomposition into BCNF although it may not be dependency-preserving.

Second, come up with a relation with some functional dependencies that make it dependency-preserving but not lossless-join.

Explain every step.

Answer 1

some basic definitions:-

dependency preservation decomposition:-F

Relational schemas R with FD set F decomposed into relations R_1,R₂,R₃,...,R_n with FD sets F₁,F₂,F₃,...,F_n then

if [F₁UF₂UF₃U...UF_n] is equal to F then it is dependency preservation decomposition.

Lossless join decomposition:-

Relational schemas R with FD set F decomposed into relations R_1,R₂,R₃,...,R_n subrelations

if R₁ join R₂ join R₃....join R_n is equal to R then it is lossless join decomposition.

A very simple trick to check if lossless join had occured or not is check if two relations which are being joined have atleast one attribute in common and atleast in one relation that common attribute is key.

Now, a relation which is not in BCNF and when decomposed to form BCNF it can't be dependency preserved:-

Conside feloton RCA Br finsd we neco ine coneli date kays NoLoLy make i into bNE hydoing Lossless conopesition. CAC) den heone aibute in common notie dependeny presexsed

eleowy, L 1 s Tle penden의 a esewing beca se odl pps o R it is lost de comportam