CSE 594

University of Washington

Autumn 1999

Homework 2

See the web page for some homework guidelines, due dates, and policies.

You will write relational algebra and SQL queries against a DB whose schema is described as follows in SQL. Assume all relations are in BCNF.

/* EMPLOYEE; foreign key referencing department is added later */

create table employee (fname char(10), minit char, lname char(10), ssn numeric primary key, bdate smalldatetime, address char(30), sex char, salary numeric, superssn numeric references employee (ssn) null, dno numeric null)

/* DEPARTMENT */

create table department (dname char(20), dnumber numeric primary key, mgrssn numeric references employee (ssn), mgrstartdate smalldatetime)

/* Foreign key from EMPLOYEE to DEPARTMENT */

alter table employee add foreign key (dno) references department (dnumber)

/* DEPT_LOCATIONS */

create table dept_locations (dnumber numeric references department (dnumber), dlocation char(15))

alter table dept_locations add primary key (dnumber, dlocation)

/* PROJECT */

create table project (pname char(20), pnumber numeric primary key, plocation char(15), dnum numeric)

alter table project add foreign key (dnum, plocation) references dept_locations (dnumber, dlocation)

/* WORKS_ON */

create table works_on (essn numeric references employee (ssn), pno numeric references project (pnumber), hours numeric(18,1))

alter table works_on add primary key (essn, pno)

/* DEPENDENT */

create table dependent (essn numeric references employee (ssn), dependent_name char(10), sex char, bdate smalldatetime, relationship char(10))

alter table dependent add primary key (essn, dependent_name)

  1. (50 points) Write the following queries in relational algebra. If you determine that a query cannot be expressed in the relational algebra, explain why, and write that query in SQL instead. If the query cannot be expressed in SQL either, explain why.


    1. Retrieve the last names of all employees who work more than 30 hours per week on some project.
    2. Retrieve the social security numbers of all employees who work more than 30 hours per week on some project.
    3. Retrieve the social security numbers of all employees having the largest number of dependents.
    4. Retrieve the full name and social security number of all employees born after their manager’s start date. Do not use the relational algebra selection operator (you may use join).
    5. Retrieve the full names of all employees who can tell the employee with social security number 123456789 what to do. I.e., that employee’s boss, that employee’s boss’s boss, etc.
    6. Retrieve just the last names of all employees who work more than 30 hours per week on some project but do not work less than 10 hours per week on any project.
    7. Retrieve the social security numbers of all employees making the lowest salary.
    8. Retrieve the average employee salary.


  2. (50 points) For this question, we highly encourage you to test your solutions out on a real system, e.g. SQL Server. SQL Server commands to create and populate this DB can be found here. You can, of course, use any relational product you like (including none), but you may have to make minor adjustments to the domain names, etc. Finally, beware of one thing: It is possible to write an SQL query that gives the correct answer (for these relation instances) but is not, in general, the right query.


    3. Write SQL queries for the following. You do not need to remove duplicates unless explicitly requested to do so. What you should turn in for each question is the SQL query and the result it produced. This can be handwritten or typeset. The query and the result for each question should be together (i.e., don’t give us all the queries then, separately, all the results). All these queries can be posed in SQL, and all return non-empty results from the sample instance provided.

    a. Retrieve all department locations (just the dlocation field), with duplicates removed.

    b. Retrieve the first name of every employee in department 5 who works more than 20 hours per week on a project named "ProductX".

    c. List the names of all employees who have a dependent with a different sex than the employee.

    d. Find the social security number, last name, and salary of all employees who are directly supervised by an employee whose first name is "Franklin" and last name is "Wong".

    e. For each project, list the project name and the total hours per week (by all employees) spent on that project. Your results should contain, for each project, its name and one number, the total hours spent by all employees of that project combined.

    f. Retrieve the last names of all employees who work on the maximum number of projects. Give them a few days off, they probably need it.

    g. For each department, retrieve the department number and the average salary of all employees working in that department.

    h. Retrieve the last name, salary, address, and sex of all employees who work on every project located in Houston.

    i. Find the names and addresses of all employees whose department has no location in Houston but who work on at least one project located in Houston.

    j. List the first and last names of all employees who have no supervisor and work on no projects.