Assignment DBI

DBI202 ASSIGNMENT
[Total mark: 200]
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.
PART A:
Task 1 [50 marks]: Construct an ER-Model for the following problem description. State any
assumptions that you make.
DSS (Dig, Set, Spike) Volleyball is an amateur volleyball association. Each team in the
association represents a particular suburb. Each suburb has its own sporting complex where
the team's home games are played. Teams are identified by their id and also have a name, a
contact person and a contact phone number. Each team has a maximum of ten players and a
minimum of seven players registered. Players are identified by a unique player id and their
name, address and contact phone number need to be recorded. Each team has a coach; some
have up to two coaches. Coaches also have unique ids, and their name, address, contact
phone number and coaching qualifications are required.
During the season, each team plays two games, one as the home team and one as the visitor,
against each of the other teams. For each game the date of the game, start time and final score
need to be recorded.
As a special treat at the end of the season the association holds a break-up party for the
players. At the break-up party treats are given out to all the players’ children who are under
12. The association therefore requires each child’s name and date of birth.
Task 2 [50 marks]: Transform your ER-model into relational tables.

PART B:
Get the file Assignment.sql from your lecturer. This file contains all the CREATE and
INSERT statements you will need for this assignment.
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)
OUTDOOR_ACTIVITY (ActivityID)
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)
ACTIVITY_SUPERVISOR (SupervisorID)
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 Trigger which automatically raises an error whenever a client with Aqua phobia selects Rafting as
a preferred outdoor activity.
[15 marks – 15%]

Assignment DBI

Uploaded by

Copyright:

Available Formats

Assignment DBI

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Assignment DBI

Uploaded by

Copyright:

Available Formats

DBI202 ASSIGNMENT

[Total mark: 200]

Task 2 [50 marks]: Transform your ER-model into relational tables.

The list of tables available for this assignment is the following:

ACCOMMODATION (RoomNo, LevelNo, AccStatus, ConnectedRoomNo,

NOTE: PK is printed underlined and FK is printed italic in italics

[a – f: 7 marks each, g: 8 marks – 50%]

Task 2 [30 marks]

[a: 20 marks, b: 15 marks – 35%]

[15 marks – 15%]

You might also like