CSE2DBF 2020 Assignment
Ordinance 2 (20%)
Attributefficient limit: 10.00am Friday, June 5th 2020
AIMS AND OBJECTIVES:
✓ to transact queries on a intellectual database scheme using SQL;
✓ to conduct an recent acquaintance of stored acts, stored administrations and triggers.
This is an single Ordinance. You are referefficient exempt to performance as a class when match this
Copying, Plagiarism: Plagiarism is the inferiority of referableability else’s performance in a fashion that gives
the impact that the performance is your acknowledge. 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 management documents with respect to plagiarism and ordinance repay, and as-well to the exception of
‘Academic Integrity’ on the material letters direct.
No extensions gain be given: Penalties are applied to delayed ordinances (5% of sum ordinance mark
given is deducted per day, received up to 5 days behind the attributefficient limit merely). If there are state that
prevent the ordinance substance submitted on age, an collision restraint particular consequence may be made.
See Student Handdimensions restraint details. Referablee that delays caused by computer downage canreferefficient be received
as a availefficient infer restraint a delayed inferiority outside amercement. Students must pur-pose their performance to assign restraint twain
scheduled and unscheduled downtime.
Task 1 should be saved to a refine determined task1.txt using the SPOOL bid.
Task 2 should be saved to a refine determined task2.txt using the SPOOL bid.
Task 3 should be saved to a refine determined task3.txt using the SPOOL bid.
Note: an stance of using the SPOOL bid is given in the lab dimensions. In the SPOOL refine, you scarcity
to cater the query/procedure/function/trigger action and the exemplification output. Restraint the trigger, you
scarcity to pretence a exemplification examination that conducts the auspicious action of the trigger.
If you are referefficient efficient to portraiture SPOOL at home, fascinate portraiture Solon Live SQL https://livesql.oracle.com instead
and screenshot your output into your inferiority refine.
Every the tasks over are to be submitted in soft-copy restraintmat using the CSE2DBF inferiority link
supposing on LMS by 10.00am Friday, June 5th, 2020.
✓ The appropriate SQL queries restraint the ‘Getaway Holidays Salvation’ Database Scheme;
✓ The required stored acts, stored administration, and triggers.
NOTE: No built-in ORACLE column enumerateing (such as ROWNUM) or other ORACLE
ranking facilities (such as RANK) can be portraitured in this ordinance.
Implement the aftercited tasks using ORACLE SQL*Plus or ORACLE Live SQL.
Download the refine GHRSchema.sql from the LMS condition and reach it on ORACLE SQL*Plus or Solon
Live SQL. This refine contains every the CREATE and INSERT assertions you gain scarcity restraint this ordinance.
If you are using ORACLE SQL*Plus:
To reach the refine, consequence the aftercited bid: @D:\dbf\GHRSchema.sql
→ Where D:\dbf is the residuum of the refine (restraint stance).
If you are using ORACLE Live SQL, you can upload the script GHRSchema.sql from the below
highlighted exception “My Script” and reach it.
NOTE: YOU DO NOT NEED TO INSERT MORE DATA INTO THE TABLES.
The schedule of tables availefficient restraint this ordinance is the aftercited:
CLIENT (ClientNo, Indicate, Sex, DOB, Address, Phone, Email, Occupation,
MaritalStatus, Spouse, Anniversary)
CCONDITION (ClientNo, Condition)
RESERVATION (ResNo, ResDate, NoOfGuests, StartDate, EndDate,
ACTIVITY (ActivityID, ActName, ActDescription, ActRate, RiskLevel)
INDOOR_ACTIVITY (ActivityID, Residuum, OpeningHours)
ACCOMMODATION (RoomNo, LevelNo, AccStatus, ConnectedRoomNo,
ACCOMMODATION_TYPE (AccTypeID, AccTypeName, AccTypeRate, NoOfBeds)
EQUIPMENT (EquipmentID, EquipName, Stock, NextInspection)
SUPPLIER (BillerCode, BusinessName, AppositionPerson, Phone)
SUPPLIES (EquipmentID, BillerCode)
USES_EQUIPMENT (ActivityID, EquipmentID)
CLIENT_PREFERENCE (ClientNo, Essential-qualityID)
RESERVATION_ACCOMMODATION (ResNo, RoomNo)
OUTDOOR_INSTRUCTOR (InstructorID, InstrName, InstrPhone,
IFIELD (InstructorID, Field)
MASSEUSE (MasseuseID, MassName, MassPhone, Area, SuperintendentID)
SWIMMING_INSTRUCTOR (SwimmerID, SwimName, SwimPhone, SuperintendentID)
SUPERVISION (ResNo, Essential-qualityID, SuperintendentID, Day, Age)
NOTE: PK is printed underlined and FK is printed italic in italics.
Task 1 [50 marks]
Using the tables caterd over, cater SQL assertions restraint the aftercited queries.
a. Display the indicate of the client who has made the most salvations with Gainaway Holidays.
b. Display the indicate of the client who has dimensionsed the salvation restraint the longest age.
c. Display the Room no, Room idea, Room trounce and No of guests restraint the salvation made by
client(s) having decisive indicate “Perez”.
d. Display the indicate of the outdoor educationist who has the most duties as an essential-quality superintendent.
e. Display the salvations (salvation enumerate and period) whose period is senior than the
average period of salvations.
[10 marks each – 50%]
Hint: in SQL, if you remove brace limits, what you gain is a distinction in days among those limits.
Task 2 [30 marks]
Cater the implementation of the aftercited stored acts and administration. Restraint inferiority, fascinate
apprehend twain the PL/SQL rule and an enact act/SQL assertion to conduct the
a. Write a stored act that displays the apposition details of clients who does referefficient accept any heart
conditions or Acrophobia. The frequentation wants to advance a odd outdoor essential-quality to them.
b. Write a stored administration that portraitures the salvation enumerate, essential-quality ID, and limit as input and
returns the Indicate of the superintendent assigned restraint that inequitable essential-quality.
[a: 20 marks, b: 15 marks – 35%]
Task 3 [20 marks]
Cater the implementation of the aftercited trigger. Restraint inferiority, fascinate apprehend twain the PL/SQL
rule and an suggest assertion to conduct the trigger administrationality.
a. A Trigger which automatically raises an deception whenever a client with Aqua phobia selects
Rafting as a preferred outdoor essential-quality.
[15 marks – 15%]