Consider the Following Relational Database for Grand Travel Airlines PILOT Relation

Essay

Consider the following relational database for Grand Travel Airlines.
Grand Travel Airlines has to keep track of its flight and airplane history. A flight is uniquely identified by the combination of a flight number and a date. Every passenger who has flown on Grand Travel has a unique passenger number. For a particular passenger who has taken a particular flight, the company wants to keep track of the fare that she paid for it and the date that she made the reservation for it. Clearly, a passenger may have taken many flights (he must have taken at least one to be in the database) and every flight has had many passengers on it.
A pilot is identified by a unique pilot (or employee) number. A flight on a particular date has exactly one pilot. Each pilot has typically flown many flights but a pilot may be new to the company, is in training, and has not flown any flights, yet. Each airplane has a unique serial number. A flight on a particular date used one airplane. Each airplane has flown on many flights and dates, but a new airplane may not have been used at all, yet.
News flash!!! Grand Travel Airlines has decided to reconfigure its database as a distributed database among its four "hub" airport locations: Boston (which is also its headquarters city), Seattle, Nashville, and Columbus, Ohio.
Distributed and replicated among these four locations, the tables have the following characteristics:
Basically, all of these tables are accessed by all four locations on a fairly equal basis, except that processing at the Boston headquarters tends to be heavier than at the other three hubs. Replication is used primarily for availability purposes in case one of the sites crashes and the replicated copies are dispersed to avoid creating a bottleneck at any one site.
$$\begin{array} { | l | l | l | l |} \hline \text { Pilot } & \text { Pilot } & \text { Date of } & \text { Date of } \\\text { Number } & \text { Narne } & \text { Birth } & \text { Hire } \\\hline\end{array}$$ PILOT Relation
$$\begin{array} { | l | l | l | l | l | l | } \hline \text { Flight } & & \text { Departure } & \text { Arrival } & \text { Pilot } & \text { Airplane } \\\text { Number } & \text { Date } & \text { Time } & \text { Time } & \text { Number } & \text { Number } \\\hline\end{array}$$ FLIGHT Relation
$$\begin{array} { | l | l | l | l | } \hline \text { Passenger } & \text { Passenger } & & \text { Telephone } \\\text { Number } & \text { Name } & \text { Address } & \text { Number } \\\hline\end{array}$$ PASSENGER Relation
$$\begin{array} { | l | l | l | l | l | } \hline \text { Flight } & & \text { Passenger } & & \text { Reservation } \\\text { Number } & \text { Date } & \text { Number } & \text { Fare } & \text { Date }\\\hline\end{array}$$ RESERVATION Relation
$$\begin{array} { | l | l | l | l | l | } \hline \text { Airplarne } & & \text { Passenger } & \text { Year } & \\\text { Number } & \text { Model } & \text { Capacity } & \text { Built } & \text { Marufacturer } \\\hline\end{array}$$ AIRPLANE Relation
PILOT consists of 2,000 records and is located in Boston and Nashville.
FLIGHT consists of 1,500 records and is located in Columbus and Seattle.
PASSENGER consists of 250,000 records and is located in Columbus and Seattle.
RESERVATION consists of 1,500,000 and is located in Boston and Nashville.
AIRPLANE consists of 450 records and is located only at headquarters in Boston.
Assume that the telecommunications costs among the cities are all about the same.
Develop and justify a plan for solving the following queries.
a. A query is issued from Nashville to list the names and addresses of the passengers who have reservations for a particular flight on a particular date one month from now.
b. A query is issued from Nashville to get a list of the serial number, model, and passenger capacity of the airplanes used on a particular flight (a flight that operates daily) during the last two months.
c. A query is issued from Boston to get a list of the serial number, model, and passenger capacity of the airplanes used on a particular flight (a flight that operates daily) during the last two months.