Grand Travel Airlines Has to Keep Track of Its Flight $\longrightarrow$

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.

Attributes
Pilot Number
Pilot Name
Date of Birth
Date of Hire
Flight Number
Date (of flight)
Departure Time
Arrival Time
Passenger Number
Passenger Name
Address
Telephone Number (of passenger)
Fare
Reservation Date
Serial Number
Model
Passenger Capacity
Year Built
Manufacturer
Procedure Number
Procedure Name
Frequency
Location Name
Address
Telephone Number (of maintenance location)
Manager
Date (of maintenance)
Duration
Functional Dependencies
Pilot Number $\longrightarrow$ Pilot Name
Pilot Number $\longrightarrow$ Date of Birth
Pilot Number $\longrightarrow$ Date of Hire
Flight Number, Date (of flight) $\longrightarrow$ Departure Time
Flight Number, Date (of flight) $\longrightarrow$ Arrival Time
Flight Number, Date (of flight) $\longrightarrow$ Pilot Number
Flight Number, Date (of flight) $\longrightarrow$ Serial Number
Passenger Number $\longrightarrow$ Passenger Name
Passenger Number $\longrightarrow$ Address (of passenger)
Passenger Number $\longrightarrow$ Telephone Number Flight Number, Date, Passenger Number $\longrightarrow$ Fare
Flight Number, Date, Passenger Number $\longrightarrow$ Reservation Date
Serial Number $\longrightarrow$ Model
Serial Number $\longrightarrow$ Passenger Capacity
Serial Number $\longrightarrow$ Year Built
Serial Number $\longrightarrow$ Manufacturer
Procedure Number $\longrightarrow$ Procedure Name
Procedure Number $\longrightarrow$ Frequency
Location Name $\longrightarrow$ Address (of maintenance location)
Location Name $\longrightarrow$ Telephone Number
Location Name $\longrightarrow$ Manager
Serial Number, Procedure Number, Location Name, Date $\longrightarrow$ Duration For each of the following tables, first write the table's current normal form (as 1NF, 2NF, or 3NF.) Then, for those tables that are currently in 1NF or 2NF, reconstruct them as well-structured 3NF tables. Primary key attributes are underlined. Do not assume any functional dependencies other than those shown.
a. Flight Number, Date (of flight), Passenger Number, Departure Time, Arrival Time, Passenger Name
b. Flight Number, Date (of flight), Passenger Number, Fare, Reservation Date
c. Flight Number, Date (of flight), Serial Number, Departure Time, Arrival Time, Model, Passenger Capacity
d. Pilot Number, Pilot Name, Date of Birth, Date of Hire
e. Serial Number, Procedure Number, Location Name, Date (of maintenance), Duration, Year Built, Manufacturer, Procedure Name, Frequency