INFO20003 S2 2024 – ASSIGNMENT 2 v1.4 1
INFO20003 Semester 2, 2024
Assignment 2: SQL
Due: Week 8 - Sunday 15th September 2024, 5:59pm Melbourne Time.
Submission - Via LMS https://canvas.lms.unimelb.edu.au/
Case: “Slarc” App
“Slarc”: Super Lovely App for Requesting Communications
Description
As fellow Database experts, DOTA2 fans, and enterprising communications fans, you and your classmates have
created a new open source version of Teams/Slack called Slarc (inspired by the DOTA2 character and Slack!).
For each user, Slarc records their details such as an ID, a username, an email address, a login mechanism (which is
defined strictly as one of the following: Google, Apple, Facebook, GitHub), and a reputation score (which is an
integer from 0-100 inclusive, 100 as highly trustworthy and 0 being highly untrustworthy). Users can also upload an
image for their avatar.
Users communicate with each other by posting in channels. Each channel has an ID, name, date of creation and
optional description. When a user posts in a channel, Slarc tracks the post’s author, content and date of creation.
Users can also post a reply to an existing post, or react to a post with an emoji, of which the reaction timestamp is
recorded. There is an option for users to send attachments in a post; The system records the file size and the
dataURL of the object. Slarc automatically scans each post for any harmful content, such as swearing and not safe
for work material, and automatically restricts such posts. Similarly, it scans any attached files for potential viruses
and flags the results.
When a user's reputation becomes greater than 80, they can be promoted to become a moderator. Other users
will be able to see the date that a moderator was promoted, and their self-description if they choose to write one.
Once one becomes a moderator, they can be appointed to moderate channels, usually endorsed by another
moderator of the associated channel. Their date of appointment is also stored.
INFO20003 S2 2024 – ASSIGNMENT 2 v1.4 2
Moderators have the responsibility to ensure that posts in the channel abide by community guidelines. If they
come across an inappropriate post or one that is flagged with restricted content, they will need to investigate and
report on this case. For each case, the responsible moderator must record the case ID, give a brief explanation of
the allegation and decide on whether it requires a consequential disciplinary action. The date of allegation will be
automatically recorded. If a moderator decides that there needs to be a consequential action, then they can write
down the associated action and its date; they are able to hide any post in the channel.
The Data Model
The Data Model from MySQL Workbench is provided in Figure 1.
FIGURE 1. DATA MODEL FOR SLARC.
INFO20003 S2 2024 – ASSIGNMENT 2 v1.4 3
Assignment 2 Setup
Please pay special attention to the penalties listed [⚠].
A dataset is provided which you can use when developing your solutions. To set up the dataset, download the file
slarc.sql from the Assignment link on Canvas and run it in Workbench. This script creates the database tables and
populates them with data.
The sample dataset provided is a basic example of Slarc deployed for a DOTA2 e-sports community. You may find
that you may need to add some more sample data in Workbench to fully test out each and every query.
Note that this dataset is provided for you to experiment with: but it is NOT the same dataset as what your
queries will be tested against (the schema will stay the same, but the data itself may be different). This means
when designing your queries, you must consider edge cases even if they are not represented in this particular
data set.
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 lines at the beginning
of the script.
⚠ WARNING: Do NOT disable only_full_group_by mode when completing this assignment. This mode is
the default and is turned on in all default installs of MySQL workbench, and we’ve added a line to the top of
slarc.sql to turn it on every time you run the script in case you disable it! You can check whether it is turned on using
the command `SELECT @@sql_mode`; The command should return a string containing
ONLY_FULL_GROUP_BY or ANSI. When testing, our test server WILL have this mode turned on, and if your
query fails due to this, you will lose marks.
The SQL Tasks
Please pay special attention to the penalties listed [⚠].
In this section are listed 10 questions for you to answer. Write one (single) SQL statement per question. Each
statement must end with a semicolon (;). Subqueries and nesting are allowed within a single SQL statement –
however, you may be penalised for writing overly complicated SQL statements.
⚠ WARNING: DO NOT USE VIEWS (or ‘WITH’ statements/common table expressions) OR VARIABLES to answer
questions. Penalties apply.
INFO20003 S2 2024 – ASSIGNMENT 2 v1.4 4
❓The Questions
1. List all posts which contain no ‘react’s. Your query should return results of the form (postPermanentID,
text). (1 mark)
2. Find the most recently promoted mod (moderator) in the entire database. Assume there are no ties (only
one is the most recent). Your query should return results of the form (modID, username, dateModStatus).
(1 mark)
3. List all posts created by user ‘axe’ that have at least **00 views. Your query should return results of the
form (postPermanentID, viewCount). (1 mark)
4. Find the post which is most commented on (Hint: most ‘originalPostID’ appearances). If there are ties, then
you must return all posts with the highest number. Your query should return results of the form
(postPermanentID, totalCommentCount), with one row per post in case of a tie. (2 marks)
5. List the dataURLs for all attachments to posts in channelNames containing ‘dota2’ (e.g., ‘dota2_players’,
‘info20003_better_than_dota2’). Your query should return results of the form (dataURL, channelID). (2
marks)
6. Find which channel has the highest number of ❤
s made to posts within the channel. (Hint: ❤ s are simply
‘love’ found in the emoji ENUM). If there are ties, then you must return all results. Your query should return
results of the form (channelName, heartCount), with one row per channel in case of a tie. (2 marks)
7. Find the names of controversial users: defined as users who have < 60 reputation, have at least 1
moderatorreport on one of their posts, and at least 3 ‘love’ react’s given to their posts in total. Your query
should return results of the form (userID, reputation, totalModeratorReports, totalLoveReacts). (2 marks)
8. List the top 3 channels with the largest number of posts with attachments identified with virus(es). Also
return the total count of such attachments for each channel. Your query should return results of the form
(channelID, channelName, totalVirusInfectedAttachments). If there are ties in the top 3 positions, you
must return all ties. For example, let’s say the database contains seven channels and the ‘total number of
virus-afflicted posts’ for each channel are (5, 4, 4, 3, 3, 2, 1). The top 3 counts are 5, 4 and 3 so you need to
return the top 5 rows, which are the ones having attachment counts of (5, 4, 4, 3, 3). (3 marks)
9. We’ll use the term ‘repeater’ to describe a user who has had posts in more than one channel reported. Find
moderators, and determine how many ‘disciplinary actions’ (as per the disciplinaryAction flag) they’ve
given to ‘repeater’ users. Your query should return results of the form (modID,
numberOfDisciplinariesToRepeaters). (3 marks)
10. Find users who have not posted (or commented on any post) before 01/04/2024 in the channel
ranked_grind, but have posted at least one COMMENT (a post which is a reply to another post) in
dota2_memes on or after 01/04/2024. Your query should return results of the form (userID) for all such
users. (3 marks)
INFO20003 S2 2024 – ASSIGNMENT 2 v1.4 5
⚠ SQL Response Formatting Requirements
Please pay special attention to the penalties listed [⚠].
To help us mark your assignment queries as quickly/accurately as possible, please ensure that:
• Your query returns the projected attributes in the same order as given in the question and does not
include additional columns.
o E.g., if the question asks, ‘return as (userId, name)’, please write SELECT userId, name …
o ⚠ DO NOT return attributes in the WRONG order, e.g., SELECT name, userId…
o You can name the columns using `AS` however you’d like, only the order matters. E.g., this is
fine: SELECT userId, name AS fullName
• Please do NOT use “databaseName.tableName” format.
o E.g., please write “SELECT userId FROM users…”
o ⚠ DO NOT provide the database name, e.g. SELECT userId FROM coltonc.users
….
• Ensure that you are using single quotes ( ' ) for strings (e.g. …WHERE name = 'bob'…)and double
quotes ( " ) only for table names (e.g. SELECT name FROM "some table name with
spaces"…).
o ⚠ Do NOT use double quotes for strings: …WHERE name = "bob"….
o ⚠ Do NOT use Microsoft Word ‘smart quotes’ (the fancy ones as you see in “this” ‘example’).
• Comments are optional, but we recommend writing them for complex queries.
• ⚠Do NOT delete the special comment markers in the SQL template file. These include: -- BEGIN
QX, -- END QX, and -- END OF ASSIGNMENT (where X is the question number). They help us
mark your submission so tampering with them will hinder our marking and will attract penalties.
Assignment Submission Instructions
Please pay special attention to the penalties listed [⚠].
Your submission will be in the form of an SQL script. There is a template file on the LMS, into which you will paste
your solutions and fill in your student details (more information below).
This .sql file should be submitted on Canvas by the time indicated on the first page of these instructions.
Name your submission as 987654.sql, where 987654 corresponds to YOUR student ID number.
Please make sure that you actually submit your file on Canvas
⚠
. After uploading the file, you need to press
‘Submit Assignment’ to actually submit the file. If you submit late because you failed to press the submit button
and only noticed this after the deadline, your submission will be considered late just like any other late submission
to maintain fairness for all students.
INFO20003 S2 2024 – ASSIGNMENT 2 v1.4 6
Filling in the template file:
The template file on the LMS has spaces for you to fill in your student details and your answers to the questions.
There is also an example prefilled script available on Canvas as well.
Below (Table 1) are screenshots from those two documents explaining the steps you need to take to submit your
solutions:
TABLE 1: SCREENSHOT EXAMPLES ON HOW TO SUBMIT THE SOLUTIONS.
Step Example
1. At the top of the template,
you’ll need to replace “XXXXXXXX”
with your student number and
name
Template
Example Filled in
2. For each question **10, place
your SQL solution in between the
“BEGIN QX” and “END QX”
markers.
Ensure each query is terminated
with a semicolon “;”
Template
Example Filled in
INFO20003 S2 2024 – ASSIGNMENT 2 v1.**
3. Test that your script is valid SQL
by running it from MySQL
Workbench. Run the entire script
by copy-pasting this entire file into
a new workbench tab, placing your
cursor at the start of the file
(without selecting anything), and
pressing the lightning bolt to run
the entire file.
All queries should run successfully
one after another. If not, check to
make sure you added semicolons
‘;’ after each query.
All 10 queries ran sequentially and were successful.
Late submission
Unless you have an approved extension (see below), you will be penalised -10% of the maximum number of
marks in the assignment per calendar day that your submission is late
⚠ . For instance, if you received a 78% raw
score, but submitted 2 days late, you'd receive a 58% for the assignment.
Requesting a Submission Deadline Extension
If you need an extension due to a valid reason, you will need to provide evidence to support your request the time
indicated on the first page of these instructions. Medical certificates need to be at least two days in length.
Requests received after the assignment deadline will be rejected, based on the requirements of the Special
Consideration policy (https://eng.unimelb.edu.au/students/coursework/study-resources/extensions-and-special consideration ):
If students need to request an extension longer than 4 days or submit the request after the assessment
submission deadline, the application must have appropriate supporting documentation and be submitted via the
Special consideration portal.
(In other words, any extensions which fall within the scope/criteria of the Special Consideration policy needs to be
assessed formally and centrally by University administration and not by the teaching team.)
To request an extension:
• Email the central subject inbox:
INFO20003-semester2@unimelb.edu.au
... from your university email address, supplying your student ID, how many days you’d like to extend, and
evidence that can support the number of days you are requesting. Please include in the subject
[INFO20003 Assignment 2 Extension].
INFO20003 S2 2024 – ASSIGNMENT 2 v1.4 8
• If your submission deadline extension is granted you will receive an email reply granting the new
submission date. Do not lose this email!
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 at least 50% (35/70) or higher for the combination of quizzes and end of semester exam
It is our recommendation to students that you attempt every assignment and every question in the exam.
Reminder: Academic Integrity
Students are kindly reminded to adhere to comply with the University’s Academic Integrity Policies at all times.
Please read https://academicintegrity.unimelb.edu.au/plagiarism-and-collusion and please note:
“If a student is found to have deliberately plagiarised or colluded the penalties are severe and can include failure
of a subject or exclusion from the University. The University provides extensive resources and educates students
about academic integrity so that students are aware of what constitutes plagiarism and collusion, and the
consequences of those practices.”
(Source: https://academicintegrity.unimelb.edu.au/plagiarism-investigation-and-penalties )
GOOD LUCK!
Document Change Log for clarification:
V1.1 Uploaded 2 Sept 2024 – added disambiguations [Ed Post #345 #346 - i.e., Q5 Q6], typo fix and text
clarification/position in diagram [Ed Post #350]
V1.2 Updated 4 Sept 2024 – corrected image 0-M at moderatorreport [Ed Post #361]
V1.3 Updated 5 Sept 2024 – very minor typo [Ed Post # 366 - may->many Q9]
V1.4 Updated 9 Sept 2024 – minor typo in text [Ed Post # 398 – Q9 refers to the disciplinaryAction flag]; Q8 phrasing
disambiguated [via authoritative answer on Ed Post #385].
請加QQ：99515681 郵箱：99515681@qq.com WX：codinghelp
