数据库原理与安全笔记：关系代数

该部分为本科期间数据库原理与安全笔记备份。

Fundamental Relational-Algebra-Operations

Relational Algebra

select: \(δ\)

project（投影）: \(∏\)

union: \(𝖴\)

set difference: \(–\)

cartesian product: \(x\)

rename: \(\rho\)

Union Operation

并集：\(r𝖴s\)

\[r𝖴s=｛t|t∈r\ or \ t∈s｝ \]

Set Difference Operation

差集：\(r-s\)

\[r-s=｛t|t∈r\ or \ t∉s｝ \]

Set-Intersection Operation

交集：\(r⋂s\)

\[r⋂s=｛t|t∈r\ and \ t∈s｝ \]

\[r⋂s=r-(r-s) \]

Set operation

1. r,s must have the same arity (same number of attributes)
2. The attribute domains must be compatible

Cartesian-Product Operation

Pair each tuple of one relation with each tuple of another

将任意两个关系结合在一起。元组的个数是n1*n2

笛卡尔积：\(r×s\)

\[r×s=｛t,q|t∈r\ and \ q∈ s｝ \]

Assume: that attributes of r(R) and s(S) are disjoint，即相交为空

If attributes of r(R) and s(S) are not disjoint, then renaming must be used

Select Operation

Select tuples that satisfy a given predicate（谓词）

选择：\(δₚ（r）\)

\[δₚ（r）=｛t|t∈r\ and \ p（t）｝ \]

p： \(\bigwedge\)、\(\bigvee\)、非、\(=\)、\(\neq\)、\(≥\)、\(≤\)、\(<\)

Project Operation

\(∏_{A_1,A_2...A_k}（r）\)

\({A_1,A_2...A_k}\)为属性名

因为关系是一个元组的集合，而集合中的元素不允许重复出现，因此在关系模型中对关系作了限制， 关系中的元组不能重复，可以用键来标识唯一的元组。

Composition of Operations

Can build expressions using multiple operations

例子：Find the names of all customers who have an account at the bank, along with the account number and the balance

Rename Operation

重名：\(ρₓ(E)\)、\(ρₓ_{（A_1,A_2,...,A_n）}(E)\)

Allows us to name, and therefore to refer to, the results of relational-algebra expressions
Allows us to refer to a relation by more than one name

例子：eturns the expression E under the name X

Additional Operations

We define additional operations that do not add any power to the relational algebra, but that simplify common queries

Set intersection: \(⋂\)

Natural join: \(⋈\)

Division:\(÷\)

Join

连接运算在笛卡尔积的结果上选择属性满足一定条件的元组。

从两个关系的笛卡尔积中选取属性间满足一定条件的元组。

Type

\(θ\)-join:arbitrary conditions in the selection

Equijoin:all conditions are equalities

Natural join:combines two relations on the equality of the attributes with the same names

\(θ\)-join

从两个关系的笛卡尔积中选取属性间满足一定条件的元组。

1. \(θ\)连接运算从R ×S的笛卡尔积的运算结果中选取在A属性上的值与B属性上的值满足比较关系的元组
2. A和B是R和S上度数相等且可以比较的属性组
3. \(θ\)是比较运算符

Equijoin

R与S共有的属性值相等的列选出来进行连接。

Natural-Join

\(r⋈s\)

Let r and s be relations on schemas R and S respectively.

Then, \(r⋈s\) is a relation on schema \(R⋂S\) obtained as follows:

• Consider each pair of tuples \(t_r\) from r and \(t_s\) from s.
• If \(t_r\) and \(t_s\) have the same value on each of the attributes in \(R⋂S\), add a tuple t to the result.

自然连接是一种特殊的等值连接

要求两个关系中进行比较的分量必须是相同的属性组

• 在结果中将相同的属性组去掉
• 一般连接是从行的角度进行计算，但是自然连接还要从列的角度进行计算，消除重复的列。

其实就是等值连接之后删除重复列

example:Find the names of all customers who have a loan and an account at bank

Division

Let A have two fields x and y

Let B have one field y

A/B contains all x tuples, such that for every y tuple in B there is a xy tuple in A

\(r÷s\)

Let r and s be relations on schemas R and S respectively, where\(R=(A_1,...,A_n,B_1,...,B_n)\)

The result of \(r÷s\) is a relation on schema:

\[R-S=(A_1,...,A_m) \]

\[r÷s=｛t|t∈∏_{R-S}(r)\bigwedge∀u∈s(tu∈r)｝ \]

Where tu means the concatenation(连结) of tuples t and u to produce a single tuple

Division using basic operations:

1. Let A and B be relations and \(B⊆A\)
2. Compute all possible combinations of the first column of A and B
3. Then remove those rows that exist in A
4. Keep only the first column of the result. These are the disqualified values:\(π_x((π_x(A)×B)-A)\)
5. A/B is the first column of A except the disqualified values:

\[A/B=π_x(A)-π_x(π_x(A)×B-A) \]

example:Find all customers who have an account at all branches located in Brooklyn city

Outer Join

• An extension of the join operation that avoids loss of information
• Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join

分类:

Left Outer Join:取出左侧关系中与右侧关系中的任一元组都不匹配的元组，用空值来填充所有来自右侧关系的属性。

Right Outer Join:取出左侧关系中与右侧关系中的任一元组都不匹配的元组，用空值来填充所有来自左侧关系的属性。

Full Outer Join:取出左侧关系中与右侧关系中的任一元组都不匹配的元组，用空值来填充所有来自左、右侧关系的属性。

代数总结

易混淆点

select是在元组上进行选择，而\(π、∏\)是列选择，其中\(π\)是去重选择列（可以当做是把元组所有无重复元素放入集合中），而\(∏\)是不去重选择列。

Generalized Projection

Extends the projection operation by allowing arithmetic functions to be used in the projection list

\[∏_{F_1,F_2,...,F_n}(E) \]

E is any relational-algebra expression

Each of \({F_1,F_2,...,F_n}\) are arithmetic expressions involving constants and attributes in the schema of E.

Aggregate Functions

takes a collection of values and returns a single value as a result

avg:average value

min:minimum value

max:maximum value

sum:sum of values

count:number of values

特点

Result of aggregation does not have a name

• Can use rename operation to give it a name
• For convenience, we permit renaming as part of aggregate operation

一些容易出错的查询

example 1

student(sid, sname,sage,sdept)

course(cid,cname,ccredit)

Sc(sid,cid,score)

1. 查询至少学习了课号001和课号002的学生的学号:交运算、连接
   • \(π_{sid}(δ_{cid="001"}(sc))⋂π_{sid}(δ_{cid="002"}(sc))\) 正确
   • \(π_{sid}(δ_{sc.cid="001"⋀sc1.cid="002"}(sc⋈_{sc.cid=sc1.cid}ρ_{sc1}(sc)))\) 不行，自然连接不可以，因为相同的属性被消掉了
2. 查询没有选修002号课程的学生的姓名和年龄
   • \(π_{sname,sage}(δ_{sc.cid<>"002"}(s⋈sc))\) 错 因为当有的同学选修了002，但是也选修了其他课程的时候，这样的学生也被查询出来了
   • \(π_{sname,sage}(s-δ_{sc.cid="002"}(s⋈sc))\) 错 不满足列相容性
   • \(π_{sname,sage}-π_{sname,sage}(δ_{sc.cid="002"}(s⋈sc))\) 对

example 2

Student(S#,Sname,Sage,Ssex,Sclass)

Course(C#,Cname,Chours,Credit,Tname)

SC(S#,C#,Score)

1. 学过李明老师所有课程的学生姓名？
   • \(π_{sname,c＃}(student⋈sc)÷π_{c＃}(δ_{cname="李明"}(Course))\)
2. 至少学过一门李明老师的课程的学生姓名?
   • \(π_{sname,c＃}(δ_{Tname="李明"}(student⋈course⋈sc))\)
3. 没学过李明老师讲授任意一门课的学生姓名？
   • \(π_{sname}(student)-π_{sname}(δ_{Tname="李明"}(student⋈course⋈sc))\)
4. 求至少有一门李明老师讲授课程没学过的学生姓名？(至少有一门没学过)
   • \(π_{sname}(student)-(π_{sname，c＃}(student⋈sc))÷π_{c＃}(δ_{cname="李明"}(course))\)

Null Values

It is possible for tuples to have a null value, denoted by null, for some of their attributes

null signifies an unknown value or that a value does not exist.

The result of any arithmetic expression involving null is null

Aggregate functions simply ignore null values (as in SQL)

For duplicate elimination and grouping, null is treated like any other value, and two nulls are are assumed to be the same

Comparisons with null values return the special truth value: unknown

Result of select predicate is treated as false if it evaluates to unknown

unknown运算

1. OR:
   • unknown or true=true
   • unknown or false=unkown
   • unknown or unknown=unkown
2. AND:
   • true and unknown=unknown
   • false and unknown=false
   • unknown and unknown=unknown
3. NOT:
   • not unknown=unknown
4. SQL: “P is unknown” evaluates to true if predicate P evaluates to unknown

Assignment Operation

The assignment operation (\(←\)) provides a convenient way to express complex queries.

example:

Deletion

\(r←r-E\): where r is a relation and E is a relational algebra query.

Insertion

\(r←r⋃E\): where r is a relation and E is a relational algebra query.

Updating

\(r←∏_{F_1,F_2,...,F_n}(r)\)