assignment(emp_id, project_no, hours)
dependent(emp_id, dep_name, dep_gender, dep_birthdate, dep_relationship)
dept(dept_no, name, mgr_id, mgr_startdate)
dept_location(loc_no, dept_no, location)
employee(emp_id, last_name, first_name, street_address, city, state, zip,
birthdate, salary, parking_space, gender, dept_no, supervisor_id)
project(project_no, name, location, dept_no)
Write relational algebra expressions for the queries and requests below.
It is preferred that you use a word processor to write solutions to this assignment, but handwritten submissions will be allowed for this assignment provided they are written legibly.
If you have difficulty producing the symbols for the operators in your word processor, you can use the full names of the operators or the following abbreviations:
| SYMBOL | OPERATOR |
|---|---|
| SE | SELECT |
| PR | PROJECT |
| U | UNION |
| - | SET-DIFFERENCE |
| X | CARTESIAN-PRODUCT |
| RN | RENAME |
| /\ | SET-INTERSECTION |
| NJ | NATURAL-JOIN |
| / | DIVISION |
| <-- | ASSIGNMENT |
| AG | AGGREGATE |
| LOJ | LEFT-OUTER-JOIN |
| ROJ | RIGHT-OUTER-JOIN |
| FOJ | FULL-OUTER-JOIN |
Here are the queries and requests (each is worth 5 points):
1. Find employee last names, first names and the names of their dependents who are not their spouses. ('SPOUSE' is one of the possible values of the dep_relationship attribute.)
2. Find the employee ids of all the employees who have a higher salary than that of their supervisor.
3. Find the total hours worked on each project. (Identify each project by its project number.)
4. Find the total hours worked on projects for each employee. (Identify each employee by his/her employee id number. The total hours for one employee is for all the projects that that employee worked on combined, not listed separately for each project.)
5. Find all the employees who work on more than two projects. (Identify the employees by their employee id number.)
6. Find all employees (identified by id number) who have a dependent with the same name as a dependent of their supervisor. The resulting relation should be a list of pairs, the first element of which is the id number of an employee, and the second element of which is the name of one of employee's dependents who has the same name as one of the dependents of the employee's supervisor.
7. Find the department(s) with the highest average salary of its employees. (Identify the departments by their department number.)
8. In the assignment relation, add 10 bonus hours to each project an employee is assigned to provided the employee is a department manager. (Employees who are not department managers do not get any bonus.)
9. Delete project 7 from the project and assignment tables.
10. Find the employees (identified by id number) who work on all projects. (Result should be a list of the ids of employees who do some work on all the projects, not just some of them.)