GATE CS 2011 Q46 — Tuple Calculus and SQL

To find the output of the SQL query, let us break down the subqueries and trace the NATURAL JOIN operation step-by-step.

Step 1: Analyze Subquery $S$

The subquery $S$ selects the columns Borrower and Bank_Manager from the Loan_Records table:

$$S = \begin{array}{|c|c|} \hline \textbf{Borrower} & \textbf{Bank\_Manager} \\ \hline \text{Ramesh} & \text{Sunderajan} \\ \text{Suresh} & \text{Ramgopal} \\ \text{Mahesh} & \text{Sunderajan} \\ \hline \end{array}$$

Step 2: Analyze Subquery $T$

The subquery $T$ selects the columns Bank_Manager and Loan_Amount from the Loan_Records table:

$$T = \begin{array}{|c|c|} \hline \textbf{Bank\_Manager} & \textbf{Loan\_Amount} \\ \hline \text{Sunderajan} & 10000.00 \\ \text{Ramgopal} & 5000.00 \\ \text{Sunderajan} & 7000.00 \\ \hline \end{array}$$

Step 3: Perform the NATURAL JOIN

A NATURAL JOIN joins the two relations on their common attribute, which is Bank_Manager. We match rows from $S$ and $T$ where the Bank_Manager values are identical:

• For Bank_Manager = Sunderajan:
  • Tuples in $S$: (Ramesh, Sunderajan), (Mahesh, Sunderajan) (2 tuples)
  • Tuples in $T$: (Sunderajan, 10000.00), (Sunderajan, 7000.00) (2 tuples)
  • Joining these yields $2 \times 2 = 4$ tuples:
    1. (Ramesh, Sunderajan, 10000.00)
    2. (Ramesh, Sunderajan, 7000.00)
    3. (Mahesh, Sunderajan, 10000.00)
    4. (Mahesh, Sunderajan, 7000.00)
• For Bank_Manager = Ramgopal:
  • Tuples in $S$: (Suresh, Ramgopal) (1 tuple)
  • Tuples in $T$: (Ramgopal, 5000.00) (1 tuple)
  • Joining these yields $1 \times 1 = 1$ tuple:
    1. (Suresh, Ramgopal, 5000.00)

Step 4: Count the Tuples

The total number of tuples in the resulting relation is $4 + 1 = 5$.

Thus, the query SELECT count(*) returns 5.