HW #2 – 8.17 – Relational Algebra

Description

homework Assignment #2: Textbook Exercise 8.17 (page 282, in 7th Edition)
Note: You only do Exercise 8.17 for this homework assignment #2.
8.17 Consider the AIRLINE relational database schema shown in Figure 5.8 (page 172, in 7th Edition), which describes a database in Exercise 5.12 (page 171, in 7th Edition).  Specify the following queries in relational algebra: a., b., c., d., and e.
a.     For each flight, list the flight number, the departure airport for the first leg of the flight, and the arrival airport for the last leg of the flight.
b.     List the flight numbers and weekdays of all flights or flight legs that depart from Houston Intercontinental Airport (airport code ‘iah’) and arrive in Los Angeles International Airport (airport code ‘lax’).
c.     List the flight number, departure airport code, scheduled departure time, arrival airport code, scheduled arrival time, and weekdays of all flights or flight legs that depart from some airport in the city of Houston and arrive at some airport in the city of Los Angeles.
d.     List all fare information for flight number ‘co197’.
e.     Retrieve the number of available seats for flight number ‘co197’ on ‘2009-10-09’.
Please be aware that you need to cover both flights and flight legs for b. and c.  In other words, “List the flight numbers and weekdays of all flights or flight legs…” might be better as: “List the flight numbers and weekdays of all flights AND flight legs…”
(Note: If you have an older edition or an international edition of the textbook, you may see different problems.  Please use the problem 8.17 above:
5.12 Consider the AIRLINE relation database schema shown in Figure 5.8, which describes a database for airline flight information. Each FLIGHT is identified by a Flight_number, and consists of one or more FLIGHT_LEGs with Leg_numbers 1, 2, 3, and so on. Each FLIGHT_LEG has scheduled arrival and departure times, airports, and one or more LEG_INSTANCEs–one for each Date on which the flight travels. FARES are kept for each FLIGHT. For each LEG_INSTANCE, SEAT_RESERVATIONs are kept, as are the AIRPLANE used on the leg, and the actual arrival and departure times and airports. An AIRPLANE is identified by an Airplane_id, and is of a particular AIRPLANE_TYPE. CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs at which they can land. An AIRPORT is identified by an Airport_code.
)