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.
$$\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
a. Design a multidimensional database using a star schema for a data warehouse for the Grand Travel Airlines business environment. The subject will be "reservation" which represents a particular passenger reservation on a particular flight. Be sure to keep track of the fare that the passenger paid for the flight and the date of the reservation.
b. Describe three OLAP uses of this data warehouse.
c. Describe one data mining use of this data warehouse.