Assignment

1. Specify the following queries on the database schema shown in Figure.1 below using the relational algebra operators. Also show the result of each query if applied to the database of Figure.2. (Use the symbol σ for SELECT, Π for PROJECT, ς for EQUIJOIN, * for NATURAL JOIN, and f for FUNCTION)

(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.

Figure. 1

Figure. 2

2. Suppose each of the following update operations is applied directly to the database of Figure.2. Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints.

(a) Insert < 'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd, Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.

(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.

(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT

(d) Insert < '677678989', null, '40.0' > into WORKS_ON.

(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT

(f) Delete the WORKS_ON tuples with ESSN= '333445555'.

(g) Delete the EMPLOYEE tuple with SSN= '987654321'

(h) Delete the PROJECT tuple with PNAME= 'ProductX'

(i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER= 5 to '123456789' and '01-OCT-88', respectively

(j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to '943775543'.

(k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' and PNO= 10 to '5.0'

3. Specify the following views in SQL on the COMPANY database schema:

(a) A view that has the department name, manager name, and manager salary for every department.

(b) A view that has the employee name, supervisor name, employee salary for each employee who works in the 'Research' department.

(c) A view that has project name, controlling department name, number of employees, and total hours worked per week on the project for each project.

(d) A view that has project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it.

*Note- Send the solution in MS Word along with snaposhot images where required