Homework Assignment #$2$: Textbook Exercise $8\mathrm{.}17($page $282,$ in $7$th Edition$)$

Note: You only do Exercise $8\mathrm{.}17$ for this homework assignment #$2.$

$8\mathrm{.}17$ Consider the AIRLINE relational database schema shown in Figure $5\mathrm{.}8($page $172,$ in $7$th Edition$),$

which describes a database in Exercise $5\mathrm{.}12($page $171,$ in $7$th 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 $$co$197.$

e$.$ Retrieve the number of available seats for flight number $$co$197$ 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..."

The first three queries $($a$),($b$)$ and $($c$)$ require multiple steps. Please explain your steps to get desired

results $$ providing comments, describing what the upcoming statements are doing and$/$or your thought

process behind it$.$ Queries $($d$)$ and $($e$)$ are simple queries.

$($Note: If you have an older edition or an international edition of the textbook, you may see different

problems. Please use the problem $8\mathrm{.}17$ above. You do NOT need to do Exercise $5\mathrm{.}12.)$

$5\mathrm{.}12$ Consider the AIRLINE relation database schema shown in Figure $5\mathrm{.}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.