Homework 6: due Feb. 18th 11:59PM. [16 points) 8.16. Specify the following queries on the COMPANY relational database schema shown in Figure 55 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. a. b. C. Retrieve the names of all employees in department 4 who work more than 10 hours per week on the ProductX project. List the names of all employees who have a dependent with the same first name as themselves. Find the names of all employees who are directly supervised by "Jennifer Wallace 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. For each department, retrieve the department name and the highest salary of all employees working in that department. Retrieve the average salary of all female employees per department. e. h. [6 points) 8.22. Consider the two tables 71 and T2 shown in Figure 8.15. Show the results of the following operations a TIP -T2.4 T2 b. TI 11.Q-T2.872 a. TIT.P-12.4 12 d. Tlx 1.Q-T2.8 T2 e. TIU T2 f. TI (TIP-T2A AND TIR - 120 T2 5.2 Relational Model Constraints and Relational Database Schemas 161 Bdate Address Sex Salary Super_ssn Dno EMPLOYEE Fname Minit Lname Ssn DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pro Hours DEPENDENT Essn. Dependent_name Figure 5.5 Schema diagram for the COMPANY relational database schema Sex Bdate Relationship Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 8886655555 Alicia Zelaya 999887777 1968-01-19 3321 Castle, Spring. TXF 25000 9876543214 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 3334455555 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TXF 25000 3334455555 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 9876543214 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL DEPARTMENT DEPT LOCATIONS Dnumber Dlocation Dname Dnumber Houston Research Administration Headquarters Mar ssn Mgr start date 333445555 1988-05-22 9876543211 1995-01-01 888665555 1981-06-19 1 Stafford Bellaire Sugarland Houston 5 PROJECT Pnumber Plocation Dnum Hours 32.5 Pname ProductX Bellaire Sugarland 40.0 Houston 5 20.0 ProductY Productz Computerization Reorganization Newbenefits 3 10 20 30 20.0 Stafford Houston Stafford 10.0 100 WORKS ON Essn Pno 123456789 123456789 2 666884444 3 453453453 453453453 2 333445555 2 333445555 333445555 10 333445555 20 999887777 999887777 987987987 987987987 30 987654321 30 9876543211 20 88866555520 10.0 DEPENDENT 100 Essn 30 Dependent name Alice Theodore Sex F 30.0 100 M 333445555 333445555 333445555 987654321 Joy Bdate 1986-04-05 1983-10-25 1958-05-03 1942-02-28 1988-01-04 1988-12-30 1967-05-05 Relationship Daughter Son Spouse Spouse Son Daughter Spouse 35.0 5.0 20.0 15.0 NULL Abner M 123456789 123456789 123456789 Michael Alice Elizabeth M F F Figure 8.15 A database state for the relations T1 and 72. TABLE T1 PAR 10 a 5 15 18 25 a 6 TABLE T2 10 b 6 25 C3 10 b 5
