1. The data below are for an airline. Each flight can leave at any time from any airport and is identified by FlightID. Each flight is piloted by one and only one captain in an aircraft identified by an AirCraftID. Many passengers can fly on each flight and the same passenger may be on more than one flight. Note that one person can pilot more than one flight and more than one type of aircraft.

FlightID	Destination	cptID	CptName	AirCraftID	AirCraftType	NoSeats	PassID	PassName	SeatNo
100	NYC	Cpt10	Roger	A01	AirBus380F	853	P10	John	13B
100	NYC	Cpt10	Roger	A01	AirBus380F	853	P25	Jill	41C
100	NYC	Cpt10	Roger	A01	AirBus380F	853	P35	Jack	15E
200	Chicago	Cpt20	Renee	A02	Boeing 737	215	P40	Sue	22F
200	Chicago	Cpt20	Renee	A02	Boeing 737	215	P45	Sam	22G
200	Chicago	Cpt20	renee	A02	Boeing 737	215	P50	Sally	41A
300	San diego	Cpt30	Ralph	A03	Boeing 737	215	P60	Fred	12D
300	San diego	Cpt30	ralph	A03	Boeing 737	215	P35	Jack	9C
300	San diego	Cpt30	Ralph	A03	Boeing 737	215	P50	Sally	14D
400	Montreal	Cpt20	Renee	A01	AirBus380F	853	P60	Fred	81H
400	montreal	Cpt20	renee	A01	AirBus380F	853	P40	sue	92L

A. Identify the primary key and draw the dependency diagram?

B. Is the table in 2NF? If not, state why not and convert to 2NF. Clearly identify all keys.

C. Is the table(s) in 3NF? If not state why and convert to 3NF. Clearly identify all keys.

2. Draw an E-R model to represent the following entities associated with the airline from the previous question. Be sure to include the type of relationship, weather it is optional or mandatory, and show the cardinality. Make sure to convert any M-N relationships.