CSE2DBF 2019

Assignment 2 (20%)

Due date: 10.00am Wednesday, May 29th 2019

AIMS AND OBJECTIVES:

to perform queries on a relational database system using SQL;

to demonstrate an advanced knowledge of stored procedures, stored functions and triggers.

This is an individual Assignment. You are not permitted to work as a group when writing this

assignment.

Copying, Plagiarism: Plagiarism is the submission of somebody else’s work in a manner that gives

the impression that the work is your own. The Department of Computer Science and Information

Technology treats plagiarism very seriously. When it is detected, penalties are strictly imposed.

Students are referred to the Department of Computer Science and Information Technology’s

Handbook and policy documents with regard to plagiarism and assignment return, and also to the

section of ‘Academic Integrity’ on the subject learning guide.

代写CSE2DBF作业、代做database system作业、SQL课程作业代写、SQL编程语言作业调试

No extensions will be given: Penalties are applied to late assignments (5% of total assignment mark

given is deducted per day, accepted up to 5 days after the due date only). If there are circumstances

that prevent the assignment being submitted on time, an application for special consideration may be

made. See Student Handbook for details. Note that delays caused by computer downtime cannot be

accepted as a valid reason for a late submission without penalty. Students must plan their work to

allow for both scheduled and unscheduled downtime.

SUBMISSION GUIDELINES:

Task 1 should be saved to a file named task1.txt using the SPOOL command.

Task 2 should be saved to a file named task2.txt using the SPOOL command.

Task 3 should be saved to a file named task3.txt using the SPOOL command.

Note: an example of using the SPOOL command is given in the lab book. In the SPOOL file, you need

to provide the query/procedure/function/trigger execution and the sample output. For the trigger, you

need to show a sample test that demonstrates the successful execution of the trigger.

All the tasks above are to be submitted in soft-copy format using the submission link provided

on LMS by 10.00am Wednesday, May 29th, 2019.

SUBMISSION CHECKLIST:

The relevant SQL queries for the ‘New Endor Airlines’ Database System;

The required stored procedures, stored function, and triggers.

NOTE: No built-in ORACLE column numbering (such as ROWNUM) or other ORACLE

ranking facilities (such as RANK) can be used in this assignment.

Implement the following tasks using ORACLE SQL*Plus.

Download the file NEASchema.sql from the LMS site and run it on ORACLE SQL*Plus. This file

contains all the CREATE and INSERT statements you will need for this assignment.

To run the file, issue the following command: @D:\dbf\NEASchema.sql

→ Where D:\dbf is the location of the file (for example)1

.

NOTE: YOU DO NOT NEED TO INSERT MORE DATA INTO THE TABLES.

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

MODEL(modelID, economySeats, buisnessSeats, firstClassSeats, cargoCapacity, fuelCapacity, length,

wingspan, serviceHours)

LOCATION(airportCode, country, address, phone)

TICKET(ticketNum, luggageLimit, seatNum, classCode, medicalCondition, mealChoice, customerID,

flightID)

ROUTE(routeID, description, arriveAirportCode, departAirportCode)

IRREGULAR_EVENT(eventNumber, flightID, eventDateTIme, eventDescription)

SERVICE(serviceDate, aircraftID, description, cost)

AIRCRAFT(aircraftID, mailCargoCapacity, numMedPacks, numDefibritlators, haulType, modelID)

NEACC_MEMBER(memberID, flightGoldPoints)

STAFF(staffID, name, address, email, phone, passportNum, pilotYesNo, prevHrsPilotExp,

attendantYesNo, memberID)

CUSTOMER(customerID, name, address, country, email, phone, birthdate, passportNum, memberID)

FLIGHT(flightID, estDepartureDateTime, actDepartDateTime, actArriveDateTime, avgSpeed,

avgHeight, estDuration, estFuel, haulType, captainStaffID, firstOfficerStaffID, routeID, aircraftID)

ADDITIONAL_PILOT(staffID, flightID, activityCode, activityDesc)

HOSTING(staffID, flightID)

PILOT_QUALIFICATION(qualification, staffID)

NOTE: PK is printed underlined and FK is printed in italics.

1 Please note that if you are using sqlplus in latcs7 server through putty, you cannot refer to a file on your laptop

or PC. This is because the server does not know about your local file path.

Task 1 [50 marks]

Using the tables provided above, provide SQL statements for the following queries.

a. The airline is being audited for tax evasion. Retrieve the total cost of aircraft services for

aircraft A0001 in the financial year from 1st July 2017 to 30th June 2018. [5 marks]

b. List the model ID, aircraft ID, total number of seats, destination country and actual flight

duration (in hours) of every flight that has departed from NEX airport (excluding flights that

have not arrived at their destinations yet). [5 marks]

c. Print 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. [5 marks]

d. For each pilot, give the total amount of flying experience so far in hours (including

experience prior to joining NEA and experience with NEA as a captain, first officer, or

additional pilot). [5 marks]

e. The analytics team want to investigate the amount of pilot experience on board flights.

Produce a table with the flightID and estimated departure date of each flight, along with the

staff ID's of the captain and first officer on board, their respective previous hours of flying

experience prior to joining NEA, and each of their total hours of experience with new endor

airlines before that flight (so the table will have a total of 8 columns). Order the result by

estimated departure date. Hint: It may be easier to complete Question d first. [5 marks]

f. The management team wish to determine if an appropriate number of hosts were assigned to

each departed flight. For each departed flight, display the flight number, number of tickets

sold, and number of hosts assigned. [5 marks]

g. List the model ID, aircraft ID, haul type and number of seats for each aircraft whose model

has amongst the top 2 greatest number of seats of all models owned by NEA. [5 marks]

h. 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). [5 marks]

i. Display a table of all customers who have purchased tickets for at least 5 flights, excluding

any flights that have experienced irregular events. Display the customer ID and number of

tickets purchased. [5 marks]

j. For each flight that has already departed, 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 departure. The displayed table should have 4 columns. Display the date

in the same format as '01st January 2019’ and display the time in 12-hour format ending in

'am' or 'pm' with hours and minutes shown (but not seconds). [5 marks]

Task 2 [30 marks]

Provide the implementation of the following stored procedures and function. For submission, please

include both the PL/SQL code and an execute procedure/SQL statement to demonstrate the

functionality.

a. Create a stored procedure that takes a desired flight ID, luggage limit, class (economy 'E',

business 'B' or first class 'F'), and desired number of seats as input, and outputs whether the flight

is available for booking or not. The procedure should make sure there are enough seats available

in the desired class; there is enough luggage room (after subtracting mail cargo capacity from the

total cargo capacity); and that the flight is estimated to depart at least 3 hours from the time that

the stored procedure is executed.

b. You may find it easier to complete Question (a) before this question. Create a stored procedure

that takes a desired arrival airport code, departure airport code, luggage limit, class (economy 'E',

business 'B' or first class 'F') and desired number of seats as input and prints out the flight

number ('NE' followed by the last 3 digits of the flight ID) of all flights that are available for

booking that will travel between the specified airports. As in Question (a), the procedure should

check whether there are enough seats available in the desired class, whether there is enough

luggage room (after subtracting mail cargo capacity from the total cargo capacity), and whether

each flight is estimated to depart at least 3 hours from the time that the stored procedure is

executed. Hint: It may help to use the SUBSTR function.

c. The airline has decided that economy class passengers should not get meals on board short haul

flights. Write a stored function that takes flight number (any flight, short or long haul) and meal

code ('ST' or 'VG') as input and returns the number of meals (of that type) required on board the

flight. The function should also return 0 if there are no tickets sold yet for the specified flight.

For the execution, produce a table that gives the number of meals of each type that are required so

far for each flight in the database.

[10 marks each]

Task 3 [20 marks]

Provide the implementation of the following triggers. For submission, please include both the

PL/SQL code and a DML statement (insert, update or delete) to demonstrate the trigger functionality.

a. Create a trigger that prevents a captain or first officer from being used on a flight if their staff

ID is not that of a pilot, or if they do not have the qualification 'ATPL' (Air Transport Pilots

License). The trigger error message should clearly explain why the error occurred in each

case, and must display the offending first officer or captain staff ID.

b. Create a trigger that backs up the flight ID, ticket number and seat number for any deleted

ticket if the ticket corresponds to a flight that has not yet departed. To back-up the

information, the trigger should insert all the necessary data into a table called

BACKUP_TICKET (which you need to create beforehand).

[10 marks each]

 

因为专业，所以值得信赖。如有需要，请加QQ：99515681 或邮箱： 

微信：codinghelp