将聚合运算符从SQL转换为关系代数

一尘不染

将聚合运算符从SQL转换为关系代数

sql

我编写了一些SQL查询，希望将其转换为关系代数。但是，某些查询使用聚合运算符，而我不知道如何转换它们。值得注意的是，它们使用COUNT和GROUP BY
.. HAVING运算符。

这是模式：

水手（ sid ，sname，评分）储备（ sid ，出价，价格）船（出价，bname）

这是我正在做的一个示例：查找恰好2个水手保留的所有船只的出价和名称。

SELECT B.bid, B.bname
FROM Boats B, Reserves R
WHERE B.bid = R.bid
GROUP BY R.bid
HAVING 2 = (SELECT COUNT(*)
FROM Reserves R2
WHERE R2.bid = B.bid);

允许的关系代数运算：选择，投影，联接，条件联接，重命名，并集，交集，叉积，除法

阅读 171

2021-03-10

共1个答案

一尘不染

这只是答案的一半…

可以使用条件连接和投影来找到“由两个或多个水手预定的船”的关系，它们都在您允许的操作集中：

SELECT DISTINCT R1.bid
  FROM Reserves AS R1 
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid;

可以使用条件联接（两次）和投影来找到“三个或三个以上船员预订的船”的关系，它们都在您允许的操作集中：

SELECT DISTINCT R1.bid
  FROM Reserves AS R1
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid
       JOIN Reserves AS R3
          ON R1.bid = R3.bid
          AND R2.sid < R3.sid;

如果我们EXCEPT在标准SQL中有减号运算符，例如：

SELECT DISTINCT R1.bid
  FROM Reserves AS R1 
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid
EXCEPT
SELECT DISTINCT R1.bid
  FROM Reserves AS R1
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid
       JOIN Reserves AS R3
          ON R1.bid = R3.bid
          AND R2.sid < R3.sid;

如果
我们有限制（WHERE在SQL中）和半差分（又名antijoin）运算符（例如NOT IN在SQL中）：

SELECT DISTINCT R1.bid
  FROM Reserves AS R1 
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid
 WHERE R1.bid NOT IN (
                      SELECT DISTINCT R1.bid
                        FROM Reserves AS R1
                             JOIN Reserves AS R2
                                ON R1.bid = R2.bid
                                   AND R1.sid < R2.sid
                             JOIN Reserves AS R3
                                ON R1.bid = R3.bid
                                AND R2.sid < R3.sid
                     );

…但是您允许的操作集不包括限制，半差或减:(

2021-03-10