Chapter 6. Relational Algebra

Size: px

Start display at page:

Download "Chapter 6. Relational Algebra"

畏江
7 years ago
Views:

1 第六章关系代数

2 内容概述形式定义操作的语法和语义查询举例更新比较 SQL 2

3 概述过程性语言操作对象是关系, 操作结果仍然是关系. 3

4 形式化定义关系代数的基本表达式是如下形式之一 : 数据库中的关系常数关系 4

5 形式化定义 - 续. 令 E 和 E 2 关系代数表达式 ; 如下表达式都是关系代数表达式 : E E 2 E - E 2 E x E 2 5

6 形式化定义 - 续. F (E ), F 是 E 中属性上的谓词 A (E ), A 是 E 中的属性列表 x (E ), x 是 E 结果的新名字 6

7 操作的语义 7

8 选择操作关系 r A B C D A=B ^ D > 5 (r) A B C D

9 选择操作 - 续 F (r) F 被称为选择谓词 9

10 选择操作 - 续定义 : F (r) = {t t r and F(t)} 其中 F 是由比较运算及逻辑运算组成的条件表达式 : (and), (or), (not) 比较运算 : <attribute>op <attribute> 或 <constant> 这里 op 是 : =,, >,. <. 0

11 选择操作 - 续例 : city= London (S)

12 投影 Relation r A B C A,C (r) A C A C = 2 2 2

13 投影 - 续. A, A2,, Ak (r) 其中 A, A 2 是属性名,r 是关系名 r{a,a2, Ak} 3

14 投影 - 续. 结果是 k 列关系, 通过消除不在操作列表中的属性获得冗余元组消除例. 消除 city 属性后的 S 关系 s#,sname,status (S) 4

15 并关系 r, s: r A B s A B r s: A B 2 3 5

16 并 - 续 r s r union s 定以为 : r s = {t t r or t s} 6

17 并 - 续 r s 有效的条件. r, s 必须同度其属性域必须兼容例. 查询 supplier 或 part 所在的城市 city (S) city (P) 7

18 差关系 r, s: r A B s A B r s : A B 8

19 差 - 续. r s 定义为 : r s = {t t r and t s} 集合差的操作必须在两个匹配的关系上. r 和 s 必须同度 r 和 s 的属性必须兼容 9

20 交 r, s: r s r A B 2 s A 2 3 B A B 2 20

21 交 - 续. r s 定义为 : r s ={ t t r and t s } 条件 : r, s 同度 r 和 s 兼容注 : r s = r - (r - s) 2

22 22 笛卡尔积关系 r, s: r x s : A B 2 A B C D E a a b b a a b b C D E a a b b r s

23 笛卡尔积 - 续. r x s 定义为 : r x s = {t q t r and q s} 假设 r(r) 和 s(s) 的属性不相交. ( 即, R S = ). 如果 r(r) 和 s(s) 属性相交, 则需要重命名 23

24 组合操作 r x s A=C (r x s) A B C D E a a b b a a b b A B C D E a a b 24

25 重命名操作例 : x (E) 对表达式 E 重新按 X 命名 25

26 重命名操作 - 续如果表达式 E 是 n 度, 则 x (A, A2,, An) (E) 返回 E 在 X 下的命名结果, 其属性重命名为 A, A2,., An. 26

27 查询举例查询状态大于 20 的供应商 status > 20 (S) 查询状态大于 20 的供应商号 s# ( status > 20 (S)) 27

28 查询举例 - 续. 查询所有供应商或零件所在的城市. city (S) city (P) 查询所有同时有供应商和零件的城市. city (S) city (P) 28

29 查询举例 - 续. 查询供应了 London 城市零件的供应商号 s# ( city= London ( p.p# = sp,p# (P x SP))) 29

30 查询举例 - 续.. 查询状态超过 20, 并且其所在城市没有零件的供应商的城市. city ( status >20 (S)) city (P) 30

31 查询举例 - 续.. 查询供应了 London 城市零件的供应商号. Query s# ( city = London ( p.p# = sp.p# (P x SP))) Query 2 s# ( p.p# = sp.p# (( city = London (P)) x SP)) 3

32 查询举例 - 续. 查询零件供应量的最大量重命名 SP 关系为 d 查询语句 : qty (SP) - SP.qty ( SP.qty < d.qty (SP x d (SP))) 32

33 自然连接 Mar.26 令 r 和 s 分别是模式 R 和 S 上的关系结果是模式 R S 上的关系, 该关系的元组是通过每一个来自 r 的元组 t r 和来自 s 的元组 t s 组成的元组对获得. 33

34 自然连接 - 续. 如果 t r 和 t s 在每个 R S 的属性上有相同值, 元组 t 被加入结果集中, 其中 t 有 r 上相同的 t r 值 t 有 s 上相同的 t s 值 34

35 自然连接 - 续. 例 : R = (A, B, C, D) S = (E, B, D) 结果关系模式 = (A, B, C, D, E) r s = r.a, r.b, r.c, r.d, s.e ( r.b = s.b r.d = s.d (r x s)) 35

36 36 自然连接 - 续. 关系 r, s: A B C D a a b a b B D a a a b b E r A B 2 C D a a a a b E s r s

37 - 连接 - 连接是不需要两个关系有相同属性的连接, 并且其连接条件可以是任意比较操作. Equi- 连接时要求连接条件必须是等于比较操作的连接. 37

38 除适合于那些含有全部短语查询需求的查询. 令 r 和 s 分别是模式 R 和 S 上的关系, 这里 R = (A,, A m, B,, B n ) S = (B,, B n ) 38

39 除 - 续. r s 结果是如下模式上的关系 R S = (A,, A m ) r s 的结果关系是 r s = { t t R-S (r) u s ( tu r ) } 39

40 除 - 续. 关系 r, s: r A B s B r s: A

41 4 除 - 续. A B a a a a a a a a C D a a b a b a b b E 3 关系 r, s: r s: D a b E A B a a C r s 例 :

42 除 - 续. 特征令 q 是 r s 的结果则 q 满足如下条件的最大关系 q x s r 42

43 除 - 续. 基本代数操作上的定义令 r(r) 和 s(s) 是关系, 并且令 S R, 则 r s = R-S (r) R-S ( ( R-S (r) x s) R-S,S (r)) 43

44 赋值操作赋值操作 () 为复杂查询提供了简化描述的方法. 赋值永远是付给一个临时关系变量. 44

45 赋值操作 - 续. 例 : r s 可以写为如下查询表达式的结果 temp R-S (r) temp2 R-S ((temp x s) R-S,S (r)) result = temp temp2 号右边的结果被赋值给号左边的关系变量. 后续的查询表达式中可以使用被赋值的关系变量. 45

46 查询举例查询所有职工名称, 该职工的工作来源于 Sales 与 Accounting 部门. 查询 ename ( dname= Sales (EMP ename ( dname= accounting (EMP DEPT)) DEPT)) 查询 2 ename, dname (EMP DEPT) temp(dname) ({( Sales ), ( Accounting )}) 46

47 查询举例 - 续查询在合肥所有分行都有帐号的顾客. customer-name, branch-name (depositor account) branch-name ( branch-city = hefei (branch)) 47

48 外连接为了避免信息丢失的连接操作. 计算连接, 然后将关系中与另一关系没有匹配元组的元组保留在结果关系中. 48

49 外连接 - 续. 使用 null 值 : null 表示不确定或者不存在通常涉及 null 的比较计算结果是 false 49

50 外连接 - 续关系 loan loan-number L-70 L-230 L-260 branch-name Downtown Redwood Perryridge amount 关系 borrower customer-name Jones Smith Hayes loan-number L-70 L-230 L-55 50

51 举例内连接 Inner Join loan Borrower loan-number branch-name amount customer-name L-70 L-230 Downtown Redwood Jones Smith 左外连接 Left Outer Join loan borrower loan-number branch-name amount customer-name L-70 L-230 L-260 Downtown Redwood Perryridge Jones Smith null 5

52 举例 - 续有外连接 Right Outer Join loan borrower loan-number branch-name amount customer-name L-70 L-230 L-55 Downtown Redwood null null Jones Smith Hayes 全外连接 Full Outer Join loan loan-number L-70 L-230 L-260 L-55 borrower branch-name Downtown Redwood Perryridge null amount null customer-name Jones Smith null Hayes 52

53 聚集操作聚集函数对一组值计算结果返回单个值. 聚集函数有 : avg min max sum count 53

54 聚集操作 - 续. 聚集操作的关系代数表达式定义 g G, G2,, Gn F( A), F2( A2),, Fn( An) (E) E 是关系代数表达式 G, G 2, G n 是分组属性 ( 可选项 ) F i 是分组函数 A i 是属性名 54

55 聚集操作 - 续. 关系 r: A B C g sum(c) (r) sum-c 27 55

56 聚集操作 - 续. 对关系 account 用 branch-name 分组 : branch-name account-number balance Perryridge Perryridge Brighton Brighton Redwood A-02 A-20 A-27 A-25 A branch-name g sum(balance) (account) branch-name Perryridge Brighton Redwood balance

57 数据库更新数据库的内容可以通过如下操作更新 : Deletion Insertion Updating 这些操作都使用赋值操作表达. 57

58 删除删除操作类似查询, 只是这里查询出的元组是要求系统将其从数据库中移出, 而不是展现给用户. 删除是对整个元组, 而不能针对某个属性 58

59 删除 - 续. 删除的代数表达 : r r E r 是关系, E 是关系代数查询. 59

60 举例删除所有在 London 的供应商. S S city = London (S) 删除所有供应量在 0 和 50 之间的供应记录 SP SP qty 0and qty 50 (SP) 60

61 插入可以有如下两种插入方式 : 单元组插入将一个查询结果元组集插入 6

62 插入 - 续. 插入的代数定义 : r r E r 是关系, E 是代数表达式. 单元组插入是令 E 表达为含有一个元组的常量关系. 62

63 举例向数据库中插入姓名 Wang hong 具有供应商号 s7, 状态为 20, 并且居住在 Beijing 的信息. S S {( s7, Wang hong, 20, Beijing )} 63

64 修改改变元组中部分属性值的机制 64

65 Updating-cont. 使用广义投影 ( generalized projection) 操作表达该任务 r F, F2,, FI, (E) E 是关系代数表达式 F, F 2,, F n 是涉及常数或 E 关系模式中属性的算术表达式. 65

66 举例将职工工资增加 5%. EMP e#,ename, SAL *.05 (EMP) 这里假定 EMP 仅有属性 e#, ename, 和 SAL. 66

67 代数与 SQL 例. s# ( city= London ( P.p# =SP. p# (P x SP))) Select s# From P, SP Where P.p#=SP.p# AND city = London 67

68 代数与 SQL- 续. 例. s# ( p# = p# (( city = London (P)) x SP)) Select s# From SP Where p# in (Select p# From P Where city = London ) 68

69 代数与 SQL. 例. g sum(sal) (EMP) Select sum(sal) From EMP 例. AVG(SAL)>2000 ( d# g AVG(SAL) (EMP)) Select d#, AVG(SAL) From EMP Group By d# Having AVG(SAL)>

70 作业将上一次作业的 SQL 语句改写为代数表达式 70

71 关系演算 7

72 引言基于谓词演算 ALPHA, QUEL 语言范围变量 e.g. Range of sx is S 元组演算和域演算 72

73 元组演算语法 <Range var definition>::= RANGEVAR <range var name> RANGES OVER <relational exp commlist> <range attr reference>::= <range var name>.<attr reference> [ AS <atrr name> ] <boolean exp>::= all the usual possibilities, together with: <quantified boolean exp> <quantified boolean exp>::= EXISTS <range var name> (<boolean exp>) FORALL <range var name>(boolean exp>) <relational operation>::= <proto tuple>[where <boolean exp>] <proto tuple> ::= <tuple exp> 73

74 元组演算 - 续范围变量举例 RANGEVAR sx RANGES OVER S; RANGEVAR sy RANGES OVER S; RANGEVAR spx RANGES OVER SP; RANGEVAR spy RANGES OVER SP; RANGEVAR px RANGES OVER P; RANGEVAR su RANGES OVER ( sx WHERE sx.city = London ), (sx WHERE EXISTS spx ( spx.s# = sx.s# AND spx.p# = p#( p ))); 74

75 元组演算 - 续量词 EXISTS v (P) FORALL v (P) 定义 EXISTS v (P (v) ) false OR P( t ) OR OR P( tm ) FORALL v (P (v) ) true AND P( t ) AND AND P( tm ) FORALL v ( p) = NOT EXISTS v ( NOT p ) 75

76 元组演算 - 续 e. g. r(a, b, c)={ (, 2, 3 ) (, 2, 4 ) (, 3, 4 )} EXISTS V (V.c>) : true EXISTS V (V.b>3) : false EXISTS V (V.a> OR V.c=4) : true FORALL V (V.a>) : false FORALL V (V.b>) : true FORALL V (V.a= AND V.c>2) : true 76

77 举例查找居住在 Paris 并且状态大于 20 的供应商号和状态 (sx.s#, sx.status) WHERE sx.city = paris AND sx.status>20 查找居住在同一个城市的一对供应商号. ( sx.s# AS SA, sy.s# AS SB) WHERE sx.city = sy.city AND sx.s#<sy.s# 77

78 更多举例查找供应了零件 P2 的供应商信息. sx WHERE EXISTS spx ( spx.s#=sx.s# AND spx.p#=p#( p2 ) 查找至少供应了一种红色零件的供应商名. sx.sname WHERE EXISTS spx (sx.s#=spx.s# AND EXISTS px (px.p#=spx.p# AND px.color = color( red ))) 78

79 更多举例 - 续查找至少与 S2 供应了同一种零件的供应商名. sx.sname WHERE EXISTS spx ( EXISTS spy ( sx.s#=spx.s# AND spx.p#=spy.p# AND spy.s#=s#( s2 ))) 查找供应了所有种零件的供应商名. sx.sname WHERE FORALL px ( EXISTS spx (spx.s# = sx.s# AND spx.p# = px.p# )) 79

80 更多举例 - 续查找没有供应零件 P2 的供应商号. sx.s# WHERE NOT EXISTS spx (spx.s# =sx.s# AND spx.p# = p#( p2 )) 80

81 演算 VS 代数查找至少供应了一种红色零件的供应商名. sx.sname WHERE EXISTS spx (sx.s#=spx.s# AND EXISTS px (px.p#=spx.p# AND px.color = color( red ))) sname (( color= red (P ) SP) S)) 8

82 演算 VS 代数 - 续查找供应了所有种零件的供应商名. sx.sname WHERE FORALL px ( EXISTS spx (spx.s# = sx.s# AND spx.p# = px.p# )) sname (( s#,p# (SP) p# (P )) S) 82

83 Microsoft Access QBE Microsoft Access 可以看成是基于域演算的语言 83

84 举例 Example query: Find the customer-name, account-number and balance for all accounts at the Perryridge branch 84

85 举例 - 续 Find the name, street and city of all customers who have more than one account at the bank 85

86 下一节课完整性 86

单元四数据的查询数据库原理与应用课内例题任务 5 多表查询课内例题例创建数据表 orders, 并向表中添加记录首先创建表 orders,sql 语句如下 : CREATE TABLE orders( o_num int NOT NULL AUTO_INCREMENT, o_date d

单元四数据的查询数据库原理与应用课内例题任务 5 多表查询课内例题例创建数据表 orders, 并向表中添加记录首先创建表 orders,sql 语句如下 : CREATE TABLE orders( o_num int NOT NULL AUTO_INCREMENT, o_date d 任务 5 多表查询课内例题例创建数据表 orders, 并向表中添加记录首先创建表 orders,sql 语句如下 : CREATE TABLE orders( o_num int NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int NOT NULL, PRIMARY KEY (o_num) ) ; 插入需要演示的数据,SQL