Consider the following relation
AIRLINE TABLE
Flight Date | Flight ID | Origin | Destination | Captain ID | Captain Name | Number of Passengers on the Flight |
April 10 | 111 | Chicago | L.A. | 111 | Tracy | 110 |
April 10 | 222 | L.A. | Phoenix | 222 | Matt | 105 |
April 12 | 111 | Chicago | L.A. | 444 | Tim | 98 |
April 12 | 333 | L.A. | N.Y. | 111 | Tracy | 75 |
April 12 | 444 | L.A. | N.Y. | 333 | Pat | 110 |
April 12 | 555 | L.A. | Dallas | 444 | Tim | 111 |
April 12 | 222 | L.A. | Phoenix | 555 | Pat | 107 |
Some of the requirements that this table is based on are asfollows:
- The AIRLINE is flying number of flights on various routesdaily.
- A flight with a particular Flight Id alwaysflies on the same route (same Origin and sameDestination).
- Every captain has a unique Captain Id, and anon-unique Captain Name.
- For each instance of a flight (a particular flight on aparticular Flight Date) we keep track of who wasthe captain of that instance of a flight and how many passengerswere on board (Number of Passengers on theFlight).
- A captain can fly multiple flights during the same day.
Normalize the relation to the second normal form (2NF).
Normalize the relation to the third normal form (3NF).
