initial qgm. q1.cdept=‘mgmt’ and q1.grade>=q2.$1 took max took q1 q2 q3 select grade from...
Post on 19-Dec-2015
229 views
TRANSCRIPT
![Page 1: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/1.jpg)
Initial QGM
![Page 2: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/2.jpg)
Q1.CDept=‘mgmt’And
Q1.Grade>=Q2.$1
took
Max
took
Q1 Q2
Q3SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName
SELECTMax(Grade) AS GradeFROM Child Q4
Q4
SELECT Q1.SNameFROM took Q1, temp1 Q2WHERE Q1.CDept = ‘mgmt’ AND Q1.Grade >= Q2.$1
temp1SName,CName,CDept,Grade
Grade
CName,Grade
Grade
![Page 3: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/3.jpg)
Performing Feed for the topmost Box
![Page 4: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/4.jpg)
Q1.Grade>=Q2.$1
CDept=‘mgmt’
Max
Q1 Q2
supp
took
distinct
Magic_1
SELECT DISTINCT CName FROM supp
SELECT MAX (Grade) AS GradeFROM Child
SName,CName,CDept,Grade
SName,CName,Grade
SELECT Q1.SNameFROM took Q1, temp1 Q2WHERE Q1.Grade >= Q2.$1
temp1Q4
Grade
![Page 5: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/5.jpg)
Q1.Grade>=Q2.$1
CDept=‘mgmt’
Max
Q1 Q2
supp
took
distinct
Magic_1
DCOBox
SELECT Q4.cName, Q5.$1FROM Magic_1 Q4, temp1 Q5
Q3.CName=Q1.CNameCIBox
SELECT Q3.Grade FROM DCOBox Q3 WHERE Q3.CName = Q1.CName
Q4 Q5
Q3
CName
SELECT Q1.SNameFROM took Q1, CIBox Q2WHERE Q1.Grade >= Q2.$1
SName,CName,Grade
SName,CName,CDept,Grade
CName CName,
Grade
Grade
temp1 Q6
![Page 6: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/6.jpg)
Can merge the C1Box and the topmost box into one box as every tuple in the topmost box joins with atmost one tuple in the C1Box
![Page 7: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/7.jpg)
Q1.Grade>=Q2.GradeQ1.cName=Q2.cName
CDept=‘mgmt’
Max
Q1 Q2
supp
took
distinct
Magic_1
DCOBoxSELECT Q4.cName,Q5.$1FROM Magic_1 Q4, temp1 Q5
Q4 Q5
CName
SELECT Q1.sName FROM supp Q1, DCOBox Q2WHERE Q1.cName=Q2.cName
AND Q1.Grade>=Q2.Grade
temp1
SName,CName,CDept,Grade
SName,CName,Grade
CName
CName,
Grade
Grade
Q6
![Page 8: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/8.jpg)
Starting Point for the Aggregation Box
![Page 9: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/9.jpg)
Max
distinct
Magic_1
DCOBox Q4 Q5
Q4.CName=Q3.CNameQ3
took
CName, Grade
SELECT Q4.CName, Q5.$1 AS GradeFROM Magic_1 Q4, temp1 Q5
temp1CName
CName,Grade
Grade
Q6 SELECTMax(Grade) AS GradeFROM Child Q6
SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName
Grade
![Page 10: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/10.jpg)
Performing Feed for the aggregate Box
![Page 11: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/11.jpg)
Max
distinct
Magic_1
DCOBox Q4 Q5
Q1.CName=Q3.CNameQ3
took
CName, Grade
SELECT Q4.CName,Q5.$1From Magic_1 Q4, temp1 Q5
Q7.cName=Q4.cNameCIBox
Q7
DCOBox Q8 Q9
distinct
Magic_2
CName
CName
CName
CName, Grade
CName, Grade
Grade
Grade
temp1
SELECT Grade FROM DCOBox Q7 WHERE Q7.CName = Q4.CName
SELECTMax(Grade) AS GradeFROM Child Q6
Q6
Grade
SELECT Grade FROM took Q3 WHERE Q3.CName = Q1.CName
SELECT Q8.CName, Q9.Grade FROM Magic_2 Q8, Child Q9
![Page 12: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/12.jpg)
Performing Absorb for the aggregate Box – introduce the
Group By and the LOJ
![Page 13: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/13.jpg)
Max
distinct
Magic_1
DCOBox Q4 Q5
Q1.cName=Q3.cNameQ3
took
CName, Grade
CIBoxQ7
DCOBox Q8Q9
distinct
Magic_2
SELECT CName, MAX (Grade) AS GradeFROM Child Q6GROUPBY Q6.CName
Q6
CName
CName
CName
CName, Grade
CName, Grade
Grade
CName, Grade
CName, Grade
SELECT Q4.CName, Q5.Grade FROM Magic_1 Q4 LOJ temp1 Q5 ON CName
temp1
![Page 14: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/14.jpg)
The LOJ is unnecessary here, and the C1Box is also unnecessary.
![Page 15: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/15.jpg)
Max
Q1.CName=Q3.CNameQ3
took
DCOBox Q8 Q9
distinct
Magic_2
Q6
Q1.Grade>=Q2.GradeQ1.CName=Q2.CName
SELECT CName, MAX (Grade) AS GradeFROM Child Q6GROUPBY Q6.CName
CName, Grade
CName, Grade
CName, Grade
GradeCName
SELECT Q1.SNameFROM supp Q1, temp1 Q2WHERE Q1.CName = Q2.CName AND Q1.Grade >= Q2.Grade
Q2Q1
temp1
![Page 16: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/16.jpg)
Starting Point for the bottommost SPJ Box
![Page 17: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/17.jpg)
Q8.CName=Q3.CNameQ3
took
DCOBox Q8 Q9
distinct
Magic_2
CName
Grade
CName, Grade
CName, Grade
SELECT Grade FROM took Q3 WHERE Q3.CName = Q1.CName
SELECT Q8.CName, Q9.Grade FROM Magic_2 Q8, temp2 Q9
temp2
![Page 18: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/18.jpg)
Performing Absorb for the SPJ Box
![Page 19: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/19.jpg)
Q10.CName=Q3.CNameQ3
took
DCOBox Q8 Q9
distinct
Magic_2
Q10 SELECT Q3.CName, Q3.Grade FROM Magic_2 Q10, took Q3 WHERE Q3.CName = Q10.CName
SELECT Q9.CName, Q9.Grade FROM temp2 Q9
CName
CName
CName, Grade
CName, Grade
CName, Grade
temp2
![Page 20: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/20.jpg)
Remove unnecessary DCOBox
![Page 21: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/21.jpg)
Q10.cName=Q3.cNameQ3
took
distinct
Magic_2
Q10
CName, Grade
CName, GradeCName
SELECT Q3.CName, Q3.Grade FROM Magic_2 Q10, took Q3 WHERE Q3.CName = Q10.CName
![Page 22: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/22.jpg)
The Final QGM
![Page 23: Initial QGM. Q1.CDept=‘mgmt’ And Q1.Grade>=Q2.$1 took Max took Q1 Q2 Q3 SELECT Grade FROM took Q3 WHERE Q3.CName=Q1.CName SELECT Max(Grade) AS Grade FROM](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649d3e5503460f94a17a5c/html5/thumbnails/23.jpg)
Q10.CName=Q3.CNameQ3
took
distinct
Magic_2
Q10
CName, Grade
Max Q6
Q1.Grade>=Q2.GradeQ1.CName=Q2.CName
CDept=‘mgmt’
took
SELECT Q3.CName, Q3.Grade FROM Magic_2 Q10, took Q3 WHERE Q3.CName = Q10.CName
CName, GradeCName
CName
SELECT CName, MAX (Grade) AS GradeFROM Child Q6GROUPBY Q6.CName
SName,CName,CDept,Grade
SName,CName,Grade
CName, Grade
SELECT Q1.sName FROM supp Q1, temp1 Q2WHERE Q1.CName=Q2.CName AND Q1.Grade>=Q2.Grade
Q2Q1
temp1