GATE CSE 2009 | Question: 55

Question

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?

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

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

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

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

Comments

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

                          

---

@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

---

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

---

Here given option C is wrong. (c)  who have supplied only blue parts

---

Similar example to understand this question:

$\\ Select\ S.sname\\ from\ sailors\ S\\ where\ S.sid\ NOT\ IN(Select\ R.sid\\ \quad \quad \quad \quad \quad\quad\quad\quad\quad\ from\ reserves\ R\\ \quad \quad \quad \quad \quad\quad\quad\quad\quad\ where\ R.bid\ NOT\ IN(Select\ B.bid\\ \quad \quad \quad \quad \quad\quad\quad\quad\quad\quad \quad \quad \quad \quad \quad\quad\quad\quad\quad from\ boats\ B\\ \quad \quad \quad \quad \quad\quad\quad\quad\quad\quad\quad \quad \quad \quad \quad\quad\quad\quad\quad where\ B.color= red))$

 

Output: Collection of sailor names who have not reserved anything otherwise if at all they have reserved anything then they have reserved only red boats.

---

which answer was given marks in gate 2009 for this question can't we ever know for sure ..

Answer 1

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.

Comments

The option (D) Find the names of all suppliers who have not supplied only blue parts may be the correct answer.

---

i think C is more appropriate, D is not correct because it will include a supplier who has provided suppose a blue and a red part which is not the case. Here we want suppliers who have not supplied any blue part

---

option C, if we assume that all supplier defiantly supply atleast one part, so that all supplier will exist in catalog() table.

Answer 2

There is a simple trick in the question to answer it. Just see the no of time not is coming and map it to boolean function.

i.e Not(Not(Not blue)) = Not(blue) thus supplied id of who supplied a non-blue part.

Comments

Lol Completely Wrong.Option (A) is wrong anyways.

---

is there any such short cut? if not is taking random tables and simulating the only way to answer such questions...

Answer 3

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)

Comments

what is the difference between "names of all supliers who have supplied only non-blue parts"  &  "Selects suppliers who have not supplied any blue parts"  which is given in the answear.

---

supplier	part suppied
A	red
A	blue
B	blue
C	red
D	NULL

suppose there is red(non-blue) part and blue parts only.

 

names of all suppliers who have supplied only non-blue parts

it means names of all suppliers who have supplied only red parts i.e. C

 

Selects suppliers who have not supplied any blue parts i.e. C,D

it means suppliers who have not supplied any blue parts

---

The culprit with A and C is "There may be supplier who has not supplied any part", which makes both A and C false. So, none of these is answer. 

Answer 4

FIRST of all there is a MANY-TO-MANY relation between 'sid' & 'pid' in Catalog.

So "A SUPPLIER MIGHT HAVE SUPPLIED BOTH BLUE & NON BLUE PARTS".

The Above Query will Return 'sname' if atleast COMPULSORILY 1 part supplied by a Supplier is NON-BLUE. But it is possible that the supplier might also have Supplied A Blue Part bcoz of many-to-many relation. 

Means atleast 1 should by compulsory non-blue for name to be printed. Other parts color doesnt matter .

opt (a) : TRUE -> If he has supplied a (atleast 1) non blue part , his name will be printed , he might have also supplied a blue part but doesn't matter , opt (a) has no problem with that . 

opt (b) : FALSE -> Inverse of opt(a) so FALSE.

opt (c) : FALSE -> As said above , he might have supplied both blue and non - blue bcoz of many-to-many relation.

opt (d) : FALSE -> GIVEN THAT ->Find the names of all suppliers who have not supplied only blue parts.

                               It might be confusing to understand this english .

                              -> "not supplied only blue parts"  means they assume that there are already compulsorily BLUE parts supplied.                                      Means there should be COMPULSORILY BLUE supplied , along with that , also some NON BLUE part                                                       also supplied by the SUPPLIER .

 

                                BUT THE QUERY IN QUESTION RETURNS TRUE ALSO WHEN THERE ARE ONLY NON BLUE Parts supplied                                  and no BLUE PARTS at all .

                                BUT opt (d) assumes that there are already blue parts bcoz of these 5 words : "not supplied only blue parts".