本文最后更新于：2023年4月15日晚上

资料提供:

西电数据库两套往年题^[2]
数据库课后作业答案^[3]

1. 绪论

2. 关系代数和元组关系演算

关系代数的五个基本操作:

关系代数中可能会用到的各个符号:

3. SQL

SQL与三级模式体系结构图:

1 | 数据定义

SQL提供了专门的语言定义数据库对象, 称为DDL(数据定义语言, Data Definition Language)

创建数据库

CREATE DATABASE <name>

创建基本表

格式

CREATE TABLE <表名>
(
    <列名> <数据类型> <列级完整性约束条件>,
    <列名> <数据类型> <列级完整性约束条件>,
   	...
    <表级完整性约束条件>
);

实例

create table S
    (
    SNO int not null primary key, -- 列级完整性约束
    SNAME char(10) not null,
    SA int not null,
    FNAME char(15) not null,
    CNO int not null,	
    SD char(10),
    constraint fk_fname1 foreign key(FNAME) references F(FNAME), -- 表级完整性约束, 命名约束
    foreign key(CNO) references C(CNO) -- 直接指定约束 (少了constraint xxx)
    );

常用的完整性约束:

修改基本表

格式

ALTER TABLE <表名>
(
    ADD <新列名> <数据类型> <完整性约束>
    DROP <完整性约束名> <列名>
    ALTER <列名> <数据类型>
);

实例:

-- 增加属性
alter table student add senroll date;
-- 修改属性数据类型
alter table student alter sage smallint;
-- 删除约束
alter table student drop unique(name);

2 | 查询

语法:

where子句

-- 例子
where age between 20 and 23;  -- 找年龄20-23岁的

where dept not in ('is', 'ma', 'cs'); --查询不是is, ma, cs系得

where name like '刘%'; -- 找姓刘的

where name like '__阳'; -- 找名字叫xx阳的

where cname like 'DB\_Design' ESCAPE '\'; -- escape定义转义字符, 出现在其后的第一个字符不是通配符而是字符本身

注意点:

涉及空值的查询时is NULL不能用 = NULL替代

ORDER BY

对属性列排序

asc为升序
desc为降序
默认为升序

-- 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。		
SELECT  *
FROM  Student
ORDER  BY  Sdept , Sage  DESC ;

集函数

COUNT

SUM

AVG

MAX

MIN

例子:

1 2	`select count(distinct sno) from sc; -- 查选了课程的学生人数`

group by 和 having

group by <列名> having <条件>

按指定的一列或者多列分组, 值相等的为一组, having子句作用于各个组之上

如果未对查询结果分组, 集函数作用于整个查询结果
对查询结果分组后, 集函数分别作用于各个组
使用group by子句后, select子句的列名表中只能出现分组属性和集函数, 不能出现在group by中没有出现的属性

例子:

-- 查询各个课程号和相应的选课人数
select cno, count(sno)
from sc
group by cno;

-- 查询有3门以上课程是90分以上的学生学号及其(90分以上的)课程数。
select sno, count(*)
from sc
where grade >= 90
group by sno
having count(*) >= 3;

嵌套查询

分类:

相关子查询 : 子查询执行依赖于父查询条件
不相关子查询 : 子查询执行不依赖于父查询条件

例子:

SELECT  Sname		    -- 外层查询/父查询
  FROM  Student
  WHERE  Sno  IN
    	  ( SELECT  Sno -- 内层查询/子查询
                FROM  SC
                WHERE  Cno = 'c2' ) ;

带有any或者all谓词的子查询

例子: (为不相关子查询)

使用any/all谓词与集函数具有等价关系, 而用集函数查询通常比any/all查询效率高因为前者可以减少比较次数

带有EXISTS谓词的子查询

exists仅返回true/false, 故子查询通常只用select *

使用EXISTS子查询的效率要优于使用连接查询和IN查询

EXISTS通常引入的是相关子查询, 而IN更多的是不相关子查询

集合查询

集合操作命令

命令
UNION	并
INTERSECT	交
MINUS	差

3 | 数据更新

3-1 | 插入数据

插入单个结果

格式:

1
2
3

INSERT
INTO <表名> <属性列s>
VALUES <常量s>;

如果不指定属性列, 则values必须以完整的元组插入, 且属性与表中属性顺序一致

举例:

1 2	`insert into sc(sno, cno) values ('95001', '1');`

插入子查询结果

1
2
3

INSERT
INTO <表名> <属性列s>
子查询;

举例:

INSERT
INTO  Deptage ( Sdept, Avgage )
		SELECT  Sdept, AVG(Sage)  -- 属性个数要匹配
		FROM  Student
		GROUP  BY  Sdept ;

注意完整性约束

3-2 | 修改数据

格式:

1
2
3

UPDATE <表名>
SET <列名> = <表达式>
WHERE <条件>

举例:

1
2
3

update student
set sage = 22
where sno = '95001'

1
2
3

-- 将所有学生年龄增加一岁
update student
set sage = sage + 1

3-3 | 删除数据

格式:

1
2
3

DELETE 
FROM <表名>
WHERE <条件>;

删除表中满足where指定条件的元组

例子:

1
2
3

delete
from student
where sno = '95001';

1
2
3

-- 删除所有学生的选课记录
delete
from sc;

-- 删除CS系所有学生的选课记录
delete 
from sc
where sno in (
	select sno
    from student
    where student.sdept = 'CS'
);

删除时的参照完整性:

不允许被删除
级联删除 ( 通过CASCADE参数指定 )

4 | 视图

数据库系统的三级模式: 外模式, 模式, 内模式

数据库系统的两级映像: 外模式-模式映像模式-内模式映像

外模式-模式映像用途: 保证数据的逻辑独立性
模式-内模式映像用途: 保证数据的物理独立性

视图对应的就是三级模式/两级映像体系结构中的外模式和外模式/模式映像

4-1 | 建立视图

格式:

1
2
3

CREATE VIEW <视图名> <列名>
AS <子查询>
[WITH CHECK OPTION];

DBMS执行create view语句时只是把视图的定义存入数据字典, 并不执行其中的select语句, 在对视图查询时, 按视图的定义从基本表中将

数据查出

例子:

create view IS_STUDENT
as
select sno, sname, sage
from student
where sdept = 'IS';

若一个视图是从单个基本表导出的, 并且只去掉了基本表的某些行和某些列但保留了码, 则这类视图称为行列子集视图, 上例所建立的视图就是行列子集视图

DBMS实现视图查询的方法

实体化视图: 通过视图建立临时表, 查询后删除临时表
视图消解法: 根据视图定义将对视图的查询转换为对基本表的查询

4. 数据库安全-赋权

数据库的安全性控制通过授权机制来实现, 即通过赋予用户对数据库的使用权限来保证数据的安全

1 | 授权`GRANT`语句

将数据库中的某些对象的某些操作权限赋予某些用户

格式:

Grant <权限s>
ON	<对象类型> <对象名>
TO	<用户s>
[WITH GRANT OPTION]

DBA拥有数据库操作的所有权限并可将权限赋予其他用户
建立数据库对象的用户称为OWNER, 他拥有对该对象的所有操作权限
接收权限的用户可以是一个或者多个具体用户, 也可以是全体用户PUBLIC
WITH GRANT OPTION子句: 获得某种权限的用户还可以把这种权限再授予别的用户, 没有指定with grant option时, 获得某种权限的用户只能使用该权限, 不能传播该权限

所有权限一览表

举例

1
2
3

grant select
on table student
to user1;

把对sc表的全部权限授予全部用户

1
2
3

grant all priviliges
on table sc
to public;

把查询student表和修改学生学号的权限授予给用户3和用户4

1
2
3

grant update(sno), select
on table student
to user3, user4;

把对表sc的insert权限授予给user5用户, 并允许他再将此权限授予其他用户

grant insert
on table sc
to user5
with grant option;

把在数据库S_C中的建表权限授予用户8

1
2
3

grant createtab
on database S_C
to user8;

2 | 收回权限`REVOKE`语句

1
2
3

REVOKE <权限s>
ON <对象类型> <对象名>
FROM <用户s>

举例

1
2
3

revoke update(sno)
on table student
from user4;

1
2
3

revoke select
on table sc
from public;

收回权限时, 将收回自己所级联授予出的权限
如果存在多重授予, (从不同用户处得到的相同权限, 则仍然具有该权限), 只收回自己级联授予的权限

3 | 视图机制

5. 存储过程和触发器

1 | 存储过程

创建存储过程:

CREATE PROCEDURE

执行存储过程:

EXECUTE PROCEDURE

修改存储过程:

ALTER PROCEDURE

删除存储过程:

DROP PROCEDURE

2 | 触发器

功能: 强化约束, 跟踪变化, 级联运行, 存储过程的调用

分类: 前触发器(INSTEAD OF), 后触发器 (AFTER)

创建:

CREATE TRIGGER 
ON
FOR
AS

-- 例子
create trigger reminder
on titles
for insert, update
as sql_statements

在触发器的执行过程中，系统会自动建立和管理两个逻辑表：插入表(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}^+}$

$\mathrm{X}_{\mathrm{F}^+}=\left\{ \left. \mathrm{A} \right|\mathrm{X}\rightarrow \text{A能由F根据Armstrong公理导出} \right\}$

注: 即求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 | 两段锁协议

两段锁协议:

先统一加锁, 事务结束时统一释放锁

遵守两段锁协议也会发生死锁
遵守两段锁协议是可串行化调度的充分条件

脚注

↩
https://wws.lanzous.com/b01nwkgzi 密码:auqv ↩
https://wws.lanzous.com/iM3nSk4bira ↩

#数据库 #大三期末复习 #西电

基于「数据库系统概论」的数据库期末复习

https://blog.roccoshi.top/posts/59320/

作者

RoccoShi

发布于

2021年1月6日

许可协议