Question:
Consider the data distribution of the COMPANY database, where the fragments at sites 2 and 3 are as shown in Figure 23.3 and the fragments at site 1 are as shown in Figure 3.6. For each of the following queries, show at least two strategies of decomposing and executing the query. Under what conditions would each of your strategies work well?
a. For each employee in department 5, retrieve the employee name and the names of the employee's dependents.
b. Print the names of all employees who work in department 5 but who work on some project not controlled by department 5.
Figure 23.3
Transcribed Image Text:
Figure 23.3
Complete and disjoint fragments of the WORKS ON relation. (a) Fragments of WORKS ON for employees
working in department 5 (C = [Essn in (SELECT Ssn FROM EMPLOYEE WHERE Dno = 5)). (b) Fragments of
WORKS ON for employees working in department 4 (C = [Essn in (SELECT Ssn FROM EMPLOYEE WHERE
Dno = 4). (c) Fragments of WORKS ON for employees working in department 1 (C= [Essn in (SELECT Ssn
FROM EMPLOYEE WHERE Dno = 1).
(a) Employees in Department 5
G1
G2
G3
Pno Hours
Pno Hours
10.0
Essn
Pno Hours
Essn
Essn
32.5
333445555 10
10.0
333445555 20
123456789
1
123456789
2
7.5
C2 = C and (Pno in (SELECT
Pnumber FROM PROJECT
C3 = C and (Pno in (SELECT
Pnumber FROM PROJECT
WHERE Dnum = 1))
666884444
40.0
453453453
1
20.0
WHERE Dnum = 4))
453453453
2
20.0
333445555
10.0
333445555
3
10.0
C1 =C and (Pno in (SELECT
Pnumber FROM PROJECT
WHERE Dnum = 5))
(b) Employees in Department 4
G4
G5
G6
Essn Pno Hours
C4 =C and (Pno in (SELECT
Pnumber FROM PROJECT
WHERE Dnum = 5))
Essn
Pno Hours
Essn
Pno Hours
987654321 20
999887777
30
30.0
15.0
999887777
10
10.0
C6 = C and (Pno in (SELECT
987987987
10
35.0
Pnumber FROM PROJECT
WHERE Dnum = 1))
5.0
20.0
987987987
30
987654321
30
C5 = C and (Pno in (SELECT
Pnumber FROM PROJECT
WHERE Dnum = 4))
(c) Employees in Department 1
G7
G8
G9
Essn Pno Hours
C7 =C and (Pno in (SELECT
Essn Pno Hours
Essn
Pno
Hours
888665555 20
Null
C8 = C and (Pno in (SELECT
Pnumber FROM PROJECT
WHERE Dnum = 4))
Pnumber FROM PROJECT
C9 = C and (Pno in (SELECT
WHERE Dnum = 5))
Pnumber FROM PROJECT
WHERE Dnum = 1))
%3D