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 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. Find all users (uname and city) who follow at least everyone that user 'Donald Trump' follows. Once we delete a user, we should delete all tweets posted by that user. Implement this functionality using a SQL trigger. For all users located in Montreal and posted more than 10,000 tweets, report the user name and total number of tweets. For all users who have more than 1M followers, report the user name and total number of followers. 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. Find all users (uname and city) who follow at least everyone that user 'Donald Trump' follows. Once we delete a user, we should delete all tweets posted by that user. Implement this functionality using a SQL trigger. For all users located in Montreal and posted more than 10,000 tweets, report the user name and total number of tweets. For all users who have more than 1M followers, report the user name and total number of followers
