2,422 views

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.]

1. $\Pi _{\textsf{SName}}B$
2. $S - B$
3. $S - \Pi _{\textsf{SName}}B$
4. $S - \Pi _{\textsf{SName}}((C \times S) - B)$
5. None of the above

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.

Well explained .

Just a suggestion :

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 )

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.

It subtract  shop names to those shop  which sells something.

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

by

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.

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

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}

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.
by

### 1 comment

Can u please explain Option D