Case

project 2: SQL

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:

1536139747103147.png

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.

image.pngEach 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)


4. List each student's name. For those who are in a group, list also the name of their group. (2)


5. For any groups that have more than 3 students, list the group’s id, name and number of students (3)


6. Is student “Alice Smith” free at lunch on Wednesdays? (3)


7. List all times when students 10001 and 10002 are both free. (4)


8. For each group, list the group id and name of the student whose family name is alphabetically first in the group. (4)


9. Which students are free on Wednesdays between 10am and 12 noon? Show their ids and names. (5)


10. Are the members of 'WeLoveDb' all free on Wednesday at 10am? (5)



Submission

Submit a single PDF showing your ten answers to LMS by midnight on the due date of Monday 10th September, at the start of week 8.

Ensure that you place your student number at the top of every page of 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 (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';


image.png


5 Rows Returned



SQL queries must be formatted in an easy-to-read manner. This means writing keywords in all-caps, 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);;





Academic Honesty

This project 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 project with the knowledge of the other student.

· Verbatim Copying: Material copied verbatim from text without in-line acknowledgement of the source.

· Recycling: Same project submitted more than once for different subjects.

· Ghost Writing: project written by third party and represented as own work.

· Purloining: project copied from another student's project 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.