Database

Scenario-Based Question(s) 50 Marks Study the scenario and complete the question(s) that follow: Airline Management There is a report on poor management of an airline company in South Africa. As a results, the stakeholders decided that all information concerning the assets, operations, and the employees of the company should be organised with the use of a database management system. The database needs to store information about the Airplane (identified by planeid, name as planename, and cruise_range). Flights (identified by ftnum, place of departure as departfrom, destination as arriveto, total milage as tmilage, departure time as departtime, arrival time as arrivetime, and price). Pilots (identified by empid and planeid), and Staff (identified by empid, name as empname, and salary). The Staff relation consist of all the employees of the company including the pilots, and you have been hired to design and manage the database. Source: Okonkwo, C. (2021) Question 4 Write SQL queries for the following: 4.1 Determine the empid and maximum cruise_range of the airplane for each pilot who is qualified to fly more than three airplanes. (5 Marks) 4.2 Search for the names of pilots whose salaries are less than the cost of the cheapest flight from Johannesburg to Cape Town. (5 Marks) 4.3 Determine the identity (planeids) of all airplanes that can fly from Johannesburg to Cape Town. (5 Marks) 4.4 Display the name and salary of other employees (nonpilots) with salary higher than the average salary of the pilots. (10 Marks) [Sub Total 25 Marks] Question 5 Using the scenario from Question 4, write SQL queries to represent the following questions: 5.1 A passenger wishes to go from Johannesburg to Australia with only two flight transfers. If the customer wishes to get in Australia by 16:00, provide a selection of departure times from Johannesburg (15 Marks) 5.2 Display the names of personnel who are only qualified on airplanes with cruise range more than 1500 miles. (5 Marks) 5.3 Display the names of personnel who are only qualified on airplanes with cruise ranges more than 1500 miles, but on at least two of them. (5 Marks) [Sub Total 25 Marks]