在 SQL 连接的情况下，如何将 SQL 转换为关系代数? [英] How to convert SQL to Relational Algebra in case of SQL Joins?

问题描述

我最近在研究 SQL 和关系代数.我被困在以下问题上.我能够为以下问题编写 SQL，但不知何故我所做的关系代数看起来不正确.

I am working on SQL and Relational Algebra these days. And I am stuck on the below questions. I am able to make a SQL for the below questions but somehow my Relational Algebra that I have made doesn't looks right.

下面是我的表格-

Employee (EmployeeId, EmployeeName, EmployeeCountry)
培训(TrainingCode、TrainingName、TrainingType、TrainingInstructor)
结果 (EmployeeId, TrainingCode, Grade)

所有的键都用星号*指定.

All the keys are specified with star *.

下面是问题及其 SQL 查询，它也可以正常工作-

Below is the question and its SQL query as well which works fine-

查找参加过所有培训的员工的 ID.

Find an Id of the Employee who has taken every training.

SQL 查询:

SELECT X.EmployeeID
FROM   (SELECT EmployeeID, COUNT(*) AS NumClassesTaken 
        FROM OutCome GROUP BY EmployeeID ) 
        AS X
  JOIN (SELECT COUNT(*) AS ClassesAvailable 
        FROM Training) 
        AS Y
  ON X.NumClassesTaken = Y.ClassesAvailable

我无法理解上述查询的关系代数是什么?有人可以帮我吗?

I am not able to understand what will be the relational algebra for the above query? Can anyone help me with that?

推荐答案

关系代数:

找到Employee的Id，他参加了everytraining.

其实你需要除法%运算符在关系代数中:

r ÷ s 用于当我们希望使用 all" 表达查询时:

r ÷ s is used when we wish to express queries with "all":

示例:

1. 哪些人在国内的ALL银行拥有银行账户?
2. 检索在ALLJon Smith 参与的项目中工作的员工姓名?

1. Which persons have a bank account at ALL the banks in the country?
2. Retrieve the name of employees who work on ALL the projects that Jon Smith works on?

另请阅读 这个幻灯片用于除法运算符:

Read also this slid for division operator:

您的查询还需要查询 % 运算符:已接受所有培训的员工".

You also need query % operator for your query: "Employee who has taken all training".

首先列出所有培训代码:

First list off all Training codes:

培训(TrainingCode、TrainingName、TrainingType、TrainingInstructor)

主键是:TrainingCode:

Primary key is: TrainingCode:

TC = ∏ _TrainingCode(训练)

TC = ∏ _TrainingCode(Training)

一对employeeID和trainingCode:一个员工参加培训.

A pair of employeeID and trainingCode: a employee take the training.

ET = ∏ _{EmployeeId, TrainingCode}(结果)

ET = ∏ _{EmployeeId, TrainingCode}(Outcome)

应用 % Division 操作，通过 trainingCode 为您提供所需的员工代码，然后应用投影以仅过滤掉员工代码.

Apply % Division operation which gives you desired employee's codes with trainingCode then apply projection to filter out employee code only.

Result = ∏ _EmployeeId(ET % TC)

Result = ∏ _EmployeeId(ET % TC)

数据库系统基础" 是我一直随身携带的书.

"Fundamentals of Database Systems" is the book I always keep in my hand.

定义DIVISION操作是为了方便处理涉及通用量化或all的查询状况.大多数以 SQL 作为主查询的 RDBMS 实现语言不直接实现除法.SQL有圆的方式使用 EXISTS、CONTAINS 和 NOT EXISTS 处理查询类型关键词.

6.3.4 The DIVISION Operation

The DIVISION operation is defined for convenience for dealing with queries that involves universal quantification or the all condition. Most RDBMS implementation with SQL as the primary query language do not directly implement division. SQL has round way of dealing with the type of query using EXISTS, CONTAINS and NOT EXISTS key words.

应用于两个关系的一般 DIVISION 操作 T(Y) = R(Z) %S(X), 其中 X ⊆Z 和 Y = Z - X (因此 Z =X∪Y);即 Y 是 R 的属性集S 的属性，例如X = {A}, Z = {A, B} 然后 Y = {B}, B属性在关系 S 中不存在.

The general DIVISION operation applied to two relations T(Y) = R(Z) % S(X), where X ⊆ Z and Y = Z - X (and hence Z = X ∪ Y); that is Y is the set of attributes of R that are not attributes of S e.g. X = {A}, Z = {A, B} then Y = {B}, B attribute is not present in relation S.

T(Y) DIVISION 的结果是一个包含元组的关系 t 如果元组 tR 出现在 R 与tR[Y] = t，并与tR[X] = tS for 每个元组S.这意味着.对于要出现在 DIVISION 结果 T 中的元组 t，t 必须与 S 中的每个元组一起出现在 R 中.

T(Y) the result of DIVISION is a relation includes a tuple t if tuple tR appear in relation R with tR[Y] = t, and with tR[X] = tS for every tuple in S. This means that. for a tuple t to appear in the result T of the DIVISION, the value of t must be appear in R in combination with every tuple in S.

我还要补充一点，关系代数运算集合{σ,∏,⋈,Χ,-} 即选择、投影、连接、笛卡尔交叉和减号是一个完整的集；也就是说，任何其他原始关系代数运算都可以表示为该集合中的一系列运算.除法运算%还可以用∏、⋈、⋈的形式表示，和-操作如下:

I would also like to add that the set of relational algebra operations {σ,∏,⋈,Χ,-} namely Selection, Projection, Join, Cartesian Cross and Minus is a complete set; that is any of the other original relational algebra operation can be expressed as a sequence of operations from this set. Division operation % can also be expressed in the form of ∏, ⋈, and - operations as follows:

T1 <-- ∏Y(R)
T2 <-- ∏Y((S Χ T1) - R)
T3 <-- T1 - T2

T1 <-- ∏Y(R)
T2 <-- ∏Y((S Χ T1) - R)
T3 <-- T1 - T2

要使用基本关系代数运算表示您的问题，只需将 R 替换为结果，S 替换为培训，属性集 Y 替换为 EmployeeId.

To represent your question using basic relational algebraic operation just replace R by Outcome, S by Training and attribute set Y by EmployeeId.

我希望这会有所帮助.

这篇关于在 SQL 连接的情况下，如何将 SQL 转换为关系代数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！