Classification (num_source_version - num_table - join_type)
-----------------
S means single
M means multiple
0 menas not applicable
T means temporl join only
N means non-temporal join only
B menas both types of joins
----------------------------
C1: S-S-0
C2: S-M-T
C3: S-M-N
C4: S-M-B
C5: M-S-0
C6: M-M-T
C7: M-M-N
C8: M-M-B
Two queries for each class: query name reflects its class (e.g. Q1A, Q1B belongs to Class 1, Q2A, Q2B belongs to Class 2)
Q1A. Class 1 S-S-0
---------------------
//Q1A. return the salary value of the emp 10004 on the year 2003 (Vs=V5)
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$r1 in $t1/row,
$c1 in $r1/salary,
$c2 in $r1/empno,
$c1ts in $c1/@ts,
$c1te in $c1/@te
where $c2="10004" and $c1ts < "20040101" and "20030101" < $c1te
return $c1;
Q1B. Class 1 S-S-0
---------------------
// Q1B. return the managers (of department d001) on the year 2003
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/dept,
$r1 in $t1/row,
$c1 in $r1/mgrno,
$c2 in $r1/deptno,
$c1ts in $c1/@ts,
$c1te in $c1/@te
where $c2 = "d001" and $c1ts < "20040101" and "20030101" < $c1te
return $c1;
Q2A. Class 2 S-M-T
---------------------
Find all managers that the employee 10004 worked with, on the year 2003.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$c1_1 in $r1/deptno,
$c1_2 in $r1/empno,
$c2_1 in $r2/deptno,
$c2_2 in $r2/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te
where $c1_2="10004" and $c1_1 = $c2_1 and $ts1_1 < $te2_2 and $ts2_2 < $te1_1 and
$ts1_1 < "20040101" and "20030101" < $te1_1 and
$ts2_2 < "20040101" and "20030101" < $te2_2
return $c2_2;
Q2B. Class 2 S-M-T
---------------------
Find all salary values of managers in all history, during the period of manager appointment.
(The periods of salary and manager appointment need to overlap)
Answer using data valid on the year 2003.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$c1_1 in $r1/salary,
$c1_2 in $r1/empno,
$c2_2 in $r2/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te
where $c1_2 = $c2_2 and $ts1_1 < $te2_2 and $ts2_2 < $te1_1 and
$ts1_1 < "20040101" and "20030101" < $te1_1 and
$ts2_2 < "20040101" and "20030101" < $te2_2
return $c1_1;
Q3A. Class 3 S-M-N
---------------------
Find the historical managers of department where the employee 10004 worked, in all history.
(The period of their appointments don't need to overlap.)
Answer using data valid on the year 2003.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$c1_1 in $r1/deptno,
$c1_2 in $r1/empno,
$c2_1 in $r2/deptno,
$c2_2 in $r2/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te
where $c1_2="10004" and $c1_1 = $c2_1 and
$ts1_1 < "20040101" and "20030101" < $te1_1 and
$ts2_2 < "20040101" and "20030101" < $te2_2
return $c2_2;
Q3B. Class 3 S-M-N
---------------------
Find all salary values of managers in all history.
(The periods of salary and manager appointment don't need to overlap)
Answer using data valid on the year 2003.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$c1_1 in $r1/salary,
$c1_2 in $r1/empno,
$c2_2 in $r2/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te
where $c1_2 = $c2_2 and
$ts1_1 < "20040101" and "20030101" < $te1_1 and
$ts2_2 < "20040101" and "20030101" < $te2_2
return $c1_1;
Q4A. Class 4 S-M-B
---------------------
For all managers that the employee 10004 with, find all the departments that the manager managed. (temporal join followed by non-temporal join)
(10004's and the manager's affiliation with a single department should overlap, but
the manager's manager position periods do not need to overlap, naturally.)
Answer using data valid on the year 2003.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$t3 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$r3 in $t3/row,
$c1_1 in $r1/deptno,
$c1_2 in $r1/empno,
$c2_1 in $r2/deptno,
$c2_2 in $r2/mgrno,
$c3_1 in $r3/deptno,
$c3_2 in $r3/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te,
$ts3_2 in $c3_2/@ts,
$te3_2 in $c3_2/@te
where $c1_2="10004" and $c1_1 = $c2_1 and $c2_2 = $c3_2 and $ts1_1 < $te2_2 and $ts2_2 < $ts1_1 and
$ts1_1 < "20040101" and "20030101" < $te1_1 and
$ts2_2 < "20040101" and "20030101" < $te2_2 and
$ts3_2 < "20040101" and "20030101" < $te3_2
return $c3_1;
Q4B. Class 4 S-M-B
---------------------
For all managers, find all managers in the department that he/she worked in. (two worked during the same period)
(non-temporal join follwed by temporal-join)
Answer using data valid on the year 2003.
for $db in document("test.xml")/db,
$t1 in $db/dept,
$t2 in $db/emp,
$t3 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$r3 in $t3/row,
$c1_1 in $r1/mgrno,
$c2_1 in $r2/empno,
$c2_2 in $r2/deptno,
$c3_1 in $r3/deptno,
$c3_2 in $r3/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te,
$ts3_2 in $c3_2/@ts,
$te3_2 in $c3_2/@te
where $c1_1 = $c2_1 and $c2_2 = $c3_1 and $ts2_2 < $te3_2 and $ts3_2 < $ts2_2 and
$ts1_1 < "20040101" and "20030101" < $te1_1 and
$ts2_2 < "20040101" and "20030101" < $te2_2 and
$ts3_2 < "20040101" and "20030101" < $te3_2
return $c3_2;
-------------------------------------------------------------------------------------------------
Note: all queries in Q9~Q16 is a relaxation of Q1~Q8, in terms of period.
Now we look use data V3,V4,V5.
Q5A. Class 5 M-S-0
---------------------
//Q9. return the salary values of the emp 10004 on 2003-01-01 or after
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$r1 in $t1/row,
$c1 in $r1/salary,
$c2 in $r1/empno,
$c1ts in $c1/@ts,
$c1te in $c1/@te
where $c2="10004" and $c1ts < "20060101" and "20030101" < $c1te
return $c1;
Q5B. Class 5 M-S-0
---------------------
// Q10. return the managers (of department d001) on 2003-01-01 or after
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/dept,
$r1 in $t1/row,
$c1 in $r1/mgrno,
$c2 in $r1/deptno,
$c1ts in $c1/@ts,
$c1te in $c1/@te
where $c2 = "d001" and $c1ts < "20060101" and "20030101" < $c1te
return $c1;
Q6A. Class 6 M-M-T
---------------------
Find all managers that employee 10004 worked with, with overlapping period. Answer using data valid on or after 2003-01-01.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$c1_1 in $r1/deptno,
$c1_2 in $r1/empno,
$c2_1 in $r2/deptno,
$c2_2 in $r2/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te
where $c1_2="10004" and $c1_1 = $c2_1 and $ts1_1 < $te2_2 and $ts2_2 < $te1_1 and
$ts1_1 < "20060101" and "20030101" < $te1_1 and
$ts2_2 < "20060101" and "20030101" < $te2_2
return $c2_2;
Q6B. Class 6 M-M-T
---------------------
Find all salary values of managers in all history.
(The periods of salary and manager appointment need to overlap)
Answer using data valid on or after the year 2003.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$c1_1 in $r1/salary,
$c1_2 in $r1/empno,
$c2_2 in $r2/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te
where $c1_2 = $c2_2 and $ts1_1 < $te2_2 and $ts2_2 < $te1_1 and
$ts1_1 < "20060101" and "20030101" < $te1_1 and
$ts2_2 < "20060101" and "20030101" < $te2_2
return $c1_1;
Q7A. Class 7 M-M-N
---------------------
Find the historical managers of department where the employee 10004 worked, in all history.
(The period of their appointments don't need to overlap.)
Answer using data valid on or after 2003-01-01.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$c1_1 in $r1/deptno,
$c1_2 in $r1/empno,
$c2_1 in $r2/deptno,
$c2_2 in $r2/mgrno,
$te1_1 in $c1_1/@te,
$te2_2 in $c2_2/@te
where $c1_2="10004" and $c1_1 = $c2_1 and
$ts1_1 < "20060101" and "20030101" < $te1_1 and
$ts2_2 < "20060101" and "20030101" < $te2_2
return $c2_2;
Q7B. Class 7 M-M-N
---------------------
Find all salary values of managers in all history.
(The periods of salary and manager appointment don't need to overlap)
Answer using data valid on or after the year 2003.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$c1_1 in $r1/salary,
$c1_2 in $r1/empno,
$c2_2 in $r2/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te
where $c1_2 = $c2_2 and
$ts1_1 < "20060101" and "20030101" < $te1_1 and
$ts2_2 < "20060101" and "20030101" < $te2_2
return $c1_1;
Q8A. Class 8 M-M-B
---------------------
For all managers that the employee 10004 with, find all the departments that the manager managed. (temporal join followed by non-temporal join)
(10004's and the manager's affiliation with a single department should overlap, but
the manager's manager position periods do not need to overlap, naturally.)
Answer using data valid on or after the year 2003.
XQuery XQ2 :=
for $db in document("test.xml")/db,
$t1 in $db/emp,
$t2 in $db/dept,
$t3 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$r3 in $t3/row,
$c1_1 in $r1/deptno,
$c1_2 in $r1/empno,
$c2_1 in $r2/deptno,
$c2_2 in $r2/mgrno,
$c3_1 in $r3/deptno,
$c3_2 in $r3/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te,
$te3_2 in $c3_2/@te
where $c1_2="10004" and $c1_1 = $c2_1 and $c2_2 = $c3_2 and $ts1_1 < $te2_2 and $ts2_2 < $ts1_1 and
$ts1_1 < "20060101" and "20030101" < $te1_1 and
$ts2_2 < "20060101" and "20030101" < $te2_2 and
$ts3_2 < "20060101" and "20030101" < $te3_2
return $c3_1;
Q8B. Class 8 M-M-B
---------------------
For all managers, find all managers in the department that he/she worked in. (two worked during the same period)
(non-temporal join follwed by temporal-join)
Answer using data valid on or after the year 2003.
for $db in document("test.xml")/db,
$t1 in $db/dept,
$t2 in $db/emp,
$t3 in $db/dept,
$r1 in $t1/row,
$r2 in $t2/row,
$r3 in $t3/row,
$c1_1 in $r1/mgrno,
$c2_1 in $r2/empno,
$c2_2 in $r2/deptno,
$c3_1 in $r3/deptno,
$c3_2 in $r3/mgrno,
$ts1_1 in $c1_1/@ts,
$te1_1 in $c1_1/@te,
$ts2_2 in $c2_2/@ts,
$te2_2 in $c2_2/@te,
$ts3_2 in $c3_2/@ts,
$te3_2 in $c3_2/@te,
where $c1_1 = $c2_1 and $c2_2 = $c3_1 and $ts2_2 < $te3_2 and $ts3_2 < $ts2_2 and
$ts1_1 < "20060101" and "20030101" < $te1_1 and
$ts2_2 < "20060101" and "20030101" < $te2_2 and
$ts3_2 < "20060101" and "20030101" < $te3_2
return $c3_2;