Register

GO Classes DPP | GATE CS, DA | DBMS | Join

89 views
2 2 votes

Consider the relations:

$\mathrm{Posts(pid,folder,summary)}$ and $\mathrm{Postings(post,position,user,ptext)}$.

Let $\mathrm{R_1}$ and $\mathrm{R_2}$ be two renamed copies of $\mathrm{Postings}$.

Which expression correctly returns the summary titles of all posts on which at least two different users have posted?

  1. $\pi_{\mathrm{summary}}\left(\pi_{\mathrm{R_1.post}}\left(\mathrm{R_1}\bowtie_{\mathrm{R_1.post=R_2.post}\ \land\ \mathrm{R_1.user\neq R_2.user}}\mathrm{R_2}\right)\bowtie_{\mathrm{R_1.post=Posts.pid}}\mathrm{Posts}\right)$
     
  2. $\pi_{\mathrm{summary}}\left(\pi_{\mathrm{R_1.post}}\left(\mathrm{R_1}\bowtie_{\mathrm{R_1.user\neq R_2.user}}\mathrm{R_2}\right)\bowtie_{\mathrm{R_1.post=Posts.pid}}\mathrm{Posts}\right)$
     
  3. $\pi_{\mathrm{summary}}\left(\pi_{\mathrm{R_1.post}}\left(\mathrm{R_1}\bowtie_{\mathrm{R_1.post=R_2.post}\ \land\ \mathrm{R_1.user=R_2.user}}\mathrm{R_2}\right)\bowtie_{\mathrm{R_1.post=Posts.pid}}\mathrm{Posts}\right)$
     
  4. $\pi_{\mathrm{summary}}\left(\pi_{\mathrm{R_1.post}}\left(\mathrm{R_1}\bowtie_{\mathrm{R_1.post=R_2.post}}\mathrm{R_2}\right)\bowtie_{\mathrm{R_1.post=Posts.pid}}\mathrm{Posts}\right)$

1 Answer

1 1 vote

Two things must simultaneously hold.

The two posting tuples must belong to the same post:

$\mathrm{R_1.post=R_2.post}$.

They must have been written by different users:

$\mathrm{R_1.user\neq R_2.user}$.

Therefore, the required theta join is

$\mathrm{R_1}\bowtie_{\mathrm{R_1.post=R_2.post}\ \land\ \mathrm{R_1.user\neq R_2.user}}\mathrm{R_2}$.

After finding such posts, project their $\mathrm{post}$ identifiers and connect those identifiers to $\mathrm{Posts.pid}$ to retrieve the $\mathrm{summary}$.

Hence A is correct.

B can pair different users from completely different posts.

C specifically requires the same user, which is the opposite of the requirement.

D only establishes that two tuples belong to the same post. A single user's posting can match itself, so it does not prove that two distinct users are present.

Voting & Accepting an answer helps improve the community
Answer:
← Previous · GO Classes DPP | GAT...Next → · GO Classes DPP | GAT...
← Previous in category · GO Classes DPP | GAT...Next in category → · GO Classes DPP | GAT...
Position:
Show:

Related questions

1 1 vote
1 1 answer
131
131 views
GO Classes asked Sep 22
131 views
GO Classes DPP | GATE CS, DA | DBMS | Join
Consider the relations:$\mathrm{Authors(au\_id,au\_lname,au\_fname,phone,address,city,state,zip)}$$\mathrm{TitleAuthors(au\_id,title\_id,au\_ord,royaltyshare)}$$\mathrm{T...
1 1 vote
1 1 answer
92
92 views
GO Classes asked Sep 22
92 views
GO Classes DPP | GATE CS, DA | DBMS | Join
Consider the relations:$\mathrm{Locations(locationid,name,state,altitude)}$ and $\mathrm{FallColors(week,year,locationid,color,peakpercent)}$.We want locations in New Y...
2 2 votes
1 1 answer
103
103 views
GO Classes asked Sep 22
103 views
GO Classes DPP | GATE CS, DA | DBMS | Join
Consider the relation:$\mathrm{Marks(studentID,~courseID,~courseType,~score)}$Let, $\mathrm{M_1=\rho_{M_1}(Marks)}$ and $\mathrm{M_2=\rho_{M_2}(Marks)}$.Which expression ...
1 1 vote
1 1 answer
150
150 views
GO Classes asked Sep 22
150 views
GO Classes DPP | GATE CS, DA | DBMS | Relational Algebra
Assume the expressions below are schema-valid and relations use set semantics.Which of the following are always true?$(\mathrm{R}\bowtie\mathrm{S})\bowtie\mathrm{T}=(\mat...