辅导CSE2DBF、讲解database system、SQL辅导、SQL编程语言调试 辅导R语言编程|讲解SPSS
- 首页 >> 其他 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.
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]
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.
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]