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

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

Sir, what about option (a) in 55 ??

suppliers who have supplied non blue parts ???

---

for 55, best possible ans here would be c ? if we consider each supplier is at least supplying sm part

---

@Anurag yes.

---

arjun am having some  problem in understanding this statement 

"each supplier and each street within a city has unique name"

doesnt' it mean that city,sname is unique hence it would be c.k ..

also street,city is unique therefore it is also c.k....

and so its in bcnf?

---

street name is unique inside a city. But <city, street> can repeat when we have multiple suppliers from same street in a city.

---

For question 56,they have strictly mentioned that no other fd's are possible other than prime and candidate keys then why we dont answer directly as BCNF??

---

"each supplier and each street within a city has unique name" doesn't mean (street,city)--> sname.  (city,sname)--> street is implied as it is candidate key otherwise if we just take the statement for figuring out the dependencies then we can also say that a supplier has two addresses(just like we say a street can have two suppliers) but since it is ck it is not possible. And since '(street,city)--> sname' doesn't hold this should be BCNF. 

---

@Marv

each supplier and each street within a city has unique name

what FD(s) you take from the above? It is surely an ambiguous statement. 

---

I guess my interpretation was wrong- I have corrected. sname is the only identifier for street- so there won't be any more FD.

---

For Q55 option C is correct, since if a supplier exists in the catalog he must sell a part too because (Sid,Pid) is a primary key and therefore can't be NULL. So, if a supplier isn't selling a blue part he must be selling a non-blue part.

---

No, given query also select suppliers who have not supplied any parts.

---

very well explained Sir!!

---

http://sqlfiddle.com/#!9/a6eb01/1/0

---

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

---

just make condition that all supplire supplied atleast one part then option c is correct.

---

while designing que they have assumed that all suppliers supply some part and hence option c is correct

---

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.

---

I find such type of questions very confusing. What would be the ideal way to solve such problems? Should i make tables with arbitrary data or solve verbally?

---

why have you even considered a case where supplier hasn't provided a part? when it is written in question that "Assume that relations corresponding to the above schema are not empty"

---

Let us consider 3 categories of sellers .

Class A-Sell only Blue parts

Class B-Sell only non-blue parts.

Class C-Sell Blue and Non-blue parts.

Now

SELECT C.sid 
                    FROM Catalog C 
                    WHERE C.pid NOT IN (SELECT P.pid 
                                        FROM Parts P 
                                        WHERE P.color<>'blue')

 

Above query will select All sellers belonging to Class A, and Class C(Because he sells atleast one blue part).

So, Now my outer query will reject class of sellers A and C from final result set, giving only sellers belonging to class 2.-Sellers who sell only non-blue parts or sellers who have not supplied any blue parts.

---

Thank you! @Ayush Upadhyaya

---

@Arjun Sir, For such question in SQL. What is the best approach to solve?

Should we think logically and go on eliminating options and select one which is relevant or should we take multiple exemplar values and then try solving.

Sir, please do reply! 

---

@Arjun Sir,for question 55 Line in the question 'Assume that relations corresponding to the above schema are not empty.' I think it means that there does not exist any supplier who did not sell any part which will make option c correct

---

@Arjun Sir, Don’t you think (C) would be the correct option since the query provided will never include suppliers who have not supplied any parts. The sid is selected from the relation Catalog which has it’s primary key as sid, pid and hence pid can’t be NULL.

---

A supplier SUPPLIES ,that is why he is called a Supplier , right? Then we can rightly assume that all suppliers would have atleast one entry in ‘CATALOG’ table. That way the answer is obviously (C).

---

@Arjun sir, since  sid and pid combination forms primary key in catalog, what if a person has supplied both blue and non blue parts, will his name be selected?

---

what was the answer in the official key?

---

i think c is correct… dont understand the reason for it being wrong…

which one should we go with if this question repeats???

---

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