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