3.6k views

Let R and S be two relations with the following schema

$R(\underline{P}, \underline{Q}, R1, R2, R3)$

$S(\underline{P}, \underline{Q}, S1, S2)$

where $\left\{P, Q\right\}$ is the key for both schemas. Which of the following queries are equivalent?

1. $\Pi_P \left(R \bowtie S\right)$

2. $\Pi_P \left(R\right) \bowtie \Pi_P\left(S\right)$

3. $\Pi_P \left(\Pi_{P, Q} \left(R\right) \cap \Pi_{P,Q} \left(S\right) \right)$

4. $\Pi_P \left(\Pi_{P, Q} \left(R\right) - \left(\Pi_{P,Q} \left(R\right) - \Pi_{P,Q} \left(S\right)\right)\right)$

1. Only I and II
2. Only I and III
3. Only I, II and III
4. Only I, III and IV
+14
Query 1,3 and 4 filter the rows based on equality of both P and Q attribute but in Query 2 it is only P.
0
(y) answer in one line !!
0
I have only one doubt, Should not P and Q be underlined together instead of being underlined separately?
0
the best way to check is to look at 2nd & 3rd options carefully you will see that in 3rd one there is intersection of both p&q means in both the tables i.e R.p=S.p & R.q=S.q but in 2nd option ,only R.p=S.p need to be satisfied so 2nd option gets eliminated now check other options all are equivalent.
0
@AHWAN he also mentioned separately {P,Q} is key for both tables means a compound key (>= 2 attributes) is defined for the tables.
0
2nd query is differ from rest of all becoz it use only p for filtering. all query are use p and q both atrribute for filtering.
0
The best approach to filter out the options would be

Since (A n B)=(A-(A-B))

so (III) and(IV) are definitely going to be equal

(d) i, iii, iv

iv) is the expansion for natural join represented with other operators.

Why ii is not equivalent? Consider the following instances of R and S

$R: \left\{\left\langle1", abc", p1", p2", p3"\right\rangle, \\ \left\langle2", xyz", p1", p2", p3"\right\rangle \right\}$

$S: \left\{\left\langle1", abc", q1", q2"\right\rangle \\ \left\langle2", def", q1", q2"\right\rangle \right\}$

Now, consider the given queries:

i. $R \bowtie S$ gives

$\left\{\left\langle1", abc", p1", p2", p3", q1", q2" \right\rangle \right\}$

Projecting $P$ gives $\left\{\left\langle 1" \right\rangle \right\}$

ii. $\pi_P\left(R\right) \bowtie \pi_P\left(S\right)$ gives

$\left\{\left\langle1" \right\rangle \left\langle 2" \right\rangle \right\} \bowtie \left\{\left \langle 1" \right\rangle \left\langle 2"\right\rangle \right\}$

$=\left\{\left\langle 1", 2" \right\rangle \right\}$

iii. $\Pi_P \left(\Pi_{P, Q} \left(R\right) \cap \Pi_{P,Q} \left(S\right) \right)$ gives

$\left\{\left\langle1", abc" \right\rangle, \left\langle 2", xyz" \right\rangle \right\} \cap \left\{\left \langle 1", abc" \right\rangle, \left\langle 2", def" \right \rangle \right\}\\ = \left\{\left\langle1", abc" \right\rangle \right\}$

Projecting $P$ gives $\left\{\left\langle 1" \right\rangle \right\}$

iv. $\Pi_P \left(\Pi_{P, Q} \left(R\right) - \left(\Pi_{P,Q} \left(R\right) - \Pi_{P,Q} \left(S\right)\right)\right)$ gives

$\left\{\left\langle1", abc" \right\rangle, \left\langle 2", xyz" \right\rangle \right\} \\- \left( \left\{\left\langle1", abc" \right\rangle, \left\langle 2", xyz" \right\rangle \right\} - \left\{\left\langle1", abc" \right\rangle, \left\langle 2", def" \right\rangle \right\} \right)$

$= \left\{\left\langle1", abc" \right\rangle, \left\langle 2", xyz" \right\rangle \right\} \\- \left\{\left\langle 2", xyz" \right\rangle \right\} \\ = \left\{\left\langle1", abc" \right\rangle \right\}$

Projecting $P$ gives $\left\{\left\langle 1" \right\rangle \right\}$
edited
0
What is the right answer ?? option c or d ? .In key everywhere it is given as option c .Anyone plz explain
+7
Key everywhere? Answer is D only.
0
very well explained
–1
ii. πP(R)⋈πP(S)gives

{⟨"1"⟩⟨"2"⟩}⋈{⟨"1"⟩⟨"2"⟩}

={⟨"1","2"⟩}

how is this correct?

shouldnt it be{<1,1>,<2,2>}?????? because join will take equality condition.
0

@arjun Sir is there any associativity for the DIFFERENCE OPERATION ?

(iv)  ΠPP,Q(R)  −  (ΠP,Q(R)  −  ΠP,Q(S)))

If we first take R-R = null
then null-S = null

Any meaning in this interpretation ?

+11
2nd query will take values of P whose corresponding Q do not match, like
one tuple in R is (P1, Q1)   (not writing extra attributes)
one tuple in S is (P1, Q2)
in all other queries such P1 won't be there but in 2nd query P1 will appear.
(all queries except 2nd filtering based on P,Q pair.)
0
what is the meaning of {P,Q} in gate 2008 question(Let R and S be two relations.....)? Either it is composite key or separate primary keys or keys.
0
Superb Explanation .
0

Relation R

 P Q R1 R2 R3 1 abc p1 p2 p3 2 xyz p1 p2 p3

Relation S

 P Q S1 S2 1 abc q1 q1 2 xyz q2 q2

### My question is can we take same values of (P,Q) in both relation R and S, since it's a key in two different tables?

@ Kamal Pratap  May you please pitch in veere?!

@ Rupendra Choudhary  Please you too pitch in brother.

+1
Two queries are equivalent means, on all possible input they will produce the same result. You showed one such input, on which the queries produce same results. Which is not sufficient to say that they will produce same results on all inputs.

However, the answer proposed an input on which the queries are producing different results. Which means that they do not produce same results on all input. Thereby, sufficient to conclude that they are not equivalent.
0
@Aravind only 4 attributes in S, from where are you adding extra column for adding q3?
0
how projecting p gives {<1>}

Natural join is based on the common columnS of the two tables.
We have 2 common columns in R and S which are P and Q

Option I: Both P and Q are used while doing the join. i.e both P and Q are used to filter.
Option 2: Q is not used here for filtering. Natural join is done on ( all P's from R and all P's from S ). Clearly different from option 1.

For Option 3 and Option 4, draw a venn diagram and it will be clear that: R-(R-S) = R $\bigcap$ S. And both these options use P and Q as filters. So 1,3 and 4 are equivalent.

0
Absolutely Correct.
0
how we know that they are using natural join as we denote the Natural join by (*)
0
because we have common attributes in both the relations...and whenever this is the case join is actually natural join ....

1
2