The following show the tables and sample data of La Trobe Airline. The PLANE and PILOT tables store the…

The following show the tables and sample data of La Trobe Airline. The PLANE and PILOT tables store the…

Question:

 

Transcribed Image Text:

The following show the tables and sample data of La Trobe Airline. The PLANE
and PILOT tables store the list of planes owned by the airline and the pilots working
there respectively. PASSENGER table stores the information of the passenger flying
with the airline. FLIGHT table stores information of the flights. This table includes
information about the pilot and plane used. CITY table identifies the city where the
airline flies to along with the airport information. PASSENGERFLIGHTS stores the
relationship between passenger and flights. These are the ONLY tables in the airline
database.
NOTE: Primary Key (PK) is underlined, Foreign Key (FK) is printed in italic, PK that
is also FK is underlined and printed in italic.
PLANE
SerialNo Capacity
ABC100
150
ABC200
ABC300
ABC400
ABC500
CITY
CityCode
MEL
ADL
CBR
SYD
PER
150
100
200
300
NextServiceDate
04/08/2022
City Name
Melbourne
Adelaide
Canberra
Sydney
Perth
05/09/2023
01/09/2023
03/08/2022
01/08/2022
State
VIC
SA
ACT
NSW
WA
PASSENGER
Name
Jamie
Roberts
Lily Chan
Duke Gold
Harry
Nguyen
Sonya
Tavlor
Airport Dese
Large
Medium
Medium
Large
Medium
Address
Bundoorn
Kew
Mill Park
Williamstown
Camberwell
PILOT
PilotID
P1
P1
12
12
Pa
PhoneNo
95695687
95821456
96582563
93569282
95869682
PilotName
Yuka Yamashita
Matt Green
Bernie Winston
Kay Malson
Cliff Marsden FLIGHT
Flight No
Date
LAT123
01/06/2022
LAT345 01/08/2022
LAT456 01/08/2022
LAT567 01/08/2022
PASSENGERFLIGHTS
Name
Jamie Roberts
Lily Chan
Duke Gold
Harry Nguyen
DepTime ArrTime Origin
09:00
10:15
MEL
10:30
ADL
11:00
11:30
FlightNo
LAT123
LAT123
LAT456
LAT567
12:15
12:20
MEL
MEL
Dest
SYD
PER
SYD
ADL
Pilot No
P1
12
P4
PS
Serial No
ABC100
ABC200
ABC 300
ABC400
Page 20 of 20
a) Write a stored procedure that receives a given date as input and prints the
details of the aircraft/plane scheduled for flights on that date and separately
prints those, which are not scheduled for a flight. The following show the tables and sample data of La Trobe Airline. The PLANE
and PILOT tables store the list of planes owned by the airline and the pilots working
there respectively. PASSENGER table stores the information of the passenger flying
with the airline. FLIGHT table stores information of the flights. This table includes
information about the pilot and plane used. CITY table identifies the city where the
airline flies to along with the airport information. PASSENGERFLIGHTS stores the
relationship between passenger and flights. These are the ONLY tables in the airline
database.
NOTE: Primary Key (PK) is underlined, Foreign Key (FK) is printed in italic, PK that
is also FK is underlined and printed in italic.
PLANE
SerialNo Capacity
ABC100
150
ABC200
ABC300
ABC400
ABC500
CITY
CityCode
MEL
ADL
CBR
SYD
PER
150
100
200
300
NextServiceDate
04/08/2022
City Name
Melbourne
Adelaide
Canberra
Sydney
Perth
05/09/2023
01/09/2023
03/08/2022
01/08/2022
State
VIC
SA
ACT
NSW
WA
PASSENGER
Name
Jamie
Roberts
Lily Chan
Duke Gold
Harry
Nguyen
Sonya
Tavlor
Airport Dese
Large
Medium
Medium
Large
Medium
Address
Bundoorn
Kew
Mill Park
Williamstown
Camberwell
PILOT
PilotID
P1
P1
12
12
Pa
PhoneNo
95695687
95821456
96582563
93569282
95869682
PilotName
Yuka Yamashita
Matt Green
Bernie Winston
Kay Malson
Cliff Marsden FLIGHT
Flight No
Date
LAT123
01/06/2022
LAT345 01/08/2022
LAT456 01/08/2022
LAT567 01/08/2022
PASSENGERFLIGHTS
Name
Jamie Roberts
Lily Chan
Duke Gold
Harry Nguyen
DepTime ArrTime Origin
09:00
10:15
MEL
10:30
ADL
11:00
11:30
FlightNo
LAT123
LAT123
LAT456
LAT567
12:15
12:20
MEL
MEL
Dest
SYD
PER
SYD
ADL
Pilot No
P1
12
P4
PS
Serial No
ABC100
ABC200
ABC 300
ABC400
Page 20 of 20
a) Write a stored procedure that receives a given date as input and prints the
details of the aircraft/plane scheduled for flights on that date and separately
prints those, which are not scheduled for a flight.

Expert Answer:

Answer rating: 100% (QA)

Unfortunately without the ability to interact with a live database I am unable to write and test an
View the full answer