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

 

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. 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.

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