lossless join
TRANSCRIPT
-
8/9/2019 Lossless Join
1/18
Jan. 2012 Yangjun Chen 1
Lossless-join
Outline: Lossless-joinChapter 15 3r! e!. "Chap. 15 # th$ 5th e!.% Chap. 1&$ &th e!.'
()asi* !e+inition o+ Lossless-join
(, a ples
(/esting algorith
-
8/9/2019 Lossless Join
2/18
Jan. 2012 Yangjun Chen 2
Lossless-join
()asi* !e+inition o+ Lossless-join
A !e*o position 1$ 2$...$ 4 o+ has the lossless join propert 6ith respe*t to the set o+ !epen!en*ies 7 on i+$+or e8er relation r o+ that satis+ies 7$ the +ollo6ing hol!s$
"1
"r'$ ...$ "r'' r$
6here is the natural join o+ all the relations in .
/he 6or! loss in lossless re+ers to loss of information $ not toloss o+ tuples.
-
8/9/2019 Lossless Join
3/18
Jan. 2012 Yangjun Chen 3
Lossless-join
(, a ple: !e*o posion-1
SS ;< hours , A
-
8/9/2019 Lossless Join
4/18
Jan. 2012 Yangjun Chen #
Lossless-join
(, a ple: !e*o position-2
SS ;< hours , A
-
8/9/2019 Lossless Join
5/18
Jan. 2012 Yangjun Chen 5
Lossless-join
(!e*o posion-1
A1SS A2, A21
>31
>12
>22
>32
>13
>23
>33
>1#
>2#
>3#
>15
>25
>35
>1&
>2&
>3&
1
2
3
a1
>21
a1
a2
>22
>32
>13
a3
a3
>1#
a#
>3#
>15
a5
>35
>1&
>2&
a&
1
2
3
-
8/9/2019 Lossless Join
6/18
Jan. 2012 Yangjun Chen &
Lossless-join
a1
>21
a1
a2
>22
a2
>13
a3
a3
>1#
a#
>3#
>15
a5
>35
>1&
>2&
a&
1
2
3
a1
>21
a1
a2
>22
a2
>13
a3
a3
>1#
a#
a#
>15
a5
a5
>1&
>2&
a&
1
2
3
SS , A
-
8/9/2019 Lossless Join
7/18Jan. 2012 Yangjun Chen ?
Lossless-join
(!e*o position-2
A1SS A2, A21
>12
>22
>13
>23
>1#
>2#
>15
>25
>1&
>2&
1
2
>11
a1
a2
>22
>13
a3
>1#
a#
a5
a5
>1&
a&
1
2
/he atri *an not >e *hange!@
SS , A
-
8/9/2019 Lossless Join
8/18Jan. 2012 Yangjun Chen
Lossless-join
a1 a2 >13 >1# >15 >1& >21 >22 a3 a# a5 a&
a1 a2 a3 a# a5 a&
,< B OJ
h D
a1 a2
>21 >22
1 >13 >1# >15a3 a# a5
a1 >13 >1& >21 a3 >2&a1 a3 a&
2
3
e*o position-1:
-
8/9/2019 Lossless Join
9/18Jan. 2012 Yangjun Chen 9
Lossless-join
1 3 13
h D
e*o position-1:
a1 a2 >13 >1&
a1 a2 a3 a&
>21 >22 a3 >2&
13 2
a1 a2 >13 >1# >15 >1&
>21 >22 a3 a# a5 a&
a1 a2 a3 a# a5 a&
-
8/9/2019 Lossless Join
10/18Jan. 2012 Yangjun Chen 10
Lossless-join
>11 a2 >13 >1# a5 >1&a1 >22 a3 a# a5 a&
,< B OJ
h D
a2 a5
>22 a5
1
>11 >13 >1# a5 >1&
a1 a3 a# a5 a&
2
e*o position-2:
-
8/9/2019 Lossless Join
11/18Jan. 2012 Yangjun Chen 11
Lossless-join
h D
e*o position-2:
1 2
>11 a2 >13 >1# a5 >1&a1 a2 a3 a# a5 a&
>11 >22 >13 >1# a5 >1&
a1 >22 a3 a# a5 a&Spurious tuples
-
8/9/2019 Lossless Join
12/18Jan. 2012 Yangjun Chen 12
Lossless-join
stu!entBno *ourseBno instrBno
Instructors teach onecourse only
Student takes a courseand has one instructor
stu!entBno$ *ourse4 instrBnoinstrBno *ourseBno
Stu!ent-*ourse-instru*tor:
-
8/9/2019 Lossless Join
13/18Jan. 2012 Yangjun Chen 13
Lossless-join
stu!entBno *ourseBno instrBno
stu!entBno instrBno
CourseBno instrBno1
2
A1stu-no
A2*ourse-no
A3instr-no
>11
>21
>12
>22
>13
>23
1
2
A1stu-no
A2*ourse-no
A3instr-no
>11
a1
a2
>22
a3
a3
1
2
>11
a1
a2
a2
a3
a3
1
2
stu!entBno$ *ourse4 instrBnoinstrBno *ourseBno
-
8/9/2019 Lossless Join
14/18Jan. 2012 Yangjun Chen 1#
Lossless-join
stu!entBno *ourseBno instrBno
stu!entBno *ourseBno
CourseBno instrBno1
2
A1stu-no
A2*ourse-no
A3instr-no
>11
>21
>12
>22
>13
>23
1
2
A1stu-no
A2*ourse-no
A3instr-no
>11
a1
a2
a2
a3
>23
1
2
>11
a1
a2
a2
a3
>23
1
2
instrBno *ourseBno
stu!entBno$ *ourse4 instrBnoinstrBno *ourseBno
-
8/9/2019 Lossless Join
15/18Jan. 2012 Yangjun Chen 15
Lossless-join
stu!entBno *ourseBno instrBno
stu!entBno *ourseBno
stu!entBno instrBno1
2
A1stu-no
A2*ourse-no
A3instr-no
>11
>21
>12
>22
>13
>23
1
2
A1stu-no
A2*ourse-no
A3instr-no
a1
a1
>12
a2
a3
>23
1
2
a1
a1
>12
a2
a3
>23
1
2
stu!entBno$ *ourse4 instrBnoinstrBno *ourseBno
-
8/9/2019 Lossless Join
16/18Jan. 2012 Yangjun Chen 1&
Lossless-join
Testing algorithminput: A relation $ a !e*o position 1$ 2$...$ 4 o+ $ an!
a set 7 o+ +un*tion !epen!en*ies.
1. Create an initial atri S 6ith one ro6 i +or ea*h relation i in$ an! one *olu n j +or ea*h attri>ute A j in .
2. Set S" i, j ' : > ij +or all atri entries.3. For ea*h ro6 i representing relation s*he a i Do
for ea*h *olu n j representing A j doif relation i in*lu!es attri>ute A j then
set S" i, j ' : a j%4
#. epeat the +ollo6ing loop until a *o plete loop e e*ution resultsin no *hanges to S.
-
8/9/2019 Lossless Join
17/18Jan. 2012 Yangjun Chen 1?
Lossless-join
#. epeat the +ollo6ing loop until a *o plete loop e e*ution resultsin no *hanges to S.
for ea*h +un*tion !epen!en* E Y in 7 dofor all ro6s in S 6hi*h ha8e the sa e s >ols in the
*olu ns *orrespon!ing to attri>utes in E doaFe the s >ols in ea*h *olu n that *orrespon! to
an attri>ute in Y >e the sa e in all these ro6s as +ollo6s:i+ an o+ the ro6s has an GaH s >ol +or the *olu n$set the other ro6s to the sa e GaH s >ol in the *olu n.=+ no GaH s >ol e ists +or the attri>ute in an o+ thero6s$ *hoose one o+ the G>H s >ols that appear in oneo+ the ro6s +or the attri>ute an! set the other ro6s tothat sa e G>H s >ol in the *olu n%44
5. =+ a ro6 is a!e up entirel o+ GaH s >ols$ then the !e*o po-sition has the lossless join propert % other6ise it !oes not.
-
8/9/2019 Lossless Join
18/18Jan 2012 Yangjun Chen 1
Lossless-join
a1
>21
a1
a2
>22
>32
>13
a3
a3
>1#
a#
>3#
>15
a5
>35
>1&
>2&
a&
a2 >21a1
>22 >32
1ISS $ , A15a3 a# a5a3 >3# >35
a1 >13 >1& >21 a3 >2&a1 a3 a&
3ISS $ ;3#$ >35$ a1$ a3$ a&
;< A