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.

For each project, list the project name and the total hours per week (by all employees) spent on that project.

Retrieve the names of all employees who work on every project.

Retrieve the names and SSN of all employees who do not work on any project.

EMPLOYEE Fname Minit John Franklin Alicia J Jennifer S Ramesh Joyce A Ahmad V James E Lname Ssn Bdate Address Sex Salary Super_ssn Dno Smith 1234567891965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 8886655555 Zelaya 999887777 1968-01-19 3321 Castle, Spring, TXF 25000 987654321 4 Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 4 Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 English 453453453 1972-07-31 5631 Rice, Houston, TXO F 25000 3334455555 Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 9876543214 Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 DEPT_LOCATIONS Dnumber Dlocation Dnumber DEPARTMENT Dname Research Administration Headquarters Mgr_ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 4 4 - 5 Houston Stafford Bellaire Sugarland Houston 5 WORKS ON Essn Dnum Pnumber 1 Plocation Bellaire 5 123456789 123456789 PROJECT Pname ProductX ProductY Productz Computerization Reorganization Newbenefits 666884444 Pno Hours 32.5 7.5 40.0 20.0 20.0 12 10.0 10.0 10.0 2010.0 453453453 453453453 333445555 333445555 333445555 T2 Sugarland Houston Stafford Houston Stafford 10 20 30 4 11 4 DEPENDENT 333445555 Essn 999887777 30 10 999887777 30.0 10.0 35.0 5.0 987987987 30 Dependent_name Alice Theodore Joy Abner Michael Alice Elizabeth 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Sex Bdate F 1986-04-05 M 1983-10-25 F 1958-05-03 | 1942-02-28 M 1988-01-04 F 1988-12-30 FI 1967-05-05 987987987 Relationship Daughter Son Spouse Spouse Son Daughter Spouse 20.0 987654321 987654321 888665555 30 20 20 15.0 NULL EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT | Pname Pnumber Plocation | Dnum | WORKS_ON Essn Pno Hours DEPENDENT Essn Dependent_name Figure 5.5 Schema diagram for the COMPANY relational database schema. Sex Bdate Relationship