Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Tasks: Consider a simplified version of a known social application, Twitter! At a high-level, Twitter works as follows: Users post tweets that are short pieces

image text in transcribed

Tasks:image text in transcribed

Consider a simplified version of a known social application, Twitter! At a high-level, Twitter works as follows: Users post tweets that are short pieces of text. Users may tag their tweets with zero or more tags of their own choices. A tag must begin with the hash tag sign '#'. For example, a user tweeting about the Database Applications course may decide to tag the tweet with #DBApps #socool. Users may follow zero or more other users. The tweets of the former are visible to the latter. Given the above Twitter's description, we define the following relation schemas: Users (uname, city, street) - you may assume that uname is unique Follows (uname1, uname2) - user with unamel follows user with uname2 Tweets (tid, t_tittle, t_text) - tweet with tid has title t title & content t text UserTweets (uname, tid, ts) - user uname posted a tweet with tid at time ts Tweet Tags (tid, tag) - tweet with tid has tag in its list of tags. Now, we would like to extract some useful information from the database and we leave this job to our database expert (you!). For each of the following questions, write an SQL query. State the reason clearly if an expression and or query cannot be expressed. Write all your SQL queries in a PDF document named P2-.pdf and indecate the used RDBS, namely Postgres or MySQL. Create the tables using SQL statements including constrinas, such as primary key, foreign key, and general constrinas. Find all users (uname) who posted a tweet with tag "#DBApps". Find all distinct tags ever used since the launch of Twitter. Considering a particular user 'Donald Trump, find all distinct tags of all tweets by users whom Donald follows. In other words, find Donald's reading interests. Find all users (uname and city) who follow users who follow user 'Donald Trump'. Find all users who read about iPhone 11 before its launch (i.e., before Nov. 3, 2019). Consider a simplified version of a known social application, Twitter! At a high-level, Twitter works as follows: Users post tweets that are short pieces of text. Users may tag their tweets with zero or more tags of their own choices. A tag must begin with the hash tag sign '#'. For example, a user tweeting about the Database Applications course may decide to tag the tweet with #DBApps #socool. Users may follow zero or more other users. The tweets of the former are visible to the latter. Given the above Twitter's description, we define the following relation schemas: Users (uname, city, street) - you may assume that uname is unique Follows (uname1, uname2) - user with unamel follows user with uname2 Tweets (tid, t_tittle, t_text) - tweet with tid has title t title & content t text UserTweets (uname, tid, ts) - user uname posted a tweet with tid at time ts Tweet Tags (tid, tag) - tweet with tid has tag in its list of tags. Now, we would like to extract some useful information from the database and we leave this job to our database expert (you!). For each of the following questions, write an SQL query. State the reason clearly if an expression and or query cannot be expressed. Write all your SQL queries in a PDF document named P2-.pdf and indecate the used RDBS, namely Postgres or MySQL. Create the tables using SQL statements including constrinas, such as primary key, foreign key, and general constrinas. Find all users (uname) who posted a tweet with tag "#DBApps". Find all distinct tags ever used since the launch of Twitter. Considering a particular user 'Donald Trump, find all distinct tags of all tweets by users whom Donald follows. In other words, find Donald's reading interests. Find all users (uname and city) who follow users who follow user 'Donald Trump'. Find all users who read about iPhone 11 before its launch (i.e., before Nov. 3, 2019)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Databases questions