CPS 116 Fall 2004
Supplemental Problem Set #1
(Not due; for use as optional additional exercises)
Problem 1.
An airline database contains the following tables:
• Flight (flno, from_city, to_city, distance, departs, arrives, price)
• Aircraft (aid, aname, cruising_range)
• Employee (eid, ename, salary)
• Certified (eid, aid)
The Employee table describes pilots as well as other types of employees. Employees who are
certified to operate on some aircraft are considered pilots. Run
/home/dbcourse/examples/db-flights/setup.sh to setup a database with some
sample data. For the SQL database schema, please refer to the file create.sql in the same
directory. Write SQL statements to answer the following queries.
(a) Find the names of aircraft such that all pilots certified to operate them earn more than
$80,000.
(b) Find the names of pilots whose salary is less than the price of the cheapest direct flight
from Los Angeles to Honolulu.
(c) Find the names of pilots certified for some Boeing aircraft.
(d) Find the aid’s of all aircraft that can be used to fly from Los Angeles to Chicago.
(e) A customer wants to travel from Madison to New York with no more than two
changes of flight. List the choice of departures from Madison if the customer wants to
arrive at New York by 6pm.
(f) Print the name and salary of every non-pilot whose salary is more than the average
salary for pilots.
(g) Print the names of employees who are certified only for aircrafts with cruising range
longer than 1000 miles.
Problem 2.
Which queries in Problem 2 cannot be formulated in basic relational algebra? For relational
algebra, assume that selection and join conditions may use built-in SQL predicates on strings,
times, etc., but no SQL aggregation functions are allowed.
Problem 3.
Consider a relation R (A, B, C, D) with FD’s AB→C, C→D, and D→A.
(a) Show that {A, B} is a key of R (remember a key has to be minimal).
(b) What are the other keys of R? (Hint: B must be in every key of R; why?)
(c) D→A is a BNCF violation. Using this violation, we decompose R into R1 (A, D) and
R2 (B, C, D). What are the keys of R1?
