Assignment 2 (20%)
Due date: 10.00am Friday, June 5th 2020
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.
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.
If you are not able to use SPOOL at home, please use Oracle Live SQL https://livesql.oracle.com instead and screenshot your output into your submission file.
All the tasks above are to be submitted in soft-copy format using the CSE4DBF submission link provided on LMS by 10.00am Friday, June 5th, 2020.
✓ The relevant SQL queries for the ‘Getaway Holidays Reservation’ 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 or ORACLE Live SQL.
Download the file GHRSchema.sql from the LMS site and run it on ORACLE SQL*Plus or Oracle Live SQL. This file contains all the CREATE and INSERT statements you will need for this assignment.
If you are using ORACLE SQL*Plus:
To run the file, issue the following command: @D:\dbf\GHRSchema.sql
→ Where D:\dbf is the location of the file (for example).
If you are using ORACLE Live SQL, you can upload the script GHRSchema.sql from the below highlighted section “My Script” and run it.
NOTE: YOU DO NOT NEED TO INSERT MORE DATA INTO THE TABLES.
The list of tables available for this assignment is the following:
CLIENT (ClientNo, Name, Sex, DOB, Address, Phone, Email, Occupation, MaritalStatus, Spouse, Anniversary)
CCONDITION (ClientNo, Condition)
RESERVATION (ResNo, ResDate, NoOfGuests, StartDate, EndDate, ClientNo, Status)
ACTIVITY (ActivityID, ActName, ActDescription, ActRate, RiskLevel)
INDOOR_ACTIVITY (ActivityID, Location, OpeningHours)
ACCOMMODATION (RoomNo, LevelNo, AccStatus, ConnectedRoomNo, AccTypeID)
ACCOMMODATION_TYPE (AccTypeID, AccTypeName, AccTypeRate, NoOfBeds)
EQUIPMENT (EquipmentID, EquipName, Stock, NextInspection)
SUPPLIER (BillerCode, BusinessName, ContactPerson, Phone)
SUPPLIES (EquipmentID, BillerCode)
USES_EQUIPMENT (ActivityID, EquipmentID)
CLIENT_PREFERENCE (ClientNo, ActivityID)
RESERVATION_ACCOMMODATION (ResNo, RoomNo)
OUTDOOR_INSTRUCTOR (InstructorID, InstrName, InstrPhone, SupervisorID)
IFIELD (InstructorID, Field)
MASSEUSE (MasseuseID, MassName, MassPhone, Area, SupervisorID)
SWIMMING_INSTRUCTOR (SwimmerID, SwimName, SwimPhone, SupervisorID)
SUPERVISION (ResNo, ActivityID, SupervisorID, Day, Time)
NOTE: PK is printed underlined and FK is printed italic in italics.
Task 1 [50 marks]
Using the tables provided above, provide SQL statements for the following queries.
a. Display the name of the client who has made the most reservations with Getaway Holidays.
b. Display the name of the client who has booked the reservation for the longest period.
c. Display the Room no, Room type, Room rate and No of guests for the reservation made by client(s) having last name “Perez”.
d. Display the Name of the equipment and the name of their suppliers (if any) for the outdoor activities with high risk level. You need to display all the equipment including equipment which is not supplied by any supplier.
e. Display the name of the outdoor instructor who has the most duties as an activity supervisor.
f. Display the reservations (reservation number and duration) whose duration is greater than the average duration of reservations.
g. For each reservation, display the total income coming from activities & total income coming from accommodations along with the reservation number. (Do not consider the discount schemes but consider every reservation even if it does not have any of the costs associated with it).
[a – f: 7 marks each, g: 8 marks – 50%]
Hint: in SQL, if you subtract two dates, what you get is a difference in days between those dates.
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. Write a stored procedure that displays the contact details of clients who does not have any heart conditions or Acrophobia. The resort wants to promote a new outdoor activity to them.
b. Write a stored function that uses the reservation number, activity ID, and date as input and returns the Name of the supervisor assigned for that specific activity.
[a: 20 marks, b: 15 marks – 35%]
Task 3 [20 marks]
Provide the implementation of the following trigger. For submission, please include both the PL/SQL code and an insert statement to demonstrate the trigger functionality.
a. A Trigger which automatically raises an error whenever a client with Aqua phobia selects Rafting as a preferred outdoor activity.
[15 marks – 15%]