基于「数据库系统概论」的数据库期末复习
本文最后更新于:2023年4月15日 晚上
资料提供:
1. 绪论
2. 关系代数和元组关系演算
关系代数的五个基本操作:
关系代数中可能会用到的各个符号:
3. SQL
SQL与三级模式体系结构图:
1 | 数据定义
SQL提供了专门的语言定义数据库对象, 称为DDL(数据定义语言, Data Definition Language)
创建数据库
CREATE DATABASE <name>
创建基本表
格式
1 |
|
实例
1 |
|
常用的完整性约束:
修改基本表
格式
1 |
|
实例:
1 |
|
2 | 查询
语法:
where子句
1 |
|
注意点:
- 涉及空值的查询时
is NULL
不能用= NULL
替代
ORDER BY
对属性列排序
asc为升序
desc为降序
默认为升序
1 |
|
集函数
COUNT
SUM
AVG
MAX
MIN
例子:
1 |
|
group by 和 having
group by <列名> having <条件>
按指定的一列或者多列分组, 值相等的为一组, having子句作用于各个组之上
如果未对查询结果分组, 集函数作用于整个 查询结果
对查询结果分组后, 集函数分别作用于各个组
使用
group by
子句后,select
子句的列名表中只能出现分组属性和集函数, 不能出现在group by中没有出现的属性
例子:
1 |
|
嵌套查询
分类:
- 相关子查询 : 子查询执行依赖于父查询条件
- 不相关子查询 : 子查询执行不依赖于父查询条件
例子:
1 |
|
带有any或者all谓词的子查询
例子: (为不相关子查询)
使用any/all谓词与集函数具有等价关系, 而用集函数查询通常比any/all查询效率高因为前者可以减少比较次数
带有EXISTS谓词的子查询
exists仅返回true/false, 故子查询通常只用select *
使用EXISTS子查询的效率要优于使用连接查询和IN查询
EXISTS通常引入的是相关子查询, 而IN更多的是不相关子查询
集合查询
集合操作命令
命令 | |
---|---|
UNION | 并 |
INTERSECT | 交 |
MINUS | 差 |
3 | 数据更新
3-1 | 插入数据
插入单个结果
格式:
1 |
|
如果不指定属性列, 则values必须以完整的元组插入, 且属性与表中属性顺序一致
举例:
1 |
|
插入子查询结果
1 |
|
举例:
1 |
|
注意完整性约束
3-2 | 修改数据
格式:
1 |
|
举例:
1 |
|
1 |
|
3-3 | 删除数据
格式:
1 |
|
删除表中满足where指定条件的元组
例子:
1 |
|
1 |
|
1 |
|
删除时的参照完整性:
- 不允许被删除
- 级联删除 ( 通过CASCADE参数指定 )
4 | 视图
数据库系统的三级模式: 外模式, 模式, 内模式
数据库系统的两级映像: 外模式-模式映像 模式-内模式映像
外模式-模式映像用途: 保证数据的逻辑独立性
模式-内模式映像用途: 保证数据的物理独立性
视图对应的就是三级模式/两级映像体系结构中的外模式和外模式/模式映像
4-1 | 建立视图
格式:
1 |
|
DBMS执行create view语句时只是把视图的定义存入数据字典, 并不执行其中的select语句, 在对视图查询时, 按视图的定义从基本表中将
数据查出
例子:
1 |
|
若一个视图是从单个基本表导出的, 并且只去掉了基本表的某些行和某些列但保留了码, 则这类视图称为行列子集视图, 上例所建立的视图就是行列子集视图
DBMS实现视图查询的方法
- 实体化视图: 通过视图建立临时表, 查询后删除临时表
- 视图消解法: 根据视图定义将对视图的查询转换为对基本表的查询
4. 数据库安全-赋权
数据库的安全性控制通过授权机制来实现, 即通过赋予用户对数据库的使用权限来保证数据的安全
1 | 授权GRANT
语句
- 将数据库中的某些对象的某些操作权限赋予某些用户
格式:
1 |
|
- DBA拥有数据库操作的所有权限并可将权限赋予其他用户
- 建立数据库对象的用户称为OWNER, 他拥有对该对象的所有操作权限
- 接收权限的用户可以是一个或者多个具体用户, 也可以是全体用户PUBLIC
WITH GRANT OPTION
子句: 获得某种权限的用户还可以把这种权限再授予别的用户, 没有指定with grant option时, 获得某种权限的用户只能使用该权限, 不能传播该权限
所有权限一览表
举例
1 |
|
把对sc表的全部权限授予全部用户
1 |
|
把查询student表和修改学生学号的权限授予给用户3和用户4
1 |
|
把对表sc的insert权限授予给user5用户, 并允许他再将此权限授予其他用户
1 |
|
把在数据库S_C中的建表权限授予用户8
1 |
|
2 | 收回权限REVOKE
语句
1 |
|
举例
1 |
|
1 |
|
收回权限时, 将收回自己所级联授予出的权限
如果存在多重授予, (从不同用户处得到的相同权限, 则仍然具有该权限), 只收回自己级联授予的权限
3 | 视图机制
5. 存储过程和触发器
1 | 存储过程
创建存储过程:
CREATE PROCEDURE
执行存储过程:
EXECUTE PROCEDURE
修改存储过程:
ALTER PROCEDURE
删除存储过程:
DROP PROCEDURE
2 | 触发器
功能: 强化约束, 跟踪变化, 级联运行, 存储过程的调用
分类: 前触发器(INSTEAD OF
), 后触发器 (AFTER
)
创建:
1 |
|
在触发器的执行过程中,系统会自动建立和管理两个逻辑表:插入表(inserted)和删除表(deleted)。这两个表与触发器所对应的基本表有着完全相同的结构,但为只读表,驻留于内存之中,直到触发器执行完毕,系统会自动删除。这两个表是事务回滚的重要依据。
6. 数据库设计
1 | 数据库设计的流程
1-需求分析 | 2-概念结构设计 | 3-逻辑结构设计 | 4-物理结构设计 |
---|---|---|---|
ER图 | ER图->关系模型 |
2 | ER图
三种表示
表示方式 | |
---|---|
实体 | 矩形 |
属性 | 圆形 |
联系 | 菱形 |
三种不同的联系
- 一对一联系
- 一对多联系
- 多对多联系
3 | ER图向关系模型的转换
联系 | 转换 |
---|---|
1 : 1 | 两实体任意一端添加另一端的主键 |
1 : N | 在N端添加另一端的主键 |
N : M | 将联系转化为实体, 并在实体中加入联系两端实体的主键 |
1 : 1 : N - N : M : P | 同 1 : N - N : M (在N端添加另外两端主键 - 联系转化为实体添加三端实体主键) |
7. 关系数据理论
关系模式的简记: R<U, F>
其中U表示属性集, F表示数据依赖
1 | 关系模式的存储异常
不满足2NF, 3NF, BCNF范式的关系模式存在下列异常
- 数据冗余
- 更新异常
- 插入异常
- 删除异常
2 | 函数依赖
Functional Dependencies简写为FD
- 平凡函数依赖: ($\text{X}\rightarrow\text{Y}, \text{Y}\subseteq \text{X}$) 平凡函数依赖没什么用, 不研究
- 例如(sno, cno)->sno
非平凡函数依赖: ($\text{X}\rightarrow\text{Y}, \text{Y}\nsubseteq \text{X}$) 如果不特别声明, 我们总是讨论非平凡函数依赖
- 例如(sno, cno)->grade
完全函数依赖, 部分函数依赖
- 传递函数依赖: (A->B B->C), 则Z传递依赖于X, 注意Y$\nrightarrow $X如果Y$\rightarrow$X
则为直接依赖
3 | 范式
1NF
所有属性必须是原子的, 不允许表中套表
2NF
满足1NF且不存在属性对候选码的部分函数依赖
3NF
满足2NF且不存在属性对候选码的传递函数依赖
BCNF
对于R的每一个函数依赖X->Y, X必包含码
4 | Armstrong公理系统
六条推理规则
- 自反律
- 增广律
- 传递律
- 合并规则
- 分解规则
- 伪传递规则
函数依赖集的闭包
在关系模式R中为F所逻辑蕴含的函数依赖的全体叫做F的闭包, 记为$\text{F}^+$
注: $\text{F}^+$一般超级多, 求$\text{F}^+$属于NP完全问题
属性集的闭包
设F为属性集U上的一组函数依赖, $\mathrm{X}\subseteq \mathrm{U}$, X关于函数依赖集F的闭包为$\mathrm{X}_{\mathrm{F}^+}$
注: 即求X可以导出的所有属性集合
最小函数依赖集
即用最少的函数依赖表示全部属性之间的依赖关系, 记为$\mathrm{F}_{\min}$
最小函数依赖集的定义:
考点: 最小函数依赖集的求解算法
第一步: 对每个函数依赖作右部属性分离
第二步: 去掉左部的冗余属性
第三步: 去除多余的函数依赖
注意: 最小函数依赖集不是唯一的
例子
在第三步的时候可以采用「除本求包」的方法, 即除去正在考察的这个函数依赖, 看左部属性的闭包是否包含正在考察的函数依赖的右部属性, 如果包含则正在考察的这个函数依赖为多余的函数依赖, 例如上面的A->D, 除去A->D的这个函数依赖求A的闭包为{A, B, C, D, E}包含D, 则A->D为多余, 应该去除。
正则覆盖: 将求出的最小函数依赖集左部属性相同的函数依赖合并(例如A->B A->C合并为A->BC)
考点: 候选码求解算法
第一步: 根据函数依赖集F将R的所有属性分为L类, R类, LR类和N类属性[1], 令X为L, N类的集合, Y为LR类的集合
第二步: 如果$\mathrm{X}_{\mathrm{F}^+}$=U, 则X为R的唯一候选码, 结束, 否则到第三步
第三步: 逐一取Y中的单一属性A, 若$\left( \mathrm{XA} \right) _{\mathrm{F}^+}$=U, 则XA为候选码, 令Y = Y - {A}, 到第四步
第四步: 依次取Y中的两个, 三个…属性与X组成属性组XZ, 若XZ不包含已求得的候选码, 则求其关于F的闭包$\left( \mathrm{XZ} \right) _{\mathrm{F}^+}$
若$\left( \mathrm{XZ} \right) _{\mathrm{F}^+}$=U, 则XZ为候选码, 重复第四步直到所有Y中的属性取完为止, 算法结束
例子
5 | 模式分解
分解应该考虑的问题
- 分解不能丢失信息
- 分解应该保持函数依赖
- 分解需要保持无损连接 (可以通过自然连接还原)
考点: 模式的无损连接性判定算法
方法一
R的一个分解为R1, R2
若$\mathrm{U}_1\cap \mathrm{U}_2\rightarrow \mathrm{U}_1-\mathrm{U}_2\in \mathrm{F}^+$或者$\mathrm{U}_1\cap \mathrm{U}_2\rightarrow \mathrm{U}_2-\mathrm{U}_1\in \mathrm{F}^+$则分解R1, R2保持无损连接 (充分必要条件, 用于一分为二模式的无损连接判断)
方法二
构造表
将模式分解为BCNF并保持无损连接
8. 嵌入式SQL
1 | 主语言 SQLCA 主变量 游标
主语言: C++, JAVA
SQLCA: sql communication area (SQL通信区)
主变量: sql使用主语言中的变量
游标: 一段缓冲区, 用于存放sql的执行结果
数据库和源程序工作单元间的通信
- SQLCA向主语言传递SQL语句的执行信息(执行状态)
- 主语言通过主变量向SQL提供参数
- 主变量和游标将SQL语句查询数据库的结果交主语言处理
9. 事务
1 | 事务的ACID特性
- 原子性Atomicity: 事务要么全做, 要么全不做
- 一致性Consistency: 事务让数据库从一个一致性状态—>另一个一致性状态
- 隔离性Isolation: 事务的执行不能被其他事务干扰
- 持续性Durability: 事务提交后对数据库中数据的改变为永久性的
2 | 并发控制
几种并发冲突:
- 丢失修改 (写-写冲突)
- 不可重复读 (读-写冲突)
- 读“脏”数据 (写-读冲突)
3 | 封锁机制
X锁: 排他锁, 持有X锁的人能读写数据库
S锁: 共享锁, 持有S锁的人能读不能写数据库
仅能有一个事务拥有X锁, 可以有多个事务同时拥有S锁
一级封锁协议
修改数据之前先加X锁, 事务结束后释放
解决问题: 丢失修改
二级封锁协议
在一级封锁协议基础上增加在读取数据前必须对数据加S锁, 读完后即可释放
解决问题: 丢失修改和读“脏”数据
三级封锁协议
在一级封锁协议的基础上增加在读取数据前必须对数据加S锁, 在事务结束后才可释放
解决问题: 丢失修改, 不可重复读, 读“脏”数据
4 | 并发调度的可串行性
可串行化调度的定义:
定义: 多个并发事务的执行是正确的, 当且仅当其结果与按一定次序串行地执行这些事务时的结果相同
可串行性是并发事务正确调度的准则, 一个给定的并发调度当且仅当它是可串行化的才认为是正确的调度
冲突可串行化调度:
将并发调度保证冲突操作次序不变的情况下交换不冲突操作的次序得到另一个串行调度, 则称这个并发调度为冲突可串行化的调度, 如果一个调度是冲突可串行化的, 那么则一定是可串行化的调度。
注意: 冲突可串行化调度是可串行化调度的充分非必要条件
5 | 两段锁协议
两段锁协议:
先统一加锁, 事务结束时统一释放锁
遵守两段锁协议也会发生死锁
遵守两段锁协议是可串行化调度的充分条件