Relational Algebra Please read carefully and please try to type your answers as
ID: 3757056 • Letter: R
Question
Relational Algebra
Please read carefully and please try to type your answers as handwritten answers are hard to read.
Thanks!
Consider the following database schema for a simplified Twitter database (primary keys are bold):
Person (pname, city, ttext) [Assume that pname is unique]
Follows (pname1, pname2) [Person pname1 follows person pname2]
Tweets (tid, ttitle, ttext) [Tweet with tid has title ttitle and text ttext]
PersonTweets (pname, tid, ts) [Person pname posted tweet tid at timestamp ts]
TweetTag (tid, tagname) [Tweet tid had tagname in its list of tags]
Users post ‘tweets’, that are short pieces of text. They may tag their tweets with zero or more tags of their own choice. For example, a user tweeting about the G20 summit may decide to use the tag ’G20’. A user ‘u’ may follow zero or more other users, which means that their ’tweets’ are visible to user ‘u’ when he/she logs in.
For your convenience, you can use the follow abbreviations: P for Person, F for Follows, T for Tweets, PT for PersonTweets, TT for TweetTag. Express the following queries in the standard Relational Algebra.
1. Find all the names of all people who posted a tweet with tag ‘Obama’.
2. Find all the tags ‘Larry Page’ uses in his tweets (i.e.,Larry’s tweeting interests).
3. Find all the tags ‘Larry Page’ reads in the tweets of the people he follows (i.e., Larry’s reading interests).
4. Find the names and cities of all people who follow people who follow ‘Tim Cook’ (i.e. second-level followers).
5. Find the names and cities of all people who follow at least everyone that ‘Larry Page’ follows.
6. Find all pairs of names of people who have at least one follower in common.
Explanation / Answer
1.answer)
pname ( personTweets tweets)
(OR)
pname ( tweets.tid=persontweets.tid (Tweets x tweets))
2.answer)
tagname ((pname="larry page" (persontweets) ) tweettag)
3.answer)
tagname (((pname1="larry page" (follows) )pname2=pname (persontweets))tweettag)
4.answer)
pname,city ((person)((follow) x (pname2="tim cook" (follows) )))
5.answer)
pname,city( (follows) / (pname2="larry page" (follows) ) )