SQL ->关系代数 [英] SQL -> Relational Algebra

问题描述

假设我有以下关系:

Branch (branchNo(PK), street, city, postcode)工作人员 (staffNo(PK), fName, lName, sex, branchNo(FK))

这对这个问题并不重要，但 PK = 主键 &FK = 外键

我将如何为以下查询编写关系代数:

<块引用>

列出在格拉斯哥工作的所有女性员工的姓名.

我的尝试:

σStaff.sex=F &Branch.city = Glasgow(πfName, lName, sex, branchNo(Staff) x πcity, branchNo(Branch))

我知道我的选择 (σ) 语句(不要与 SELECT 混淆)在语法上是不正确的:

σStaff.sex=F &Branch.city = 格拉斯哥

如何在不同的关系上写两个选择?或者换句话说，如何在关系代数中的 WHERE 子句中表达具有两个或多个条件的 SQL 语句?我用过'&'但这不可能是对的?我是否必须将一个选择嵌入到另一个中?

不是家庭作业

解决方案

形式关系代数使用逻辑 conjunction 和 disjunction 和(通常)相同的符号(∧ 和 &或;)，尽管作者可以自由选择他们自己的语法.查询可以写成:

πfName, lName(σ(gender=F ∧ city=Glasgow)(Staff ⋈ Branch))

请注意，x(而是 ⨯)是笛卡尔积的符号.对于自然连接，您需要 ⋈(领结).

如果你想要笛卡尔积而不是自然连接，你基本上通过向选择添加适当的条件来实现自然连接.您还需要处理这样一个事实，即属性 branchNo 对两种关系都是通用的，您可以使用 重命名运算符 (ρ).

πfName, lName(σ(gender=F ∧ city=Glasgow ∧ branchNo=bNum)(Staff ⨯ ρbNum/branchNo(Branch)))

正式地，您可以这样做，因为:

R ⋈S = πα(R),α(S)-α(R)(σα(R)∩α(S)=t1..k(R ⨯ ρ t1..k/α(R)∩α(S)(S))))

其中 α(T) 是关系 T 的属性名称(使 α(R) ∩ α(S) 是公共属性名称)和 t_1..k ⊈α(R) ∪α(S) 是公共属性的新名称.

Suppose I have the following relations:

Branch (branchNo(PK), street, city, postcode)

Staff (staffNo(PK), fName, lName, sex, branchNo(FK))

Not that it matters for this question, but PK = primary key & FK = foreign key

How would I write the relational algebra for the following query:

List the names of all female staff that work in Glasgow.

My attempt:

σStaff.sex=F & Branch.city = Glasgow(πfName, lName, sex, branchNo(Staff) x πcity, branchNo(Branch))

I know that my selection (σ) statement (NOT TO BE CONFUSED WITH SELECT) is syntactically incorrect:

σStaff.sex=F & Branch.city = Glasgow

How do I write two selections on different relations? Or in other words, how do I express an SQL statement with two or more conditions in the WHERE clause in relational algebra? I have used '&' but this cannot be right? Do I have to embed one selection within the other?

NOT HOMEWORK

解决方案

Formal relational algebra uses logical conjunction and disjunction and (typically) the symbols for same (∧ and ∨, respectively), though authors are free to pick their own syntax. The query could be written as:

πfName, lName(σ(gender=F ∧ city=Glasgow)(Staff ⋈ Branch))

Note that x (rather, ⨯) is the symbol for Cartesian product. For natural joins, you want ⋈ (bowtie).

If you want the Cartesian product rather than natural join, you basically implement a natural join by adding the appropriate condition to the select. You'll also need to deal with the fact that the attribute branchNo is common to both relations, which you can do by using the rename operator (ρ).

πfName, lName(σ(gender=F ∧ city=Glasgow ∧ branchNo=bNum)(Staff ⨯ ρbNum/branchNo(Branch)))

Formally, you can do this because:

R ⋈ S = πα(R),α(S)-α(R)(σα(R)∩α(S)=t1..k(R ⨯ ρ t1..k/α(R)∩α(S)(S))))

where α(T) are the attribute names for relation T (making α(R) ∩ α(S) the common attribute names) and t_1..k ⊈ α(R) ∪ α(S) are new names for the common attributes.

这篇关于SQL ->关系代数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！