GATE CSE 2011 | Question: 46

Question

Database table by name $\text{Loan_Records}$ is given below.
$$\begin{array}{|c|c|c|} \hline \textbf {Borrower} & \textbf {Bank_Manager} &\textbf {Loan_Amount} \\\hline  \text{Ramesh }& \text{Sunderajan} & 10000.00 \\\hline \text{Suresh} & \text{Ramgopal} & 5000.00 \\\hline \text{Mahesh} & \text{Sunderajan} & 7000.00\\\hline \end{array}$$
What is the output of the following SQL query?

SELECT count(*)
FROM (
    SELECT Borrower, Bank_Manager FROM Loan_Records) AS S 
    NATURAL JOIN
    (SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T
);

• A. $3$
• B. $9$
• C. $5$
• D. $6$

Comments

How the tables are joined in this , please somebody explain ?
@Arjun sir

---

trick here is before applying Natural join take out the tables S & T and then we find that only one column(attribute) Bank_Manager is common for both the tables, and then as we know Natural Join will apply equality condition on common attribute of both tables, i.e., Bank_Manager, and then apply cross product, you will find best answer meaningful.

---

But sir here bank_manager is common attribute then loss less decomposition not working here?

 

Lossless and lossy working only cross product? Not in join

---

@Ankur29 for lossless decomposition, the common attribute must be a key in one of the two decomposed relations. Here it is not the case and therefore it is lossy.

Also, i think Cartesian products will be lossy most of the time except few cases where the original relation itself is total order relation between the two decomposed tables.

Answer 1

The answer is (C).

When we perform the natural join on $S$ and $T$ then result will be like this
$${\begin{array}{|c|c|c|}\hline
\textbf{Borrower}&   \textbf{Bank_Manager}&  \textbf{Loan_Amount} \\\hline
\text{Ramesh}&     \text{Sunderajan}&    10000.00  \\ \hline  
\text{Ramesh}&     \text{Sunderajan}&7000.00 \\     \hline
\text{Suresh}&     \text{Ramgopala}&  5000.00    \\\hline
\text{Mahesh}&     \text{Sunderajan}& 10000.00     \\\hline
\text{Mahesh}&     \text{Sunderajan}&   7000.00 \\\hline   
\end{array}}$$
After that count (*) will count total tuples present in this table  so here it is $5.$

Comments

@learncp: just once as you can see in the output above

---

No sir I have some doubt in it.How this table comes after joining.Plz elaborate it

---

Cross Product both the tables then match common attributes (here common attribute in both the tables is Bank Manager) Select only matching attributes then you will get the table as above.

Answer 2

best answer