将关系代数转换为相应的 SQL 查询 [英] Converting Relational Algebra to corresponding SQL Query

问题描述

我想了解如何将关系代数正确转换为SQL查询

I am trying to understand how to convert properly from Relational Algebra into SQL queries

我发现这是我难以在两者之间转换的事情之一.有人可以分享任何提示吗?

I find this is one of the things I struggle to convert between. Would anyone be able to share any tips?

表格:

Room(_roomId_, numberOfBeds, price)

Guest(_guestId_, homeTown, age)

Reservation(_roomId_, _guestId_, date)

关系代数:

Πprice(ΠroomId(ΠguestId(σage<20)Guest) Natural Join Reservation) Natural Join Room

查询:我设法从部分声明中推断出以下内容:

Query: I managed to deduce the following from part of the statement:

SELECT roomId FROM (SELECT guestID from Guest WHERE age<20) Natural Join Reservation

我很亲近吗?

推荐答案

您可以通过多种方式来看待这个问题.如果您更熟悉数据库而不是关系代数，则第一个选项可能更容易，而如果您熟悉关系代数，则第二个选项会更容易(并且对于更复杂的问题更准确).

There are couple of ways that you could look at this. The first option might be easier if you are more familiar with databases then relational algebra, while the second will be easier (and more accurate for more complex problems) if you are familiar with relational algebra.

表格优先:

首先弄清楚您的连接.你知道你有你的三套(表)Guest、Reservation和Room，这三个with都是自然连接的(内连接).所以你可以像这样开始编写你的查询:

Start by figuring out your joins. You know that you have your three sets (tables) Guest, Reservation, and Room, all three of with are natural joined (inner joined). So you could start writing you query like so:

SELECT *
FROM Guest g
    INNER JOIN Reservation res
        ON g._guestId_ = res._guestId_
    INNER JOIN Room r
        ON res._roomId_ = r._roomId_;

完成后，应用您的条件:

Once that is complete, apply your conditions:

SELECT *
FROM Guest g
    INNER JOIN Reservation res
        ON g._guestId_ = res._guestId_
    INNER JOIN Room r
        ON res._roomId_ = r._roomId_
WHERE g.age < 20;

或者你可以把g.age的条件放在Reservation的join里，但是建议把条件放在WHEREINNER JOIN 的子句.

Alternatively you can put the condition for g.age in the join to Reservation, but it is recommended to put conditions in the WHERE clause for INNER JOIN.

最后，您填充SELECT:

SELECT g._guestId_,
    res._roomId_,
    r.price
FROM Guest g
    INNER JOIN Reservation res
        ON g._guestId_ = res._guestId_
    INNER JOIN Room r
        ON res._roomId_ = r._roomId_
WHERE g.age < 20;

操作顺序

为此，您可以使用操作顺序编写查询.所以括号内的所有内容都会首先执行.这样做，您首先要针对 Guest 编写查询:

For this, you write your query using order of operations. So everything inside a parenthesis gets executed first. Doing it this way, you start by writing the query against Guest:

SELECT g._guestId_
FROM Guest g
WHERE g.age < 20;

下一组将是 Reservations，这是自然连接:

The next set would be Reservations, and that is natural joined:

SELECT g._guestId_,
    res._roomId_
FROM Guest g
    INNER JOIN Reservation res
        ON g._guestId_ = res._guestId_;

终于来到Room集，再次自然加入:

Finally, you come to the Room set, again natural joined:

SELECT g._guestId_,
    res._roomId_,
    r.price
FROM Guest g
    INNER JOIN Reservation res
        ON g._guestId_ = res._guestId_
    INNER JOIN Room r
        ON res._roomId_ = r._roomId_
WHERE g.age < 20;

这篇关于将关系代数转换为相应的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！