2.4k views

Consider the following relational schema:

$\text{Suppliers}(\underline{\text{sid:integer}},\text{ sname:string, city:string, street:string})$

$\text{Parts}(\underline{\text{pid:integer}}, \text{ pname:string, color:string})$

$\text{Catalog}(\underline {\text{sid:integer, pid:integer}}, \text{cost:real})$

Consider the following relational query on the above database:

SELECT	S.sname
FROM    Suppliers S
WHERE S.sid NOT IN (SELECT C.sid
FROM Catalog C
WHERE C.pid NOT IN (SELECT P.pid
FROM Parts P
WHERE P.color<>'blue'))


Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?

1. Find the names of all suppliers who have supplied a non-blue part.

2. Find the names of all suppliers who have not supplied a non-blue part.

3. Find the names of all suppliers who have supplied only non-blue part.

4. Find the names of all suppliers who have not supplied only blue parts.

SELECT P.pid FROM Parts P WHERE P.color<>’blue’

Select all non blue parts

SELECT C.sid FROM Catalog C WHERE C.pid NOT IN

Selects all suppliers who have supplied a blue part

SELECT	S.sname
FROM    Suppliers S
WHERE S.sid NOT IN

Selects suppliers who have not supplied any blue parts.

So, none of the options matches.

Option C is wrong as it does not select suppliers who have not supplied any parts which the given query does.

Option A is wrong because it even selects those suppliers who have supplied blue and non-blue parts and also does not include those suppliers who have not supplied any parts.

edited by
but it has been mentioned in question that assume that the relations in above schema are not empty
@Aanshi yes, all relations are assumed non empty - but how it helps?
But what is wrong with D?
If a person does not supply any part. he is still not supplying only blue parts right?

Arjun Sir is not it D?

SELECT P.pid FROM Parts P WHERE P.color<>’blue’ select product ids of all non blue parts

SELECT C.sid FROM Catalog C WHERE C.pid NOT IN(SELECT P.pid FROM Parts P WHERE P.color<>’blue’) : supplier who have not supplied any non blue part , ie. the supplier who have supplied only blue part

therefore,

SELECT S.sname FROM Suppliers S WHERE S.sid NOT IN(SELECT C.sid FROM Catalog C WHERE C.pid NOT IN(SELECT P.pid FROM Parts P WHERE P.color<>’blue’)) : supplier who have supplied at least one non blue part ie. supplier who have not supplied only blue parts hence option d.

SELECT C.sid FROM Catalog C WHERE C.pid NOT IN(SELECT P.pid FROM Parts P WHERE P.color<>’blue’) :

supplier who have not supplied any non blue part , ie. the supplier who have supplied only blue part

This is wrong. This selects suppliers who have supplied at least one blue part. You can think why or try an example.
Yes , got it .. :) Thanks ..
thanks Arjun Sir....
wrong.. this query will select all those suppliers who do not supply only blue parts and it also select supplies even he does not supply any parts

So had it been like :

SELECT	C.sid
FROM    Catalog C
WHERE C.sid NOT IN

then option C(just sids in place of names) would have been correct right?

@Arjun Sir

55 (d)

put a venn diagram for this u will get it

for 56) A it is in bcnf

since, super key are id, (name, city)

street can be given by ((name,city), and id)
so if  a supplier who has supplier a red part and a blue part then should it be reflected in the output of this query ?? according to D it should be in the output but the query wont show such records as it will only show the suppliers who have supplied only non blue parts
what's  wrong  with  A..?
@Arvind how to draw venn diagram
@Aravind pls explain how to draw venn diagram for this
Exactly, What's wrong with A? Please explain?
–1 vote

The subquery “SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’” gives pids of parts which are not blue. The bigger subquery “SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’)” gives sids of all those suppliers who have supplied blue parts. The complete query gives the names of all suppliers who have supplied a non-blue part

and in made easy book they also given A so please confirm and i also think A because in your answer also you have said

"Selects suppliers who have not supplied any blue parts." so in other words it can be said as  names of all suppliers who have supplied a non-blue part. and that is option A