INFS1200 Module 3 Assignment

Due: 7 May 2021 @ 04:00 PM AEST

Weighting: 15%

1. Overview

The purpose of this assignment is to test your ability to use and apply SQL concepts to complete tasks in a real-world scenario. Specifically, this assessment will examine your ability to use SQL Data Manipulation Language to return specific subsets of information which exist in a database.

This assignment can be completed either in groups of two or individually. If you chose to work in a group, your group member must be enrolled in the same course as you.

2. Submission

All submissions must be made through an electronic marking tool called Gradescope, which will also be used for providing feedback. For this assignment, you will need to submit through two different gradescope portals. The details for each are outline below:

• Autograder: For each question, you are required to submit a .sql or .txt file which contains your SQL query solution for that question (only one of these files, if you submit both, the .sql file will be graded). The file should only contain the SQL query(s), no additional text. The file should be named using the following format: q$.sql where the $ is replaced with the respective question number. For example, q1.sql, q2.sql, etc. Questions 1-9 should contain at most one query and question 10 may contain at most three queries. More details will be provided about submitting to the Gradescope autograder closer to the assignment deadline. Note that you will be able to resubmit to the autograder an unlimited number of times before the deadline.

• Document Submission: In addition, you must also record all your answers in the spaces provided in this document and submit this to Gradescope. Altering the format or layout of this document in anyway will attract penalties. The queries submitted to the autograder must be the same as the ones submitted via this document.

For group submissions, only one member should submit on Gradescope. That member must submit to both the autograder and the document submission portal and they must link their partner to the submission in Gradescope. Penalties will apply if this is not completed properly.

3. Marking

The Module 3 assignment is worth 15 course marks (of 60 course marks total for all four assignments). Marking will be primarily completed using the autograder software, however we reserve the right to change to hand marking via the document submission should the need arise. Upon submitting to the autograder, you will be able to see the results of two basics tests: (i) file existence and (ii) compilation. More details will be provided regarding how you can interpret the results of these tests and what it means for your assignment grade during practicals.

4. Plagiarism

The University has strict policies regarding plagiarism. Penalties for engaging in unacceptable behaviour can range from loss of grades in a course through to expulsion from UQ. You are required to read and understand the policies on academic integrity and plagiarism in the course profile (ECP Section 6.1).

If you have any questions regarding acceptable level of collaboration with your peers, please see either the lecturer or your tutor for guidance. Remember that ignorance is not a defence!

5. Task

For this assignment you will be presented with the simplified schema of an event management application. The goal of the application is to track both the events attended by users and relationships between users and other users. The system is then able to use this data to effectively market recommended events to users based on the events their friends have attended. You will be required to write ten SQL DDL queries which answer higher level questions about the data in this database. (Note: Your queries must compile using a MySQL DBMS).

Assignment Specification

On-the-line is a company which provides software allowing devices to connect to each other in a peer-to-peer network. They maintain a database of network connections created by their software. A simplified version of their database schema has been provided below including foreign key constraints.

Relational Schema

Device [mac, state, brand, model, serialNumber]

DeviceUser [mac, user]

Connects [host, client, timestamp, duration]

Foreign Keys

DeviceUser.mac references Device.mac

Connects.host references Device.mac

Connects.client references Device.mac

For this assignment you will be required to write SQL queries to answer to complete the following tasks. Please use the submission boxes provided to record your answers, but do not forget to submit to the autograder!