Chapter 1 Overview

Size: px

Start display at page:

Download "Chapter 1 Overview"

鞭时
4 years ago
Views:

1 第二章关系数据库 1

2 内容数据库系统概述数据模型数据库体系结构关系数据库关系模型关系操作关系数据库语言 2

3 数据库系统概述数据库系统相关数据的集合 (DB) 一组访问数据的程序 Adding/Removing files to DB Retrieving data from DB Inserting/ Changing/Deleting data from DB DBMS 的数据是某个特定企业的信息 DBMS 提供用户方便 ( convenient) 的和有效 ( efficient) 的应用环境 3

4 数据与数据模型数据给定事实数据模型抽象的, 自含的, 对象和操作符等的逻辑定义, 所有这些一起组成了抽象机实现真实机器上的物理实现 4

5 数据与数据模型 - 续数据抽象实体与联系实体 (Entities) supplier, part, project, warehouse, employee. 联系 (Relationships) sp, sj, spj. 特征 (Properties) 供应商 : location. 零件 : weight.. 5

6 实体 - 联系图 6

7 数据独立数据库技术的主要目标适应应用需求, 数据物理表示和访问技术的改变是必要的两类独立性物理数据独立逻辑数据独立 7

8 体系结构 8

9 体系结构 ANSI/SPARC 数据库管理系统研究组网状模型 1974 年的 DBTG 报告 9

10 三层体系结构 10

11 概念 ( 层 ) 模式数据库中全体数据的逻辑结构和特征的描述数据记录由哪些数据项构成数据项的名字类型取值范围数据之间的联系数据的完整性等不涉及数据物理存储的细节和硬件环境一个数据库只有一个概念模式通过模式 DDL 进行定义 11

12 外 ( 层 ) 模式单个用户所看到的局部数据的逻辑结构和特征的描述用户与数据库系统的数据接口, 对于用户而言, 外模式就是数据库用户可以使用习惯的语言 (C++, Java ) 主语言 & 子语言建立在概念模式之上, 同一概念模式上可有多个不同的外模式通过外模式 DDL 进行定义 12

13 内 ( 层 ) 模式数据物理结构和存储方式的描述记录的存储方式 : 顺序存储按 B 树组织还是散列存储索引按什么方式组织 : 排序散列数据是否加密, 是否压缩存储不涉及物理块 ( 或页 ) 的大小, 也不考虑具体设备的柱面或磁道大小一个数据库只有一个内模式通过内模式 DDL 定义 13

14 举例 User 1: 学生 User 2: 选课管理人员 User 3: 课程评价管理人员外模式课程信息自己的选课信息课程信息全部学生的选课信息课程信息课程评价信息课程信息概念模式全部的选课信息课程评价信息物理模式单个数据库文件课程号聚簇索引 14

15 二级映象和数据独立性外模式外模式外模式二级映象实现三级模式结构间的联系和转换, 使用户可以逻辑地处理数据, 不必关系数据的底层表示方式和存储方式外模式 / 模式映象概念模式模式 / 内模式映象内模式数据库 15

16 外模式 / 模式映象定义了外模式与概念模式之间的对应关系属性名称可能不同外模式中的属性可能由模式中的多个属性运算而得当概念模式发生改变时, 只要修改外模式 / 模式映象, 可保持外模式不变, 从而保持用户应用程序不变, 保证了数据与用户程序的逻辑独立性数据的逻辑独立性 16

17 模式 / 内模式映象定义了概念模式与内模式之间的对应关系概念模式中的逻辑记录和字段在内部如何表示当数据库的内部存储结构发生改变时, 只要修改模式 / 内模式映象, 可保持概念模式不变, 从而保持外模式以及用户程序的不变, 保证了数据与程序的物理独立性数据的物理独立性 17

18 举例外模式 :EMP(EMP#,DEPT#,NAME) E# EMP# Create View EMP(EMP#,DEPT#,NAME) As Select E# as EMP#,D# as DEPT#,name From Employee 此语句定义了外模式, 同时也定义了外 / 模映象关系模式 :Employee(E#,D#,Name,Salary) 内模式 : 顺序文件, 索引文件, D# DEPT# 18

19 模式和实例模式 (Schema) 是数据库中全体数据的逻辑结构和特征的描述, 它仅仅涉及类型的描述, 不涉及具体的值模式的一个具体值称为模式的一个实例 (Instance) 数据库实例化数据库模式 ( 数据字典 ) 数据数据的语义 ( 元数据 ) 19

20 20

21 数据库读写过程 buffer OS

22 DBA 协调数据库系统的所有活动 ; 对整个企业的信息资源和需求有好的理解 22

23 DBA 续 DBA 的职责 : 模式定义 ( 逻辑数据库设计 ) 存储结构和访问方法的定义模式和物理组织的修改授权用户访问数据库完整性约束声明动作类似用户别名定义备份和重载原则监视性能和相应修改需求 23

24 DBMS 软件处理所有对数据库的访问数据定义数据操作 DML 申请 ( 计划的 / 无计划的 ) 优化 & 执行数据安全 & 完整性数据恢复 & 并发数据字典性能 24

25 关系数据库 25

26 引言 Mar E.F.CODD A Relational Model of Data for Large Shared Data Bank CACM 1970 数学基础 ( Relational Theory) 发展在 1980s 大多数商用 DBMS 是关系型 26

27 模型的三个方面数据结构语义完整性数据操作数据结构 ( 模式 ) 关系模型关系 (Relation) - 表规范化的二维表格结构表示实体集 27

28 举例 28

29 关系模式术语域 (Domain) 一组具有相同数据类型的值的集合每个属性有一个域属性 (Attribute) 二维表格的每一列元组 (Tuple) 每一行称为关系的一个元组基数 (Cardinality) 元组的数目度 (Degree) 列的数目 29

30 30

31 关系的形式化定义给定域值集合 D 1, D 2,. D n, 关系 r 是如下笛卡尔积的子集 D 1 x D 2 x x D n 也就是, 关系是 n- 元组集 (a 1, a 2,, a n ) 这里 a i D i 31

32 关系的形式化定义续举例 : 如果 customer-name = {Jones, Smith, Curry, Lindsay} customer-street = {Main, North, Park} customer-city = {Harrison, Rye, Pittsfield} 则 r = { (Jones, Main, Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield)} 是 customer-name x customer-street x customer-city 上的关系 32

33 关系的性质一个关系是一个规范化的二维表格属性值不可分解不允许表中有表元组不可重复因此一个关系模式至少存在一个候选码没有行序, 即元组之间无序关系是元组的集合, 集合的元素是无序的没有列序, 即属性之间无序关系模式是属性的集合 DB 中的关系的元组个数是有限值 33

34 操作选择 (select) 从表中抽取指定的行投影 ( Project) 从表中抽取指定的列连接 (Join) 在公共列的基础上连接两个表 34

35 操作 - 续以集合为单元操作算子和结果都是表闭包性质一个操作的的输出结果可以是另一个操作的输入. 35

36 关系与关系变量关系变量不同时间有不同的关系值基本关系变量 (Base RELVAR) 有元组的真实关系视图 (Views) 其元组是依赖于基本关系的虚关系 36

37 DEPT & EMP 是关系变量, 它的值是关系值从 DEPT & EMP 中删除一个元组后 37

38 关系 EMP 和 DEPT View EMP-sal View EMP-DEPT 38

39 KEY 候选键 (Candidate Keys) 当且仅当如下条件成立 K 是关系 R 候选键唯一性 (Uniqueness) 最小性 (Irreducibility) e.g. S(s#) 是键, S(s#, age) 是超键 ( super key ) 39

40 KEY- 续. 主关键字 (Primary Keys) 与候选键 Primary key 是被关系选中的候选键主关键字不能取空值 (not null) 40

41 Primary Keys: E# Alternate Keys: E#, SSC# 41

42 KEY- 续. 外关键字 (Foreign Key) R2 是一关系, R2 中的外关键字是 R2 的一组属性, FK, 满足 : 存在关系 R1 有候选键 CK R2 中的每个 FK 值一定与 R1 中某个元组的 CK 值相等. 42

43 Primary Keys: D# Foreign key D# 43

44 KEY- 续. 术语引用 ( reference) 引用约束 ( referential constraint) 被引用元组引用 / 被引用关系 44

45 Referential diagram s# p# S SP P Referential path R 3 R 2 R 1 R n R n-1... R 2 R 1 Self-referential Referential cycle 45

46 KEY- 续. 引用完整性 (Referential integrity) 数据库不能含有任何不匹配的外关键字值定义 FOREIGN KEY {<item commalist>} REFERENCES <relvar name> (<item commalist>) 46

47 KEY- 续. 引用动作 ON DELETE CASCADE/RESTRICT/NO ACTION ON UPDATE CASCADE/RESTRICT/NO ACTION 47

48 48 48

49 关系数据库数据库由多个关系组成整个组织的信息被分解成多个部分, 每个关系就存储每一部分信息 E.g.: account : 存储帐号信息 depositor : 存储客户与帐号的关联信息 customer : 存储客户信息 49

50 关系模式 A 1, A 2,, A n 是属性 R = (A 1, A 2,, A n ) 是关系模式, 例如 : Customer-schema = (customer-name, customer-street, customer-city) r(r) 关系模式 R 上的关系例 :customer (Customer-schema) 50

51 语言 SQL 关系代数关系演算 51

52 SQL 语言 52

53 引言 IBM SYSTEM R SEQUEL ANSI 标准 SQL 1990 ISO 标准 SQL 1992 SQL3 (SQL99) 53

54 体系结构 View Table File V/T 映射用户定义 T/F 映射系统完成 54

55 SQL DDL 包括完整性与安全性 DML 55

56 需要创建的结构 Table View Index SQL DDL Create {table, view, index} < name > <description for that> 例. Create Table DEPT ( DEPT# Number, DNAME Char(5), Budget Number( 7,2)); 56

57 索引 SQL DDL 续 Create index <name> on <table name> ( <index attr name list> ) E.g. Create index I1 on EMP (E#); Create index I2 on EMP (Ename); 唯一性索引 E.g. Create unique index I1 on EMP (E#); 57

58 聚集索引 SQL DDL 续元组按照索引值顺序, 物理上尽可能的存储在一起, 在索引值上执行扫描 (scan) 操作时可以减少 I/O. E.g. Create cluster index CI1 on EMP (E#); 58

59 ,11,12,22 13, ,25,26,34., ,11,12,13 14, ,25,26,34.,30 59

60 基本查询块典型的 SQL 查询语句格式 : select A 1, A 2,..., A n from r 1, r 2,..., r m where P A i s 代表属性 r i s 代表关系 P 是谓词. 60

61 举例查找所有居住在 London 并且状态大于 20 的供应商的供应商号 select S# from S where city= London AND status> 20 order by S# 查找所有已供应零件的供应商名字和零件号 select sx.sname, spx.p# from S sx, SP Spx where sx.s#=spx.s# 61

62 举例 - 续查询年龄 40 岁以下不同部门平均工资大于 6000 的部门, 并按部门升序列表 select d#, avg (SAL) from EMP where age < 40 group by d# having avg (SAL) > 6000 order by d#; 62

63 举例 - 续查询在 sale 部门工作的职工名. select distinct Ename from EMP where d# in (select d# from DEPT where Dname= sale ) 63

64 举例 - 续查询缺少某门课成绩的学生学号 Select s# From SC Where score IS NULL 查询只选修了 1 门或 2 门课程的学生学号姓名和课程数 Select student.s#, sname, count_c# From (Select s# as s#, count(s#) as count_c# From sc Group by s#) SC2, student Where sc2.s# = student.s# and (count_c#=1 OR count_c#=2) 64

65 举例 - 续查询供应了所有在 London 的零件的供应商. select distinct Sx.sname from S Sx where not exists ( (select pname from P where city = London ) except (select Px.pname from SP Tx, P Px where Tx.p# = Px.p# and Sx. S# = Tx.s#)) Yue 65

66 删除删除所有居住在 London 的供应商 delete from S where city = London 删除供应零件 p2 的供应商. delete from S where s# in ( select s# from SP where p#= p2 ) 66

67 删除所有低于平均工资的职工. delete from EMP where SAL < (select avg (SAL) from EMP) 此语句 SQL 的解决方法 : 首先计算平均工资然后删除元组 67

68 插入插入新元组到 S insert into S values ( s6, wang ping, 20, shanghai) 或等价地 : insert into S (status, city, s#, sname) values (20, shanghai, s6, wang ping, ) 对 S 插入新元组, 但 city 是空 insert into S values ( s7, Li hong, 30, null) 68

69 插入 - 续对所有在黄山路支行的贷款者 $200 存款的礼物. insert into account select loan-number, branch-name, 200 from loan where branch-name = huangshanlu 69

70 更新工资大于 $4,000 的增加 6%, 其他职工工资增加 5%. update EMP set SAL = SAL 1.06 where SAL > 4000 update EMP set SAL = SAL 1.05 where SAL 4000 这里两个语句的顺序很重要 70

71 含有完整性定义的关系定义例子 Create Table SP ( s# s# not null, p# p# not null, qty qty not null, primary key (s#, p#), foreign key (s#) references S on delete cascade on update cascade, foreign key (p#) references P on delete cascade on update cascade, check (qty >0 and qty <5001) ); 71

72 嵌入 SQL SQL 标准给出了 SQL 在各种程序语言中的嵌入方式, 可嵌入的程序语言有 :Pascal, PL/I, Fortran, C, 和 Cobol 等. SQL 语句嵌入的语言称作主语言 ( host language), 允许在主语言中出现的 SQL 结构称作嵌入 SQL( embedded SQL). 72

73 嵌入 SQL- 续 EXEC SQL 语句用于标识嵌入 SQL EXEC SQL <embedded SQL statement > 注意 : 这种标识随语言不同而不同. 如 JAVA 使用 # SQL {. } ; 73

74 单元组查询嵌入 SQL- 续 EXEC SQL Select Ename INTO :ename From EMP Where e# = e1 注 : :ename 称作主变量 (host variable ), 主变量需要在特定的声明语句中定义. 74

75 嵌入 SQL- 续 EXEC SQL BEGIN DECLARE SECTION; Char SQLSTATE[6]; Char P# [6]; int Weight; EXEC SQL END DECLARE SECTION; P#= P2 ; EXEC SQL Select P.weight INTO :weight FROM P WHERE P.P#=:P#; If SQLSTATE= Then. Else.. ; 75

76 嵌入 SQL- 续 SQLSTATE 语句 EXEC SQL WHENEVER <condition> <action> 条件 Not found 没查询到元组 Sqlerror 出错 76

77 嵌入 SQL- 续多元组查询在主语言和 SQL 之间存在不匹配问题 77

78 举例查询供应了 amount 数量零件的供应商名字和其所在的城市. 先定义游标 ( cursor) EXEC SQL declare c cursor for select sname, city from S, SP where S.s# = SP.s# and SP.QTY > :amount 78

79 嵌入 SQL- 续游标打开语句 ( open ), 执行游标查询 EXEC SQL open c 游标取语句 (fetch ) 获取一个元组送入主变量. EXEC SQL fetch c into :cn, :cc 通过循环调用 fetch 语句, 获取所有元组 79

80 嵌入 SQL- 续 SQLSTATE 变量在 SQL 通讯区中 (SQLCA) 获取值得知是否还有元组存在游标关闭语句 ( close ). EXEC SQL close c 80

81 游标更新定义 declare c cursor for select * from EMP where city = Parise for update 修改当前游标元组 update EMP set SAL = SAL where current of c 游标更新 81

82 关系代数 82

83 引言以关系为运算对象的一组运算集合运算结果仍是关系以集合操作为基本运算传统关系代数传统集合操作 (4 个 : 并交差笛卡儿积 ) 关系代数专门关系操作 (4 个 : 选择投影连接除 ) 附加关系代数 ( 分组 / 聚集排序半连接半差赋值 ) 83

84 关系代数的符号数学符号表示并交差 - 笛卡儿积选择 σ 投影 π 联接除重命名 x (E) 赋值英语关键字表示并 Union 交 Intersect 差 Minus 笛卡儿积 Times 选择 Where 投影 {All But } 联接 Join 除 Devidedby 84

85 关系代数表达式关系模型中数据操作都通过关系代数表达式来表示关系代数中的基本表达式是关系代数表达式, 基本表达式由如下之一构成 : 数据库中的一个关系一个常量关系设 E1 和 E2 是关系代数表达式, 则下面的都是关系代数表达式 : E1 E2 E1-E2 E1 E2 σ P (E1), 其中 P 是 E1 中属性上的谓词 π s (E1), 其中 S 是 E1 中某些属性的列表 ρ x (E1), 其中 x 是 E1 结果的新名字 85

86 并 R S={t t R t S} t 是元组变量 R 和 S 是关系代数表达式 R 与 S 的 degree 必须相同 R 与 S 的属性类型必须相同学号姓名年龄 R 001 John Mary 22 学号姓名年龄 001 John 20 S 学号姓名年龄 001 John Rose Mary Rose 22 86

87 交 R S={t t R t S} 学号姓名年龄 R 001 John Mary 22 学号姓名年龄学号姓名年龄 001 John 20 S 001 John Rose 22 87

88 差 R-S={t t R t S} 学号姓名年龄 R 001 John Mary 22 - 学号姓名年龄学号姓名年龄 004 Mary 22 S 001 John Rose 22 88

89 积 R S={t t=<t r,t s > t r R t s S} R 学号姓名年龄 001 John Mary 22 S 学号姓名年龄 001 John Rose 22 R. 学号 R. 姓名 R. 年龄 S. 学号 S. 姓名 S. 年龄 001 John John John Rose Mary John Mary Rose 22 89

90 选择 σ F (R)={t t R F(t)=True} 水平划分关系 F 是一个逻辑表达式, 表示所选的元组应满足的条件 F 由逻辑运算符 (NOT) (AND) (OR) 连接算术表达式构成算术表达式形为 Xθ Y,θ 可以是 >,<,=,, 或,X 和 Y 可以是属性名常量或简单函数 R 学号姓名年龄 001 John Rose Mary 22 σ 年龄 >20(R) 学号姓名年龄 002 Rose Mary 22 90

91 投影 π A (R)={t[A] t R}, 其中 A 是 R 的属性子集垂直划分关系, 选取若干列所构成的关系 A 中的属性不可重复 R 学号姓名年龄 001 John Rose Mike Mary Rose 22 Π 姓名, 年龄 (R) 姓名年龄 John 22 Rose 22 Mike 21 Mary 22 91

92 联接自然联接 (Natural-Join) θ 联接 (Theta-Join) 等值联接 (Equi-Join) 92

93 自然联接设 R 的属性集为 {X,Y},S 的属性集为 {Y,Z} R S=t t=<x,y,z> t[x,y] R t[y,z] S} 相当于在 R S 中选取 R 和 S 的所有公共属性值都相等的元组, 并在结果中去掉重复属性学号姓名年龄 001 John 20 R S 002 Rose Mary 22 学号课程号成绩 001 c 学号姓名年龄课程号成绩 001 John 20 c Rose 22 c c

94 θ 联接设 R 的属性集为 {X,Y},S 的属性集为 {Y,Z} R S=t t=<t r,t s > t r R t s S t r [A]θ t s [B]} 相当于在 R S 中选取 R 的属性 A 值与 S 的属性 B 值满足比较关系 θ 的元组 R S 学号姓名年龄 001 John Rose 23 教师号姓名年龄 001 Bill Rose 30 R. 年龄 > S. 年龄学号 R. 姓名年龄教师号 S. 姓名年龄 001 John Bill Rose Bill 22 94

95 等值联接 (equijoin) 在 θ 联接中, 当 θ 为等号时, 称为等值联接等值联接是 θ 联接中比较常见的形式 R S 学号姓名年龄 001 John Rose 23 教师号姓名年龄 001 Rose Rick 30 R. 姓名 = S. 姓名学号 R. 姓名 R. 年龄教师号 S. 姓名 S. 年龄 002 Rose Rose 22 95

96 关系代数封闭性任意关系代数操作的结果仍是一个关系关系代数的封闭性保证了关系代数操作的可嵌套性例如 :(S Join P) Where City= Athens 联接选择 96

97 关系代数 - 删除数据更新仍通过关系代数实现 R R-E:R 是关系,E 是关系代数查询例从数据库中删除姓名为 Rose 的学生 Student Student-σ name= Rose (Student) 97

98 关系代数 - 插入 R R E:R 是关系,E 是关系代数表达式例 : 插入一个新的学生 S1 S1 { ( 001, Rose,19) } 98

99 关系代数 - 修改 R π F1,F2,...,Fn (R): 通过广义投影实现例 1: 将每个学生的学号前加上字母 S Student π S sno,name, sex, age (Student) 如果只想修改 R 中的部分元组, 可以用下式 R π F1,F2,...,Fn (σ p (R)) (R-σ p (R)) 例 2: 将所有男学生的学号前加上字母 M Student π M sno,name, sex, age (σ sex= M (Student)) (Studentσ sex= M (Student)) 99

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

101 举例 - 续查询有供应商或零件或二者皆有的城市. city (S) city (P) 查询即有供应商又有零件的城市. city (S) city (P) 101

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

103 举例 - 续查询状态大于 20 并且没有零件的城市. city ( status >20 (S)) city (P) 103

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

105 关系代数与 SQL 供应商关系模式 :S (S#, SNAME, STATUS, CITY) 求住在同一个城市里的供应商号码对 π S1#,S2# ( σ S1.S1# < S2.S2# ( S1(S1#,CITY) (π S#,CITY (S)) S2(S2#,CITY) (π S#,CITY (S)))) 相应的 SQL 查询语句 SELECT S1#, S2# FROM (Select S# AS S1#,city From S) S1, (Select S# AS S2#,city From S) S2 WHERE S1.city = S2.city AND S1.S1# < S2.S2# 105

106 DB 公司

107 国产 DB 南大通用 GBASE 数据库 (GBase) 金仓数据库 (Kingbas... 达梦数据库 (DM) OceanBase OpenBASE 神通数据库 107

108 Gartner 发布了 2018 年的数据库系列报告,5 家中国 DB 上榜阿里云数据库华为数据库 SequoiaDB 巨杉数据库腾讯云数据库 Transwarp Technology 108

109 练习给定如下数据库 Movie (title, year, length, incolor, studioname, producerc#) StarsIn (movietitle, movieyear, strname) MovieStar (name, address, gender, birthdate) MovieExec (name, address, cert#, networth) Studio (name, address, presc#) 用 SQL 语言和关系代数写出下面的查询语句 : 1. 查询 Sandra Bullock 的出生日期 2. 查询资产至少 $10,000,000 的制片人 3. 查询在 1995 年 MGM 电影公司制作的影片中出演的影星 4. 查询 MGM 公司的总经理 109

110 下次课数据存储 110

Microsoft PowerPoint - adb02.pptx

Microsoft PowerPoint - adb02.pptx 第 2 章关系数据库回顾主要内容数据库体系结构 (Database Architecture) 关系数据模型 (Relational Data Model) SQL 2 一数据库体系结构从模式的角度描述一般数据库系统的概念和结构可以用于解释特定数据库系统的内部结构 ANSI/SPARC 体系结构三级模式结构 + 两级映象 Oracle Informix 等 SQL 数据库系统的模式结构可通过