代写INFO20003、代做SQL编程语言

日期：2024-09-18 09:44

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 9000 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 1-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.4 7

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-specialconsideration

):

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].