本文介绍了将聚合运算符从SQL转换为关系代数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我编写了一些SQL查询,希望将其转换为关系代数。但是,某些查询使用聚合运算符,但我不知道如何转换它们。

I have several SQL queries written that I want to convert to relational algebra. However, some of the queries use aggregate operators and I don't know how to convert them. Notably they use COUNT and GROUP BY.. HAVING operators.

以下是模式:

Sailors( sid ,sname,等级)
储备( sid 出价,价格)
船(出价,bname)

Sailors(sid, sname, rating)Reserves(sid, bid, price)Boats(bid, bname)

这里是我正在做的一个示例:找到恰好2个水手预定的所有船只的出价和名称。

Here is an example of what I'm doing: find the bids and bnames of all boats reserved by exactly 2 sailors.

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

允许的关系代数运算:选择,投影,连接,条件连接,重命名,并集,交点,叉-产品,部门

Allowable relational algebra operations: selection, projection, join, conditional join, rename, union, intersection, cross-product, division

推荐答案

这只是答案的一半...

This is only half an answer...

可以使用条件连接和投影来找到由两个或多个水手保留的船的关系,它们都在您允许的操作集中:

The relation "boats reserved by two or more sailors" can be found using conditional join and projection, which are both in your set of allowed operations:

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

可以使用条件连接(两次)找到由三个或更多水手预定的船的关系,投影,都在您允许的操作集中:

The relation "boats reserved by three or more sailors" can be found using conditional join (twice) and projection, which are both in your set of allowed operations:

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中:

If we had a minus operator e.g. EXCEPT in Standard 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;

如果我们有限制条件(在哪里)和半分运算符(也称为)(例如不输入在SQL中)

If we had restriction (WHERE in SQL) and a semi difference (a.k.a. antijoin) operator (e.g. NOT IN 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
                     );

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

...but your set of allowed operations does not include restriction, semi difference or minus :(

这篇关于将聚合运算符从SQL转换为关系代数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 01:26