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
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:
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.
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.
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
For solution, connect with our online professionals.