在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)
Training ( TrainingCode , TrainingName, TrainingType, TrainingInstructor)
Outcome ( EmployeeId, TrainingCode , Grade)

Employee (EmployeeId, EmployeeName, EmployeeCountry)
Training (TrainingCode, TrainingName, TrainingType, TrainingInstructor)
Outcome (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 Qyery:

SQL Qyery:

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?

推荐答案

关系代数用于:

找到已劫持 training的Employee的Id.

实际上，您需要在关系代数中使用 除法%运算符 a>:

Actually you need division % operator in relational algebra:

r ÷ s是我们希望通过all表达查询的地方:

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

示例:

1. 在哪个国家/地区的ALL银行中有一个银行帐户?
2. 是否检索在ALL工作的项目中工作的员工的姓名?

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:

Training ( 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)

应用％除法操作，该操作可为您提供所需的带有TrainedCode的员工代码，然后应用投影仅过滤出员工代码.

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.

6.3.4除法运算

定义DIVISION操作是为了方便处理 涉及universal quantification或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.

一般的除法运算适用于两个关系T(Y) = R(Z) % S(X)，其中X ⊆ Z 和Y = Z - X(并因此是Z = X ∪ Y)； Y是R的属性集 S的属性，例如X = {A}, Z = {A, B} then 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)除的结果是一个包含元组 t 的关系，如果 元组tR出现在与R的关系中 tR[Y] = t，并与 tR[X] = tS用于 every 元组 S.这意味着.为了使元组 t 出现在除法的结果T中，必须将 t 的值与每个元组一起出现在R中在S中.

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屋！