INFO90002
Assignment 2 s2 © 2023 The University of Melbourne v1b 1
INFO90002 S2 2023
Assignment 2 - SQL
Due: see LMS
Submission: Via the Canvas LMS
Weighting: 10% of your total assessment (Graded out of 200 marks)
Dog Behaviour Specialists
Dog Behaviour Specialists is a medium size business operating at several venues positioned near dog
parks. Every staff member is associated with a specific venue where they provide dog training
services from puppy school basics to addressing behavioural issues to running events for dogs and
owners to socialise.
Figure 1. Dog Behaviour Specialists Data Model
INFO90002
Assignment 2 s2 © 2023 The University of Melbourne v1b 2
Instructions
1. Rename all tables to have the last 4 digits in table names the same as the last 4 digits of your
student ID
• Download the files named A2_2023s2_DogTraining_Local.sql and
A2_2023s2_DogTraining_ENG.sql from the LMS.
• Open each file in a text editor, e.g. in MySQL Workbench, Notepad, Notepad++ or some
other Text editor
• Change all occurrences of 9999 to the last 4 digits of your student ID (one way to do this is
to perform a find and replace). For example, if your student ID is 12349876, your tables will
get renamed as venue9876, owner9876, dog9876, etc.
Make sure you rename tables in BOTH files.
Note, if renaming is not done, you cannot get full marks, a heavy penalty of 50% will be
applied.
2. Run the script on the engineering server (and / or on your local MySQL server). This will create
the Dog Behaviour Specialists database with all required tables and populate them with data.
3. Write the following SQL statement and execute it.
SELECT '123459999' as StuID, venue9999.*
FROM venue9999
(Note that in the above statement 123459999 should be replaced with your Student ID
and both occurrences of 9999 would be last 4 digits of your student ID, as in step 1 above)
Notice how each row contains your student id and all attributes from the depot table.
You are expected to include your student ID in all queries
It is expected that
• your script will produce correct results
• your code meets standards of quality as discussed in lectures
• your code runs on the university engineering server
Write a single SQL statement to answer the following questions. Do not use inline views / schema on
read, views unless explicitly instructed to do so. Views, inline views and schema on read for Q1-Q9
will earn 0 marks.
INFO90002
Assignment 2 s2 © 2023 The University of Melbourne v1b 3
If your result set is less than 10 rows, show ALL results. If it is longer, show at least 10 rows (a couple
of rows extra is not a problem). Specify how many results were returned in red font under the
screenshot.
Questions
1. List all events that were conducted at the venues in the suburbs starting with A. Your
results should show Suburb and postcode of the venue, as well as description and date
of the event. Order the results alphabetically by suburb and by event date within each
suburb.
If your results show more than one suburb, scroll down to make a screenshot that
includes at least 2 suburbs.
(10 marks)
2. List all services that has never been requested at the venue located in Keilor Downs.
Your list should show package name and individual lesson cost ordered alphabetically
by package name.
(20 marks)
3. List all events in order of their popularity (from most popular to least popular).
Popularity is measured by the number of attendees (dogs) across all venues. So the list
should show event description and corresponding total number of attending dogs in
the order of highest to lowest numbers.
(15 marks)
4. List trainers who provided more than 20 sessions/bookings from highest number of
sessions to the lowest. Your list should show trainers full name as a combination of
first and last name, suburb and number of sessions that trainer provided.
(20 marks)
5. List all venues and total for all lessons they provided in the previous year using Group
lesson cost data. The report is supposed to include only lessons that offer group mode.
Your list should show venue ID, suburb and the total amount in the ascending order of
total amounts. Previous year is currently 2022, but the query should be valid whenever
it is run in the future.
(20 marks)
6. List all owners and their dogs who booked any type of obedience lesson (lesson that
has the term “obedience” in its package name) at the Caulfield venue. The list should
show owner full name as a combination of first and then last name, dog name and
package name in the alphabetical order of owner last name.
(20 marks)
7. List all owners and their dogs who participated in lessons at Brighton venue in the
second quarter of 2023. Second quarter includes April, May, June. The results should
display owner full name as first and last, dog name and breed, and lesson date,
ordered by lesson date.
(15 marks)
INFO90002
Assignment 2 s2 © 2023 The University of Melbourne v1b 4
8. List all owners who attended at least one event or had at least one booking for their
dog(s) in January of the previous year, whatever that year might be when the query is
run (currently 2022). Your results should show Owner full name (as first and last) and
appointment or attendance date. Duplicated records (if any) are allowed.
(20 marks)
9. Which dogs attended National Dog Day in the past 3 years (i.e. current year, previous
year and the one before that)? Your list should display dog name and breed and should
be sorted alphabetically by breed, then dog’s name. The query should be useful in the
future years so that it shows results to the three years relative to when it is run.
(25 marks)
10.
a. Write the SQL DDL to create a view that lists the dog breed and number of
bookings for the service “Advanced Obedience" across all venues.
Do not include StuID in this view.
In addition to the code, you need to provide 2 screenshots
– the list of tables and views from the left pane of Workbench showing your created
view and
– the results of running SELECT from your View (we recommend explicitly showing
SELECT statement used to create the View).
(20 marks)
b. Using the View you created in Task 10a, list the breeds with the highest number
of bookings. Your query needs to display breed name and the number of lessons
booked.
You must include your student ID in task b.
(15 marks)
Submission Details:
Submit a single PDF showing your answers to all questions
Specify your student name and ID at the top of your answer document.
Formatting requirements for your submission
For each question, present an answer in the following format:
• Show the question number and question in black text.
• Show your answer (the SQL statement) in blue text (DO NOT use a screen shot)
• Show a screenshot of the result from Workbench. If your result set is less than 10
rows, show ALL results. If it is longer, show at least 10 rows (few rows extra is allowed).
• Show how many rows were actually returned, in red text.
• Remember to include your student ID (the only exception is q.10a).
INFO90002
Assignment 2 s2 © 2023 The University of Melbourne v1b 5
• Show each query on a separate page.
• You must not use in-line views, schema on read, views for questions unless explicitly
instructed to do so (Q10 only).
• We may be testing your code on the engineering server so make sure you install the
database and test your answers on the “engineering” MySQL server.
Example:
Q.XX List all services where the recommended number of lessons is 4. The output should show
name of the package and recommended number of lessons.
SELECT '123459999' as StuID, PackageName, RecommendedNumSessions
FROM services9999
WHERE RecommendedNumSessions=4
2 rows
IMPORTANT: ATTEMPT EVERY QUESTION!
Ensure your scripts run on the engineering unimelb server
APPENDIX A. Dog Behaviour Specialists Business Rules
Dog owners
A dog owner must own at least one dog.
Trainers
A trainer works at one venue only.
Attendance for Events and Lessons Bookings
An owner may attend an event with one dog only. If they have more than one dog, a separate event
attendance is recorded for each dog.
Lessons are booked for dogs so again if an owner has more than one dog, each dog requires a
separate lesson booking.
INFO90002
A2 S2^2021 INFO90002 © The University of Melbourne 6
APPENDIX B. Sample Marking Schema
In this sample marking rubric Questions 1 and 2 are referring to a different case study. They each are worth 10 marks out of 200. Please attempt every
question. The approach is as important as the result.
Q1
(10)
SELECT (2)
first_name,
last_name,
job_title,
dept_name
0.5 marks
each
FROM (2)
staff
departments
1 mark
each
JOIN (4)
LEFT | RIGHT
OUTER JOIN
4 - correct
3 - natural join |
inner join
2 - left | right join
without OUTER ;
incorrect join
condition
1 - Cartesian or
any other join
RESULT (2)
118 rows
Kimberly
Grant must
be in the set
2 correct
0 other
Q2
(10)
SELECT (2)
country_name
(1 mark)
count(staff_id)
(2 marks)
(alias ok too)
FROM + JOIN (2)
INNER JOIN | NATURAL
JOIN
2 marks
outer joins for no staff
1 mark
GROUP BY
(2 marks)
ORDER BY
(2 marks)
RESULT
(2 marks)
correct order
(1 marks)
incorrect order
unordered but
correct data
(0 marks)
incorrect
result; no
result
Any questions? Check the Assignment 2 LMS Ed Discussion forum for suggestions and hints.