INFO90002
Assignment 2 s1 © 2023 The University of Melbourne v1c 1
INFO90002 S1 2023
Assignment 2 - SQL
Due: see LMS
Submission: Via the Canvas LMS
Weighting: 10% of your total assessment (Graded out of 200 marks)
Elite Dog Grooming
Elite Dog Grooming is a medium size company that currently has several franchise branches. They
provide grooming services as requested by dog owners. They mainly focus on pure bred dogs. Dog
owners can book grooming appointments for their dogs at any branch, they don’t need to stay loyal
to one branch. They can buy a membership at any branch, even at more than one branch. Having a
membership entitles dog owners for a discount on all services. Each groomer works for a particular
branch.
Figure 1. Elite Dog Grooming Data Model
INFO90002
Assignment 2 s1 © 2023 The University of Melbourne v1c 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 file named Dog_Grooming.sql from the LMS.
• Open this file in a text editor, e.g. in MySQL Workbench, 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 branch9876, groomer9876, dog9876, etc.
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/university server (and / or on your local MySQL server). This
will create the dog grooming database with all required tables and populate them with data.
3. Write the following SQL statement and execute it.
SELECT '123459999' as StuID, branch9999.*
FROM branch9999
(Note that in the above statement 123459999 should be replaced with your Student ID
and all occurrences of 9999 would be the 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 branch table.
You are expected to include your student ID in all queries as shown in the above example.
It is expected that
• your script will produce correct results
• your code meets standards of quality as discussed in lectures
• your scripts will run on the engineering/university server and there will be evidence that you
tested it there
Write a single SQL statement for each of the following questions. Do not use inline views / schema
on read unless explicitly instructed to do so. Views, inline views and schema on read for Q1-Q10 will
earn 0 marks.
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.
INFO90002
Assignment 2 s1 © 2023 The University of Melbourne v1c 3
Questions
1. List staff members in each branch. The list should show branch name, staff last and
first name, staff email, ordered by branch name, then by staff last name.
(10 marks)
2. List all owners and the memberships they have with the details of branches – only
active memberships should be listed. Your list should show owner ID, name (first, then
last), their membership ID, branch name. List the result in alphabetical order of branch
names, then by owner last and first name.
(15 marks)
3. How many appointments have been booked at each branch in the previous year
(whatever that year happens to be at the time of running the report)? The results
should display branch ID, branch name and number of appointments sorted
alphabetically by branch name.
(15 marks)
4. How many times was each service requested in each branch? The results should
display branch ID, service description and cost, number of times it was requested,
sorted by branch ID and then alphabetically by service description.
(15 marks)
5. List the dog owners and number of dogs they have for owners with at least 2 dogs. Dog
owner’s details should include name as one full name and mobile. List results
alphabetically by owner last name.
(15 marks)
6. List all invoices paid in January of the current year, whatever the current year is. The
list should be in the order of dates and should show invoice ID, pay date, and amount
due. The query should be usable in the future years. Amount due should be before any
discounts, just the total cost of all services in each invoice.
(20 marks)
7. Modify the previous query to add owner First name and Last name as one full owner
name to the list. All other query requirements are the same.
(25 marks)
8. List all dogs that had a “Flea wash” service. Your list should show breed, dog name, full
owner name and service description. The results should be ordered alphabetically by
breed.
(15 marks)
9. Which dog breeds have never had neither nail trim nor any type of wash service
requested? Your list should show dog breeds in alphabetical order.
(20 marks)
INFO90002
Assignment 2 s1 © 2023 The University of Melbourne v1c 4
10. List service ID, description and cost for all services that have not been requested in the
current year, whatever that year may be. The list must be ordered by cost. This query
must be useful in the future years.
(15 marks)
11.
a. Write the SQL DDL to create a view that lists the branch id, branch name, and
total of costs of all services provided by each branch in the previous year
whatever this previous year is at the time of creating the view (it is 2022 this
time). This view must be useful in the future years.
Ignore membership discounts, work only with cost of services.
Do not include StuID
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.
(20 marks)
b. Using the View you created in Task 11a, display the branch with the highest
income. Your query needs to display branch name and the amount.
You must include your student ID in task b.
(15 marks)
INFO90002
Assignment 2 s1 © 2023 The University of Melbourne v1c 5
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.11a).
• 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 (Q11 only).
Example:
Q.XX List all services provided by Elite Grooming which cost $10. The output should show
description and cost.
SELECT '123459999' as StuID, description, cost
FROM service9999
WHERE cost=10;
2 Rows
IMPORTANT: ATTEMPT EVERY QUESTION!
Ensure your scripts run on the engineering unimelb server
INFO90002
Assignment 2 s1 © 2023 The University of Melbourne v1c 6
APPENDIX A. Elite Grooming Business Rules
Dog owners
A dog owner may have no membership at any branch. A dog owner can have several memberships
(one with Chadstone branch, one with Hawthorne branch, etc.)
A dog owner must own at least one dog.
Branches
A branch may have no memberships associated with it.
Staff
A groomer works for one branch only. New groomers may have never had any appointments.
Membership
A membership is active for 1 year. Close to expiry or when it expires an owner needs to pay and a
new membership record is created.
Membership status is denoted as 1 for active memberships, and 0 for the lapsed/expired ones.
Owners with an active membership at the branch where their appointment took place get a discount
recorded in the invoice.
Services
There may be services on offer that no-one has ever requested.
Appointments
Each appointment is for a particular dog with a particular groomer at a specified branch.
Each appointment will have one or more services requested.
INFO90002
A2 S2^2021 INFO90002 © The University of Melbourne 7
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
In general marks are deducted when a non-optimised solution is used or user-friendliness is not considered. For example, subqueries are used when they
can be avoided, or column names are not user-friendly.
Any questions? Check the Assignment 2 LMS Ed Discussion forum for suggestions and hints.