Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Sex Address Frame...

   

Transcribed Image Text:

Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Sex Address Frame Minit Lhame John B Smith 1234567891965-01-00 731 Fondren, Houston, TX M 333445555 1955-12-08 638 Voss, Houston, TX M Frankdin T Wong Alicia F Jennifer S Ramesh K J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX Walace 987654321 1941-06-20 291 Berry, Bellaire, TX F Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M A English 453453453 1972-07-31 5631 Rice, Houston, TX M Joyon Ahmad V Jabbar 987987987 1060-03-29 980 Dallas, Houston, TX 888665555 1937-11-10 450 Stone, Houston, TX James E Borg DEPARTMENT Dame Research Administration Headquarters Dnumber 5 4 1 WORKS ON Esen Poo 123456789 1 123456789 2 #88884444 453463453 453463450 333445550 333445660 333445555 333445555 Hours 32.5 7.5 3 40.0 1 20.0 2 20.0 2 10.0 3 10.0 10 10:0 28829 San 20 10.0 999887777 999887777 907987987 987987987 987654321 967654321 20 888065556 20 30.0 10 10.0 10 35.0 5.0 30 30 20.0 15.0 NULL Bdate Mgr sen 333445555 987654321 888665555 Mgr start date 1988-05-22 1995-01-01 1981-06-19 DEPENDENT Esen 333445555 333445556 333445555 987654321 123456789 123456789 123456789 PROJECT Pname ProductX Product Product Computerization Reorganization Newbenefits Alice Theodore F Joy Abner Michael Alice Elizabeth Dependent name Sex F M F M 55000 NULL M M Salary Super_san De 30000 333445555 5 40000 888665555 5. F 25000 987654321 43000 888665555 F 5 38000 333445555 25000 333445555 25000 987654321 4 5 1 DEPT LOCATIONS Dnumber 1 Pnumber 1 Bellaire 2 Sugarland Houston 3 10 Stafford 20 Houston 30 Stafford 4 5 5 5 Diocation Houston Stafford Bellaire Sugarland Houston Plocation 4 4 Drum 5 5 5 1983-10-25 Son 1958-05-03 1942-02-28 Bdate Relationship 1986-04-05 Daughter 4 1 4 1988-01-04 Son 1988-12-30 1967-05-05 Spouse Spouse Daughter Spouse A. (15 points) You need to implement the database using MySQL RDBMS or any Open-source RDBMS of your choice. Examples of Open-source free RDBMS are SQLcl and SQL Developer Command Line, PostgreSQL, etc. B. (5 points) Populate the database with the tuples given in Figure 5.6 on page 162 of the Elmarsi textbook. C. (30 points) after the database is defined and populated, you then need to perform the following queries: 1. List the names of all male employees with two or more dependents 2. Find the names of all employees who are directly supervised by the employee whose SSN is '888665555' 3. For each project, list the project name and the total hours per week (by all employees) spent on that project. 4. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees 5. List the names of all employees who work on more than two projects 6. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Sex Address Frame Minit Lhame John B Smith 1234567891965-01-00 731 Fondren, Houston, TX M 333445555 1955-12-08 638 Voss, Houston, TX M Frankdin T Wong Alicia F Jennifer S Ramesh K J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX Walace 987654321 1941-06-20 291 Berry, Bellaire, TX F Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M A English 453453453 1972-07-31 5631 Rice, Houston, TX M Joyon Ahmad V Jabbar 987987987 1060-03-29 980 Dallas, Houston, TX 888665555 1937-11-10 450 Stone, Houston, TX James E Borg DEPARTMENT Dame Research Administration Headquarters Dnumber 5 4 1 WORKS ON Esen Poo 123456789 1 123456789 2 #88884444 453463453 453463450 333445550 333445660 333445555 333445555 Hours 32.5 7.5 3 40.0 1 20.0 2 20.0 2 10.0 3 10.0 10 10:0 28829 San 20 10.0 999887777 999887777 907987987 987987987 987654321 967654321 20 888065556 20 30.0 10 10.0 10 35.0 5.0 30 30 20.0 15.0 NULL Bdate Mgr sen 333445555 987654321 888665555 Mgr start date 1988-05-22 1995-01-01 1981-06-19 DEPENDENT Esen 333445555 333445556 333445555 987654321 123456789 123456789 123456789 PROJECT Pname ProductX Product Product Computerization Reorganization Newbenefits Alice Theodore F Joy Abner Michael Alice Elizabeth Dependent name Sex F M F M 55000 NULL M M Salary Super_san De 30000 333445555 5 40000 888665555 5. F 25000 987654321 43000 888665555 F 5 38000 333445555 25000 333445555 25000 987654321 4 5 1 DEPT LOCATIONS Dnumber 1 Pnumber 1 Bellaire 2 Sugarland Houston 3 10 Stafford 20 Houston 30 Stafford 4 5 5 5 Diocation Houston Stafford Bellaire Sugarland Houston Plocation 4 4 Drum 5 5 5 1983-10-25 Son 1958-05-03 1942-02-28 Bdate Relationship 1986-04-05 Daughter 4 1 4 1988-01-04 Son 1988-12-30 1967-05-05 Spouse Spouse Daughter Spouse A. (15 points) You need to implement the database using MySQL RDBMS or any Open-source RDBMS of your choice. Examples of Open-source free RDBMS are SQLcl and SQL Developer Command Line, PostgreSQL, etc. B. (5 points) Populate the database with the tuples given in Figure 5.6 on page 162 of the Elmarsi textbook. C. (30 points) after the database is defined and populated, you then need to perform the following queries: 1. List the names of all male employees with two or more dependents 2. Find the names of all employees who are directly supervised by the employee whose SSN is '888665555' 3. For each project, list the project name and the total hours per week (by all employees) spent on that project. 4. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees 5. List the names of all employees who work on more than two projects 6. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.

Answer rating: 100% (QA)

Query SELECT FnameLnameCOUNTPno AS NoofProjects FROM workson department employee WHERE worksonESS... View the full answer