Question
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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started