CSCI 2141 Intro to Database Systems Winter-2018
Assignment 2 [50 marks]
Submission Deadline: SATURDAY, MARCH 03
PROBLEM STATEMENT:
The ERD of the “Tourista” database is shown below. You can use the database you created in
Assignment 1, or preferably, use the provided script. for creation of the database and populating
its tables in case you have modified the previous assignment’s database. Use this database to
answer the Assignment questions that follow.
Tourista Database:
The complete ERD for the database is provided in Figure below:
SUBMISSION INSTRUCTIONS:
• Use this document as an answer template.
• Complete and submit this document with your answers filled in (scripts and screenshots of
the results of each task) through Brightspace, before the submission deadline.
• Remember to rename the document with your ID (e.g. B00123456.doc or docx or pdf)
• Doing this assignment on your own will immensely improve your knowledge of SQL (The more
you struggle with it, the more you learn. Do ask for help if you get “really” stuck)
Dalhousie University
Faculty of Computer Science
ASSIGNMENT TASKS:
Alice is the CEO of Tourista, and she is impressed by the work you have done to design and create
the database. Now she would like to make use of this data for informational and decision-making
purposes. She would like to see several reports (or lists), some of which may be quite complex.
For each of the report Alice wants, write the SQL statement that generates the required data (Note
that in some cases, the sample output is provided though incomplete). Then paste a screenshot of
your result (unless stated otherwise).
For this assignment, only use JOINs for joining tables. Do not use the old-style. join (i.e. joining
through the WHERE clause).
Assignment Questions
Warming Up:
1. Show all data from the Cottage and Booking tables. Use inner join for this query. Do NOT
paste a screenshot for this question. How many rows are returned? [2]
2. Repeat Question 2 above but this time use left outer join for your query. Do NOT paste a
screenshot for this question. How many rows are returned? How is the result different
from that of question 2? Explain the reason for this difference, if any. [3]
Lets Get to Business:
3. Alice would like to see a list of the name of the region(s) that do not have any cottages as
Tourista looks to expand its business in the area. [2]
4. Tourista’s customer services department informs her that people like to book cottages that
have a pool. Alice now wants you to provide her the names of all cottages that do not have a
pool in them [3]
5. Alice also wants to see the maximum price that each of her cottages are booked for. You
decide to list all cottage ids with their maximum booking price. [2]
6. Alice now wants to see a list of all cottages that have never been booked, along with their
region names. She points out to you (rightly so) that id’s do not make much sense to her, so
instead of cottage_id, she would rather prefer names that she can remember. (i.e. cottage
name and region name) [3]
7. Tourista’s CEO is also concerned that cottages in some regions are not very profitable. She
wants a list of all region names where none of the cottages were ever booked for a price
over 500. [3]
8. Alice also wants to see a list containing cottage_id, cottage_name, booking_date and
booking_price of all bookings in which the booking_price was greater than the average
booking_price for that cottage. [3]
9. Alice also wants to see how many times each of Tourista’s cottages have been booked. This
will help her to separate popular cottages from the not-so-popular ones. She wants you to
provide a list the names of all cottages, the number of times they have been booked (0 if
never booked) and their average booking price. It would make more sense to her if the
output is sorted by number of bookings in descending order and then by cottage name in
ascending order. [3]
10. Alice wonders if there are customers who have never booked any of Tourista’s cottages.
She asks you to provide the full name (first and last name together) of such customers. She
can then direct the marketing department to contact those customers directly. [2]
Rough Waters Ahead:
11. Tourista is also rolling out a loyalty program that may offer discount for customers in
future. For now, Alice wants to know who her loyal customers are, so she asks you to
provide her a list of customers containing their full name (i.e. first and last name together)
along with the number of cottages they booked in 2017. For now she does not want to
include customers that had no bookings in 2017. [3]
(Hint: Use the YEAR(…) date-time function for filtering your results)
12. The loyalty program is being rolled out by Tourista’s Marketing team, so they want to find
out the customers who have booked with them the longest. They need a list containing full
name (first and last name together) and the number of days that elapsed between their
first and last bookings for customers with at least 2 bookings. [3]
13. Alice is also concerned that some cottages may not have sufficient facilities to attract
customers. She wants you to prepare a list containing cottage names, their region name and
the number of facilities in each cottage, including those cottages that have 0 facilities. [3]
14. To determine which facilities are more popular, Alice wants to see a list containing the
facility name and the number of times that cottages containing that facility have ever been
booked. [3]
Only the Best Can Survive
15. Tourista needs to pay tax on all its bookings made in 2017. The tax rate for booking price
under 500 is 10% while it is 20% for booking price of 500 or more. Generate a report
containing the id and name of cottage, its booking price(s), the applicable percentage of tax,
and the amount of tax. A sample output is shown below. [3]
16. Alice is often confused by the date and fails to notice whether the first number represents
the month or the day. She hence asks you (the database developer) to provide her a report
that shows the customer’s full name (first and last name together), the cottage name, the
region name of the cottage, the date of booking (in the format 15th of December, 2017), the
booking price, and the difference between the booking price of that cottage and the average
booking price of all cottages (to the nearest 1 decimal place in the format $yy.y e.g. 25.6),
arranged in descending order by full name. A sample output is given below. [3]
17. Alice also wants to know which months are busier than others and how much money
Tourista is making in those months. She wants a report that provides a monthly
breakdown of the number of bookings, the total booking price and the average booking
price correct to two decimal places. (A sample output is shown below. Note the column
headings) [3]
Nightmare!
18. Noting from your report that Tourista is charging less in busier months, Alice decides to
increase the booking price of all cottages by 20% based on the current average booking
price for that cottage. In case the cottage has never been booked before, she wants the
booking price to be shown as 499. She wants to see a report listing each cottage, the total
number of times it has been booked, the average price of all its bookings, and the new price
based on a 20% increment of the average price, rounded off to the nearest whole number.
(A sample output is shown below. Note the column headings) [3]
Select cottage_name as Name, count(booking_id) as Booked, avg(booking_price) as
'Average Price', case when avg(Booking_Price) is null then 499 else
round(1.2*avg(Booking_Price)) end as 'New Price' from cottage left join booking
using(cottage_id) group by cottage_id order by Booked desc, cottage_name;
Challenge: [BONUS 3 marks, subject to total not exceeding 50]
19. Alice is running a new marketing campaign and expects bookings to rise in February. Based
on the new price calculated in Q17 above, Alice wants to see projections for the month of
February, assuming that the cottages will be booked the same number of times in February
as they were in total before. In addition, she expects cottages that were not booked ever
before to be booked once in February. She wants you prepare a report that includes the
following columns for each cottage that Tourista owns:
(i) Name of the cottage
(ii) Total number of bookings for that cottage so far
(iii) Total price of all bookings for that cottage so far
(iv) Average price all bookings for that cottage so far
(v) New price that is 20% more than the average price based on column (iv) above and
rounded to the nearest whole number, or 499 if it was not booked before
(vi) The expected number of bookings in February based on her assumptions
(vii) Total price of all bookings for that cottage based on the new price and expected
number of bookings in February
(viii) Profit that Tourista stands to make on that cottage (column vii – column iii) if her
assumptions are correct.
(A sample output is shown below. Note the column headings)