Specify the following queries on the COMPANY relational database schema shown in Figure 5.5belowusing relational algebra expressions. Also show the result of each query as it would apply to the database state of Figure 5.6.
(a) Retrieve the names of employees that have at least one dependent.[5 marks]
(b) List the first names of employees who work in the Research department and whose salary is below 39000.[5 marks]
(c) Find the first names of male employees that are not supervised by Jennifer Wallace'.[5 marks]
(d) List the first names of all employees who work on the project Newbenefits and who have a daughter as a dependent.[5 marks]
(e) Retrieve the first names of employees who work on every project.[5 marks]
(f) Retrieve the first names of employees who do not work on any project.[7.5 marks]
(g) Retrieve the salary of all male employeesworking in the department with department number 4.[5 marks]
(h) Find the first names and addresses of employees who work on at least one project located in Houston but whose department has its location in either Sugarlandor Bellaire.[7.5 marks]
(i) List the last name of eachdepartment manager who hasa spouseas a dependent.[5 marks]
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 5 5 Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Frame Minit Lname San Bdato Address Sex Salary Super_ssn Dno John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 Franklin TWong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 Alicia Zolaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 075 Fire Oak, Humble, TX M 38000 333445555 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 Ahmad V Jabbar 987987987 1960-03-29 080 Dallas, Houston, TX M 25000 987654321 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 4 4 5 5 4 DEPARTMENT Dname Research Administration Headquarters 1 Dnumber 5 4 1 Mgr_ssn 333445555 987654321 888665555 Mgr_start date 1988-05-22 1995-01-01 1981-06-19 DEPT LOCATIONS Dnumber Dlocation Houston Stafford Bellaire Sugarland Houston 4 5 5 WORKS ON Essn 123456789 123456789 Dnum 5 5 PROJECT Pname ProductX ProductY Productz Computerization Reorganization Nowbenefits Pno Hours 1 32.5 2 75 3 40.0 1 20.0 2 20.0 2 10.0 666884444 Pnumber Plocation 1 Bellaire 2 Sugarland 3 Houston 10 Stafford 20 Houston 30 Stafford 5 4 453453453 453453453 1 333445555 4 3 10.0 333445555 333445555 10 10.0 333445555 20 10.0 30.0 999887777 999887777 987987987 30 10 10 10.0 35.0 5.0 DEPENDENT Essn 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent_name Sex Alice F Theodore M Joy F Abner M Michael M Alice F Elizabeth F Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse 987987987 30 987654321 30 20.0 987654321 20 15.0 888665555 20 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 5 5 Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Frame Minit Lname San Bdato Address Sex Salary Super_ssn Dno John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 Franklin TWong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 Alicia Zolaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 075 Fire Oak, Humble, TX M 38000 333445555 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 Ahmad V Jabbar 987987987 1960-03-29 080 Dallas, Houston, TX M 25000 987654321 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 4 4 5 5 4 DEPARTMENT Dname Research Administration Headquarters 1 Dnumber 5 4 1 Mgr_ssn 333445555 987654321 888665555 Mgr_start date 1988-05-22 1995-01-01 1981-06-19 DEPT LOCATIONS Dnumber Dlocation Houston Stafford Bellaire Sugarland Houston 4 5 5 WORKS ON Essn 123456789 123456789 Dnum 5 5 PROJECT Pname ProductX ProductY Productz Computerization Reorganization Nowbenefits Pno Hours 1 32.5 2 75 3 40.0 1 20.0 2 20.0 2 10.0 666884444 Pnumber Plocation 1 Bellaire 2 Sugarland 3 Houston 10 Stafford 20 Houston 30 Stafford 5 4 453453453 453453453 1 333445555 4 3 10.0 333445555 333445555 10 10.0 333445555 20 10.0 30.0 999887777 999887777 987987987 30 10 10 10.0 35.0 5.0 DEPENDENT Essn 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent_name Sex Alice F Theodore M Joy F Abner M Michael M Alice F Elizabeth F Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse 987987987 30 987654321 30 20.0 987654321 20 15.0 888665555 20 NULL
