INFO20003 A2 S2 2019 1 INFO20003 Semester 2, 2019 Assignment 2: SQL Due: 6:00pm Friday 20th of September Submission: Via LMS https://lms.unimelb.edu.au Wellbeing Application You are the SQL programmer for a mental wellbeing app, which allows users to do two types of things. The first is to participate in a Twitter-style social network. The second is to do online units called Steps, which are interactive therapy modules designed to exercise and develop a range of psychological skills. How the app works When users register into the system, a record is created in the ‘user’ table. Upon registering, a user can follow other users to see their posts and activity. If user x (following) starts following user y (followed), an appropriate entry is created in the ‘user_follow’ table (x following y does not necessarily mean that y follows x). The weight attribute of this table holds an integer that reflects the extent to which x follows y; for example, this number would increase the more x likes or comments on posts created by y. To help connect users, each user can also select, from a list stored in the ‘interest’ table, interests that they have (e.g. movies, music, books). For each interest that a user selects, a record is created in the associative table ‘user_interest’. On the therapy side of this app, users can do online modules called Steps. Different Steps take different amounts of time to complete and users can do a Step in one sitting or return to a Step and complete it later on. Sometimes they might not finish a Step and will instead move on to something else. Also, they can do any given Step as many times as they would like and each time, they can rate how it made them feel (1 being the lowest and 5 being the highest). As soon as user x makes a start on doing Step z, an entry is created in the ‘step_taken’ table to record the fact that x started doing z. The timestamp of this start is also automatically recorded in the ‘when_started’ column. If the user successfully completes the Step, the datetime of completion is recorded in the ‘when_finished’ column. INFO20003 A2 S2 2019 2 Steps are also categorised under themes, which are used to indicate what types of things a Step might help with. For example, a Step that helps with mindfulness would be categorised under the ‘Mindfulness’ theme. A given step can be categorised under more than one themes. Data Model Setup Script A dataset is provided against which you can test your solutions to the assignment. To set up the dataset, download the file wellbeingapp.sql from the Assessments folder on LMS and run it in MySQL Workbench. This script creates the database tables and populates them with data. The script is designed to run against your account on the Engineering IT server (info20003db.eng.unimelb.edu.au). If you want to install the schema on your own MySQL Server installation, uncomment the BYOD section at the beginning of the script. INFO20003 A2 S2 2019 3 The SQL Tasks In this section are listed 10 questions for you to answer. Write one (single) SQL statement per question. Subqueries and nesting are allowed within a single SQL statement – however, you will be penalized for writing overly complicated SQL statements. DO NOT USE VIEWS to answer questions. 1. Find all the ongoing/unfinished steps. Display the title of these steps and full names of the users who are taking these steps. (1 mark) 2. List the themes and the number of the steps associated with these themes. Display the theme name and number of associated steps sorted in descending order. (1 mark) 3. Which step is the least popular based on the average rating given by users? Display the title and ID of the step and its average rating (formatted to 2 decimal places). Only include those steps which are rated by at least one user. (1 mark) 4. Find the steps that are taken the greatest number of times. Display the ID, title and count of the times the step has been taken. In case of ties, display all the steps with the same number of times taken. (2 marks) 5. Who is the most followed user between age of 15 and 18? Display the age (as an integer), first name, and last name of such user along with the number of followers. (2 marks) Hint: The function TIMESTAMPDIFF can be used to subtract two timestamps. The function CURDATE returns current date. 6. Find all steps that are never taken or are taken exactly once? Display the id and title of these steps along with the indication how many times the step has been taken (0 or 1). (2 marks) 7. Find users who started taking step ‘Doing and being’ after they had started the step ‘Panic’ but have never completed ‘Panic’. Display the user ID, first name and last name. (2 marks) 8. What finished steps were completed both by a user with first name "Alice" and a user with first name "Bob"? Display the ID and title of such steps along with the number of times each user has completed these steps. (3 marks) 9. Find the top two users with the highest number of interests. For those two users, find out the common steps taken by both of them. Display the titles of the common steps they have taken and the number of times those steps are taken by each user. (3 marks) 10. For each user taking a step, calculate how many other users have taken the same step. We are only interested in the cases where the step is performed by at least 5 other users. Display the user ID, number of other users (at least 5 other users) who are taking the same step and the title of the taken step. (3 marks) INFO20003 A2 S2 2019 4 Submission Instructions Submit a single PDF showing your answers to all questions to the Assessment page on LMS by 6pm on the due date of Friday 20th of September. Name your file 987654.pdf, where 987654 corresponds to YOUR student id. Other formatting requirements are listed below. 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 screenshot). • Show a screenshot from Workbench containing the output of the query. o If the query returns more than 10 rows, take a screenshot of only the first 10 rows. • Show how many rows were actually returned in red text. • Show each query on a separate page. Example: QXX. List the names of the themes. SELECT name FROM theme; 7 rows returned INFO20003 A2 S2 2019 5 Requesting a Submission Deadline Extension If you need an extension due to a valid (medical) reason, you will need to provide evidence to support your request by 9pm, Thursday 19th of September. Medical certificates need to be at least two days in length. To request an extension: 1. Email Farah Khan (
[email protected]) from your university email address, supplying your student ID, the extension request and supporting evidence. 2. If your submission deadline extension is granted you will receive an email reply granting the new submission date. Do not lose this email! Replies may take up to 12 hours, so please be patient. Reminder: INFO20003 Hurdle Requirements To pass INFO20003 you must pass two hurdles: • Hurdle 1: Obtain at least 50% (15/30) or higher for the three assignments (each worth 10%) • Hurdle 2: Obtain a grade of 50% (35/70) or higher for the End of Semester Exam Therefore, it is our recommendation to students that you attempt every assignment and every question in the exam. GOOD LUCK!
