------------------------------------------------------------------------
Total scores: 40
Fall 1998
Due : Tuesday, Oct. 27
------------------------------------------------------------------------
N.B. Choose one of the two questions marked as 4).

1.(16 points) Consider the following relational database:

    person(SIN number, pname, street, city, phone number)
    works-for(SIN number, cname, salary)
    company(cname, city)
    manages(mname, pname)

Answer each of the following queries in:

    * SQL

    * QBE

a. Find the names of all employees who work for the Victoria Company and live in the same cities as their managers.

b. Find all employees (their SIN numbers, names, cities of residence) who earn more than every employee of the Victoria Company.

c. Find those companies whose employees earn a higher salary ,on average, than the average salary at the  Victoria Company.

d. Assume that some employees may work for more than one company. Find the names ,addresses, phone numbers of the employees who work for more than two companies.

2.(12 points) Give expressions in QBE, Quel and Datalog equivalent to each of the following two queries:
 
 
 
 
 
 
 
 
 
 
 
 

3.(6 points)Consider the relational database of question 1. Write a Datalog program for each of the following queries:

a. Find all cities of residence of all employees who work for the Victoria Company.

b. Find all pairs of employees who have a manager in common.

4 .(6 points) SQL-92 defines the coalesce operation as follows:

coalesce(A1,A2,...,An) returns the nonnull Ai in the list A1,A2,...,An,

and returns null if all of A1,A2,...,An are null.

Let a and b be relations with the schemas A(name,adress,title) and  B(name,address,salary). Show how to express a natural full outer join b using the full outer join operation with an on condition and the coalesce operation. Make sure that the result relation does not contain two copies of the attributes name and address ,and the solution is correct even if some tuples in a and b have null values for attributes name or address.
 

4.(6 points) Consider the SQL query

select p.a1

from p,r1,r2

where p.a1=r1.a1 or p.a1=r2.a1

Under what conditions does the preceding query select values of p.a1
that are either in r1 or in r2? Examine carefully the cases where one of
r1 or r2 may be empty.