CODEWITHSUNDEEP
sql
Sam
Sam

Reputation: 309

Relational Algebra Division sql equivalent

I am having trouble understanding the translation of the sql equivalent of relational algebra division. I am trying to find the sids of suppliers who supply every part. In the nested query is it essentially stating that it is looking "for all suppliers who does not supply every part" and do a not exist? But wouldn't the condition WHERE C1.sid = C.sid AND C1.pid = P.pid) also target the sid's of suppliers who actually supply every part? 

Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)

SQL Translation

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

Upvotes: 3

Views: 1370

Answers (1)

KangarooWest
KangarooWest

Reputation: 846

Before answering your questions directly, let me walk you through how you should approach translating the query. Let's look at the outer subquery as a whole first:

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

This snippet gets "the parts that are not supplied by C.sid". Note that the column in the select clause in the subquery here actually has no meaning. We can essentially just write the query like below and still convey exactly the same meaning, so you should not worry about what C1.sid means in the innermost query.

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

Now with the main query involved

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

This means we want the suppliers (C.sid) for which there does not exist "the parts that are not supplied by them (C.sid)" (Note I copied the translation of the subquery from the paragraph above). This essentially means that we want suppliers who supply every part.

Now back to your two questions:

In the nested query is it essentially stating that it is looking "for all suppliers who does not supply every part" and do a not exist?

No. Just see the interpretation above.

But wouldn't the condition WHERE C1.sid = C.sid AND C1.pid = P.pid) also target the sid's of suppliers who actually supply every part?

It's not the right approach to view the inner most query in relation to the main query. This innermost query is there for the first subquery to choose the parts we want. The suppliers being selected here are not relevant as shown by substitution with * above.

Upvotes: 2

Related Questions