DBMS RAGHU RAMAKRISHNAN

Question

 

Relation schema is:-

suppliers(sid,sname,addr)

parts(pid,pname,color)

catalog(sid,pid,cost)

 

please expalin what does the above query do?

specially how this inner query working?

Comments

inner query means  cross multiplication or join of two table?

No mam,

There are basically two type of queries

1) Nested Query

       again divided as Co-related query and Non-corelaed quey.

2) Non-Nested Query

But every Nested query can be written as Non-Nested query with the help of Joins or Cartesian Products.

---

then  how u got tuple (2,102,...)??

---

queries are written but entire tuples are different.

Answer 1

The given query is Co-related Nested query and it's execution order it TOP-BOTTOM-TOP.

Query is

SELECT C.sid
FROM Catalog C
WHERE NOT EXISTS (SELECT P.pid
                  FROM Parts P
                  WHERE P.color='Red'
                  AND (NOT EXISTS (
                  SELECT C1.sid
                  FROM Catalog C1
                  WHERE C1.sid=C.sid 
                  AND
                  C1.pid=P.pid
                  )))

Here we have 2 cases

Case 1: No red Color part is available: In this case 

                             SELECT P.pid FROM Parts P WHERE P.color='Red'

Would return an empty row Set and the "AND" condition after it would fail, so inner query result set would be empty always and hence NOT exists true always and hence all suppliers from the Catalog relation would be listed.

Case 2: Red Color part is available:

SELECT C.sid
FROM Catalog C

 Select a tuple from Catalog C, and take it's sid(C.sid)

             SELECT P.pid
                  FROM Parts P
                  WHERE P.color='Red' .........(1)
   
                  AND
                (NOT EXISTS
                   (
                  SELECT C1.sid
                  FROM Catalog C1
                  WHERE C1.sid=C.sid 
                  AND
                  C1.pid=P.pid ...................(2)
                  )))

    Condition (1) would select all RED color parts, with their part ID's

    Condition (2) will take the Sid from the outermost query (C.sid) and for this supplier, it will check whether he has supplied a Red color part, and if he does, he will be enlisted by condition (2).

But NOT EXISTS is applied on condition (2) and this will cause to select only those Red color part and their part Id's, which this supplier(C.sid) has not supplied.

Consider there are three red color parts (P1,P2,P3) and there are two suppliers S1 which supplies P1,P2 and Supplier S2 suppliers P1,P2,P3.

For Supplier S1,

Condition (1) would enlist P1,P2,P3

Condition (2) along with NOT EXISTS would result in FALSE for Part P1 and P2 , but true for P3.

so Condition 1 AND 2 would result in tuple with part Id P3 and this is a RED color part not Supplied by Supplier S1.

Since NOT Exists is present in the outermost query, this supplier S1, would be rejected from the result set.

For Supplier S2,

Condition (1) would enlist P1,P2,P3

Condition (2) along with NOT EXISTS would be false for All parts P1,P2,P3 and hence

Condition (1) AND Condition (2) would be false and result in an empty result set, resulting in setting NOT EXISTS clause in the outer query to be TRUE, and Supplier S2 would be enlisted in result set.

Hence, the given query selects all Suppliers who supply every red Part provided Red parts were Available.

Comments

@Ayush

u stated from middle query?

from red part?

Can u plz chk Shaik table once?

It is only returning 1, and 3

But 1 is accepting 103 too.

Can u tell me, how is it running?

---

@srestha-You mean to say the seller with seller Id 1 is also selling a GREEN part and still getting in result set?

See, here we don't bother whether a seller sells any NON-Red part.But if Red parts are available in the Parts relation, then only those seller will be listed who sells all red parts.

What your inner query does is, first it selects all the Red parts from Parts relation, then for a given seller it checks to see, whether from all red parts present in the Part relation, is there any part which is NOT sold by this seller and if there exist such part, then inner query returns that PART.And since then in this case, the inner query becomes non-empty, then outer query's NOT EXISTS becomes false, thereby claiming the fact that there exists atleast one red Part which is not sold by this seller and hence this seller was rejected from the result set.So, only those seller are enlisted who sell all Red parts.