7.8k 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.

retagged | 7.8k views
+2

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

+2

@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

0

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

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

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.

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.

by Veteran (431k points)
edited by
0

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

suppliers who have supplied non blue parts ???

+4
for 55, best possible ans here would be c ? if we consider each supplier is at least supplying sm part
0
@Anurag yes.
0

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?

0
street name is unique inside a city. But <city, street> can repeat when we have multiple suppliers from same street in a city.
0
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??
0

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

0

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

0
I guess my interpretation was wrong- I have corrected. sname is the only identifier for street- so there won't be any more FD.
0
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.
+2
No, given query also select suppliers who have not supplied any parts.
+1
very well explained Sir!!
+2
0
but it has been mentioned in question that assume that the relations in above schema are not empty
0
@Aanshi yes, all relations are assumed non empty - but how it helps?
0
But what is wrong with D?
If a person does not supply any part. he is still not supplying only blue parts right?
0

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.

+6
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.
0
Yes , got it .. :) Thanks ..
0
thanks Arjun Sir....
0
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
0

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

0
just make condition that all supplire supplied atleast one part then option c is correct.
0
while designing que they have assumed that all suppliers supply some part and hence option c is correct
+2

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.

0
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?
0
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"
+6

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.

0

0

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

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)
by Active (3.4k points)
0
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
+1
what's  wrong  with  A..?
+1
@Arvind how to draw venn diagram
0
@Aravind pls explain how to draw venn diagram for this
0
Exactly, What's wrong with A? Please explain?
+1

is it right???

0
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.
+1
 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

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.

by Junior (799 points)

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

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

by Active (1.4k points)