edited by
379 views
1 votes
1 votes

Let $R = (A,B,C)$, and let $r1$ and $r2$ both be relations on schema $R$. Select a SQL expression among these options that is equivalent to the following query. $ \Pi AB(r1) \bowtie \Pi BC(r2)$

  1. select r1.A, r2.B, r2.C from r1, r2 where r1.B = r2.B
    
  2. select * from r1 where (A, B, C) in (select *from r2)
    
  3. select r1.A  from r1 where (A, B, C) not in (select ∗ from r2)
  4. select r1.A , r2.B , r1.C  from r1, r2 where r1.B = r2.B

     

edited by

1 Answer

Best answer
1 votes
1 votes

Lets take example and evaluate the query.

$$\textbf{r1}\\\begin{array}{|l|l|l|l|} \hline A & B & C \\ \hline 1 & a & \text{Raju}   \\ \hline 2 & a & \text{Rani}  \\ \hline 3 & b & \text{Rakesh}  \\ \hline 4 & b & \text{Vani}    \\ \hline \end{array} \\ \textbf{r2}\\\begin{array}{|l|l|l|l|} \hline A & B & C \\ \hline 1 & a & \text{Raju}   \\ \hline 2 & a & \text{Rani}  \\ \hline 3 & b & \text{Rakesh}  \\ \hline 4 & b & \text{Vani}    \\ \hline \end{array}$$

When we perform $\Pi_{AB}(r1),$ we get $$\textbf{r1}\\\begin{array}{|l|l|l|l|} \hline A & B \\ \hline 1 & a  \\ \hline 2 & a  \\ \hline 3 & b   \\ \hline 4 & b    \\ \hline \end{array}$$

And when we perform $\Pi_{BC}(r2),$ we get $$\textbf{r2}\\\begin{array}{|l|l|l|l|} \hline B & C \\ \hline a & \text{Raju}   \\ \hline  a & \text{Rani}  \\ \hline  b & \text{Rakesh}  \\ \hline b & \text{Vani}    \\ \hline \end{array}$$

Now, perform natural join, so $r1.B=r2.B$ will be combine.

$\Pi_{AB}(r1)\Join \Pi_{BC}(r2)$, we get $$\textbf{r1}\\\begin{array}{|l|l|l|l|} \hline A & B & C \\ \hline 1 & a & \text{Raju} \\ \hline  1 & a & \text{Rani}   \\ \hline 2 & a & \text{Raju}    \\ \hline 2 & a & \text{Rani}  \\ \hline 3 & b & \text{Rakesh}  \\ \hline 3 & b & \text{Vani}  \\ \hline 4 & b & \text{Rakesh} \\ \hline 4 & b & \text{Vani}  \\ \hline \end{array} $$

Same thing  we can do in SQL

select r1.A, r2.B, r2.C from r1, r2 where r1.B = r2.B

Here we can't select C from r1$($we project C only on r2$).$

So, the correct answer is $(A)$.

selected by
Answer:

Related questions

1 votes
1 votes
1 answer
1
Bikram asked Nov 26, 2016
319 views
Consider the join of relation R with a relation S. If R has $m$ tuples and S has $n$ tuples, then the maximum and minimum sizes of the join, respectively, are __________....
0 votes
0 votes
1 answer
2
Bikram asked Nov 26, 2016
228 views
A functional dependency of the form x → y is trivial ify ⊆ xy ⊂ xx ⊆ yx ⊂ y
0 votes
0 votes
1 answer
3
Bikram asked Nov 26, 2016
293 views
What does the following Tuple Relational Calculus query produce?The expression σθ1 (E1 ⋈θ2 E2) is the same as: E1 ⋈θ1^ θ2 E2 (σθ1 E1) ∧ (σθ2 E2 ) E1 ⋈ θ...