Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
INDIVIDUAL PROJECT (10%)
You are the SQL programmer for an app that supports student group work. The app allows students to form groups and then share their hours of availability. Your job is to write SQL queries that allow the app to work. The data model is as follows:
Figure 1: data model
How the app will be used
First, the id and names of the university’s 10,000 students are entered in the system.
Each individual student can then record their weekly hours-of – availability to work on the project; i.e., when they are free each week, using the user interface on the left.
The UI shows the days of the working week along the top and the work hours from 9am to 5pm down the left-hand side. The student clicks to highlight their free hours, which are recorded in the Availability table.
Students can then form and name their groups, recording their group details in the system.
Figure 2: User interface for entering free times
The Calendar table restricts the times of interest to the working week, and allows us to label some special times, e.g. “lunch”.
Setup Script
To set up the database in your MySQL server, download the file asst2-2018s2-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. There are actually 2 versions of the script – it is different 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 a new schema on the UniMelb server.
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, a question that mentions “lunch” should involve a search of the calendar for times marked “lunch”.
You should answer “yes/no” questions by printing ‘yes’ or ‘no’, or ‘1’ or 0’.
An example of a yes/no question is “Is student 10001 free at 10am on Wednesdays?” (MySQL prints True as ‘1’ and False as ‘0’. To see this, run the statement: SELECT 1 = 2; )
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.
The Questions
(marks are in brackets)
1. What is the longest student name? (The length of a student’s name is the sum of the lengths of their given and family names) (1)
2. List the names of students who have not yet entered any free times. (1)
3. Which students are free on Wednesday at 10am? (show id and name) (2)