-1

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.

Maria
  • 47
  • 1
  • 7
  • [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) [Re SQL querying.](https://stackoverflow.com/a/33952141/3404097) – philipxy Jun 22 '20 at 16:11
  • Please: Ask 1 question per post. Explain why the parts you think are right are right. Explain why you are not sure of the parts you are not sure of. Explain about the 1st place you are stuck. In code questions give a [mre]. Format code with reasonable indentation. What does "translate" mean? Same result? Corresponding structure? [ask] [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) [What is the policy here on homework?](https://meta.stackexchange.com/q/18242/266284) – philipxy Jun 22 '20 at 16:24

1 Answers1

0

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
David Alvarez
  • 1,226
  • 11
  • 23