Translating from Relational Algebra to SQL

Question

the following exercise asks me to translate from relational algebra to SQL code.

I am still not very familiar with relational algebra, but I have tried to code the following relations in SQL, but I think I made some mistakes.

    **>  [Customer × Product ]―[π{Cid, Name, Pid, Label}(Customer ⋈ Orders ⋈ line_item)]**

SELECT *  FROM Customer, Product  WHERE Cid, Name, Pid, Label NOT IN
(SELECT Cid, Name, Pid, Label FROM Customer NATURAL JOIN Orders
NATURAL JOIN line_item);

For this one I really do not know how to deal with this algebra relation:

**>  πName,Name2(σCid<Cid2 (πCid,Name,Pid (Customer ⋈ Orders ⋈ line_item)
⋈ βCid→Cid2,Name→Name2 (πCid,Name,Pid (Customer ⋈ Orders ⋈
line_item))))**

It would be highly appreciated if you could explain me the reasoning process in order to deal with this type of algebra relationships.

Answer 1

For the first query, it looks almost correct, except that I do not think you can give several columns with NOT IN. I would use WHERE NOT EXISTS:

SELECT * FROM Customer c1, Product
WHERE NOT EXISTS 
  (SELECT Cid, Name, Pid, Label FROM Customer c2
  NATURAL JOIN Orders
  NATURAL JOIN line_item
  WHERE c1.Cid = c2.Cid); -- assuming Cid is a primary key of your customer table

For the second part,

πName,Name2(σCid<Cid2 (πCid,Name,Pid (Customer ⋈ Orders ⋈ line_item)
⋈ βCid→Cid2,Name→Name2 (πCid,Name,Pid (Customer ⋈ Orders ⋈
line_item))))

can be written like that

R1 = (πCid,Name,Pid (Customer ⋈ Orders ⋈ line_item))

R2 = (πCid,Name,Pid (Customer ⋈ Orders ⋈ line_item) ⋈ βCid→Cid2,Name→Name2 R1))

R3 = πName,Name2(σCid<Cid2 R2)

which would translate:

R1 = (SELECT Cid, Name, Pid FROM Customer NATURAL JOIN Orders NATURAL JOIN line_item)

R2 = (SELECT Cid, Name, Pid FROM Customer NATURAL JOIN Orders NATURAL JOIN line_item NATURAL JOIN (SELECT Cid as Cid2, Name as Name2 FROM R1))

R3 = SELECT Name, Name2 FROM R2 WHERE Cid < Cid2