EMPLOYEE FNAME MINIT John Franklin Alicia Alicia Jennifer Ramesh DEPARTMENT WORKS ON Joyce Ahmad James DEPENDENT...

Transcribed Image Text:

EMPLOYEE FNAME MINIT John Franklin Alicia Alicia Jennifer Ramesh DEPARTMENT WORKS ON Joyce Ahmad James DEPENDENT ESSN 123456789 123456789 666884444 453453453 453453453 453453453 433430490 333445555 300-440000 B T DNAME Research Administration Headquarters 333445555 JOH10000 333445555 COOHOOO 333445555 000110000 999887777 cocodr 999887777 90000mm 987987987 987987987 987654321 987654321 E ESSN 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Narayan English Jabbar Borg 2 3 3 1 4 2 4 2 3 2 LNAME Smith Wong Zelaya Wallace PNO HOURS 32.5 10 10 20 cu 30 SU 10 10 30 30 20 7.5 40.0 40.0 20.0 20.0 20.0 DNUMBER MGRSSN 5 333445555 4 987654321 1 888665555 10.0 10:0 10.0 10:0 SSN 123456789 333445555 10.0 Loke 10.0 100 30.0 10.0 35.0 5.0 20.0 15.0 999887777 999887777 987654321 666884444 1968-07-19 1941-06-20 1962-09-15 1972-07-31 987987987 1969-03-29 888665555 1937-11-10 453453453 BDATE 1965-01-09 1955-12-08 Alice Theodore Joy Abner Michael Alice Elizabeth PROJECT DEPENDENT NAME SEX F M F M M F F ADDRESS 731 Fondren, Houston, TX 638 Voss, Houston, TX 3321 Castle, Spring, TX 291 Berry, Bellaire, TX 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 980 Dallas, Houston, TX 450 Stone, Houston, TX DEPT LOCATIONS MGRSTARTDATE 1988-05-22 1995-01-01 1981-06-19 BDATE 1986-04-05 1983-10-25 1958-05-03 1942-02-28 1988-01-04 1988-12-30 1967-05-05 PNAME ProductX ProductY ProductZ Computerization Reorganization Newbenefits RELATIONSHIP DAUGHTER SON SPOUSE SPOUSE SON DAUGHTER SPOUSE SEX M L F F M M PNUMBER 1 2 3 10 20 30 SALARY 30000 40000 25000 43000 38000 25000 25000 55000 DNUMBER 1 4 5 5 SUPERSSN DNO 333445555 5 888665555 5 4 987654321 888665555 4 333445555 333445555 987654321 mull DLOCATION Houston Stafford Bellaire Sugarland Houston PLOCATION Bellaire Sugarland Houston Stafford Houston Stafford DNUM 5 5 4 1 4 4 1 Specify the following queries on the database schema shown in above Figure using the relational algebra discussed in class. This is not asking you to write SQL queries. 1. Retrieve the names of employees who work on the 'ProductZ' project. 2. Find the names of employees that works for 'Administration' department 3. Retrieve the names of department managers whose department controls 'ProductZ' project. 4. Retrieve the names of employees who do not work on any project. (An employee does not work on any project if his/her ssn is not in works_on) 5. Find the names and addresses of employees who work on at least one project located in Stafford. 6. List the last names of female employees who have no dependents. 7. Find the average salary of all female employees who works for 'Administration' department 8. For each department, list the department name and the total number of project controlled by the department. 9. Retrieve the names of employees who work on every project controlled by department 5 10. For each employee, retrieve employee's name, and the total hours working in projects EMPLOYEE FNAME MINIT John Franklin Alicia Alicia Jennifer Ramesh DEPARTMENT WORKS ON Joyce Ahmad James DEPENDENT ESSN 123456789 123456789 666884444 453453453 453453453 453453453 433430490 333445555 300-440000 B T DNAME Research Administration Headquarters 333445555 JOH10000 333445555 COOHOOO 333445555 000110000 999887777 cocodr 999887777 90000mm 987987987 987987987 987654321 987654321 E ESSN 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Narayan English Jabbar Borg 2 3 3 1 4 2 4 2 3 2 LNAME Smith Wong Zelaya Wallace PNO HOURS 32.5 10 10 20 cu 30 SU 10 10 30 30 20 7.5 40.0 40.0 20.0 20.0 20.0 DNUMBER MGRSSN 5 333445555 4 987654321 1 888665555 10.0 10:0 10.0 10:0 SSN 123456789 333445555 10.0 Loke 10.0 100 30.0 10.0 35.0 5.0 20.0 15.0 999887777 999887777 987654321 666884444 1968-07-19 1941-06-20 1962-09-15 1972-07-31 987987987 1969-03-29 888665555 1937-11-10 453453453 BDATE 1965-01-09 1955-12-08 Alice Theodore Joy Abner Michael Alice Elizabeth PROJECT DEPENDENT NAME SEX F M F M M F F ADDRESS 731 Fondren, Houston, TX 638 Voss, Houston, TX 3321 Castle, Spring, TX 291 Berry, Bellaire, TX 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 980 Dallas, Houston, TX 450 Stone, Houston, TX DEPT LOCATIONS MGRSTARTDATE 1988-05-22 1995-01-01 1981-06-19 BDATE 1986-04-05 1983-10-25 1958-05-03 1942-02-28 1988-01-04 1988-12-30 1967-05-05 PNAME ProductX ProductY ProductZ Computerization Reorganization Newbenefits RELATIONSHIP DAUGHTER SON SPOUSE SPOUSE SON DAUGHTER SPOUSE SEX M L F F M M PNUMBER 1 2 3 10 20 30 SALARY 30000 40000 25000 43000 38000 25000 25000 55000 DNUMBER 1 4 5 5 SUPERSSN DNO 333445555 5 888665555 5 4 987654321 888665555 4 333445555 333445555 987654321 mull DLOCATION Houston Stafford Bellaire Sugarland Houston PLOCATION Bellaire Sugarland Houston Stafford Houston Stafford DNUM 5 5 4 1 4 4 1 Specify the following queries on the database schema shown in above Figure using the relational algebra discussed in class. This is not asking you to write SQL queries. 1. Retrieve the names of employees who work on the 'ProductZ' project. 2. Find the names of employees that works for 'Administration' department 3. Retrieve the names of department managers whose department controls 'ProductZ' project. 4. Retrieve the names of employees who do not work on any project. (An employee does not work on any project if his/her ssn is not in works_on) 5. Find the names and addresses of employees who work on at least one project located in Stafford. 6. List the last names of female employees who have no dependents. 7. Find the average salary of all female employees who works for 'Administration' department 8. For each department, list the department name and the total number of project controlled by the department. 9. Retrieve the names of employees who work on every project controlled by department 5 10. For each employee, retrieve employee's name, and the total hours working in projects

Answer rating: 100% (QA)

9 RELATIONAL ALGEBRA QUERY dnum COUNTpnumber EXPLANATION Her... View the full answer