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.

retagged | 4k views
0

Let Parts Table( i am ignoring the attributes which are not neede)

pid:integer  color:string

1                  blue

2                   green

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

Let Catalog     (sid:integer, pid:integer)

10              1

11              2

13              1

13              2

SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (pid-2)---------------gives 10, 13

Let suppliers table sid(10, 11, 12, 13)

SELECT	S.sname
FROM    Suppliers S
WHERE S.sid NOT IN  (10, 13) gives 11, 12

11 is the supplier who supplied green, 12 is the supplier not supplied anything

10 is the supplier supplied blue part, 13 is the supplier supplied blue and green part.

we got 11, 12

1. Find the names of all suppliers who have supplied a non-blue part.   -( a implying 11,13------- wrong not matching with 11,12)

2. Find the names of all suppliers who have not supplied a non-blue part. - ( b implying 10, 12)

3. Find the names of all suppliers who have supplied only non-blue part. -wrong eventhough 12 not supplied anything we got it( c implying we should get only 11 but we got 11, 12)

4. Find the names of all suppliers who have not supplied only blue parts.( d implying  11 and 12 not supplied blue parts so it is matching with our query)

So i think d is the answer

0

@Dileep

Option D says, Find the names of all suppliers who have not supplied only blue parts.

So the suppliers may supplied blue parts and non blue parts

0

Let say shopkeeper A sells blue B sells Green C sells nothing D sells Blue and green

shopkeepers who have not selling only blue. ------------- we say B, C right is anything wrong here @Harish

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.

answered by Veteran (342k points)
edited by
0
But what is wrong with D?
If a person does not supply any part. he is still not supplying only blue parts right?
0

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.

+1
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.
0
Yes , got it .. :) Thanks ..
0
thanks Arjun Sir....
0
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
0

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

0
just make condition that all supplire supplied atleast one part then option c is correct.
0
while designing que they have assumed that all suppliers supply some part and hence option c is correct
0

Arjun sir is correct.

None of the option is correct.

Option (c) may look to be correct but it will not select those suppliers who have not supplied any part but the given query does.

Below results may be useful.

The above was dummy data on which query was executed. In catalog, respective part colors are highlighted.

Below is the sql result

S3 is the supplier which supplies only blue part and s5 does not supply any part.

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)
answered by Active (3.3k points)
0
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
+1
what's  wrong  with  A..?
+1
@Arvind how to draw venn diagram
0
@Aravind pls explain how to draw venn diagram for this
0
Exactly, What's wrong with A? Please explain?

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

answered by Active (1.3k points)