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:

    Answer:



  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:


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



    Solution 1:



    Alternative solution:

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




Lives-in Database Query solutions (PDF files)

  1. Query 1: click here
  2. Query 2: click here
  3. Query 3: click here
  4. Query 4: click here
  5. Query 5: click here
  6. Query 6: click here
  7. Query 7: click here
  8. Query 8: click here
  9. Query 9: click here
  10. Query 10: click here