数据库-从删库到跑路

连载中

关于数据库的一些知识

第一部分 关系数据库

码

• 超码：一个或多个元素的集合，可以唯一标识一个元组
• 候选码：最小超码
• 主码：数据库设计者选中的一个候选码
• 外码：一个属性是存在于本关系，并且是另一个关系中的主码

关系运算

• 选择
• 投影
• 自然连接
• 笛卡尔积
• 并

sql

常用命令

create table
select * from … where …
insert
update
alter
delete

完整性约束

实体完整性

not null
unique
check

参照完整性

foreign key … references

用户自定义完整性

关键字

distinct 去重
all 不去重
natural join 自然连接
as 表更名
upper 大写
lower 小写
like 表达模式
order by … desc/asc 降序/升序排序 默认升序
union 并运算
intersect 交运算
except 差运算
is (not) null 是否空值
group by 分组聚集
having 针对分组的限定条件

视图：从关系中取出部分属性展示，视图并不预先计算存储，在使用时才通过执行查询并计算出来

索引：允许数据库系统高效地找到关系中那些在索引属性上取给定值的元组，而不用扫描所有元组

授权

授予权限

grant <权限列表>
on <关系名或视图名>
to <用户/角色列表>

回收权限

revoke <权限列表>
on <关系名或视图名>
from <用户/角色列表>

触发器

当数据库作修改时，自动被系统执行的一条语句

第二部分 数据库设计

实体-联系模型

实体：现实世界中可区别于所有其他对象的一个事物
实体集：实体的集合
联系：多个实体间的相互关系
联系集：联系的集合

实体集和联系集对应数据库表

属性

简单属性：不能被划分
复合属性：可以再划分
单值属性：只有一个值，如id
多值属性：可以有多个值，如电话号码
派生属性：可以从其他属性中派生出来，如生日和年龄

约束

映射基数

一对一
一对多
多对一
多对多

参与约束

全部：实体集E中的每个实体都参与到集合R的至少一个联系中
部分：

码

同关系模型

实体-联系图

分成两部分的矩形：实体集，第一部分为实体集名，第二部分为所有属性
菱形：联系集
未分割的矩形：联系集的属性
线段：将实体集连接到联系集
虚线：将联系集属性连接到联系集
双线：实体在联系集的参与度
双菱形：连接到弱实体集的标志性联系集

一对一：双向箭头
一对多：箭头指向”一“那边
多对多：无箭头

弱实体集

没有足够的属性形成主码的实体集，每个弱实体集必须与称为标识（或属主实体集）的实体集关联

转换为关系模式

简单属性 强实体集

直接转换

复杂属性 强实体集

拆分复杂属性

弱实体集

其所依赖的强实体集的主码与弱实体集的分辨符组合而成的主码，建立外码约束

联系集

多对多：参与实体集的主码属性的并集为主码
一对一：任意实体集的主码选为主码
多对一/一对多：联系集中”多“的那一方的实体集的主码构成主码

范式

函数依赖：某个属性集决定另一个属性集时，称另一属性集依赖于该属性集

无损分解：对关系模式进行分解时，原关系模型下任一合法的关系值在分解后应能通过自然连接恢复，反之则是有损分解

原子域：该域的元素被认为是不可分的单元

希腊字母表示属性集，例如α
用小写的罗马字母后面跟着一个用一对圆括号括住的大写字母表示关系模式，例如r(R)，其中r是关系名称，R是属性集
当属性集是一个超码时，用K表示

合法实例：一个关系的满足所有这种现实约束的实例，称为关系的合法实例

超码的严格定义：令r(R)是一个关系模式，R的子集K是r(R)的超码的条件是：在关系r(R)的任意合法实例中，对于r的实例中的所有元组对t1和t2总满足，若t1≠t2，则t1[K]≠t2[K]

函数依赖的严格定义：给定r(R)的一个实例，对实例中所有的元组对t1和t2，若t1[α]=t2[α]，t1[β]=t2[β]

如果在r(R)中的每一个合法实例都满足函数依赖α→β，则该函数依赖在r(R)上成立

用函数依赖定义超码：如果函数依赖K→R在r(R)上成立，则K是r(R)的一个超码

平凡依赖：在所有关系中都满足的函数依赖

一般的，如果β属于α，则形如α→β的函数依赖是平凡的

闭包：能够从给定函数依赖集F推导出所有函数依赖的集合

范式的通俗介绍

第一范式：关系中所有属性的域是原子的

第二范式：若关系模式R∈1NF，并且每一个非主属性都完全函数依赖于R的码，则R∈2NF [ 消除部分子函数依赖 ]
简单的说，是表中的属性必须完全依赖于全部主键，而不是部分主键。所以只有一个主键的表如果符合第一范式，那一定是第二范式。这样做的目的是进一步减少插入异常和更新异常。

第三范式：满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
举例3:
　　每一个员工都有一个所属部门，假如有一个员工信息表Employee(emp_id,emp_name,emp_age,dept_id,dept_name,dept_info)。这张员工信息表的属性是emp_id，因为这个属性能够唯一确定其他所有属性，比如知道员工编号emp_id以后，肯定能够知道员工姓名，所属部门编号，部门名称和部门介绍。所以这里dept_id不是主属性，而是非主属性。但是，我们又可以发现dept_name,dept_info这两个属性也可以由dept_id这个非主属性决定，即dept_name依赖dept_id，而dept_id依赖emp_id，这样就存在了传递依赖。而且我们可以看出传递依赖的一个明显缺点就是数据冗余非常严重。

BC范式：设关系模式R<U，F>∈1NF，如果对于R的每个函数依赖X→Y，若Y不属于X，则X必含有候选码，那么R∈BCNF。
　　解释一下：对于关系模式R，若 R中的所有非平凡的、完全的函数依赖的决定因素是码，则R属于BCNF。
　　若R∈BCNF
　　每一个决定属性集（因素）都包含（候选）码
　　R中的所有属性（主，非主属性）都完全函数依赖于码
　　R∈3NF（证明）
　　若R∈3NF 则 R不一定∈BCNF
　　在关系模式STJ（S，T，J）中，S表示学生，T表示教师，J表示课程。
　　每一教师只教一门课。每门课由一名教师教，某一学生选定某门课，就确定了一个固定的教师。某个学生选修某个教师的课就确定了所选课的名称 ： (S，J)→T，(S，T)→J，T→J
　　由关系模式的定义可以得到如下结论，若R属于BCNF，则R有：
　　1.所有非主属性对每一个码都是完全函数依赖。
　　2.所有的主属性对每一个不包含它的码,也是完全函数依赖。
　　3.没有任何属性完全函数依赖于非码的任何一组属性。
　　由于R∈BCNF,按定义排除了任何属性对码的传递依赖与部分依赖,所以R∈3NF。但是若R∈3NF,则R未必属于BCNF。
　　
　　
1.第一范式：数据库的字段是单一属性，不可再分。
解释：
不能是复合属性，如果存在，应该拆分为多个属性
不能是多值属性，如果存在，应该建立一个实体，而让此属性与其存在1对多的关系)
不能是重复属性
2.第二范式：任何非关键字段不能部分依赖任一侯选关键字（即必须完全依赖）。
解释：
表中必须存在侯选关键字，即每一行不同于其他任一行，是惟一区分的
任何非关键字段不能依赖于侯选关键字的一部分
3.第三范式：任何非关键字段不能传递依赖任一侯选关键字
解释：
非关键字字段必须直接依赖任一侯选关键字
非关键字段C不能依赖非侯选关键字B，因为样会形成传递依赖：侯选关键字A=>B=>C，因为这时的B往往是外键，即其他表的主键，也就是说表中不能含有其他表的非主属性
4.BC范式：任何字段都不能传递依赖任一侯选关键字
解释：
与第三范式相比，一个是“任何非关键字段不能”，一个是“任何字段不能”，显然更严格了
侯选关键字或其部分字段不能传递依赖其他的侯选关关键字
注释：
侯选关键字：又叫侯选码，惟一标识一行数据，其真子集不能是侯选关键字，一个表可以存在多个侯选关键字，如用户表的username，userid
主关键字：又叫主键，主码，被选中的用来区分其它行的侯选关键字，一个表只有一个主关键字
部分依赖：(A,B)->C,D,如A->C,则C部分依赖A
传递依赖：A->B->C,则C传递依赖A
注意点：
数据库连接会带来一部分的性能损失
并不是数据库范式越高越好
有时会在数据冗余与范式之间做出权衡，在实际的数据库开发过程中，往往会允许一部分的数据冗余来减少数据库连接。

范式的严格定义

3NF：

对F+中所有形如α→β的函数依赖（其中α、β均属于R），下面至少有一项成立

• α→β是平凡依赖
• α是模式R的一个超码
• β - α中每一个属性A都包含于R的一个候选码中

BCNF

对F+中所有形如α→β的函数依赖（其中α、β均属于R），下面至少有一项成立

• α→β是平凡依赖
• α是模式R的一个超码

函数依赖理论

逻辑蕴含：给定关系模式r(R)，如果r(R)的每一个满足F的实例也满足f，则R上的函数依赖f被r上的函数依赖集F 逻辑蕴含

函数依赖集的闭包：F的闭包是被F逻辑蕴含的所有依赖的合集，记作F+

Armstrong公理：

• 自反律：若α为一属性集且β属于α，则α→β成立
• 增补律：若α→β成立且γ为一属性集，则γα→γβ成立
• 传递律：若α→β和β→γ成立，则α→γ成立

其他规则：

• 合并律：若α→β和α→γ成立，则α→βγ成立
• 分解律：若α→βγ成立，则α→β和α→γ成立
• 伪传递律：若α→β和βγ→δ成立，则αγ→δ

函数确定：如果α→B，则属性B被α函数确定

属性集的闭包：函数依赖集F下被α函数确定的所有属性的集合

无关属性：如果去除函数依赖中的一个属性不改变该属性依赖集的闭包

正则覆盖：F的正则覆盖Fc是一个依赖集，使得F逻辑蕴含Fc中所有依赖，并且Fc逻辑蕴含F中所有的依赖，并满足如下性质

• Fc中任何函数依赖都不含无关属性
• Fc中函数依赖的左半部都是唯一的

无损分解：令R、R1、R2和F，R1和R2是R的分解，如果以下函数依赖中至少有一个属性属于F+

• R1∩R2→R1
• R1∩R2→R2

即R1∩R2是R1或R2的超码

保持依赖：

分解算法

BCNF

（1）设R为不属于BCNF的一个模式。则存在至少一个非平凡的函数依赖α→β，其中α不是R的超码。则可与用以下两个模式取代R

• (α ∪ β)
• (R - (β - α)

关系数据库设计基础(函数依赖、无损连接性、保持函数依赖、范式、……)