Database Systems: Lossless-join

Assalammualaikum...==))

Kepada kawan-kawan  yg penah tanya us pasal database and how to solve it. Jadi us share kan  kat blog ni. Hope kwn2 bole paham bcoz us rasa question ni antara paling mudah untuk kita faham and thinking .

Basic definition of Lossless-join: 

     A decomposition D = {R1, R2,.Rm} of R has the lossless  join property with respect to the set of dependencies F on R if, for every relation r of R that satisfies F, the following holds  *(pR1(r),., pRm(r)) = r, where * is the natural join of all the relations in D.The word loss in lossless refers to loss of information, not to loss of tuples. 

Question1:  Suppose that we decompose the schema R = (A,B,C,D,E) into(A,B,C) and (A, D, E) Show that this decomposition is a lossless-join decomposition if the following set of F of functional dependencies holds A -> BC,  CD -> E,  B -> D, E -> A

 R = (A,B,C,D,E) , F= A -> BC,  CD -> E,  B -> D, E -> A,  R1 (A,B,C) and R2(A, D, E) . The decomposition is lossless because the common attribute A is a key for R1 (and R2).This decomposition of R is a lossless-join if at least one of the following functional dependencies are in F+.

            R = (A,B,C,D,E) , R1 (A,B,C) and R2(A, D, E)  

FD1= A -> BC,

FD2= CD -> E,    

FD3= B -> D,      

FD4= E -> A,  

R1 Ç  R2 = A

R1 - R2 = BC

Check A-> BC in F, yes, therefore the decomposition to be lossless join. A functional dependency A-> BC is is non-trivial if YÇX = Æ  .  This dependencies not hold for all relation instances.


check CD -> E,in F ? NO
check B -> D,  in F ? NO

check E -> A,  in F ? NO

Lossless Check:

	A	B	C	D	E
ABC	a1	a2	a3	b14	b15
ADE	a1	b22	b23	a4	a5

Consider FD: A->BC

	A	B	C	D	E
ABC	a1	a2	a3	b14	b15
ADE	a1	a2	a3	a4	a5

Consider FD: CD->E

	A	B	C	D	E
ABC	a1	a2	a3	b14	a5
ADE	a1	a2	a3	a4	a5

Consider FD: B->D

	A	B	C	D	E
ABC	a1	a2	a3	a4	a5
ADE	a1	a2	a3	a4	a5

Consider FD: E->A

	A	B	C	D	E
ABC	a1	a2	a3	a4	a5
ADE	a1	a2	a3	a4	a5

Decomposition is lossless join,because A  row ends up with all a’s, (Yg bertanda merah dlm lajur A)