retagged
38,209 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.

retagged

8 Answers

Best answer
101 votes
101 votes
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
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 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)
1 votes
1 votes

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

61 votes
61 votes
6 answers
1
39 votes
39 votes
5 answers
2
Kathleen asked Sep 22, 2014
43,278 views
What is the maximum height of any AVL-tree with $7$ nodes? Assume that the height of a tree with a single node is $0$.$2$$3$$4$$5$
30 votes
30 votes
2 answers
4