TIFR CSE 2013 | Part B | Question: 19

Question

In a relational database there are three relations:

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

Which of the following relational algebra expressions returns the names of shops that have no customers at all? [Here $\Pi$ is the projection operator.]

• A. $\Pi _{\textsf{SName}}B$
• B. $S - B$
• C. $S - \Pi _{\textsf{SName}}B$
• D. $S - \Pi _{\textsf{SName}}((C \times S) - B)$
• E. None of the above

Comments

Customers as $C$

Cname

A

B

C

D

Shops as $S$

Sname

$s_{1}$

$s_{2}$

$s_{3}$

Buys as $B$

Cname	Sname
A	$s_{1}$
B	$s_{2}$
C	$s_{1}$
D	$s_{1}$

In this example I take  

   $s_{1}$ is the Shop name, who have at least three Customers(A,C,D)

   $s_{2}$ is the Shop name, who have at least one Customers(B)

   $s_{3}$ is the Shop name who do not have any Customers(the names of shops that have no customers at all)

$(A)\Pi_{Sname}B$

Sname

$s_{1}$

$s_{2}$

$(B)S-B$

 It is not possible, because it is not Subtraction compatible.

$(C)S-\Pi_{Sname}B$

$\Pi_{Sname}B$

Sname

$s_{1}$

$s_{2}$

Now, $S-\Pi_{Sname}B$

Sname

$s_{3}$

So,this is true.

$(D)S-\Pi_{Sname}((C\times S)-B)$

Now, $C\times S$

Cname	Sname
A	$s_{1}$
A	$s_{2}$
A	$s_{3}$
B	$s_{1}$
B	$s_{2}$
B	$s_{3}$
C	$s_{1}$
C	$s_{2}$
C	$s_{3}$
D	$s_{1}$
D	$s_{2}$
D	$s_{3}$

Now,$(C\times S)-B$

Cname	Sname
A	$s_{2}$
A	$s_{3}$
B	$s_{1}$
B	$s_{3}$
C	$s_{2}$
C	$s_{3}$
D	$s_{2}$
D	$s_{3}$

Now,$\Pi_{Sname}((C\times S)-B)$

Sname

$s_{1}$

$s_{2}$

$s_{3}$

Here clearly see $S-\Pi_{Sname}((C\times S)-B)=\phi$

So,$(C)$ is the right choice

please correct me if i''m wrong.

---

@Lakshman Patel RJIT 

Well explained .

Just a suggestion : 

In Buys B , try to satisfy ( no buys, all buys, some buys) conditions so that no option will be left.

for eg. : i think your example can not catch option D ( though not needed in this question)  if question were " find  equivalent expression that gives name of customer that bought from all shops.

( may be i am not fully correct but think over it )

---

@HeadShot

as this question, I frame an example, if they ask, which expression is equivalent, I can frame another example.

I think $(D)$ option works fine for this example.

thank you for suggesting me.

---

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

Analysis of Option D is also discussed in above video.

Answer 1

Answer will be (C)

It subtract  shop names to those shop  which sells something.

So as a result we are getting shops which have no customer.

Comments

chk it :https://gateoverflow.in/19246/tifr2010-b-33

---

@srestha @Satbir

I understand $\mathbf C$ is the correct answer.

But what is fundamentally wrong with option $\mathbf A$.

It doesn't contain the name of any customers, right?

It only contains shop names which can be NUL as well.

Can you please explain this.

 

---

what is $CName$ then in $B$ ?

$B$(buys) in option $A$ is a relation which denotes customer having $CName$ buys from shop named $Sname$ and in option $A$ we are simply projecting all the shop names.

see the table given by Lakshman patel

Answer 2

c)                                               S                            −                   Π_SNameB
                                                  ↑                                                       ↑
since it has only one attribute no need of projection       it will project all the shop name which has at least                          it will project all the shop name                                         one customer

s1                                                                                                       c1   s1 
s2                                                                                                       c2   s2
s3
s4      

{s1,s2,s3,s4} - {s1,s2} = {s3,s4}          

Answer 3

1. $\Pi$SNameB  = shops name from which atleast one customer buys.
2. S−B  =  not Subtraction compatible .
3. S−$\Pi$SNameBS  = Shops from which no customer buy.
4. S−$\Pi$SName((C×S)−B)  = shops name from which every customer buy.

Comments

Can u please explain Option D

---

@anchitjindal07

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

Analysis of Option D is also discussed in above video.