Consider the Following Relational Database for Grand Travel Airlines PILOT Relation

question 37

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. Identify the candidate keys of each relation.
b. Identify the primary key and any alternate keys of each relation.
c. How many foreign keys does each relation have?
d. Identify the foreign keys of each relation.
e. Indicate any instances in which a foreign key serves as part of the primary key of the relation in which it is a foreign key. Why does each of those relations require a multi-attribute primary key?
f. Identify the relations that support many-to-many relationships, the primary keys of those relations, and any intersection data.
g. Using the informal relational command language described in this chapter, write commands to:
i. Retrieve the record for airplane number 36325.
ii. Retrieve the record for the pilot named Sarah Johnson who was born on 5/22/1959.
iii. List all of the airplanes manufactured by Boeing.
iv. List the airplane number and passenger capacity of every airplane manufactured by Boeing in 1997 .
v. List the airplane number, model, and manufacturer of every airplane.
vi. What company manufactured airplane number 53489?
vii. What was the name and date of birth of the pilot of flight number 182 on 10/30/2003?
viii. List the airplane number, model, and manufacturer of every airplane that was used on flight 118 in 2003.

---

Definitions:

Observational Learning

The process of learning behaviors by watching the actions of others, and the outcomes of those actions.

Instrumental Conditioning

A learning process through which the strength of a behavior is modified by reinforcement or punishment, also known as operant conditioning.

Acquisition

The process of learning or developing a new skill, behavior, or ability.

Contiguity

The condition of being in close association or proximity, often used in psychology to describe the closeness in time or space between two stimuli.