Reference no: EM133694196

Database Fundamental

AIMS AND OBJECTIVES:

to perform queries on a relational database system using SQL;
Implement the following tasks using ORACLE Application Express (APEX).
Download the file NEASchema.sql from the LMS site and run it on APEX. This file contains all the CREATE and INSERT statements you will need for this assignment. Run the schema file on APEX following the same process we followed to run a schema during the lectures and labs.

The list of tables available for this assignment is the following:

MODEL(modelID, economySeats, buisnessSeats, firstClassSeats, cargoCapacity, fuelCapacity, length, wingspan) LOCATION(airportCode, country, address, phone) TICKET(ticketNum, luggageLimit, seatNum, classCode, medicalCondition, mealChoice, customerID, flightID)
ROUTE(routeID, description, arriveAirportCode, departAirportCode) AIRCRAFT(aircraftID, modelID)
STAFF(staffID, name, address, email, phone, passportNum) PILOT (staffID, prvHrsPilotExp)
FLIGHTATTENDANT (staffID)
CUSTOMER(customerID, name, address, country, email, phone, birthdate, passportNum)
FLIGHT(flightID, estDepartureDateTime, actDepartDateTime, actArriveDateTime, avgSpeed, avgHeight, estDuration, estFuel, captainStaffID, firstOfficerStaffID, routeID, aircraftID) ADDITIONAL_PILOT(staffID, flightID, activityCode, activityDesc) HOSTING(staffID, flightID)
PILOT_QUALIFICATION(qualification, staffID)

Task 1

Using the tables provided above, provide SQL select statements for the following queries. For some of the queries, we have provided some notes, which you may find helpful.

List the model ID, cargo capacity, and fuel capacity of all aircraft models in NEA database and order them in descending order based on their length.

Display the staffID, name, and address for all pilots in NEA who have a commercial pilot licence (CPL).
Note: A good place to start would be to investigate the data in PILOT_QUALIFICATION table.

Display the staffID, name, and address for all pilots in NEA who have attended a ‘Navigation Exercise' as part of their training flights.
Note: A good place to start would be to investigate the data in ADDITIONAL_PILOT table.

List the model ID, aircraft ID, total number of seats, destination country and actual flight duration (in hours) of every flight to have departed from NEX airport (excluding flights for which actual departure/arrival datetime has not been recorded yet).

Note: if you subtract two dates in Oracle, you get the difference in days which can also be a fraction. After that, you can multiply the result of the subtraction with 24 to get the hours between the two dates. You can play around with this type of queries - ‘select (date1-date2)*24 from table1' as a starting point.

The management want to check the trend in popularity of gluten free meal requests. For each flight in NEA database, list the number of vegan and gluten free (meal choice code ‘VG' in the data) meal preferences.

For each flight that has already departed (i.e., actual departure datatime is recorded), display the flight ID, actual departure date, actual departure time (in a separate column), and the number of days that have passed between now (system date) and the depature. The displayed table should have 4 columns.

List the model ID, length, wingspan, destination country and actual flight duration (in hours) of the longest (actual duration) flight to ever depart from NEX airport.
Note: You may want to use an inner query to calculate the maximum duration.

The management team wish to determine if an appropriate number of hosts have been assigned to each flight. For each departed flight display the flight number, number of tickets sold, and number of hosts assigned.
Note: You may want to ‘count' distinct things in a collection of things.

Display a table of all customer departures and arrivals from the airport with airport code 'NED'. For each row display the flight ID, customer ID, customer name, and the details of the preferred contact method. The preferred method is the customer's phone number for departures and email for arrivals. The displayed table should have exactly 4 columns. Order the result by flight number (first) and customer ID (second).