COS 425, Spring 2000: Problem Sets

COS 425, Spring 2000 - Problem Set 2

Due at 11am, Wednesday February 23, 2000.

Collaboration Policy

You may discuss problems with other students in the class. However, each student must write up his or her own solution to each problem independently. That is, while you may formulate the solutions to problems in collaboration with classmates, you must be able to articulate the solutions on your own.

1. (25 points) In Database Management Systems by Ramakrishnan and Gehrke, Chapter 4, exercise 4.4 pg. 117.

2. (25 points) For this problem we will use the following relational database (this database and some of the questions come from the recommended text Database System Concepts by Silberschatz, Korth and Sudarshan. )

relation employee with attributes (name, street, city)
relation company with attributes (co_name, city)
relation works with attributes (name, co_name, salary)
relation manages with attributes (employee_name, manager_name)

Express the following queries with relational algebra expressions. You may use any relational algebra operations, including joins and division.

Find the names and cities of residence of all employees who work for Summit Bank.
Find the names of all employees who work for Summit Bank and earn more than $100,000 per year.
Find all triples representing an employee's name, the employee's salary, and the salary of the employee's manager.
Find the names of all companies that do not have a location in Princeton.
Find the names of all employees who earn more than every employee of Fred's Pizza Co.
Assume companies may be located in several cities. Find the names of all companies located in every city in which Fred's Pizza Co. is located.

3. (25 points)Express the queries of Problem 2 in the tuple relational calculus.

4. (15 points)Express the queries of Problem 2 in the domain relational calculus.

5. (10 points)To prove that the tuple relational calculus is as expressive as the relational algebra, one must show how each relational algebra expression can be represented in the tuple relational calculus. In this problem you will give the correspondence for one relational algebra operation -- projection. Given the relation R, how does one represent in the tuple relational calculus the relation resulting from the projection of R on S, where S is a subset of the attributes of R, listed by name.