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.

Questions

1.     List staff members in each branch. The list should show branch name, staff last and first name, staff email, ordered by branch, 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 name. (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)

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.

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)

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

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.