Air Cargo is an aviation company that provides air transportation services for passengers and freight. Air Cargo uses its aircraft to provide different services with the help of partnerships or alliances with other airlines.The company wants to prepare reports on regular passengers, busiest routes, ticket sales details, and other scenarios to improve the ease of travel and booking for customers.
You, as a DBA expert, need to focus on identifying the regular customers to provide offers, analyze the busiest route which helps to increase the number of aircraft required and prepare an analysis to determine the ticket sales details. This will ensure that the company improves its operability and becomes more customer-centric and a favorable choice for air travel.
Note: You must download the dataset from the course resource section in the LMS and create the tables to perform the above objective.
1.Customer: Contains the information of customers
customer_id – ID of the customer first_name – First name of the customer last_name – Last name of the customer date_of_birth – Date of birth of the customer gender – Gender of the customer
2.passengers_on_flights: Contains information about the travel details
aircraft_id – ID of each aircraft in a brand route_id – Route ID of from and to location customer_id – ID of the customer depart – Departure place from the airport arrival – Arrival place in the airport seat_num – Unique seat number for each passenger class_id – ID of travel class travel_date – Travel date of each passenger flight_num – Specific flight number for each route
3.ticket_details: Contains information about the ticket details
p_date – Ticket purchase date customer_id – ID of the customer aircraft_id – ID of each aircraft in a brand class_id – ID of travel class no_of_tickets – Number of tickets purchased a_code – Code of each airport price_per_ticket – Price of a ticket brand – Aviation service provider for each aircraft
4.routes: Contains information about the route details
Route_id – Route ID of from and to location Flight_num – Specific fight number for each route Origin_airport – Departure location Destination_airport – Arrival location Aircraft_id – ID of each aircraft in a brand Distance_miles – Distance between departure and arrival location