GATE CSE
First time here? Checkout the FAQ!
x
+13 votes
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.

asked in Databases by Veteran (64.6k points)   | 2.4k views

3 Answers

+24 votes
Best answer
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 (295k points)  
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

 

+3 votes
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 Loyal (3.3k points)  
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

source - http://www.geeksforgeeks.org/gate-gate-cs-2009-question-55/

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 Loyal (3.2k points)  
Answer:

Related questions



Top Users Sep 2017
  1. Habibkhan

    7194 Points

  2. Warrior

    2686 Points

  3. Arjun

    2594 Points

  4. rishu_darkshadow

    2568 Points

  5. A_i_$_h

    2280 Points

  6. nikunj

    1980 Points

  7. manu00x

    1856 Points

  8. makhdoom ghaya

    1770 Points

  9. Bikram

    1744 Points

  10. SiddharthMahapatra

    1718 Points


26,164 questions
33,742 answers
79,996 comments
31,125 users