Complete the Following:

1. Consider the data distribution of the Company database, where the whole database (shown in Figure 5.6) is stored at site 1, and the replicated fragments shown in Figure 23.2. Are stored at sites 2 and 3.

a. Write an SQL query to retrieve employee name and the names of the employees dependents for each employee in department 5.

b. Assume that the query in (a) is submitted at site 2.

I. Design at least two alternative query-processing strategies for executing the query.

II. For each strategy decompose the query (if applicable) into the corresponding subqueries.

III. For each strategy, calculate the amount of data transfer.

Data sizes for Employee and Department are given in Figure 23.4; assume that the Dependent table has 5,000 records, each records is 43 bytes long, Essen field is 9 bytes long, Dependent name field is 15 bytes long, and half of the dependents are related to employees working in department 5.

iv. Select the better option, justify your choice.

c. Repeat part (b) under assumption that the query is submitted at site 3.

d. Would data replication or different horizontal or vertical partitioning of the database allow you to create an even more efficient query-processing strategy? Why or why not?

FIGURE 5.6

Figure 23.2 (Site 1 & 2)

FIGURE 23.4

Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Fname Minit Lname Address Sex Salary Super_ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Franklin T Wong 3334455551955-12-08 638 Voss, Houston, TX M 40000 888665555 5 Alicia JZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F25000 987654321 4 JenniferS Wallace 987654321 1941-06-20 291 Berry, Bellaire, TXF 43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 AhmadV Jabbar 987987987 1969-03-29 980 Dallas, Houston, TXM 25000 987654321 4 James EBorg 888665555 1937-11-10 450 Stone, Houston, TX M55000 NULL DEPARTMENT DEPT LOCATIONS Dnumber DnumberDlocation 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 Research 5 Houstorn Stafford Bellaire Sugarland Houstorn Administration 4 5 5 WORKS ON PROJECT Pno Hours Pnumber Plocation 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 10.0 30.0 10.0 35.0 5.0 20.0 20 15.0 ProductY Productz Computerization10 Stafford Reorganization Sugarland Houston 4 20 Houstorn Stafford 4 DEPENDENT nt name Sex Bdate Relationship 30 333445555 333445555 333445555 Joy 987654321 123456789 123456789 123456789 F 1986-04-05Daughter M 1983-10-25 Son F 1958-05-03 Spouse M 1942-02-28 Spouse M | 1988-01-04 | Son F 1988-12-30 Daughter F 1967-05-05Spouse 30 30 Michael NULL Elizabeth Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Fname Minit Lname Address Sex Salary Super_ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Franklin T Wong 3334455551955-12-08 638 Voss, Houston, TX M 40000 888665555 5 Alicia JZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F25000 987654321 4 JenniferS Wallace 987654321 1941-06-20 291 Berry, Bellaire, TXF 43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 AhmadV Jabbar 987987987 1969-03-29 980 Dallas, Houston, TXM 25000 987654321 4 James EBorg 888665555 1937-11-10 450 Stone, Houston, TX M55000 NULL DEPARTMENT DEPT LOCATIONS Dnumber DnumberDlocation 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 Research 5 Houstorn Stafford Bellaire Sugarland Houstorn Administration 4 5 5 WORKS ON PROJECT Pno Hours Pnumber Plocation 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 10.0 30.0 10.0 35.0 5.0 20.0 20 15.0 ProductY Productz Computerization10 Stafford Reorganization Sugarland Houston 4 20 Houstorn Stafford 4 DEPENDENT nt name Sex Bdate Relationship 30 333445555 333445555 333445555 Joy 987654321 123456789 123456789 123456789 F 1986-04-05Daughter M 1983-10-25 Son F 1958-05-03 Spouse M 1942-02-28 Spouse M | 1988-01-04 | Son F 1988-12-30 Daughter F 1967-05-05Spouse 30 30 Michael NULL Elizabeth