数据库复习2. Relational Algebra 关系代数

Relational Algebra 关系代数

• Relational Algebra is the language to describe operations in relational DBMS helps understanding of Query execution,and particularly Query optimisation.

  关系代数是描述关系DBMS中操作的语言，有助于理解查询执行，特别是查询优化。

• It is basic set of relational operations to retrieve, filter and restructure relations.

  它是一组用于检索、筛选和重组关系的基本关系操作。

• Result of operations is always a new relation *.

  原表经关系代数操作后的结果是一个新表

给定下列几张表作为操作实例用表：

STUDENT

FName	LName	ReaNum	BDate	Address	Gender	DName
Bart	Simpson	1111	1991-10-10	10 Evergreen Terrace, Springfield	M	Computer Science
Lisa	Simpson	2222	1993-06- 14	10 Evergreen Terrace, Springfield	F	Physics
Milhouse	Van Houten	3333	1991-09-07	Mill House, Springfield	M	History
Ralph	Wiggum	4444	1993- 12-22	Wiggum House, Springfield	M	History
Todd	Flanders	5555	1992-06-28	9 Evergreen Terrace, Springfield	M	Religious Studies
Rod	Flanders	6666	1994-02-27	9 Evergreen Terrace, Springfield	M	Religious Studies
A pu	Naha…	7777	1968-03-17	Kwik E Mart, Springfield	M	Computer Science
Monty	Burns	8888	1898-11-11	Nuclear Power Plant, Springfield	M	Physics

Department

DName	HoD	NoO/Emps
Physics	Homer Simpson	22
Computer Science	Edna Krabappel	34
Religious Studies	Rev. Lovejoy	12
History	Abe Simpson	18

DEPT_LOCATIONS

DName	DLocation
Physics	Science Building
Computer Sc ience	Science Building
Computer Sc ience	Computer Centre
Religious Studies	Main Building
History	Main Building

The SELECT operation 选择操作

• Definition: Selects subset of tuples from relation on basis of a selection condition. 定义：根据选择条件从表中选出元组的子集的操作。

• Denoted by σ（sigma) as the form σ<selection condition>(R): 用σ符号表示成σ<selection condition>(R)的形式：

  • <Selection condition>: 选择条件：

    1. Selection condition is formed by arbitrarily connecting clauses using boolean operators AND,OR and NOT

       选择条件由使用布尔运算符“AND”、“OR”和“NOT”的任意连接子句构成。

    2. Selection condition is a boolean expression made up of a number of clauses of form:

       选择条件可以是一个布尔表达式，以下列形式表示：

  <attribute name> <comparison op> <constant value>
  or
  <attribute name> <comparison op> <attribute name>
  
  <comparison op> `is normally one of ｛ =，≠，＜，＞，≤ ，≥ ｝
  

  • (R): R is just name of a database relation R是被操作表的表名

Note:

1. Result of SELECT operation is always a relation (as with all other relational algebra operations).

   SELECT操作后的结果依然是一张表（这一点和其他关系操作符一样）

2. Relation resulting from SELECT has same attributes as R

   SELECT操作后得到的新表与原表R有相同的表头

3. Number of tuples in resulting relation always less than or equal to number of tuples in R

   新表的行数小于等于原表

4. SELECT operation is commutative 交换律

σ<cond1>(σ<cond2>(R)) = σ<cond2>(σ<cond1>(R) )

5. A sequence of SELECTs can be applied in any order, or can be converted to a single SELECT operation asσ＜condl> AND <cond2> (R)

   多个SELECT语句可以由任意顺序应用，或者可以由布尔操作符连接成一个SELECT语句，例如：σ＜condl> AND <cond2> (R)

6. SELECT Examples: 下面来看SELECT语句的实例：

σ(LName ＝’Simpson’AND Gender = ’M’)OR(DName = ’Religious Studies’)(STUDENT)

FName	LName	ReaNum	BDate	Address	Gender	DName
Bart	Simpson	1111	1991-10-10	10 Evergreen Terrace, Springfield	M	Computer Science
Todd	Flanders	5555	1992-06-28	9 Evergreen Terrace, Springfield	M	Religious Studies
Rod	Flanders	6666	1994-02-27	9 Evergreen Terrace, Springfield	M	Religious Studies

The PROJECT operation. OPRATION操作

• Definition: PROJECT selects some of columns on basis of an attribute list from attributes of a relation R

  PROJECT操作按照属性列表选取R中的对应列组成新表

• Denoted by π（ pi)， it takes the form π＜attribute list>(R)

  用π（ pi)表示，形式为 π＜attribute list>(R)

  • Where R is a relational algebra expression (whose result is a relation), its simplest case R is just name of database relation.R是关系代数表达式，它的最简形式是被操作表的表名。

Note:

1. Result of PROJECT operation is always a relation (as with all other relational algebra operations)

   PROJECT操作后的结果依然是一张表（这一点和其他关系操作符一样）

2. Relation resulting from PROJ ECT has only attributes specified in<attribute list> and Attributes in same order as in list.

   PROJECT操作后的新表只含有<attribute list>中的属性，且属性排列顺序和<attribute list>一致。

3. PROJECT operation is not commutative. PROJECT操作不具有交换律。

4. π＜listl> （ π＜list2>(R) ) ＝π＜listl>(R)，provided <list 2> contains all attributes in<list 1>otherwise left hand side is an invalid expression.提供的 <list 2> 要包含<list 1>中的所有属性，否则π＜listl>是无效表达式。

5. Number of tuples in resulting relation is always less than or equal to number of tuples in R

   新表的行数小于等于原表

6. Remember: result is a relation so no duplicates.PROJECT的结果没有重复值。

7. Number of tuples in resulting relation is always less than or equal to number of tuples in R

   新表的行数小于等于原表

   The PROJECT operation举例：

The RENAME Operation 改名操作

1. 将经过SELECT操作的STUDENT的结果表命名为DEPT_CS.
2. 将经过PROJECT操作的DEPT_CS的结果表中的{Fname,Lname}属性命名为RESULT(FirstName, LastName).

UNION, INTERSECTION, DIFFERENCE Operatiion 并，交，补操作

Application Condition应用条件：

1. Two relations must have same type of tuples. 两张表必须行数相同。

2. Formally, two relationsR(A1,A2, ...,An ) and S( B1, B2, ...,Bn) are union compatible if They have same degree n and

dom( Ai) = dom( Bi). R和S可兼容前提是他们的列数相同且每个属性的定义域相同。

• UNION 并

  • Denoted by R∪S
  • Results in relation that includes all tuples that are either in R, or in S, or in both R and S (Duplicate tuples are eliminated)

• INTERSECTION 交

  • Denoted by R∩S

  • Results in a relation that includes all tuples that are in both R and S

• DIFFERENCE 补

  • Denoted byR - S

    一 Results in a relation that includes all tuples that are in R but not in S

• UNION and INTERSECTION are commutative but DIFFERE NCE is not.并，交操作具有交换律但是补操作没有。

CARTESIAN PRODUCT Operation 笛卡尔积操作

• Also a standard binary set operation. 二进制集合操作

• Denoted by × 用×表示

• Also known as CROSS PRODUCT or CROSS JOIN operation. 也被称为叉乘或交叉联合操作

• Relations on which it is applied do not have to be union compatible. 叉乘的两张表不需要满足联合兼容（属性数同，属性定义域同）

• Result of R( A1, A2,..., An )×S( B1, B2,,,,Bm) is relation Q with n + m attributes in that order i.e. Q( A1, A2, ..., An, S1, S2, ..., Sm),Q has one tuple for each combination of tuples: one from R and one from S

  - Hence, if R has n tuples and S has m tuples then R×S will have n*m tuples.

  笛卡尔积R( A1, A2,..., An )×S( B1, B2,,,,Bm)的结果表中有n+m列和n*m行

The JOIN Operation

• Used to combine related tuples from two relations into single tuples. 被用来将两张表中相关的元组结合成一张表。

• Denoted by ⋈ .用⋈表示。

• Result of the JOIN is a relation Q with n + m attributes in that order. i.e. Q(A1,A2, ...,An, B1, B2, ..., Bm)

  JOIN的结果表Q有n+m个属性且按照Q(A1,A2, ...,An, B1, B2, ..., Bm)的顺序排列。

• However, unlike CARTESIAN PRODUCT resulting relation Q in a JOIN has one tuple for each combination of tuples whenever combination satisfies join condition.

  JOIN的本质是先对A、B两张表做笛卡尔积，然后再按照 join condition选取合适的行。

  举例：选取FEM_STUDS和DEPARTMENT表中StudDept和DName相等的元组并将新表命名为FEM_STUD_HODS.

1. 先将FEM_STUDS和DEPARTMENT做笛卡尔积，得到：

2. 再按照JOIN条件中StudDept=DName选出结果：

NATURAL JOIN

• NATURAL JOIN was created to get rid of the additional redundant attribute in EQUIJOIN.

  NATURAL JOIN在EQUIJOIN的基础上去掉了多余的属性。

• Denoted by* 用*表示

AGGREGATE FUNCTION Operation 聚合操作

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	求平均值
MAX()	最大值
MIN()	平均值
…	…

举例：

OUTER JOIN Operation

• Sometimes we might wa nt to keep all tuples in R or S or both in results of JOIN regardless of whether they satisfy join condition or not.

  有时，我们可能希望在JOIN的结果中保持所有元组在R或S中，或两者都在，而不管它们是否满足连接条件。

• There are 3 types of OUTER JOIN operations.

  • LEFT OUTER JOIN 左外连接

  • RIGHT OUTER JOIN 右外连接

  • FULL OUTER JOIN 全连接

若右表没有对应的值，则将其取值为NULL

若左表没有对应的值，则将其取值为NULL

保留左右表所有的元组，无法对应取值为NULL