INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2

© 2019 The University of Melbourne
Assignment 2: SQL
INDIVIDUAL PROJECT (10%) Case
You are working on an exercise tracking system for a chain of gyms found throughout Australia. The
system allows gym members to wear a device that records their movements while running, to
encourage them to exercise more. Your job is to write SQL queries that analyse the data. You have
been provided with some test data to help you refine your queries. The data model is as follows:
Data recorded by the system
There are several gyms: each has a
name and is located in a particular city.
Each gym member or user wears a
device that measures their location
once per minute while they are
running. The location data are sent to
our database for analysis. For example,
the map on the left shows the locations
that have been recorded by user Alice.
Most users are registered members of the gym. But we also offer the tracking program to members of
the public who are not registered at gym members. Setup Script
To set up the database in your MySQL server, download the file asst2-2019s2-setup.sql from LMS
and run it in Workbench. This script creates the database tables and populates them with test data.
Note the comments near the start of the script. You can run this script in two ways, depending on
whether you run it on the UniMelb server or your own server. If it is your own server, you will want
to uncomment some lines near the top, so that you create a new schema to store the tables in. You
can’t create new schemas on the UniMelb server.

INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2

© 2019 The University of Melbourne
The SQL queries you must write
Over the page are listed 10 questions for you to answer. Write one SQL statement to answer each
question. Do not use views or temporary tables to answer the questions.
Beside each question is a maximum mark which reflects the difficulty of the question.
Your total score will be scaled to 10% of your overall marks in the subject.
Your SQL queries should use the same inputs that are used in the questions. For example, your
answer to a question that mentions “Alice” should involve a filter on the word “Alice”.
Make sure your code and output are readable – this will affect your mark. Where it would improve
readability, order your output and use aliases. Format large numbers and fractions appropriately.
Assessment
Your work will be assessed according to three criteria:
• Correctness of output (70%)
• Simplicity of code (20%)
• Correctness of formatting (10%)
The “simplicity” requirement means that your code should not be unnecessarily complex. For
example, a query that produces correct output, but say, joins more tables than is necessary, may not
achieve full marks, even though it produces the right output.
Location Data
We use a precision of 4 decimal places: for example, the Doug McDonell building is at
longitude 144.9630, latitude -37.7990 .
Calculating Distance
Calculating the distance between two points P1 and P2 requires a complex formula which you can
read about at https://en.wikipedia.org/wiki/Haversine_formula . For the purposes of this assignment
you can use the following simplified formula based on the Pythagorean theorem, which works well
enough in southern Australia:
distance in km = sqrt( (P1.latitude – P2.latitude)^2 + (P1.longitude – P2.longitude)^2 ) * 100
INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2

© 2019 The University of Melbourne
The Questions (marks are in brackets after each question)
1. Print each user’s name, along with the number of times they have
recorded a location. (1)
2. How many cities are in the same state as Melbourne?
(Don’t count Melbourne in your answer.) (1)
3. List the names of any members of Academia gym who have been
north of Brunswick gym. (1)
4. How many users are registered with gyms in the state of Vic? (1)
5. What percentage of the total number of users are not affiliated
with gyms? (1)
6. How much time elapsed between the first and last recorded locations
of the user with id 4? (2)
7. Print as two columns: the average number of locations recorded by
registered users, and the average number of locations recorded by
unregistered users. (3)
8. List the names of users who have run within 100m of the Doug McDonell
building. (DMD is at longitude 144.9630, latitude -37.7990 .) (3)
9. What is the distance between the northern-most and southern-most
locations to which Alice has run? (3)
10. Show the total distance that Alice has run. Calculate this by summing
the individual distances between each successive pair of locations. (4)
INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2

© 2019 The University of Melbourne
Submission
Submit a single PDF showing your ten answers in the format below, to LMS by midnight Friday at
the end of week 8. Submit also an SQL (text only) file containing your queries.
Ensure that you place your student number at the top of every page of your submission.
In the PDF, 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 (not a screen shot)
• Show a screenshot from Workbench showing output of 10 or fewer lines.
• Show how many rows were returned, in red text
For example:
7. List all users with the last name ‘Altman’

SELECT *
FROM User
WHERE lastName = 'Altman';

5 Rows Returned

SQL queries must be formatted in an easy-to-read manner. This means writing keywords in upper-
case, placing most clauses on new lines, and indenting subqueries. For example, this is acceptable:
SELECT DISTINCT saleId
FROM Sale
WHERE departmentID IN
(SELECT departmentId FROM Department
WHERE floor = 2);

whereas this is not acceptable:
select distinct saleId FROM Sale where departmentID IN (SELECT departmentId from
DEPARTMENT WHERE Floor = 2);;
INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2

© 2019 The University of Melbourne
Academic Honesty
This assignment must be your own work. Plagiarism - the copying of another's work without proper
acknowledgment - is not permitted. Nor is allowing another person to copy your work. Work
submitted for assessment purposes must be the independent work of the student concerned. Please
refer to http://academichonesty.unimelb.edu.au/ for details about academic honesty.
Academic misconduct occurs when students portray someone else's work as their own. There are
many ways in which academic misconduct can occur. Some of these are:
• Sham Paraphrasing: Material copied verbatim from text, with source acknowledged in-line,
but represented as paraphrased.
• Illicit Paraphrasing: Material paraphrased from text without in-line acknowledgement of
source.
• Other Plagiarism: Material copied from another student's assignment with the knowledge of
the other student.
• Verbatim Copying: Material copied verbatim from text without in-line acknowledgement of
the source.
• Recycling: Same assignment submitted more than once for different subjects.
• Ghost Writing: Assignment written by third party and represented as own work.
• Purloining: Assignment copied from another student's assignment or other person's papers
without that persons knowledge.
The University is committed to graduating students with "a profound respect for truth, and for the
ethics of scholarship... we want our graduates to be capable of independent thought, to be able to do
their own work, and to know how to acknowledge the work of others" (Professor Peter McPhee).
As such, the university takes a dim view of students who are not able to correctly acknowledge the
work of others, or who try to pass this work off as their own.
All students should check the web site http://academichonesty.unimelb.edu.au/ which provides
practical advice to students about how to avoid academic misconduct.