1. The data below are for an airline. Each flight can leave at any time from any
ID: 3674002 • Letter: 1
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 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.
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
Explanation / Answer
Primary keys are FID, CPTID, AirCraftID and PassID,
After 2nd Normal form the tables divided are
FID, DEST, CPTID, AIRCRAFTID, NO_OF_SEATS
CPTID, CPTNAME
AIRCRAFTID, AIRCRAFT_NAME
PASSID, PASSNAME
FID, PASSID, SEATNO
From above relation there is no other transitive dependecy then
above tables are in 3NF
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.