GATE CS 2019 Set 1 — Question 65

The given relational algebra query is:$$\Pi_X(\sigma_{P.Y=R.Y \wedge R.V=V2} (P \times R)) - \Pi_X(\sigma_{Q.Y=R.Y \wedge Q.T>2} (Q \times R))$$Let's evaluate the two parts of the expression separately.Part 1: $\Pi_X(\sigma_{P.Y=R.Y \wedge R.V=V2} (P \times R))$

• P.Y = R.Y
• R.V = V2

First, let's find tuples in R where R.V = V2:

• (Y3, V2)
• (Y2, V2)

Now, we find tuples in P that can join with these based on the P.Y = R.Y condition:

• For R tuple (Y3, V2), we need a tuple in P with P.Y = Y3. There are no such tuples in P.
• For R tuple (Y2, V2), we need a tuple in P with P.Y = Y2. The tuple (X2, Y2, Z2) in P matches.

So, the result of the selection is the joined tuple corresponding to P(X2, Y2, Z2) and R(Y2, V2). The resulting tuple is (X2, Y2, Z2, Y2, V2).

• Projecting X from (X2, Y2, Z2, Y2, V2) gives {X2}.

So, the result of the first part is the set {X2}.Part 2: $\Pi_X(\sigma_{Q.Y=R.Y \wedge Q.T>2} (Q \times R))$

• Q.Y = R.Y
• Q.T > 2

First, let's find tuples in Q where Q.T > 2:

• (X1, Y2, 5)
• (X1, Y1, 6)

Now, we find tuples in R that can join with these based on the Q.Y = R.Y condition:

• For Q tuple (X1, Y2, 5), we need tuples in R with R.Y = Y2. The matching tuples are (Y2, V3) and (Y2, V2).
• For Q tuple (X1, Y1, 6), we need a tuple in R with R.Y = Y1. The matching tuple is (Y1, V1).

The tuples resulting from the selection are:

• (X1, Y2, 5, Y2, V3)
• (X1, Y2, 5, Y2, V2)
• (X1, Y1, 6, Y1, V1)

3. Projection ($\Pi_X$): We project the X attribute from the result of the selection.

• Projecting X from the three resulting tuples gives {X1, X1, X1}. Since projection returns a set, duplicates are removed.
• The result is {X1}.

So, the result of the second part is the set {X1}.Final Step: Set Difference (-)We now perform the set difference between the results of the two parts:
Result of Part 1 - Result of Part 2
= {X2} - {X1}
= {X2}The final result is the set {X2}, which contains one tuple.Therefore, the number of tuples returned by the query is 1.