MySQL健身房连锁锻炼跟踪系统的SQL查询

INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2
Assignment 2: SQL INDIVIDUAL PROJECT (10%)
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
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.
Case
Setup Script
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.
© 2019 The University of Melbourne
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.

The SQL queries you must write
INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2
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
© 2019 The University of Melbourne

The Questions
(marks are in brackets after each question)
INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2
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)
© 2019 The University of Melbourne

Submission
INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2
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);;
© 2019 The University of Melbourne

INFO90002 Database Systems and Information Modelling Assignment 2, 2019 s2
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.
© 2019 The University of Melbourne