GATE 2025 DA Q.62: solution and answer

Official answer: 1Source: Official answer key, GATE 2025 DA (IIT Roorkee)

1. Division $R÷S$ returns every value of the attribute that is in $R$ but not in $S$ (here customer_name) that is paired in $R$ with every tuple of $S$. The query is ${\pi }_{\text{branch\_name, customer\_name}}(\text{Loan}\bowtie \text{Borrower})÷{\pi }_{\text{branch\_name}}(\text{Loan})$.

2. The divisor is $S={\pi }_{\text{branch\_name}}(\text{Loan})=\{\text{Banjara Hills, Kondapur, SR Nagar, Balanagar}\}$. Duplicates collapse, so $S$ has 4 branches.

3. Natural join on loan_num, row by row: L11 gives (Banjara Hills, Anand) and (Banjara Hills, Karteek); L14 gives (Kondapur, Karteek); L15 gives (SR Nagar, Ankita); L19 gives (SR Nagar, Gopal); L22 gives (SR Nagar, Karteek); L23 gives (Balanagar, Karteek) and (Balanagar, Sunil); L25 gives (Kondapur, Sunil). After projection onto (branch, customer) there are 9 distinct tuples.

4. Group the branches by customer: Anand $\{\text{Banjara Hills}\}$; Ankita $\{\text{SR Nagar}\}$; Gopal $\{\text{SR Nagar}\}$; Sunil $\{\text{Balanagar, Kondapur}\}$; Karteek $\{\text{Banjara Hills, Kondapur, SR Nagar, Balanagar}\}$.

5. A customer is in the result only if their set of branches contains all 4 branches of $S$. Karteek does. Sunil lacks Banjara Hills and SR Nagar, and the others each have one branch.

6. The result is $\{\text{Karteek}\}$, so the query returns 1 tuple.