Marks task marks provide the implementation the following trigger

CSE2DBF 2020

Assignment 2 (20%)

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.

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

All the tasks above are to be submitted in soft-copy format using the CSE2DBF submission link provided on LMS by 10.00am Friday, June 5th, 2020.

SUBMISSION CHECKLIST:

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 ORACLESQL*Plus:
To run the file, issue the following command: @D:\dbf\GHRSchema.sql

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) OUTDOOR_ACTIVITY (ActivityID)
INDOOR_ACTIVITY (ActivityID, Location, OpeningHours)
ACCOMMODATION (RoomNo, LevelNo, AccStatus, ConnectedRoomNo,
AccTypeID)

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 outdoor instructor who has the most duties as an activity supervisor. e. Display the reservations (reservation number and duration) whose duration is greater than the average duration of reservations.

[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.