Find the most recent user rating record

Assignment Help PL-SQL Programming

Reference no: EM133517902

Database Systems

Case: "MewTube" App

Description: As fellow Database experts, cat lovers, and social media enthusiasts, you and your classmates have created a start- up like YouTube called MewTube (inspired by the Pokemon character!). MewTube is a modern social video platform which hosts videos and enables content creators to have a friendly, less polarising, environment to engage with their fans; and allows content creators to collaborate on new videos.

For each user, MewTube records their details such as username, one 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).

Each user can be optionally linked with a content creator account if they produce videos. Each content creator has an id, real name, screen name, and optional website. In addition, a content creator can have hashtags to describe themselves such as #music, #news, #memes, etc. For each content creator, MewTube tracks their videos. Content creators who collaborate on a video (e.g., MrBeast x BTS) are known as co-creators.

For each video, MewTube stores its id, title, upload timestamp, two long URLs storing the video object (the actual video data) and thumbnail image respectively, and a view counter. As above, each video is linked to its co-creators (which might just be a single content creator for solo-authored content). Same as each content creator, each video can be associated with a few hashtags, again, e.g., #news, #BTS, #experiment, #viral, etc. Also, each video might have some annotations - which are links that appear in a video that links to other videos.

Each annotation identifies its source video (i.e., where it is seen), a destination video (i.e., the connection to another video), timestamp (when it pops up on the source video), duration (how long it appears on screen, in seconds), and description (text that is in the pop up).

Finally, each video has ratings that are left by other users. Each ratings record consists of a rating - i.e., either one of {Dislike, Neutral, Like}; a timestamp; and optional comment for the rating.

The Data Model

Assignment 2 Setup

A dataset is provided which you can use when developing your solutions. To set up the dataset, download the file mewtube.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.

The SQL Tasks

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.

Question 1. List all videos which contain no annotations (i.e., find videos that do not have an annotation linking to another video). Your query should return results of the form (videoID, title).

Question 2. Find the most recent user rating record in the entire database. Assume there are no ties (only one is the most recent). Your query should return results of the form (videoID, username, ratingTimestamp).

Question 3. List all videos created by content creator TaylorSwiftOfficial that have at least 1 million views. Note that ‘TaylorSwiftOfficial‘ is the screen name of the account. Your query should return results of the form (videoID, title).

Question 4. Find the video which is most linked to (i.e., appears the most as ‘destination video' for annotations). If there are ties, then you must return all videos with the highest number. Your query should return results of the form (videoID, title, linkedCount), with one row per video in case of a tie.

Question 5. List the upload datetime for the videos that have #memes as a hashtag and have been rated at least 3 times. Your query should return results of the form (videoID, uploadDatetime, ratingCount).

Question 6. Find the names of controversial content creators, defined as users who have < 50 reputation, but have at least 3 videos, and at least 6 ratings given to their videos in total. Your query should return results of the form (username, realName, screenName).

Question 7. Find which hashtag has the highest number of polite comments made to videos using that hashtag. Polite comments are comments that contain ‘thank you' or ‘well done' (you can ignore the casing of these phrases). Ignore hashtags in content creator profiles. If there are ties, then you must return all results. Your query should return results of the form (hashtag, commentCount), with one row per hashtag in case of a tie.

Question 8. List the top 3 hashtags with the highest total annotations as a destination video. Also return their total duration in annotations. Your query should return results of the form (hashtag, totalAnnotationsAsDestination, totalDuration). If there are ties in the top 3 positions, you must return all ties. For example, let's say the database contains seven hashtags and the annotation counts for each hashtag 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 annotation counts of (5, 4, 4, 3, 3).

Question 9. Find the content creators whose own hashtags include ‘#memes' who have co-created at least one video with at least one other creator whose hashtags contain ‘#technology'. (Note: do NOT consider the hashtags of the videos themselves). Note that we only want to consider co-created videos where the #memes creator is distinct from the #technology creator. To elaborate: if MrBeast is a #memes and #technology creator, we need a collaboration with a different creator who has a #technology hashtag for MrBeast to be included in the results. Your query should return results of the form (realName, screenName) of the content creators associated with #memes.

Question 10. Find the content creators who have not co-created a video before the start of this year (01/01/2023) with the creator INFO20003Memes but have co-created at least one video with INFO20003Memes on or after 01/01/2023 (i.e., new co-creator partnerships on or after 01/01/2023). Note that ‘INFO20003Memes' is the screen name of the account. Your query should return results of the form (realName, screenName) for all such creators. Do not return a row for INFO20003Memes.

Reference no: EM133517902

Questions Cloud

Describe the expected health goal for the population : Describe the expected health goal for the population of African American Infant Mortality. Explain strategies to identify diverse stakeholders for influencing

Implementation of adaptive strategies for expansion : What role does marketing play in the implementation of adaptive strategies for expansion? Is marketing ever involved in reduction of scope strategies?

Discuss how the results of previous research demonstrate : Explain why a quality improvement initiative is needed in this area and the expected outcome. Discuss how the results of previous research demonstrate support

Advance their position or further develop their skills : Often times, employees come to a point when they are ready to advance their position or further develop their skills.

Find the most recent user rating record : INFO20003 Database Systems - University of Melbourne, Find the most recent user rating record in the entire database. Assume there are no ties

Context of seeking family violence protection order : The location and role of the local court in the context of seeking a family violence protection order;

Explain interrelationships among health, safety, nutrition : Explain the interrelationships among health, safety, nutrition, and prevention. Then explain how they impact a child's well-being separately and together?

Ambitious pieces of social legislation : Title VII of the Civil Rights Act of 1964 is considered one of the most ambitious pieces of social legislation ever passed by the United States government.

What are the consequences of less-educated, lower skilled : What are the consequences of less-educated, lower skilled immigrant population, and how does it impact employment, economic equality, and health care access?

User Account

All Pages