EMPLOYEL Frame Minit Crane San BADOS Sairy Super San Do DEPARTMENT Drame Dnumber Mort de DEPT LOCATIONS Drumbet.com PROJECT Pramo Pumber Pocation Onun WORKS ON Enh PedHours DEPENDENT Esen Dependent name Figure 5.5 Schedam for the COMPANY relation database schema Reco Figure 5.6 One posible database for the COMPANY rational schem EMPLOYEE FM Bdute Serwono BS 123456700 1085-01-03 od Hostin, TX 30000303645656 Franti T Wong 303445 1966-1900 HTXM40000000665666 5 Ali ZG81777 1068-01-10 321 ComingTXF25000087654321 4 S We 9878501941-08 20291 BTX F43000 SB666555 4 Rush kay 0688.446 1907-08-15 0.75 ml, TX M 58000 56445556 Joyce A English 458655458107201-31 01 MTE200000044666 Ahmad V Jober 7-03-10 DHTX M 2500007064321 E B 1927-11-10450 STX M 60000 NULL 1 DEPARTMENT DEPT LOCATIONS number Deca 1 Heard Adriation 187654321 1998 01-03 5 5 Belle Sugarland Houston WORKS ON 5 PROJECT Poor Dum 1 2 3 1 Hoon 325 25 400 200 Sugarland Houston 5 123456780 123456700 6684444 453453453 453453453 333445556 333445556 335445555 Product Pod Pod Coro Region News 1 2 a 10 20 30 2 2 200 10.0 Houston Stafford 4 11 3 10.0 10 100 10.0 DEPENDENT 20 30 10 10 30.0 100 35.0 5.0 20.0 16.0 Alice Theodore Joy 900837777 999887797 987997007 007907907 907054321 087854321 8205555 F M F 239645055 300 45000 33046556 987654321 123456700 1234567 123456700 M Scho 1986-04-05 Daug 1989-10-26 Son 1968-05-05 Spouse 1942-02-20 Spo 1988-01-04 1000-1200 Daugh 1967-05-05 Spouse 30 M 20 Alice " F NULL F Specify the following queries on the COMPANY relational database schema shown in Figure 5.5 using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state in Figure 5.6. Retrieve the names of all empleveein-department 5 who work more than 10 hours per week on the Product-projeco b. List the names of all employees who have a dependent with the same first name as themselves. e. Find the names of all employees who are directly supervised by Franklin Wong's d. For each project, list the project name and the total hours per week (by all employees) spent on that project. e. Retrieve the names of all employees who work on every project. f. Retrieve the names of all employees who do not work on any project. g. For each department, retrieve the department name and the average salary of all employees working in that department. h. Retrieve the average salary of all female employees. i. Find the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston. EMPLOYEL Frame Minit Crane San BADOS Sairy Super San Do DEPARTMENT Drame Dnumber Mort de DEPT LOCATIONS Drumbet.com PROJECT Pramo Pumber Pocation Onun WORKS ON Enh PedHours DEPENDENT Esen Dependent name Figure 5.5 Schedam for the COMPANY relation database schema Reco Figure 5.6 One posible database for the COMPANY rational schem EMPLOYEE FM Bdute Serwono BS 123456700 1085-01-03 od Hostin, TX 30000303645656 Franti T Wong 303445 1966-1900 HTXM40000000665666 5 Ali ZG81777 1068-01-10 321 ComingTXF25000087654321 4 S We 9878501941-08 20291 BTX F43000 SB666555 4 Rush kay 0688.446 1907-08-15 0.75 ml, TX M 58000 56445556 Joyce A English 458655458107201-31 01 MTE200000044666 Ahmad V Jober 7-03-10 DHTX M 2500007064321 E B 1927-11-10450 STX M 60000 NULL 1 DEPARTMENT DEPT LOCATIONS number Deca 1 Heard Adriation 187654321 1998 01-03 5 5 Belle Sugarland Houston WORKS ON 5 PROJECT Poor Dum 1 2 3 1 Hoon 325 25 400 200 Sugarland Houston 5 123456780 123456700 6684444 453453453 453453453 333445556 333445556 335445555 Product Pod Pod Coro Region News 1 2 a 10 20 30 2 2 200 10.0 Houston Stafford 4 11 3 10.0 10 100 10.0 DEPENDENT 20 30 10 10 30.0 100 35.0 5.0 20.0 16.0 Alice Theodore Joy 900837777 999887797 987997007 007907907 907054321 087854321 8205555 F M F 239645055 300 45000 33046556 987654321 123456700 1234567 123456700 M Scho 1986-04-05 Daug 1989-10-26 Son 1968-05-05 Spouse 1942-02-20 Spo 1988-01-04 1000-1200 Daugh 1967-05-05 Spouse 30 M 20 Alice " F NULL F Specify the following queries on the COMPANY relational database schema shown in Figure 5.5 using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state in Figure 5.6. Retrieve the names of all empleveein-department 5 who work more than 10 hours per week on the Product-projeco b. List the names of all employees who have a dependent with the same first name as themselves. e. Find the names of all employees who are directly supervised by Franklin Wong's d. For each project, list the project name and the total hours per week (by all employees) spent on that project. e. Retrieve the names of all employees who work on every project. f. Retrieve the names of all employees who do not work on any project. g. For each department, retrieve the department name and the average salary of all employees working in that department. h. Retrieve the average salary of all female employees. i. Find the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston