COMP1350 Introduction to Database Design and Management Assignment 2 Answer

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

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