EECS 341 Intro to Databases Assignment #5 solution


Original Work


5/5 - (5 votes)

Consider the following design for an airline company database:
passenger(id, name, home_airport) FK home_airport references
ticket(passenger_id, flight_id, price) FKs to and
flight(id, number, departure_datetime, route_id) FK route_id references
route(id, departure_airport, arrival_airport, miles) FKs to
The following query displays all future flights for a given passenger, identified by the
placeholder “:PASS_ID”. Note that the “now()” function in the predicate returns the
current time.
select, f.departure_datetime,,
from passenger p, ticket t, flight f, route r, airport departure, airport arrival
where = t.passenger_id and = :PASS_ID and
t.flight_id = and
f.route_id = and
f.departure_datetime > now() and = r.arrival_airport and = r.departure_airport
(1) Write a naive (straightforward) expression of this query in relational algebra using only
cross joins, and applying predicate selections after the joins.
(2) Convert the expression from (1) into a parse tree.
(3) Apply equivalence rules and heuristic optimizations to create an optimized parse tree,
for example, by using theta joins and pushing down selections and projections.
(4) Suppose that on login we would like to show a passenger how many total flights they
have completed and how many miles they have traveled. A materialized view could be
used to precompute and cache this information so that information display on login is
fast. Write a definition for a view that computes this information for a passenger.