Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
Database Systems INFO20003
INFO20003 Semester
Assignment 2: SQL
Due: 6:00pm Friday 29th April
Weighting: 10% of your total assessment
newQuora Startup Database
Description
You and a group of fellow undergrads have created a start-up called ‘newQuora’. The company’s goal
is to create an online question-and-answer forum for users around the world.
The system has two kinds of users: general and admins. Most user attributes are straightforward
personal information and are listed in the ER diagram below.
Discussions are organized into forums, each of which is about a particular topic. New forums can be
opened by admins. Users can subscribe to any number of forums to get regular updates.
Users can create a ‘post’ in any forum, which becomes a topic for discussion. Any user can comment
on a post, or on comments of a post (nested comments). Users can also “upvote” a post or comment.
General users (but not admins) can have different “relationships” among themselves. One user can be
“following” another to receive updates when they post or comment. Note that following is a non-
symmetric relation: if A is following B, that does not imply B is following A. A is denoted as a “follower”
of B.
General users (but not admins) can also add each other to friend-lists. When one user sends a friend-
request to another, the latter can reject or accept the friendship. If the latter accepts, the pair are now
friends. Note that friendship is a symmetric relation: if A is a friend of B, then B is a friend of A, whether
A sent the friend request to B or vice-versa. Once a pair of users are friends, either may later unfriend
the other, in which case the friendship ends for both. A user’s “friends” means their current “confirmed”
friends, not those where the friendship has ended or not begun.
Database Systems INFO20003 A2 S1 2022 Page 2 of 7
The Data Model
The physical ER model of newQuora startup database.
Notes on Implementation
Posts + Comments:
- Comments are stored in the same table as Posts, and are connected to their parent
posts/comments via a unary relationship.
- Posts have a non-NULL FK to the forum they are posted in, and a NULL FK for their ‘parentpost’.
- Comments have NULL for forum FK (since they’re not a “forum” post), and a non-null ‘parentpost’
FK.
- Both posts and comments can be ‘parentposts’, i.e. users can comment on posts or on another
comment. A post/comment can be the ‘parentpost’ of many comments, but a comment will only
have one ‘parentpost’.
Database Systems INFO20003 A2 S1 2022 Page 3 of 7
An example of a forum with post + comments, and how this relates to the `parentpost` and `forum` attributes in
the post table.
Friendships:
- We record when a friend request is made, and accepted/rejected/confirmed in the FriendOf
table. newQuora does not allow you to send a friend request to a user that you have rejected or
unfriended, and there is no way for the other person to send another friend request to you if you
have rejected or unfriended them.
- `User1` in the friendOf table was the user that sent the friend request.
Assignment 2 Setup
A dataset is provided against which you can use when developing your solutions. To set up the dataset,
download the file newQuora_2022.sql from the Assignment link on Canvas and run it in Workbench.
This script creates the database tables and populates them with data. 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.
Note: 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. 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.
Database Systems INFO20003 A2 S1 2022 Page 4 of 7
The SQL tasks
In this section are listed 10 questions for you to answer. Write one (single) SQL statement per question.
Subqueries and nesting are allowed within a single SQL statement – however, you may be penalized
for writing overly complicated SQL statements. DO NOT USE VIEWS (or ‘WITH’ statements/common
table expressions) to answer questions.
1. List all users who have sent a friend-request which is still ‘pending’ (i.e. a friend request has
been sent but not confirmed or rejected yet). Your query should return results of the form (userID)
(1 mark)
2. List all the forums that have at least one subscription. Your query should return results of the
form (forumId, topic, numSubs). (1 mark)
3. Find the forum with the most recent post. You may assume there are no ties, i.e. there is only
one forum with the most recent post. Your query should return one row of the form (forumId,
postId, whenPosted)." (1 mark)
4. For each user with at least one follower, list the IDs of all their followers. Your query should return
results of the form (userId of followed, userId of follower). E.g. If user #1 is followed by users #2
and #3, you’d return two rows: (1,2) and (1,3). (2 marks)
5. Which forum has the highest number of upvotes on its posts (sum of upvotes of all posts in that
forum), and which admin created that forum? Assume there are no ties for first place, and that
at least one forum has a post with at least one upvote. Return one row as (adminId, forumId,
numberOfUpvotesInForum). (2 marks)
6. List all the users who have no “followers” (i.e., no other user is “following” them) and no friends.
Your query should return results of the form (userId, username). (2 marks)
7. A “high-quality contributor” is a user who has a mean average of at least one upvote for every
post or comment they’ve made, and has made at least one post or comment. Find all of the high-
quality contributors and their average upvotes. Return as (userId, avgUpvotes). (2 marks)
8. Find all comments or posts that have fewer likes than every comment that they are the
parentpost of. (I.e. every direct reply/comment to the parent post/comment has more upvotes
than the parent). Return as (PostOrCommentId). (3 marks)
9. List all the ‘general’ users who have only upvoted posts or comments made by an admin OR
someone who is currently their friend (i.e. they don’t like any posts of someone who is not an
admin and not currently their friend). Users who have liked their own posts should not be
returned. Your query should return results of the form (userID). (3 marks)
10. For each admin, show the forum with the highest number of subscriptions that they have created.
If an admin has created multiple forums with an equal highest number of subscriptions, you must
return all forums with this tied highest value. Your query should return results of the form
(adminID, forumId, numSubscriptions), one row per admin (unless there are ties). If an admin
has never created a forum, return (adminId, NULL, 0). (3 marks).
Database Systems INFO20003 A2 S1 2022 Page 5 of 7