Consider the following business environment.
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. In addition, every flight has an actual departure time and an actual arrival time. Every passenger who has flown on Grand Travel has a unique passenger number plus their name, address, and telephone 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 name, date of birth, and date of hire. 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 model, manufacturer name, passenger capacity, and year built. 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.
Grand Travel also wants to maintain data about its airplanes’ maintenance history. A maintenance procedure has a unique procedure number, a procedure name, and the frequency with which it is to be performed on every airplane. A maintenance location has a unique location name, plus an address, telephone number, and manager. Grand Travel wants to keep track of which airplane had which maintenance procedure performed at which location. For each such event it wants to know the date of the event and the duration (Hints: The airplane, maintenance procedure and maintenance location are involved in a ternary relationship.)
(a) Draw an entity-relationship diagram that describes the above business environment. Underline the primary key in each entity. You don't need to include foreign key(s) in the entity.
(b) Design a multidimensional database by drawing 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.
(c) Describe one OLAP use of this data warehouse.
(d) Describe one data mining use of this data warehouse.