FIT3171 Introduction to Databases 2021 Semester 1 Assignment 2B - SQL - MonHeli (MH) Learning Outcomes: 2, 5, 6, 8 (see Unit Preview) Assignment weighting 15% Assignment marked out of 100 and released as a grade out of 15 MonHeli is a thriving helicopter company. The company owns a range of helicopters whose standard cost of hire may vary depending on the type of charter and the number of hours flown. Each helicopter is identified by their call sign; much like a registration number identifies a car. The total time each helicopter is flown is recorded to keep track of maintenance schedules. The standard cost of hire for a helicopter depends on the helicopter type however this may be varied over time and for certain clients (eg. frequent flyers). Each of the helicopters has a fixed number of passenger seats available. The company’s charter work is categorised as: scenic flight, transport, filming, geological survey, fire mapping, fuel reduction burning, logging coupe regeneration burning, aerial seeding, training, and search and rescue. The company has a number of employees consisting of pilots, engineers and a secretary. Details are kept of each employee’s name, address, contact number, Tax File Number, salary and next of kin. As only qualified pilots are allowed to take charters, records are kept of the pilots’ endorsements. An endorsement is the credential a pilot has to fly a certain type of helicopter. Therefore, when a pilot is endorsed to fly a certain helicopter type (eg. JetRanger) a record is kept of the pilot’s endorsement for this type of helicopter and the total hours the pilot has flown in this helicopter type. This indicates the pilot’s level of experience in this type of aircraft. For a pilot to have a current endorsement for a helicopter type they must be reviewed annually and the date of their last review is kept for each of their endorsements. Charters can vary in time – from several days to part of an hour. A charter is flown by one pilot using one helicopter. As clients only pay for the time spent flying, a record is kept of every time the helicopter takes off and lands during a charter flight (a leg) - a charter may consist of one or more legs. When a client initially makes a booking, the destination, the estimated time of departure (ETD) and estimated time of arrival (ETA) are recorded for each leg of the charter. For example, a client may book a helicopter to fly them from Gondor Airport to Rivendell for a 10:00 am meeting, then leave at 12:00 noon to fly to Mount Doom for 3 hours skiing and then back to Gondor Airport by 6:00 pm. Page 1 of 9 The actual time of departure (ATD) and actual time of arrival (ATA) are recorded for the charter so that clients are charged for the actual time spent flying (the accumulated engine time). The engine time for each leg is the difference in minutes between the ATA and ATD. A data model has been developed for MonHeli, the logical model is shown below: Page 2 of 9 Task 1 - SQL Select (80 marks) The tables for MonHeli have been created in the Monash Oracle server and are available from the user MH who has given you select rights on the tables. You will use these tables to answer the SQL Queries listed below. Your answers for these tasks must be placed in the supplied SQL Script mh-queries.sql You must ONLY use the data as provided in the text of the questions. Where a particular case (upper case, lower case, etc.) for a word is provided you must only use that case. You may divide names such as Zora Mandrey into the first name of Zora and a last name of Mandrey if required. Failure to adhere to this requirement will result in a mark of 0 for the relevant question. ANSI joins must be used where two or more tables are to be joined, under no circumstances can "implicit join notation" be used - see the week 7 workshop slides and the week 8 tutorial. You must NOT use PL/SQL or Views in Task 1. Where a question indicates "Your output must have the form shown below" - this means the same appearance and alignment of columns/data as the sample output shows. Clearly your actual data may be different, this is a live database so changes may occur. When required to show output which involves the client or pilot name as a full name, the name must not have any leading spaces (ie. not start with a space). Q1 List all endorsements held by pilots where the endorsements last annual review date was after 31st March 2020. Show the helicopter type number, employee number, employee lastname, employee firstname and the date of the review. This listing should be displayed in ascending order of the last annual review date. Your output must have the form shown below (partial output only shown): [4 marks] Page 3 of 9 Q2 List those charters where special requirements are specified. Show the charter number, client number, client lastname, client firstname and the special requirements specified. This listing should be displayed in ascending order of the charter number. [4 marks] Q3 List the charter details for those charters which meet the following requirements: ● destination is Mount Doom, and ● the charter cost is less than $1000 per hour, or no special requirements are specified Show the charter number, client lastname, client firstname and the charter cost per hour. The client name should be listed in a single column called FULLNAME (sample output: Gandalf The Grey). The listing should be displayed in the descending order of the client FULLNAME. [8 marks] Q4 For each helicopter type for which there are at least two helicopters of that type, list the total number of helicopters of that type. Show the helicopter type number, helicopter type name and the number of helicopters owned. The listing should be displayed in the descending order of the number of helicopters. [8 marks] Q5 For each location that has been used as the origin of a charter leg more than once, list the location as well as the number of times it has been used as an origin. Show location number, location name and the number of times the location has been used as an origin. The listing should be displayed in the ascending order of the number of times a location has been used as an origin. [8 marks] Q6 List the number of hours flown for EVERY helicopter type. Show helicopter type number, helicopter type name and the total number of hours flown. Types which have no recorded hours should be shown as 0 hours flown. The listing should be displayed in the ascending order of hours flown. [8 marks] Page 4 of 9 Q7 List all the charters that Frodo Baggins has flown (completed flights), with the most recent charters appearing at the top of the list. Show the charter number and the date/time of departure for leg one (note: a charter may span several days). [8 marks] Q8 List those charters where the total cost is less than the average total cost for all charters. The total cost for a charter is obtained by multiplying the charter cost per hour with the actual duration obtained from the leg actual departure and actual arrival times. Show charter number, client number, client lastname, client firstname, total charter cost. The total charter cost should be rounded to two decimal digits and displayed with a $ symbol e.g. $1234.56 For this question, if either client name is empty '-' should be displayed. The listing should be displayed in the descending order of total charter cost. Your output must have the form shown below (partial output only shown): [10 marks] Q9 Which charters have been able to depart at the time estimated for all legs of its flight? Show the charter number, pilot’s name and the client’s name (both names should be shown in a single column). Order the output by charter number. Your output must have the form shown below (partial output only shown): [10 marks] Page 5 of 9 Q10 For each client, list the name of their favorite destination location/s and how many times they have visited that destination. The clients favorite destination will be the location/s they visit the most based on completed flights. Show the client number, client full name in one column, the name of the destination location and the number of times the location has been visited by the client. Order the output by the client number. Where a client has several favourite destinations, order them by destination name. [12 marks] Task 2 - Triggers (20 marks) Your answers for these tasks must be placed in the supplied SQL Script mh-triggers.sql These tasks should be attempted only after task 1 has been successfully completed. Use mh-partial-schema.sql to create and load data into HELI_TYPE, HELICOPTER, CHARTER, CHARTER_LEG, and LOCATION tables under your account. Note that these tables contain testing data only. Use these tables to answer the questions below. For each of these questions, as part of your answer, you must create a set of SQL commands which will demonstrate the successful operation of your trigger (test harness) - these tests are part of the awarded marks for each question. Place these commands below your trigger definition for each of the tasks. Ensure your trigger definition finishes with a slash(/) followed by a blank line as detailed in the week 9 workshop and week 10 tutorial. In addition, when coding your trigger(s), you must provide output messages where appropriate. Q1 MH, from now on, would like to maintain the integrity of the locations added into the CHARTER_LEG table. When the origin and destination locations are inserted/updated, the origin location must be different from the destination location. If both locations are the same, the trigger should prevent the action. Code a single trigger to enforce this requirement. [4 marks] Q2 MH, from now on, would like to automatically maintain the integrity of data inserted into the CHARTER table. When charter data is added or modified, the charter’s cost per hour must be equal or higher than the helicopter’s cost per hour. MH would also like to ensure that the charter’s maximum number of passengers is not above the helicopter’s number of seats. If either of these requirements is violated, then the trigger should prevent the action of inserting/updating the charter. Code a single trigger to enforce both of these requirements. Note: you may use any numeric values for ctype_nbr, client_nbr, and emp_nbr when testing your trigger. [7 marks] Page 6 of 9 Q3 Create a trigger to automatically update the value of heli_hours_flown in the HELICOPTER table based on the actual time of departure and the actual time of arrival for a CHARTER_LEG. The charter_leg data is inserted when the customer initially makes a booking with the estimated time of departure and arrival. The actual time of departure and arrival are updated when that leg is completed in a single update. Here is an example of the update statement: UPDATE charter_leg SET cl_atd = TO_DATE('01/06/2020 16:03', 'dd/mm/yyyy hh24:mi'), cl_ata = TO_DATE('01/06/2020 17:08', 'dd/mm/yyyy hh24:mi') WHERE cl_leg_nbr = 2 AND charter_nbr = 1; The value of heli_hours_flown must then be updated based on the time the helicopter has actually flown for that charter leg. Once the actual time of departure and arrival have been entered, they must not be allowed to be further updated. [9 marks] Page 7 of 9 SUBMISSION REQUIREMENTS Due Date: Friday 28th May at 5 PM AEST (week 12) Please note, if you need to resubmit, you cannot depend on your tutors' availability, for this reason, please be VERY CAREFUL with your submission. It is strongly recommended that you submit several hours before this time to avoid such issues. For this assignment there are two files you are required to submit: ● mh-queries.sql ● mh-triggers.sql If you need to make any comments to your marker/tutor please place them at the head of each of your solution scripts in the "Comments for your marker:" section. Do not zip these files into a zip archive, submit the SQL script as it is. The SQL script must also have been pushed to the FIT GitLab server with an appropriate history as you developed your solutions (a minimum of four pushes). Please ensure your commit comments are meaningful. Late submission will incur penalties at the rate of -5 mark for every 12 hours the submission is late. Please note we cannot mark any work on the GitLab Server, you need to ensure that you submit correctly via Moodle since it is only in this process that you complete the required student declaration without which work cannot be assessed. It is your responsibility to ENSURE that the files you submit are the correct files - we strongly recommend after uploading a submission, and prior to actually submitting, that you download the submission and double-check its contents. Your assignment MUST show a status of "Submitted for grading" before it will be marked. If your submission shows a status of "Draft (not submitted)" it will not be assessed and will incur late penalties after the due date/time. Please carefully read the documentation under the "Assignment Submission" on the Moodle Assessments page which covers things such as extensions and resubmission. Page 8 of 9 CRITERIA FOR MARKING Submissions will be graded on: ● the correct application of SQL statements and constructs to: ○ retrieve the required data in the required format, and ○ where a layout or column heading has been specified, appropriately reflect these requirements, ● the correct application of triggers to maintain data integrity. Submissions will be grade penalised if they: ● contain SET ECHO … or SPOOL commands, ● do not have a semicolon (;) closing the query for every query submitted, ● use subqueries and SQL conditions unnecessarily (although you are not required to consider efficiency of your solution you should try an ensure that you use the minimum number of subqueries and SQL conditions when arriving at your answer), ● use PL/SQL or Views in Task 1, ● do not use to_char/to_date where appropriate in handling dates, ● do not include slash (/) followed by a blank line after the create trigger statements, ● do not provide output messages where appropriate when coding triggers, ● do not have an appropriate development history on the FIT GitLab server for all source files (at least four pushes required). Page 9 of 9












































































































































































































































学霸联盟