GATE CSE 2019 | Question: 55

Question

Consider the following relations $P(X,Y,Z), Q(X,Y,T)$ and $R(Y,V)$.
$$\overset{\textbf{Table: P}}{\begin{array}{|l|l|l|} \hline \textbf{X} & \textbf{Y} & \textbf{Z} \\\hline \text{X1} & \text{Y1} & \text{Z1} \\\hline \text{X1} & \text{Y1} & \text{Z2} \\\hline \text{X2} & \text{Y2} & \text{Z2} \\\hline \text{X2} & \text{Y4} & \text{Z4} \\\hline  \end{array}} \qquad \overset{\textbf{Table: Q}}{\begin{array}{|l|l|l|} \hline  \textbf{X} & \textbf{Y} & \textbf{T} \\\hline \text{X2} & \text{Y1} & 2 \\\hline \text{X1} & \text{Y2} & 5 \\\hline \text{X1} & \text{Y1} & 6 \\\hline \text{X3} & \text{Y3} & 1 \\\hline  \end{array}} \qquad \overset{\textbf{Table: R}}{\begin{array}{|l|l|} \hline \textbf{Y} & \textbf{V}  \\\hline \text{Y1} & \text{V1} \\\hline \text{Y3} & \text{V2} \\\hline \text{Y2} & \text{V3} \\\hline \text{Y2} & \text{V2} \\\hline  \end{array}}$$
How many tuples will be returned by the following relational algebra query?

$\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))$

Answer: ________

Comments

Only X2 will be selected as Set Difference operation is being performed in the last step::

              {X2} – {X1} which projects only X2.

---

small observation i want to add here ::

if you liik at the quesry after minus in right side:

q.t>2 ---->if you will apply this condition on relation then only 2 tuple of q you will get and in both tuple X value is

X1 and after that you will look at projection then that projection is on X so only value of X will get selected and that will

be X1 only no need to do cross product and join table.

---

Detailed Video Solution: https://youtu.be/xa9WX9R2nXg

Answer 1

$R \cdot V = V2$, there are two tuples which have $Y$ parameter as $Y3$ and $Y2$.

$P \cdot  Y = R \cdot Y$, there are no coincide with $Y3$, and there is one tuple coincide with $Y2$ which have $X$ parameter as $X2$.

$\Pi_X (\sigma _{(P.Y=R.Y\; \Lambda \;R.V = V2)}(P \times R)) = \{X_2\}$

$Q \cdot T > 2$, there are two tuples which have $Y$ parameter as $Y1$ and $Y2$ which have $X$ parameter as $X1$.

check atleast one tuple is matched $Q.Y=R.Y$ in this query, that's enough to result $X1$.

$\Pi_X (\sigma _{(Q.Y=R.Y\; \Lambda \;Q.T >2)}(Q \times R)) =  \{X_1\}$

$\Pi_X (\sigma _{(P.Y=R.Y\; \Lambda \;R.V = V2)}(P \times R)) - \Pi_X (\sigma _{(Q.Y=R.Y\; \Lambda \;Q.T >2)}(Q \times R)) = \{X_2\} - \{X_1\} = \{X_2\}$

Number of Tuples $= 1$

Comments

@Shubham_Singh_4097, clarification of your doubt already answered in comment section. You just need to read the discussion 

---

@Shubham_Singh_4097 If it was like  (x1)-(x1) then it's 0. Or like (x1)-(x1,x2) then also 0. 

Inshort, (x1)- (x1, anything)=0

These are basic set operations

---

At last i got 2 sets as (X2) - (X1, X1, X1)

 

Answer 2

At last, (X1 X1 X2 X2) - (X1 X1 X1) will be executed leading to result of only 1 tuple i.e X2.

Answer 3

🔹 Step 1: π_X(σ(P.Y = R.Y ∧ R.V = V2)(P × R))

• R.V = V2 → (Y3, V2) and (Y2, V2)

• Match P.Y = R.Y:

  • P has Y2 → (X2, Y2, Z2)

  • P has no Y3

• So selected X: X2

✅ Result of Part 1: {X2}

🔹 Step 2: π_X(σ(Q.Y = R.Y ∧ Q.T > 2)(Q × R))

• Q.T > 2 → (X1, Y2, 5), (X1, Y1, 6)

• Match R.Y:

  • R has Y2 and Y1 → both match

• So selected X: X1

✅ Result of Part 2: {X1}

🔹 Final Set Difference:

{X2} − {X1} = {X2}

✅ Final Answer:

1 tuple selected: X2

Comments

@Ujjwal_Nikam

Do you mean this is your own answer or it is from someother AI model ?

---

@Shaik Masthan I typed the approach in a rough way, and it was formatted by another AI model.

---

However, that is already covered in the answers/comments available for this question.

Answer 4

Thus only 1 tuple with X2 in it