Database Management Systems Assignment
Consider the Company Database given in the handout. This Database contains 6 relations, namely: EMPLOYEE, DEPARTM ENT, DEPT_LOCATI ON, WORKS ON, PROJECT and DEPENDENT. Each table is defined in the handout.
(I) Using any two available ER Tools to draw the ER Diagram for the Company Database. The Requirements were discussed in the class. Write two-page report to discuss your comparative results.
(II) First using Orade SQL * Plus create the schema of this database. You need to check the database referential integrity to decide the order to create tables. Then follow the Orade syntax to create tables, please see Create-Tables-Notes-Company-DB at the end of this notes for your reference.
Continue by inserting the data records as presented in the handout. You should print out the result of final tables
If you want to learn "I nsert Multiple Records", please see an example at the end of this notes for your reference.
(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) Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston.
(j) List the last names of department managers who have no dependents
(IV) Solve the following queries in SQL. For each query, you need to specify the SQL using the concept of nested queries and show the result of each query i f applied to the Company Database.
(k) Retrieve the names of al I employees who work i n the department that has the employee with the highest salary among all employees,
(l) Retrieve the names of all employees who supervisor's supervisor has '888665555' for ssn.
(m) Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company.
(V) Specify the following queries in (III) (a), (b), (c), (e), (f), (i), (j) on the Company relational database schema using the Relation Algebra Statements (i.e., the relational operators). Also, show the intermediate result of each query if applied to the Company Database.
(VI) Specify the following queries in (III) (a), (b), (c), (e), (f), (i), (j) on the Company relational database schema in both tuple and domain relational calculus.
Attachment:- Database-Assignment.pdf