CS 457 - Database Systems
Homework 4 Solution

Formulate a Relational Algebra Query on the Company database for the following:

1. Find the department name of departments that have some (at least one) employees who work on a project controlled by department 5 (5 is the dnumber)

2. Find first and last names of employees who only have female dependents

3. Find first & last names of employees who work at least 20 hours on the "ProductX" project

4. Find first & last names of employees who work more than 40 hours total

5. Find the name of the department(s) that pay the highest salary to a female employee (the female employee must work in that department)

6. Find the name of projects controlled by the "Research" department that are worked on by all employees in the "Research" department

Alternate solution:

7. Find the name of projects that is not worked on by any employee who has more than 2 dependents

8. For each department, find the department name and the total salary paid to the male and female employees in the department. (You may assume that dname is a secondary key in relation department)

The output relation has the following format:

 ``` DNumber Sex Sum -------------------------------------- 5 F .... 5 M .... 4 F .... (There is no row for departments with out employees of certain sex) ```

9. Find the fname & lname of employee(s) who work the highest total number of hours

10. Find the name of department(s) in which all employees in the department have (at least one) dependent

Notice that:

• Department(s) in which all employees in the department have (at least one) dependent =

 Department(s) in which no employees in the department have zero dependent

We can find the "department(s) in which no employees in the department have zero dependent" as follows:

 Department(s) in which no employees in the department have zero dependent = All departments − Department(s) in which some employees in the department have zero dependent

Solution 1:

Alternative solution:

 count the number of employees per department, and count the number of employees with dependents per department

If both counts are equal, then every employee in the department has at least one dependent:

Lives-in Database Query solutions (PDF files)