数据库原理-总结

一些简单概念

  • DB:DataBase,数据库
  • DBS:DataBase System,数据库系统,由其它三者+应用程序构成
  • DBMS:DataBase Management System,数据库管理系统
  • DBA:DataBase Administrator,数据库管理员

数据库系统(Database System),是由数据库及其管理软件组成的系统。

数据库系统是为适应数据处理的需要而发展起来的一种较为理想的数据处理系统,也是一个为实际可运行的存储维护和应用系统提供数据的软件系统,是存储介质 、处理对象和管理系统的集合体。

Example

1
2
3
4
5
6
CREATE TABLE Movies(
title VARCHAR,
year INTEGER,
length INTEGER,
genre VARCHAR
);
  1. 关系的属性:title、year、length、genre

  2. 关系模式:Movies(title, year, length, genre)

  3. 数据库模式

    Movies(

    title,

    year,

    length,

    genre

    )

  4. 每个属性合适的域(Domain)

    • title→String
    • year→Integer
    • length→Integer
    • genre→String
  5. 该关系的另一种等价描述

    交换属性的顺序即可,答案不唯一

数据类型

1

此外还有Boolean类型,取值为TrueFalseUnknown

插入语句Insert

1
2
3
4
5
-- 插入全部属性
INSERT INTO Movies VALUES(...);
-- 插入部分属性
-- 例如插入一个新的电影,电影名字是Iron Man
INSERT INTO Movies(title) VALUES('Iron Man');

修改关系模型

1
2
3
4
5
-- Delete a relation/table
DROP TABLE Movies
-- Modify a relation/table
ALTER TABLE Movies ADD boxOffice INTEGER;
ALTER TABLE Movies DROP boxOffice INTEGER;

默认值

1
2
3
4
5
6
7
-- Creating a table
CREATE TABLE Movies(
...
genre VARCHAR DEFAULT '?'
);
-- Modify a table
ALTER TABLE Movie ADD boxOffice INTEGER 0;

主键Primary Key

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Creating a table
-- Single-Attribute
CREATE TABLE Movies(
title VARCHAR PRIMARY KEY,
...
);
-- Multi-Attributes
CREATE TABLE Movies(
title VARCHAR,
year INTEGER,
...,
PRIMARY KEY(title,year)
);

Unique

1
2
3
4
CREATE TABLE Movies(
title VARCHAR UNIQUE,
...
);

Unique与主键的区别

  • 每个关系只能有一个主键,但是可以有多个UNIQUE变量
  • 主键不能为空,但是UNIQUE属性可以为空

Foreign Key

1
2
3
4
5
6
7
8
9
10
11
-- Method 1
CREATE TABLE StarsIn(
movieTitle VARCHAR REFERENCES Movies(title),
starName VARCHAR
);
-- Method 2
CREATE TABLE starsIn(
movieTitle VARCHAR,
starName VARCHAR,
FOREIGN KEY(movieTitle) REFERENCES Movies(title)
);

基本操作符

2

  • Union:并,∪

  • Intersection:交,∩

  • Differencr:差,—

  • Prijection:投影,Π,π

    3

    Example

    πtitle,year(Movies)→title | year

    4

  • Selection:选择,σ

    σC®={ t | t∈R ∩ C(t)=true }

    Example

    5

  • Cartesian Product:笛卡尔积,×

    Example

    6

    7

  • Natural Join:自然连接,⋈

    Example

    8

    9

  • 外连接,左连接,右连接

    在自然连接的基础上,加上没用上的元组,无对应属性时填null

    Example

    10

  • Theta Join:⋈θ

    θ为一个条件

    11

    先做笛卡尔积,再找符合条件的元组

    Example

    12

  • Division:除法,÷

    13

  • Rename:重命名,ρ

    14

    Example

    15

  1. 子查询
1
2
3
4
SELECT title
FROM Movies
WHERE length>(SELECT...)
-- Wrong when subquery returns more than one result tuple
  1. EXISTS

    EXISTS R返回True当且仅当R不为空

  2. IN

    s IN R返回True当且仅当s与R中一个元组相同

  3. ALL

    s > ALL R返回True当且仅当s比R中所有对应的值都大

    19

  4. ANY

    s < ANY R返回True当且仅当R中存在一个元组使得s小于该元组中对应的值

    20

  5. EXISTS, ALL, ANY前可以加NOT

21

  1. Distinct - 消除重复元组

    22

    UNION, INTERSECT, EXCEPT默认都会消除重复元组

    使用关键词可以ALL保留重复元组

    23

    24

    25

  2. Aggregation Operators

    26

    NULL is ignored in any aggregation operators

    The result of aggregating NULL is NULL

  3. GROUP BY

    27

    28

  4. HAVING

    通常与GROUP BY一起用

    29

    如果HAVING语句后不使用集成操作,那么使用的属性必须出现在GROUP BY语句后

    30

  5. INSERTION

    31

    如果插入的属性并不是这个关系的所有属性,那么丢失的属性的值会被置为默认值

  6. DELETE

    32

  7. UPDATE

    33

    34

概念

事务指的是满足ACID特性的一组操作,可以通过COMMIT提交一个事务,也可以使用ROLL BACK进行回滚

35

ACID

  1. 原子性(Atomicity)

事务被视为不可分割的最小单元,事物的所有操作要么全部提交成功,要么全部失败回滚

回滚可以用回滚日志(Undo Log)来实现,回滚日志记录着事务所执行的修改操作,在回滚时反向执行这些修改操作即可

  1. 一致性(Consistency)

数据库在事务执行前后都保持一致性状态。在一致性状态下,所有事务对同一个数据的读取结果都是相同的

一致性状态是指在事务执行前满足给定约束,在事务执行后也满足这些约束

  1. 隔离性(Isolation)

一个事务所做的修改在最终提交以前,对其它事务是不可见的

  1. 持久性(Durability)

一旦事务提交,则其所做的修改将会永远保存到数据库中。即使系统发生崩溃,事务执行的结果也不能丢失

系统发生崩溃可以用重做日志(Redo Log)进行恢复,从而实现持久性。与回滚日志记录数据的逻辑修改不同,重做日志记录的是数据页的物理修改


事务的 ACID 特性概念简单,但不是很好理解,主要是因为这几个特性不是一种平级关系:

  • 只有满足一致性,事务的执行结果才是正确的。
  • 在无并发的情况下,事务串行执行,隔离性一定能够满足。此时只要能满足原子性,就一定能满足一致性。
  • 在并发的情况下,多个事务并行执行,事务不仅要满足原子性,还需要满足隔离性,才能满足一致性。
  • 事务满足持久化是为了能应对系统崩溃的情况。

Dirty

  • Dirty Data:脏数据,一个事务在提交之前所写的数据
  • Dirty Read:脏读,读了其它事务所写的脏数据
  • Risk:
    • 写下脏数据的事务最终可能停止或出错回滚
    • 脏数据会被数据库清除?
  • 事务的隔离性有4种级别

36

  1. 函数依赖(FD):Function Dependency

关系R上的函数依赖是指"如果R的两个元组在属性A1, A2 … An上一致(即它们对应于这些属性的分量值都相等),那么它们必定在其他属性B1, B2 … Bm上也一致"。该函数依赖形式记为A1, A2 … An → B1, B2 … Bm,并称为"A1, A2 … An决定B1, B2 … Bm"

  1. 关系的键:Key

如果下列条件满足,就认为一个或多个属性集{A1, A2 … An}是关系R的键

  • 这些属性函数决定关系的所有其他属性。也可以说,关系R不可能存在两个不同的元组,且它们具有相同的A1, A2 … An
  • 在{A1, A2 … An}的真子集中,没有一个能函数决定R的所有其他属性。也就是说,键必须是最小的(minimal)

注意:当键只包括一个单独的属性A时,称A(而不是{A})是键

主键是键的其中之一,人为规定主键

  1. 超键:Superkey

一个包含键的属性集就叫做超键,它是键的超集的简写

每个键都是超键,但超键不一定是键

超键满足键的第一个条件,它函数决定了关系中所有其他属性

  1. 平凡函数依赖

37

  1. 属性的闭包

Example

38

超键的闭包包含全部属性

移除一个属性后,主键闭包无法包含全部属性

  1. 最小基本集

A basis B满足以下三个条件

  • B中所有函数依赖右边都只有1项
  • 移除B中任何一个函数依赖,B就不再是基
  • 移除B中任何一个函数依赖左边的任何属性,B就不再是基

最小基本集就是一个关系函数依赖的最简形式,基本集中任何一个FD都不能由其他FD推出

  1. 异常-Anomaly

当试图在一个关系中包含过多信息时,产生的问题(如冗余)称为异常(anomaly)

异常的基本类型有:

  • 冗余(redundancy):信息没有必要在多个元组中重复
  • 更新异常(update anomaly):可能修改了某个元组的信息,但是没有改变其他元组中的相同信息
  • 删除异常(deletion anomaly):如果一个值集变成空集,就可能带来丢失信息的副作用

39

  1. 分解关系-Decomposition

可以通过关系分解消除异常

重复选择使用适当的分解,可以把任何一个关系模式分解为带有下列重要性质的具有多个属性的子集

  • 以这些子集为模式的关系都属于BCNF
  • 原始关系中的数据都被正确地反映在分解后的关系上

40

  1. BCNF范式

关系R属于BCNF当且仅当:如果R中非平凡FD A1, A2 … An → B1, B2 … Bm成立,则{A1, A2 … An}是关系R的超键

换言之,每个非平凡FD的左边都必须是超键。由于超键不一定要最小化,因此,BCNF的一个等价描述是,每个非平凡FD的左边必须包含键

  1. 无损连接-Lossless Join

如果分解关系R后能够从分解中恢复信息,重新获得关系R,则称该分解含有无损连接

但是仅通过BCNF范式进行关系分解,该分解不一定含有无损连接

无损连接的chase检验

Example

假设关系R(A,B,C,D)被分解为三个关系,其属性集分别为S1={A,D}, S2={A,C}, S3={B,C,D}

给定FD A→B,B→C,CD→A

由分解所得的三个关系属性集得到

A B C D
a b1 c1 d
a b2 c d2
a3 b c d

每个属性集对应一行,属性集中的属性没有脚标,不在属性集中的属性有脚标,脚标与行数相同

由FD A→B可得b1=b2

41

由FD B→C可得c=c1

42

由FD CD→A可得a=a3

43

可以看到包含一行没有脚标的属性,可以证明该分解为无损分解

  1. 第三范式

在某些情况下,把一个关系分解为一系列BCNF关系时,无法同时拥有无损连接和依赖保持两种性质。这个问题的解决方法是稍微放松BCNF的要求,以允许那些在分解为BCNF关系时不能保持函数依赖的特殊关系模式

这个放松的条件称为第三范式

关系R属于第三范式(Third Normal Form,缩写为3NF)

第三范式:对于每个非平凡FD,或者其左边是超键,或者右边仅由主属性构成,且这个主属性不在左边

主属性:某个键的成员

3NF模式综合算法:由关系R和其上成立的函数依赖集F得出由R分解出的关系集合,其中每个关系均属于3NF。分解具有无损连接和依赖保持性质

  • 找出F的一个最小基本集,记为G
  • 对于G中的每一个FD X→A,将XA作为分解出的某个关系的模式
  • 如果第2步分解出的关系的模式均不包含R的超键,则增加一个关系,其模式为R的任何一个键

Example:

考虑关系R(A,B,C,D,E),其上的FD有AB→C,C→B,A→D

3NF模式综合算法:

所给出的FD本身就是一个最小基本集

所以有分解{A,B,C}, {B,C}, {A,D},其中{B,C}可省略

由于得到的分解不包含超键,增加一个关系{A,B,E}或{A,C,E}即可

最终得到的分解为{A,B,C}, {A,D}, {A,B,E}


第一范式(First Normal Form)

  • 要求每个元组的各分量是原子值(即不能再被分解为更小的分量)
  • 关系R必须有一个键

44

第二范式(Second Normal Form):在第一范式的基础上要求不能有部分函数依赖

45

46


  1. 多值依赖-MVD

47

说明B的值与R中不在A和B中的值是独立的

多对一关系

每个FD都是一个MVD

Example

48

49

平凡多值依赖

50

  1. 第四范式(Forth Normal Form)

对于关系R中每个非平凡MVD,左边都是超键

第四范式要比BCNF范式条件更严格

  • 违反BCNF范式一定会违反4NF范式
  • 符合4NF范式也一定符合BCNF范式

51

分解为第四范式

  • 在R中找到一个4NF违例,记为A1, A2 … An →→ B1, B2 … Bm,其中{A1, A2 … An}不是超键。如果不存在,说明R自身就是一个合适的分解
  • 如果存在这样的违例,将R分解为两个模式
    • R1:A和B
    • R2:A和R中所有不属于A和B的其他属性(R中不属于B的所有属性)
  • 递归分解R1和R2

E/R模型实体/联系模型(Entity/Relationship Model)

  • 矩形表示实体集
  • 椭圆表示属性,其中带下划线的属性表示键
  • 菱形表示联系,联系用箭头指向一个实体集代表多对一关系
  • 三角形表示子类,不需要画双向箭头,表示一对一关系

设计E/R图时要保证

  • 忠实性
  • 避免冗余
  • 简单性

54

一个制片厂可以制作多部电影,所以电影为多,制片厂为一,电影通过Owns联系多对一指向制片厂

而一个影星可以出演多部电影,一个电影也可以由多部影星出演,所以Stars和Movies间不存在多对一关系

关系分为

  • 一对一

    55

    61

  • 多对多

    56

    62

  • 多对一

    57

    59

  • 一对多

    58

    60

子类

63

引用完整性

使用圆箭头()表示,可以理解为必须存在(have to exist)

64

度约束

65

弱实体集

一个实体集的键是由另一个实体集的部分或全部属性构成,这样的实体集叫做弱实体集

66

合同有一个属性salary,但它并不是键

合同的键由电影公司名字,参演影星名字,电影名字和出品日期组成

  1. 加强外键约束
  • Default:Reject the modification
  • Cascade:Make the same changes
    • Deleted executive:delete tuples
    • Updated executive:change values
  • Set NULL:Change the value to NULL

68

67

  1. Check

CHECK(…),括号中为True才可以进行后续操作

  1. Deffered

  2. 触发器Trigger

69

70

  1. 虚拟视图
1
2
3
4
5
6
7
8
9
10
11
-- 创建视图
CREATE VIEW myView
AS SELECT ...;
-- Rename
CREATE VIEW myView(newName1,newName2)
AS SELECT name1,name2
FROM ...;
-- 视图查询、更新
-- The same way as table
-- 删除视图
DROP VIEW myView;

对于有些视图是不可更新的

当FROM子句中包含两个关系时,且插入的值往往不够与原表中的属性一一对应,需要使用NULL值来填充,且表与表间设计大量主键外键的约束,SQL并不认为两个NULL值相等,所以更新不好处理,导致插入操作不成功,这样的视图为不可更新视图

当视图为可更新视图时,可以使用触发器来对原表进行更新

使用语句INSTEAD OF INSERT/UPDATE ON myView来代替插入或修改视图的操作

  1. 索引
1
2
3
4
5
6
-- 创建索引
-- 括号中可以有多个值
CREATE INDEX myIndex
ON tableName(attributeName);
-- 删除索引
DROP INDEX myIndex;

索引指向的是一个元组,并非ON后面的值

计算最佳索引

  1. 物化视图
1
2
-- 创建物化视图
CREATE MATERIALIZED VIEW myView AS ...;
Undo Log
  1. Undo日志规则

要让undo日志能使我们从系统故障中恢复,事务必须遵循两条规则。这些规则影响到缓冲区管理器能做什么,并且要求一旦事务提交就要执行某些动作。我们在此对这些规则进行概括。

  • U1:如果事务T改变了数据库元素X,那么形如<T,X,v>的日志记录必须在X的新值写到磁盘前写到磁盘(一般是在写入主存时将日志写入磁盘,即WRITE时)
  • U2:如果事务提交,则其COMMIT日志记录必须在事务改变的所有数据库元素已写到磁盘后再写到磁盘,但应尽快(在OUTPUT全部元素后,尽快将COMMIT写入磁盘)

简要概括两条规则,与事务相关的内容必须按照如下顺序写到磁盘:

  • 指明所改变数据库元素的日志记录
  • 改变的数据库元素自身
  • COMMIT日志记录

前两条的顺序是对各个数据库元素单独适用

73

Redo Log

Redo日志写入磁盘顺序:

  • 指出被修改元素的日志记录
  • COMMIT日志
  • 改变的数据库元素自身

74

Undo/Redo Log

undo/redo日志与其他日志类型有相同种类的日志记录,只有一个例外。当数据库元素修改其值时我们写入的更新日志记录有资格组成部分。记录<T,X,v,w>的含义是,事务T改变了数据库元素X的值,其改前值为v,新值为w。undo/redo日志系统必须遵循的约束可用如下规则概括:

UR1:在由于某个事物T所做的改变而修改磁盘上的数据库元素X前,更新记录<T,X,v,w>必须已写到磁盘上(通常在WRITE后更新日志)

undo/redo日志的规则UR1因而只实施undo日志和redo日志都有的约束。具体地说,<COMMIT T>日志记录可以在磁盘上任何数据库元素的修改之前或之后

75

Check Point

检查点

77

非静止检查点

  1. 写入日志记录<START CKPT(T1,T2,...,TK)>并刷新日志。其中T1,T2,…,Tk是所有活跃事务(即尚未提交和将其改写到磁盘的事务)的名字或标识符
  2. 等待T1,T2,…,Tk中的每一个提交或中止,但允许其他事务开始
  3. 当T1,T2,…,Tk都已完成时,写入日志记录<END CKPT>并刷新日志

76

  1. 查询语句中%代表任意长度的字符串(包括长度为0)

    ​ _代表一个字符

    用LIKE/NOT LIKE进行比较

  2. 对NULL进行算数操作结果是NULL

    与NULL比较结果为UNKNOWN

    16

    17

  3. ORDER BY默认升序

    DESC表示降序,ASC表示升序

    18

  4. NULL is ignored in any aggregation operators

    The result of aggregating NULL is NULL

  5. || 表示字符串拼接

    CONCAT函数也有相同的功能

  6. 范式间的关系

52

53

  1. SQL中的安全机制和用户认证

GRANT ...a ON ...b TO ...c:给c授权在b上的a权力

REVOKE ...a ON ...b FROM ...c:收回c在b上的a权力

71

72

  1. 增加约束
1
2
ALTER TABLE myTable ADD CONSTRAINT
PRIMARY KEY/FOREIGN KEY/CHECK ...;