系统架构12 - 数据库基础（下）

最新推荐文章于 2024-07-13 18:10:19 发布

银龙丶裁决

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量1.2k

点赞数 25

分类专栏：软考系统架构文章标签：数据库 sql nosql数据库

本文链接：https://blog.csdn.net/Great_est/article/details/135622176

版权

软考系统架构专栏收录该内容

29 篇文章 2 订阅

订阅专栏

函数依赖

数据依赖是通过一个关系中属性间值的相等与否体现出来的数据间的相互关系，是现实世界属性间联系和约束的抽象，是数据内在的性质，是语义的体现。函数依赖则是一种最重要、最基本的数据依赖。

定义

给定一个X，能唯一确定一个Y，就称X决定(确定)Y，或者说Y依赖于X。
例如：Y=X*X函数，此时X能确定Y的值，但是Y无法确定X的值，比如x=2，y=4，但是y=4无法确定x=2。

扩展规则

函数依赖又可扩展以下两种规则：
部分函数依赖：A可决定C，(A，B)也可决定C，既然A都可以决定C了，那么要不要B其实都无所谓了，所有这种就称之为部分函数依赖。
传递函数依赖：当A和B不相同时，A可决定B，B可决定C，则A可决定C，是传递函数依赖；若A和B相同，则不存在传递了，A就可以直接就可决定C。

公理系统

函数依赖的公理系统是指一组用于推导和证明函数依赖的规则和公理集合。
这些公理系统基本上是数学理论的扩展，包括以下公理和规则：：

自反律：对于任意属性集合X和Y，有X ⊆ Y（X属于Y），则X $\rightarrow$ Y。
增广律：对于任意属性集合X、Y和Z，如果X $\rightarrow$ Y，那么XZ $\rightarrow$ YZ。
合并律：对于任意属性集合X、Y和Z，如果X $\rightarrow$ Y和X $\rightarrow$ Z，那么X $\rightarrow$ YZ。
分解律：对于任意属性集合X、Y和Z，如果X $\rightarrow$ YZ，则X $\rightarrow$ Y和X $\rightarrow$ Z。
合成律：对于任意属性集合X、Y和Z，如果X $\rightarrow$ Y且Y $\rightarrow$ Z，那么XZ $\rightarrow$ YZ。
传递律：对于任意属性集合X、Y和Z，如果X $\rightarrow$ Y且Y $\rightarrow$ Z，那么X $\rightarrow$ Z。

键与约束

超键：能唯一标识记录的属性集合(可能不止一个)
候选键：最小的超键，用于作为主键(通常只有一个)
主属性：除候选键外的具有代表意义的非重复且非衍生属性。
主键：任选一个候选键，即可作为主键。
外键：其他表中的主键。
实体完整性约束：即主键约束，主键值不能为空，也不能重复。
参照完整性约束：即外键约束，外键必须是其他表中已经存在的主键的值，或者为空。
用户自定义完整性约束：自定义表达式约束，如规定年龄的值必须在0到100之间。

关系数据库基本术语

(1)属性 (Attribute)：在现实世界中，要描述一个事物常常取若干特征来表示。这些特征称为属性。例如学生通过学号、姓名、性别、系别、年龄、籍贯等属性来描述。
(2)域 (Domain)：每个属性的取值范围对应一个值的集合，称为该属性的域。例如，学号的域是6位整型数；姓名的域是10位字符；性别的域为{男，女}等。一般在关系数据模型中，对域还加了一个限制，即所有的域都应是原子数据 (Atomic Data)。例如，整数、字符串是原子数据，而集合、记录、数组是非原子数据。关系数据模型的这种限制称为第一范式(First Normal Form,1NF) 条件。但也有些关系数据模型突破了1NF的限制。
(3)目或度 (Degree)：目或度指的是一个关系中属性的个数。
(4)候选码 (Candidate Key)：若关系中的某一属性或属性组的值能唯一的标识一个元组，则称该属性或属性组为候选码。
(5)主码 (Primary Key)：或称主键，若一个关系有多个候选码，则选定其中一个作为主码。
(6)主属性 (Prime Attribute)：包含在任何候选码中的属性称为主属性。不包含在任何候选码中的属性称为非主属性 (Non-Prime Attribute)。
(7)外码 (Foreign Key)：如果关系模式 R 中的属性或属性组不是该关系的码，但它是其他关系的码，那么该属性集对关系模式R 而言是外码。例如，客户与贷款之间的借贷联系 c-1(c-id,loan-no), 属性c-id是客户关系中的码，所以c-id 是外码；属性 loan-no是贷款关系中的码，所以loan-no 也是外码。
(8)全码 (All-key)：关系模型的所有属性组是这个关系模式的候选码，称为全码。例如，关系模式R(T,C,S), 属性 T 表示教师，属性C 表示课程，属性S 表示学生。假设一个教师可以讲授多门课程，某门课程可以由多个教师讲授，学生可以听不同教师讲授的不同课程，那么，要想区分关系中的每一个元组，这个关系模式R 的码应为全属性 T、C和 S, 即ALL-KEY。
(9)笛卡尔积。

范式

关系数据库设计的方法之一就是设计满足适当范式的模式。
通常可以通过判断分解后的模式达到几范式来评价模式规范化的程度。
范式有： 1NF、2NF、3NF、BCNF、4NF和 5NF, 其中1NF级别最低。
这几种范式之间1NF $\supseteq$ 2NF $\supseteq$ 3NF $\supseteq$ BCNF $\supseteq$ 4NF $\supseteq$ 5NF成立。
通过分解，可以将一个低一级范式的关系模式转换成若干个高一级范式的关系模式，这种过程叫作规范化。下面将给出1NF到 4NF的定义。

1NF

第一范式1NF：要求数据库表中的所有字段都是不可分割的原子值。
若关系模式 R 的每一个分量都是不可再分的数据项，则关系模式 R 属于第一范式。记为 R∈1NF。
通俗地说，第一范式就是表中不允许有小表的存在。
例如，供应者和它所提供的零件信息，关系模式 FIRST 和函数依赖集 F 如下：
FIRST(Sno,Sname,Status,City,Pno,Qty)
F={Sno → Sname,Sno → Status,Status → City,(Sno,Pno)→ Qty}
对具体的关系 FIRST 如表所示

Sno	Sname	Status	City	Pno	Qty
S1	精益	20	天津	P1	200
S1	精益	20	天津	P2	300
S1	精益	20	天津	P3	480
S2	盛锡	10	北京	P2	168
S2	盛锡	10	北京	P3	500
S3	东方红	30	北京	P1	300
S3	东方红	30	北京	P2	280
S4	泰达	40	上海	P2	460

从表中可以看出每一个分量都是不可再分的数据项，所以是1NF 的。但是，1NF存在4个问题：

冗余度大。例如每个供应者的 Sno、Sname、Status、City要与其供应的零件的种类一样多。
引起修改操作的不一致性。例如供应者S1 从“天津”搬到“上海”,若不注意，会使一些数据被修改，另一些数据未被修改，导致数据修改的不一致性。
插入异常。关系模式 FRIST 的主码为 Sno、Pno, 按照关系模式实体完整性规定主码不能取空值或部分取空值。这样，当某个供应者的某些信息未提供时(如 Pno), 则不能进行插入操作。
删除异常。若供应商 S4 的 P2 零件销售完了，并且以后不再销售 P2 零件，那么应删除该元组。这样，在基本关系 FIRST 找不到 S4 , 可 S4 又是客观存在的。

2NF

在 1NF 的基础上，要求数据库表中的每个非主属性完全依赖于候选键。
也就是说，一个表只描述一件事物。
若关系式 R $\in$ 1NF，且每一个非主属性完全依赖于码，则关系模式 R $\in$ 2NF。
换句话说，当 1NF消除了非主属性对码的部分函数依赖，则称为2NF。
例如： $F I RST$ 关系中的码是Sno、Pno, 而 Sno→Status, 因此非主属性 Status 部分函数依赖于码，故非 2NF 的。
若此时，将 $F I RST$ 关系分解为：
$FIRST_1$ (Sno,Sname,Status,City) $\in$ 2NF
$FIRST_2$ (Sno,Pno,Qty) $\in$ 2NF
因为分解后的关系模式 $FIRST_1$ 的码为 Sno, 非主属性 Sname、Status、City完全依赖于码
Sno, 所以属于2NF; 关系模式 $FIRST_2$ 的码为 Sno、Pno, 非主属性Qty完全依赖于码，所以也
属于2NF。

3NF

在 2NF 的基础上，要求数据库表中的每个非主属性不依赖于其它非主属性。
也就是说，数据表中的每一列都和主键直接相关，而不依赖于其它列。
若关系模式 R(U,F) 中不存在这样的码X, 属性组 Y及非主属性Z(Z $\not\subseteq$ Y)
使得X→Y，(Y $\not\rightarrow$ X) Y→Z成立，则关系模式R $\in$ 3NF。
即当 2NF消除了非主属性对码的传递函数依赖，则称为3NF。
例如， $FIRST_1\not\in$ 3NF，因为在分解后的关系模式 $FIRST_1$ 中有Sno $\rightarrow$ Status，Status $\rightarrow$ City，存在着非主属性City传递依赖于码 Sno。若此时将 $FIRST_1$ 继续分解为：
$FIRST_{11}$ (Sno, Sname, Status) $\in$ 3NF
$FIRST_{12}$ (Status, City) $\in$ 3NF
通过上述分解，数据库模式FIRST转换为 $FIRST_{11}$ (Sno, Sname, Status)， $FIRST_{12}$ (Status, City)， $FIRST_2$ (Sno,Pno,Qty)3个子模式。由于这3个子模式都达到了3NF, 因此称分解后的数据库模式达到了3NF。
可以证明，3NF 的模式必是 2NF 的模式。产生冗余和异常的两个重要原因是部分依赖和传递依赖。因为3NF 模式中不存在非主属性对码的部分函数依赖和传递函数依赖，所以具有较好的性能。对于非3NF 的1NF、2NF来说，其性能弱，一般不宜作为数据库模式，通常要将它们变换成为3NF或更高级别的范式，这种变换过程称为“关系模式的规范化处理”。

BCNF(Boyce Codd Normal Form, 巴克斯范式)

规范化数据库设计的一种方法，它对关系型数据库中的表进行分解，其符合第三范式（3NF），同时尽量避免数据冗余和不一致性，提高数据的可靠性和完整性。
关系模式 R $\in$ 1NF，若X $\rightarrow$ Y 且Y $\in$ X时，X必含有码，则关系式 R $\in$ BCNF。
也就是说，当3NF消除了主属性对码的部分函数依赖和传递函数依赖，则称为BCNF。
一个满足BCNF 的关系模式，应有如下性质：

所有非主属性对每一个码都是完全函数依赖；
所有非主属性对每一个不包含它的码，也是完全函数依赖；
没有任何属性完全函数依赖于非码的任何一组属性。

例如，设R(Pno,Pname,Mname) 的属性分别表示零件号、零件名和厂商名，如果约定，每种零件号只有一个零件名，但不同的零件号可以有相同的零件名；每种零件可以有多个厂商生产，但每家厂商生产的零件应有不同的零件名。这样我们可以得到如下一组函数依赖：

Pno → Pname, (Pname,Mname)→ Pno

由于该关系模式 R 中的候选码为 (Pname,Mname) 或 (Pno,Mname)，因而关系模式 R 的属性都是主属性，不存在非主属性对码的传递依赖，所以 R 是 3NF 的。但是，主属性 Pname 传递依赖于码 (Pname,Mname)，因此 R 不是BCNF 的。当一种零件由多个生产厂家生产时，零件名与零件号间的联系将多次重复，带来冗余和操作异常现象。若将R 分解成：

R1(Pno,Pname) 和 R2(Pno,Mname)

就可以解决上述问题，并且分解后的关系模式R1、R2都属于BCNF。

4NF

关系模式 R $\in$ 1NF，若对于 R 的每个非平凡多值依赖X $\rightarrow\rightarrow$ Y 且 Y $\neq$ X 时，X必然有码，则关系式R(U, F) $\in$ 4NF。
4NF是限制关系模式的属性间不允许有非平凡且非函数依赖的多值依赖。

注意

如果只考虑函数依赖，关系模式最高的规范化程度是BCNF；
如果考虑多值依赖，关系模式最高的规范化程度是4NF。

反规范化

规范化设计后，数据库设计者希望牺牲部分规范化来提高性能。
反规范化 (Denormalization) 是加速读操作性能(数据检索)的方法，一般用这种方法有选择地在数据结构标准化后添加特定的冗余数据实例。

特点

益处：降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，能够提高查询效率。
问题：数据的重复存储，浪费了磁盘空间；可能出现数据的完整性问题，为了保障数据的一致性，增加了数据维护的复杂性，会降低修改速度。

具体方式

增加冗余列：在多个表中保留相同的列，通过增加数据冗余减少或避免查询时的连接操作。
增加派生列：在表中增加可以由本表或其它表中数据计算生成的列，减少查询时的连接操作并避免计算或使用集合函数。
重新组表：如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。
水平分割表：根据一列或多列数据的值，把数据放到多个独立的表中，主要用于表数据规模很大、表中数据相对独立或数据需要存放到多个介质上时使用。
垂直分割表：对表进行分割，将主键与部分列放到一个表中，主键与其它列放到另一个表中，在查询时减少l/0次数。

模式分解

是关系数据库规范化设计中的一个重要概念。它指的是通过对关系模式进行拆分，来消除模式中的混合组合依赖，达到将模式分解为更小的模式的过程。一般分为以下两种：

是否保持函数依赖分解：对于关系模式R，有依赖集F，若对R进行分解，分解出来的多个关系模式，保持原来的依赖集不变，则为保持函数依赖的分解。另外，注意要消除掉冗余依赖(如传递依赖)。
有损无损分解：分解后的关系模式能够还原出原关系模式，就是无损分解，不能还原就是有损。

举例

是否保持函数依赖：设原关系模式R(A，B，C)，依赖集 F(A $\rightarrow$ B，B $\rightarrow$ C，A $\rightarrow$ C)，将其分解为两个关系模式R1 (A，B)和R2(B，C)，此时R1 中保持依赖 A $\rightarrow$ B，R2 保持依赖 B $\rightarrow$ C，说明分解后的 R1 和 R2 是保持函数依赖的分解，因为A $\rightarrow$ C这个函数依赖实际是一个冗余依赖，可以由前两个依赖传递得到，因此不需要管。

判定无损

当分解为两个关系模式，可以通过以下定理判断是否无损分解：

定理：如果R的分解为 p={R1,R2}，F为R所满足的函数依赖集合，分解 p 具有无损连接性的充分必要条件是 R1 $\cap$ R2 $\rightarrow$ (R1 -R2) 或者 R1 $\cap$ R2 $\rightarrow$ (R2-R1)。
当分解为三个及以上关系模式时，可以通过表格法求解，如下：

有关系模式:成绩(学号，姓名，课程号，课程名，分数)
函数依赖:学号一姓名，课程号一课程名，(学号，课程号)一分数
将其分解为:
成绩(学号，课程号，分数)
学生(学号，姓名)
课程(课程号，课程名)
是否为无损分解?

初始表：

	学号	姓名	课程号	课程名	分数
成绩	√	×	√	×	√
学生	√	√	×	×	×
课程	×	×	√	√	×

根据学号 $\rightarrow$ 姓名，对上表进行处理，将×改成符号√ ；然后考虑课程号 $\rightarrow$ 课程名，将×改为√，得下表：

	学号	姓名	课程号	课程名	分数
成绩	√	√	√	√	√
学生	√	√	×	×	×
课程	×	×	√	√	×

从上表中可以看出，第1行已全部为√，因此本次R分解是无损分解。

并发控制

事务

由一系列DML操作组成，这些操作，要么全做，要么全不做，它从第一个DML操作开始，rollback、commit 或者 DDL 结束，拥有以下四种特性：

(操作)原子性：要么全做，要么全不做，例如银行转账，在没到最后一步完成转账之前，前面的所
有操作都是无效的。
(数据)一致性：事务发生后数据是一致的，例如银行转账，不会存在A账户转出，但是B账户没收到
的情况。
(执行)隔离性：任一事务的更新操作直到其成功提交的整个过程对其他事务都是不可见的，不同事
务之间是隔离的，互不干涉。
(改变)持续性：事务操作的结果是持续性的。

事务是并发控制的前提条件，并发控制就是控制不同的事务并发执行，提高系统效率。但是并发控制中存在下面三个问题：

丢失更新：事务1 对数据A进行了修改并写回，事务2也对A进行了修改并写回，此时事务2写回的数据会覆盖事务1 写回的数据，就丢失了事务1 对A的更新。即对数据A的更新会被覆盖。
不可重复读：事务2读A，而后事务1 对数据A进行了修改并写回，此时若事务2再读A，发现数据不对。即一个事务重复读A两次，会发现数据A有误。
读脏数据：事务1 对数据A进行了修改后，事务2读数据A，而后事务1 回滚，数据A恢复了原来的值，那么事务2对数据A做的事是无效的，读到了脏数据。

封锁协议

X锁

X锁是排它锁(写锁)。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他事务都不能再对A加任何类型的锁，直到T释放A上的锁。

S锁

S锁是共享锁(读锁)。若事务T对数据对象A加上S锁，则只允许T读取A，但不能修改A，其他事务只能再对A加S锁(也只能读不能修改)，直到T释放A上的S锁。

三级封锁协议

一级封锁协议：事务在修改数据R之前必须先对其加X锁，直到事务结束才释放。可解决丢失更新问题。
二级封锁协议：一级封锁协议的基础上加上事务T在读数据R之前必须先对其加S锁，读完后即可释放S锁。可解决丢失更新、读脏数据问题。
三级封锁协议：一级封锁协议加上事务T在读取数据R之前先对其加S锁，直到事务结束才释放。可解决丢失更新、读脏数据、数据重复读问题。

SQL语言

SQL(Structured Query Language)：是关系数据库管理系统的标准查询语言，用于存取数据以及查询、更新和管理关系数据库。
SQL的主要功能包括：

数据定义语言(DDL)：用于定义数据库对象，如表、视图、索引等。包括CREATE、ALTER、DROP等语句。
数据操作语言(DML)：用于对数据库中表的数据进行增删改操作。包括INSERT、UPDATE、DELETE等语句。
数据查询语言(DQL)：用于查询数据库中表的记录。主要是SELECT语句及其子句。
事务控制语言(TCL)：用于管理数据库事务。包括COMMIT、ROLLBACK、SAVEPOINT等语句。
数据控制语言(DCL)：用于管理数据库的权限和安全性。主要是GRANT和REVOKE语句。

数据库查询（DQL）

排序order by。默认为升序，降序要加关键字DESC：select * from t1 order by sno
分页limit。limit startIndex,pageSize，startIndex代表从第几项开始，pageSize代表展示多少项数据，startIndex从0开始算：select * from t1 limit 0,10
分组查询group by，分组时要注意select后的列名要适应分组，having为分组查询附加条件：select sno，avg(score) from student group by sno having(avg(score)>60)。

常见的DML

1 . 更名运算as：select sno as “学号” from table
2. 字符串匹配：like，%匹配多个字符串，下划线匹配任意一个字符串，如：select * from t1 where sname like ‘a_’ ；
3. 数据库插入：insert into values()： insert into t1 values(‘a’，66)
4. 数据库删除：delete from…where： delete from t1 where sno=4
5. 数据库修改：update … set … where …： update t1 set sname=‘aa’ where sno=3