所有SQL查询都可以在关系代数，域和元组关系演算中表示 [英] Can all SQL queries be represented in Relational Algebra, Domain and Tuple relational calculus

问题描述

我的查询包括一个具有和计数或所有in。这些在RA / DRC / TRC中是如何表示的？我必须简化我的SQL查询更多吗？这是一个简化的例子：

  empl（雇员（主键），城市）
经理），经理（员工的外键））
  

如果我找到所有的经理（从任何城市）所有的城市X的员工。我需要使用有/计数。不知道如何在RA / DRC / TRC中这样做。

我知道这样的查询的需要可能没有意义，但假设它是明智的这个问题。

感谢

解决方案

暧昧。确实的目的是找到所有的经理谁是每个和每个员工的经理在城市X？

如dportas指出，这是完全可行的RA。 / p>

具体方法如下：

获取城市X中所有员工的集合。调用EMPX。

获取所有管理器的集合。调用MGRS。

创建两者的笛卡尔乘积。调用MGRS_EMPX。

从表的实际值（根据需要的属性适当地投影）中减去哪些管理员管理哪个员工。这个区别包含真正存在的所有经理组合，一个位于X的员工，但是那个经理不管理那个员工。

项目的差异到管理器属性。这个关系告诉你哪些经理存在，使得在X城市中存在由该经理管理的 的一些雇员。

MGRS。显然，这个关系告诉你哪些经理存在，使得在X不存在不由该经理管理的雇员。

重写这个存在性的否定量化器作为通用量化将显示这正是您想要的结果：NOT EXISTS（EMP：EMP在X和EMP由MGR管理）=== FORALL EMP：NOT（EMP在X和EMP由MGR管理） === FORALL EMP：（EMP不在X或EMP由MGR管理）=== FORALL EMP：（如果EMP在X，则EMP由MGR管理）。

所有这些都是完美的代数运算。

（侧面练习：看看如果没有员工位于城市X，会发生什么。）

My query includes a having and count or all in. How are these represented in RA/DRC/TRC? Would I have to simplify my SQL query even more? Here is a simplified example:

empl(employee (primary key), city)
managers(employee (primary key), manager (foreign key of employee))

If I were to find all the employees who are managers (from any city) of ALL the employees in city X.. I would need to use having/count. Not sure how this would be done in RA/DRC/TRC.

I know the need for such a query might not make sense but assume it is sensible for the purpose of this question.

Thanks

解决方案

Your query was stated a bit ambiguous. It is indeed the intent to find all managers who are the manager for EACH AND EVERY employee that is in city X ?

As dportas indicated, that's perfectly doable in RA.

Here's how :

Get the collection of all the employees in city X. Call that EMPX.

Get the collection of all managers. Call that MGRS.

Make the cartesian product of the two. Call that MGRS_EMPX.

Subtract from that the actual value of the table (appropriately projected down to the needed attributes) that says which managers manage which employee. That difference holds all the combinations of managers that really exist, with an employee that is located in X, but where that manager does not manage that employee.

Project that difference down onto the manager attribute. That relation tells you which managers exist such that there exists some employee in city X that is NOT managed by that manager.

Subtract this relation from MGRS. Obviously, this relation tells you which managers exist such that there does NOT exist an employee located in city X that is NOT managed by that manager.

Rewriting this negation of an existential quantifier as a universal quantification will reveal that this is precisely the result that you want : NOT EXISTS (EMP : EMP is in X AND EMP managed by MGR) === FORALL EMP : NOT (EMP is in X AND EMP managed by MGR) === FORALL EMP : (EMP is not in X OR EMP is managed by MGR) === FORALL EMP : ( if EMP is in X then EMP is managed by MGR).

And all of these are perfectly fine algebra operations.

(Side exercise : see what happens if there are no employees located in city X at all.)

这篇关于所有SQL查询都可以在关系代数，域和元组关系演算中表示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！