CSCI 2141 Intro to Database Systems Winter-2018
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]
10 ROWS
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]
14 ROWS. The left outer join has four values, their booking_id,booking_price…. is null.
An inner join of A and B gives the result of A intersect B,
An outer join of A and B gives the results of A union B,
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]
Select REGION_NAME From Region
LEFT JOIN COTTAGE
ON(cottage.Region_Code=region.Region_Code)
WHERE (Cottage_ID IS NULL)
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]
Select cottage_id,max(booking_price )From BOOKING
group by cottage_id
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]
Select Cottage_Name,Region_Name From cottage
LEFT JOIN booking
on(cottage.Cottage_ID=booking.Cottage_ID)
left join region
on(region.Region_Code=cottage.Region_Code)
where (Booking_ID is null)
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]
Select cottage_name,region_name From cottage
LEFT JOIN booking
on(cottage.Cottage_ID=booking.Cottage_ID)
left join region
on(region.Region_Code=cottage.Region_Code)
where ( (booking_price <500 or booking_price is null )and cottage_name!= 'Lagoona')
group by cottage_name
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
na
㱦⽴㸠ൡ੮⡤⥡䅴⁴⁵⁴㱤⽴㹷⡮⥴†‾†㉡の╤†††⁴††⡢⥫⁛‼㱢 ⼾㸍ഊ਼‼† Ɱ㑥㥤㤠⁴⁴⁹㱶⽵㹦൦੩⡣⥮⁴⁴†⁴䙯⁴⁹⁴†㱬⁴⼠㹣൯੮⡴⥩⁴‾††⁴†㱯⽧㹥ബਠ†⁴䙥㱡†⼰㸠൦⡣⥩† ‾ ⁔⡨☠㭯⤠⁁㱮†⽴㹯ഠੳ†㰼†⼯㸾ਊ⡦䅡⸠⁴†⁴⥩㱩†⽴㹨ൡੴ㰠⽩㹬൩ੴ㱹⽶㹥ഠ㱶†⽢㹥੮☠㬯㰾⼍㹢ooked. [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