从关系代数到sql的转换

nukf8bse 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(508)

下面的练习要求我将关系代数转换为sql代码。
我对关系代数还不是很熟悉，但是我尝试过用sql编写以下关系，但是我认为我犯了一些错误。


**>  [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);

对于这个，我真的不知道如何处理这个代数关系：


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

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

如果你能给我解释一下处理这种代数关系的推理过程，我将不胜感激。

sql relational-algebra

来源：https://stackoverflow.com/questions/62517128/translating-from-relational-algebra-to-sql

1条答案

按热度按时间

k4emjkb11#

对于第一个查询，它看起来几乎是正确的，只是我不认为您可以用 NOT IN . 我会用 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

第二部分，

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

可以这样写

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)

也就是说：

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

赞(0）回复(0）举报 2021-07-24

我来回答

从关系代数到sql的转换

1条答案

相关问题

热门标签

最新问答