Test by Bikram | Databases | Test 2 | Question: 29

Question

Let Depositor and Lender be relational schemas such that

Depositor = {a/c number, cname , balance, branch}  &  Lender ={loan_number ,cname, amount , branch}. 

Now, consider the following queries on the database:

Query $1$ : $( t \mid \exists D \in \text{ Depositor }( t.cname = D.cname ) \wedge \exists L \in Lender ( t.cname = L.cname ) ) $

Query $2$ : $( ( \pi \text{ cname Depositor }) \cap ( \pi \text{ cname  Lender }) ) $

Query $3$ : $\text{Select cname from Depositor d where exists ( select * from Lender } \mid \text{ where } \mid \cdot \text{cname = d.cname )}$ ;

 

Which of the following is a FALSE statement?

• A. Query 1 and Query 2 give same result.
• B. Query 2 and Query 3 give same result.
• C. Query 1 and Query 3 give same result.
• D. Query 1, Query 2 and Query 3 give different results.

Comments

@bikram sir please providethe correct answer of this

---

@vivek you are correct. The question is asking for the FALSE statement.

---

Actually,it is the correct statement because query 3 can give you duplicates and 1 returns whole tuple.

Answer 1

I want to modify the question as Which of the following is a true statement ?

if Query 1 : ( t | ∃D ∈Depositor ( t.cname = D.cname ) ∧  ∃L ∈Lender ( t.cname = L.cname ) ) 

Query 2 : ( ( π_cname Depositor )  ∩ ( π_cname  Lender ) ) 

Query 3 : Select cname from Depositor d where exists ( select * from Lender l where l.cname = d.cname ) ;

then answer is all three are producing different results why because first query is returning whole tuple therefore first query results different from query2 and query3.

query2 doesn't produce duplicate values where as query3 produce duplicate values....

if Query 1 : ( t.name | ∃D ∈Depositor ( t.cname = D.cname ) ∧  ∃L ∈Lender ( t.cname = L.cname ) ) 

Query 2 : ( ( π_cname Depositor )  ∩ ( π_cname  Lender ) ) 

Query 3 : Select cname from Depositor d where exists ( select * from Lender l where l.cname = d.cname ) ;

then answer is all Query 2 is producing different results form Query 1 and Query 3  why because  query 2 doesn't produce duplicate values.

query 3 produce duplicate values obvisiouly and query 1 is produce duplicate values why because resulting tuple of TRC is checked with each tuple in the given relation therefore if a duplicate name present in the relation (in two different tuples of relation) it should be printed

Comments

Accd. to me
{t| ∃ s ∈ loan(t[amount] = s[amount]  
                   ∧ s[amount]>=10000)} would give us amount columns of all with amount >=10000

I think gatebook's video was more explicit

---

if you still didn't get it, i can't help !

---

@Shaik Masthan

Because first query is returning whole tuple therefore first query results different from query2 and query3"

 Do you mean that the first query is printing all attributes of Depositor relation?
It will print matching "cname" only from Depositor as we are copying values only to t.cname:

 "t.cname=D.cname"

In the gatebook video you mentioned, please see that he makes the tuple t containing only the name and age field... and at 6:15,he says What are you printing? : "all the names and ages",

Snapshot from Korth to support my point: