in Databases retagged
38,091 views
68 votes
68 votes

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.

in Databases retagged
38.1k views

4 Comments

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

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.

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

8 Answers

101 votes
101 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.

edited by
by

4 Comments

The option (D) Find the names of all suppliers who have not supplied only blue parts may be the correct answer.
1
1
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
1
1
option C, if we assume that all supplier defiantly supply atleast one part, so that all supplier will exist in catalog() table.
0
0
8 votes
8 votes

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.

2 Comments

Lol Completely Wrong.Option (A) is wrong anyways.
1
1
is there any such short cut? if not is taking random tables and simulating the only way to answer such questions...
0
0
2 votes
2 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)

4 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.
0
0
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

1
1

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. 

1
1
1 vote
1 vote

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

Answer:

Related questions