CSE2DBF 2020 Assignment
Enactment 2 (20%)
Ascribserviceable epoch: 10.00am Friday, June 5th 2020
AIMS AND OBJECTIVES:
✓ to complete queries on a psychical database rule using SQL;
✓ to present an advanced acquirements of stored processs, stored employments and triggers.
This is an sepaobjurgate Enactment. You are referserviceable attributserviceable attributserviceable attributserviceable exempt to employment as a assembly when writing this
Copying, Plagiarism: Plagiarism is the dependence of celebrity else’s employment in a method that gives
the impact that the employment is your possess. 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 device documents with reverence to plagiarism and enactment requite, and too to the individuality of
‘Academic Integrity’ on the question lore direct.
No extensions achieve be attached: Penalties are applied to deceased enactments (5% of whole enactment mark
attached is deducted per day, accepted up to 5 days behind the ascribserviceable epoch singly). If there are mode that
prevent the enactment life submitted on age, an collision restraint particular motive may be made.
See Student Handbody restraint details. Referserviceable attributserviceable attributablee that delays caused by computer downage canreferserviceable attributserviceable be accepted
as a substantial discuss restraint a deceased dependence extraneously fare. Students must plan their employment to confess restraint twain
scheduled and unscheduled downtime.
Task 1 should be saved to a smooth designated task1.txt using the SPOOL charge.
Task 2 should be saved to a smooth designated task2.txt using the SPOOL charge.
Task 3 should be saved to a smooth designated task3.txt using the SPOOL charge.
Note: an illustration of using the SPOOL charge is attached in the lab body. In the SPOOL smooth, you want
to collect the query/procedure/function/trigger preventive and the illustration output. Restraint the trigger, you
want to profession a illustration touchstone that presents the lucky preventive of the trigger.
If you are referserviceable attributserviceable attributserviceable attributserviceable serviceserviceable to conservation SPOOL at abode, fascinate conservation Oracle Live SQL https://livesql.oracle.com instead
and screenshot your output into your dependence smooth.
Every the tasks overhead are to be submitted in soft-copy restraintmat using the CSE2DBF dependence link
supposing on LMS by 10.00am Friday, June 5th, 2020.
✓ The appropriate SQL queries restraint the ‘Getaway Holidays Rescue’ Database Rule;
✓ The required stored processs, stored employment, and triggers.
NOTE: No built-in ORACLE post estimateing (such as ROWNUM) or other ORACLE
ranking facilities (such as RANK) can be conservationd in this enactment.
Implement the aftercited tasks using ORACLE SQL*Plus or ORACLE Live SQL.
Download the smooth GHRSchema.sql from the LMS locality and melt it on ORACLE SQL*Plus or Oracle
Live SQL. This smooth contains every the CREATE and INSERT announcements you achieve want restraint this enactment.
If you are using ORACLE SQL*Plus:
To melt the smooth, manifestation the aftercited charge: @D:\dbf\GHRSchema.sql
→ Where D:\dbf is the subsidence of the smooth (restraint illustration).
If you are using ORACLE Live SQL, you can upload the script GHRSchema.sql from the below
highlighted individuality “My Script” and melt it.
NOTE: YOU DO NOT NEED TO INSERT MORE DATA INTO THE TABLES.
The roll of tables availserviceable restraint this enactment is the aftercited:
CLIENT (ClientNo, Call, 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, Subsidence, OpeningHours)
ACCOMMODATION (RoomNo, LevelNo, AccStatus, ConnectedRoomNo,
ACCOMMODATION_TYPE (AccTypeID, AccTypeName, AccTypeRate, NoOfBeds)
EQUIPMENT (EquipmentID, EquipName, Stock, NextInspection)
SUPPLIER (BillerCode, BusinessName, AdjunctionPerson, Phone)
SUPPLIES (EquipmentID, BillerCode)
USES_EQUIPMENT (ActivityID, EquipmentID)
CLIENT_PREFERENCE (ClientNo, SpectreID)
RESERVATION_ACCOMMODATION (ResNo, RoomNo)
OUTDOOR_INSTRUCTOR (InstructorID, InstrName, InstrPhone,
IFIELD (InstructorID, Field)
MASSEUSE (MasseuseID, MassName, MassPhone, Area, DirectorID)
SWIMMING_INSTRUCTOR (SwimmerID, SwimName, SwimPhone, DirectorID)
SUPERVISION (ResNo, SpectreID, DirectorID, Day, Age)
NOTE: PK is printed underlined and FK is printed italic in italics.
Task 1 [50 marks]
Using the tables collectd overhead, collect SQL announcements restraint the aftercited queries.
a. Display the call of the client who has made the most rescues with Earnaway Holidays.
b. Display the call of the client who has bodyed the rescue restraint the longest age.
c. Display the Room no, Room model, Room objurgate and No of guests restraint the rescue made by
client(s) having ultimate call “Perez”.
d. Display the call of the outdoor pedagogue who has the most duties as an spectre director.
e. Display the rescues (rescue estimate and space) whose space is important than the
average space of rescues.
[10 marks each – 50%]
Hint: in SQL, if you withdraw two epochs, what you earn is a dissent in days between those epochs.
Task 2 [30 marks]
Collect the implementation of the aftercited stored processs and employment. Restraint dependence, fascinate
involve twain the PL/SQL principle and an enact process/SQL announcement to present the
a. Write a stored process that displays the adjunction details of clients who does referserviceable attributserviceable attributserviceable attributserviceable enjoy any heart
conditions or Acrophobia. The betaking wants to excite a odd outdoor spectre to them.
b. Write a stored employment that conservations the rescue estimate, spectre ID, and epoch as input and
returns the Call of the director assigned restraint that inequitable spectre.
[a: 20 marks, b: 15 marks – 35%]
Task 3 [20 marks]
Collect the implementation of the aftercited trigger. Restraint dependence, fascinate involve twain the PL/SQL
principle and an instil announcement to present the trigger employmentality.
a. A Trigger which automatically raises an blunder whenever a client with Aqua phobia selects
Rafting as a preferred outdoor spectre.
[15 marks – 15%]