(I) Using ER Assist Tool to draw the ER Diagram for the Company Database. The Requirements were discussed in the class.
(II) First using Oracle SQL * Plus, create the schema of this database. Continue by inserting the data records as presented in the handout. Note: You should print out the result of final tables.
(III) Solve the following queries in SQL. For each query, you need to specify the SQL and show the result of each query if applied to the Company Database.
(a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
(b) List the names of employees who have a dependent with the same first name as themselves.
(c) Find the names of employees that are directly supervised by 'Franklin Wong'.
(d) For each project, list the project name and the total hours per week (by all employees) spent on that project.
(e) Retrieve the names of employees who work on every project.
(f) Retrieve the names of employees who do not work on any project.
(g) For each department, retrieve the department name, and the average salary of employees working in that department.
(h) Retrieve the average salary of all female employees.
(i) List the last names of department managers who have no dependents.
(j) Specify a view that has the employee name, supervisor name, employee salary for each employee who works in the 'Research department.