COMP1350 Introduction To Database Design And Management Assignment 2 Answer

pages Pages: 4word Words: 890

Question :

DEPARTMENT OF COMPUTING COMP1350 2020 – ASSIGNMENT TWO

Introduction to Database Design and Management Database Implementation Assignment

Case Background

Remote Island Resort has now moved to relational databases (Thanks to your design). They are having trouble with creating tables and writing a few queries. You have been employed to construct and implement the database and write queries for some common searches on the database. Comments are given for you to understand the column and do not have to be added to the database.

Do not change the column names whilst creating the tables

Table: VillaType
Column-Name
Comments/Description
Sample Data
VillaTypeID [PK]
A unique identifier for the type of Villa
T1
VillaTypeName
A short name/description for the type of
Villa
One-bedroomVilla
without Pool
Table: Villa
Column-Name
Comments/Description
Sample Data
VillaID [PK]
A unique identifier for the Villa
V14
VillaName
A short name/description for the Villa
Serene Bliss
VillaCostPerDay
CostoftheVillaforanightof reservation (Decimal with two places)
Max cost of a villa is $2500.00/night
785.95
VillaTypeID [FK]
Theidofthetypeofvilla[FK] referencing VillaTypeID in VillaType
table
T1
Table: Customer
Column-Name
Comments/Description
Sample Data
CustomerID [PK]
A unique identifier for the customer
C345
CustomerName
The name of customer stored in format “FirstName LastName”. For ease, assume no middle names, initials are
stored
Hugh Jackman
CustomerPhNum
The phone number of customer. Only
mobile phone numbers are stored
0415871256
Table: Reservation
Column-Name
Comments/Description
Sample Data
ReservationID [PK]
A unique identifier for the Reservation
R321
ReservationDate
The date of the reservation
12th of June, 2020
CustomerID [FK]
The id of the Customer [FK] referencing
CustomerID in Customer table
C345
Table: Villa_Reservation
Column-Name
Comments/Description
Sample Data
Sample Data
ReservationID [PK,FK]
A unique identifier for the Reservationreferencing ReservationID in Reservation
table
R321
R321
VillaID [PK,FK]
A unique identifier for the Villa referencing VillaID in
Villa table
V14
V15(assuming V15 is a Villa)
DateFrom
Check-indateofthe
reservation of the villa
1stofDecember,
2020
4th of December,
2020
DateTo
Check-outdateofthe reservation of the villa
10th of December, 2020
10thof
December, 2020
Table: Payment
Column-Name
Comments/Description
Sample Data
PaymentID [PK]
A unique identifier for the Payment
P300
ReservationID [FK]
A unique identifier for the Reservation referencingReservationIDin
Reservation table
R321
PaymentDate
The date of the payment
13th of June, 2020
PaymentAmount
The amount paid under a reservation (Decimal with two places)
3000.00
Table: Activity
Column-Name
Comments/Description
Sample Data
ActivityID [PK]
A unique identifier for the activity
A01
ActivityName
A short name for the activity
Kayaking
ActivityCost
Cost of the Activity for a person (Decimal with two places)
Max cost of an activity is $999.99
120.00
ActivityType
The type of the Activity. Values are ‘I’ for indoor activity, ‘O’ for an outdoor activity and ‘B’ for an activity that could
be both Indoor and Outdoor.
B
Table: Staff
Column-Name
Comments/Description
Sample Data
Sample Data
StaffID [PK]
A unique identifier for the staff
S1
S2
StaffName
The name of staff
Chris Hemsworth
Richard Dawkins
ManagerID [FK]
A unique identifier for the Manager referencing StaffID in Staff table
null
S1
Table: ActivityBooking
Column-Name
Comments/Description
Sample Data
ActivityID [PK,FK]
A unique identifier for the Activity
referencing ActivityID in Activity table
A01
ReservationID [PK,FK]
A unique identifier for the Reservation referencingReservationIDin
Reservation table
R321
ActivityTime [PK]
The date and time of the activity stored
as a datetime value
5th of December, 2020 at
13:30
NumPeople
The number of people that would partake in the activity
4
GuideID [FK]
A unique identifier for the Guide/Staff referencing StaffID in Staff table
S2
Table: SupportStaff
Column-Name
Comments/Description
Sample Data
RosterID [PK]
A unique identifier for the Roster of a Staff to an Activity Booking
R2351
ActivityID [FK]
A unique identifier for the Activity referencingActivityIDin
ActivityBooking table
A01
ReservationID [FK]
A unique identifier for the Reservation referencingReservationIDin
ActivityBooking table
R321
ActivityTime [FK]
The date and time of the activity stored asadatetimevaluereferencing
ActivityTime in ActivityBooking table
5th of December, 2020 at 13:30
StaffID [FK]
A unique identifier for the Support Staff
referencing StaffID in Staff table
S1
HoursNeeded
The number of hours the support staff is needed for the activity booking
4
Table: Package
Column-Name
Comments/Description
Sample Data
Sample Data
PackageActivityID [PK, FK]
A unique identifier for the Package
Activity referencing ActivityID in Activity table
A01
A01
ChildActivityID [PK, FK]
Auniqueidentifierforthe
ChildActivity referencing ActivityID in Activity table
A06(assuming
A06isan activity)
A12 (assuming
A12isan activity)

Understanding how the assignment works

Please read these instructions carefully to understand how the assignment works. A sample schema (as a pdf file) is provided in the Assignment-2 folder. It should give you an idea of which tables are connected to which other tables

The assignment is broken down into 4 sections

  1. Section One: Pass (up to 65 marks)
  2. Section Two: Credit (65-75 marks)
  3. Section Three: Distinction (75-85 marks)
  4. Section Four: High Distinction (up to 100 marks)

You will have to score full marks/have a reasonable attempt to be eligible for the next section. Let’s take a couple of scenarios

Student 1 has received 63 marks with a couple of tiny errors and has proceeded to attempt all the questions in Section Two. Section Two will be graded.

Student 2 has received 52 marks with multiple errors, failure to attempt a query in Section One. Student has attempted all sections. In this case, further sections will not be considered, only receiving Section One mark of 52.

To note:

  1. Creation of tables in Sections Two to Four have no marks. This means you are expected to create tables and insert data for your queries to work. You will have to provide the codes for table creation and data insertion
  2. At least 5 records must be inserted in every table that is created in any of the sections.
  3. Execution of every query must contain at least 2 resulting rows. This means you will have to go back and insert more data, if required.
  4. All column names must have a proper alias (if they are computed fields/fields with some calculation)
  5. Remove duplicate results, wherever applicable
  6. You should be adding comments wherever applicable, especially if you have made assumptions
  7. Natural Join shall not be used within the realm of the assignment.

Task Descriptions

Section-One

This section has 13 questions. Each of the questions are worth 5 marks.

Task 1 (5 marks):

Create these tables based on the schema provided: VillaType, Villa, Villa_Reservation, Reservation, Payment and Customer.

Insert at least 5 records into each of the tables. No extra marks will be provided for adding more records in, but more records may be needed depending on the query results for different questions.

Task 2 (5 marks):

Write a query to print all the details (ID, Name, Cost) of the villa. Note that the cost must be prefixed with a '$' sign. Sort the records in order of price with the most expensive villa at the top of the list.

Task 3 (5 marks):

Write a query to print the reservation details (VillaID, check in and out dates, along with the number of days) that each of the villas in the reservation are reserved for.

Task 4 (5 marks):

Write a query to print the all details (Name, Cost) of the villa if they are two-bedroom villas. You will need to have the phrase ‘two-bedroom’ in the description of villa type.

Task 5 (5 marks):

Write a query to print the ReservationID if the reservation was paid for within 5 days from the date of reservation. Please ensure no duplicate results are included.

Task 6 (5 marks):

Using a subquery, print Customer names and phone numbers, if they have made a reservation (reservation date) within the last 6 months calculated from today

(Today here implies the date the query is run. Must not hardcode the date)

Task 7 (5 marks):

Rewrite Task 6 using a Join.

Task 8 (5 marks):

Write a query to print all the names of customers who have booked villas that costs less than $1000 per day. Please ensure no duplicate results are included in the result.

Task 9 (5 marks):

Write a query to print the total amount of payments that have been made for each reservation. Sort the records in order of the total payments made with the most paid reservations at the top of the list. (UPDATED)

Task 10 (5 marks):

Write a query to print the reservation details (ID, Date) along with the number of villas that have been booked for each reservation, but only show the reservation details if the number of villas reserved are more than one.

Task 11 (5 marks):

Write a query to print the details of all villas which have never been booked

Task 12 (5 marks):

Write a query to print the details of any payment that is more $1500. Only include the payments that have been made in either January of any year or in any months in the year of 2020 or the year of 2018. Sort the results by payment amount in descending order.

Task 13 (5 marks):

Write a query to print the details of any payment that has been made on a reservation of a one-bedroom villa by a customer whose surname begins with J.

Section-Two

This section has 2 questions. Each of the questions are worth 5 marks. You may be eligible for partial marks if there are errors in your answers. To be able to answer the questions, you will have to create and populate the following tables based on the schema provided: Activity, Staff, ActivityBooking

Task 14 (5 marks):

Write a query to print the ReservationID and the total amount that it has costed (Cost of villa per night * number of days it has been reserved for). Only include reservations that exceed a total amount of $10,000.

Task 15 (5 marks):

Write a query to print the names of the customers who have made bookings of outdoor activities those of which have a cost that is strictly less than the average cost of outdoor activities. The average should include both outdoor and package activities.

Section-Three

This section has 2 questions. Each of the questions are worth 5 marks. You may be eligible for partial marks if there are errors in your answers. To be able to answer the questions, you will have to create and populate the ‘SupportStaff’ table based on the schema provided.

Task 16 (5 marks):

Write a query to print the names of the customers and all the activities they have booked in the afternoon (after mid-day and before 4pm) along with the names of the guides. Only include guides who are Managers.

Task 17 (5 marks):

Write a query to print the names of Staff and their managers, only if the managers manage 2 staff or more

Section-Four

This section has 2 questions. Each of the questions are worth 5 marks. You may be eligible for partial marks if there are errors in your answers. To be able to answer the questions, you will have to create the ‘Package’ table based on the schema provided

Task 18 (5 marks):

Write a query to print the details of activity booking (ActivityID, ReservationID, Time of the Activity Reservation, Name of the Activity) and the names of staff involved in the activities. This should involve the guide and all the support staff involved

Task 19 (5 marks):

List the details of package activities (id, name and cost) along with the details (id, name and cost) of its least expensive sub activities (UPDATED)

Task 20 (5 marks):

Write a query to list the details of reservation (id) along with the total cost (in currency format) of both activities calculated from the booking (number of people * the cost of an activity) and villa reservation (number of days* Cost per night) for each of the villa) for each of the reservation. If the reservation doesn't involve activity booking, 0 must be displayed then 0 must be displayed as the cost. L

Show More

Answer :

For solution, connect with our online professionals.