TIFR CSE 2010 | Part B | Question: 33

Question

In a relational database there are three relations:

• $Customers = C \textsf{(CName)}$
• $Shops = S \textsf{(SName)}$
• $Buys = B \textsf{(CName, SName)}$

Then the Relational Algebra expression ( $\Pi $ is the projection operator).

                   $C-\Pi _\textsf{CName}((C \times S)-B)$

returns the names of 

• A. Customers who buy from at least one shop.
• B. Customers who buy from at least two shops.
• C. Customers who buy from all shops.
• D. Customers who do not buy buy anything at all.
• E. None of the above.

Comments

This one is really nice question.

---

Detailed Video Solution, with Complete Analysis: https://youtu.be/h3pJZbed9M8?t=3584&feature=shared

---

option $C$ is correct. 

Answer 1

It is division in relational algebra 
Division = ${\pi_{AB}} (R) /{\pi_ {B}} (S)$      Results in 'A' values for which here should be 'B' in R for every 'B' of S.

${\pi _{AB}}(R)/{\pi_{B}} (S) = {\Pi _{A}}(R) -{\pi _ {A}}({\pi _ {A}}(R)\times S-R)$  Retrieve all A's who are related to every B

$C−{\Pi_{CName}}((C\times S)−B)$

$C\times S$ gives the complete relation of each customer to every shop

$(C\times S)−B)$ :gives the relation of the customer which is not related to every shop.

${\Pi_{CName}}((C\times S)−B)$: gives the customer name who is not related to every shop.

$C−{\Pi_{CName}}((C\times S)−B)$: gives the customer who is related to every shop.

Option C) Customers who buy from all shops.

Comments

So answer will be either "no output" or Customers who buy from all shops.

---

Customers who buy from all shops. already includes no output case.

---

this question has some match https://gateoverflow.in/25872/tifr2013-b-19

Answer 2

lets solve by taking example

Customer                                                                         

Customer name

A

B

C

                                          

Shop

name

1

2

C*S will be possible combinations of customers and shop(cartesian product)

∏name (C*S-B ) will give names of customers who do not went to all shop in our example its  B and C

now when we C-∏name (C*S-B ) we get A as output

so output will be name of employees who went to every shop

Answer 3

Ans is C

Answer 4

According to me Best approach to solve such question  is by taking example which includes all possible options.

In case one of option is given "none of above" in that case be also check whether final answer returns null , or multiple value.

This approach is time taking but gives correct result.

customers : A,B,C,D  shops:  P1,P2,P3

Buys

A	P1
B	P2
B	P2
C	P1
C	P2
C	P3

reason why only this table taken 

first row :  

Customers who buy from at least one shop. -> then A must be there, and D will not be there

second and third row

Customers who buy from at least two shops.-> then B will be there and A, D will not be there

4,5,6th

Customers who buy from all shops. -> then C will be there A, B, D will not be there.

No row for D: 

Customers who do not buy buy anything at all. ->D will be  there A, B, C will not be there

 

ΠCName((C×S)−B)

gives 

A	P2
A	P3
B	P1
D	P1
D	P2
D	P3

I know projection will return just (A, B, D) , but for understanding purpose i have written like this

final answer 

C

Comments

Nice explanation, thanks!

Answer 5

SQL query would be

 

SELECT C.CNAME

FROM CUSTOMERS C

WHERE

NOT EXISTS

(

                    SELECT S.SNAME

                    FROM SHOPS S,CUSTOMERS C

                    WHERE (C.CNAME == S.SNAME )

                   EXCEPT (

                                          SELECT C.NAME

                                           FROM BUYS B

                                           WHERE C.CNAME=B.CNAME

                                           )

)

 

OPTION C: THIS IS HOW SQL AND TRC WORK ALL TOGETHER

Answer 6

Read it from the inside.

C×S

All possible: (Customer,Shop) pairs.

(C×S)−B

Pairs where the customer doesn't buy from that shop.

πC​(...)

Customers who don't buy from at least one shop.

C−πC​(...)

Customers who don't fail anywhere.

Therefore:

customers who buy from ALL shops​