数据库
…
数据库设计概要
- 分析需求
- 概要设计 E-R 图：屏蔽物理外键
例如个人博客：
- 用户表：写博客，创建分类
- 用户角色表
- 博客分类表：文章分类，谁创建的
- 博客文章表：文章具体信息
- 友情连接表
- 自定义表：系统信息，某关键字，主题
数据表的类型：
- innodb：默认，支持事务，数据行锁定，有外键，无全文索引，占用空间大，安全
- MyIASM：早年使用，不支持事务，表锁，无外键，有全文索引，占用空间小，速度块
事务：
- ACRD原则：原子性（全部成功、全部失败），一致性（最终一致性，过程一致性），持久性（要么恢复原状，要么持久化到数据库），隔离性（多用户时排他性）
- 脏读：一个事务读取了另一个事务没提交的数据
- 不可重复读：多次读取的结果不一致
- 幻读：多读了数据，行影响
索引：
- 主键索引
- 唯一索引 unique key - 值必须唯一
- 普通索引 key / index
- 全文索引 fulltext
- 不要对经常变动的数据加索引
- 数据少不需要索引
数据库备份：
- 直接复制文件
- SqlYog 可视化工具
- mysql dump 命令
关联查询的表不得超过3张。
数据库设计
常用数据库介绍
数据库分类
数据库分为关系型数据库和非关系型数据库。
关系型数据库如MySQL，Oracle，SQL Server，SQLite等，是基于关系表的存储结构存储数据。而非关系型数据库如Redis，MongoDB等采用键值对、图等各种方式存储数据。这两类没有优劣之分，只有适用场景之分。
在关系型数据库中，一个关系（表）代表一个对象，每个关系都有多个属性（字段）。关系与关系之间也有着一对一、一对多、多对多的关联，例如老师与课程是一对多的关联，课程与学生是多对多的关联等。
关系型数据库的属性
属性也分为超键，主键，候选键，外键以及普通字段。
假设有如下两个表：
超键：在表中能唯一标识记录的属性集称为超键。学生表中含有学号或者身份证号的任意组合都为此表的超键。如（学号）（学号，姓名）（身份证号，性别）等。
候选键：不含有多余属性的超键称为候选键。也就是候选键属于超键，它是最小的超键，就是说如果再去掉候选键中的任何一个属性它就不再是超键了。学生表中的候选键为（学号）（身份证号）。
主键：用户选作元组标识的一个候选键程序主键。主键就是候选键里面的一个，是人为规定的，例如学生表中，我们通常会让“学号”做主键，教师表中让“教师编号”做主键。
外键：如果关系模式R1中的某属性集不是R1的主键，而是另一个关系R2的主键则该属性集是关系模式R1的外键。学生表中的外键就是“教师编号”。外键主要是用来描述两个表的关系。
关系型数据库的属性依赖
字段直接含有依赖关系。一般分为三种依赖关系：部分依赖，完全依赖，传递依赖。
部分依赖：设X，Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分依赖于X。例如：通过AB能得出C，通过A也能得出C，通过B也能得出C，那么说C部分依赖于AB。
完全依赖：设X，Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全依赖于X。例如：通过AB能得出C，但是AB单独得不出C，那么说C完全依赖于AB.
传递依赖：设X，Y，Z是关系R中互不相同的属性集合，存在X→Y，Y→Z，，Y !→X，Z !→Y则称Z传递依赖于X。例如：通过A得到B，通过B得到C，但是C得不到B，B得不到A，那么成C传递依赖于A。
关系型数据库的设计范式
设计数据库也要讲究原则，我们把这些原则叫做范式。一般情况下，我们设计数据库只需满足五大范式中的前三个范式。五大范式有：
第一范式 1NF：强调的是列的原子性，即列不能够再分成其他几列。
每一列只代表一个属性，不能是多个属性的合并。例如，姓名和性别不能存储到一列中，而是应该存储在两列中。
第二范式 2NF：所有的非主属性都完全依赖于关键字。第二范式不存在非主属性对于部分候选关键字的部分依赖，不过允许非主属性之间存在着传递依赖。
下面是第二范式的优化实例：
假定选课关系表为：
关键字为组合关键字：
因为存在如下决定关系：
这个数据库表不满足第二范式，因为存在如下决定关系：
即存在组合关键字中的字段决定非关键字的情况。
由于不符合2NF，这个选课关系表会存在如下问题：
(1) 数据冗余：
同一门课程由n个学生选修，”学分”就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
(2) 更新异常：
若调整了某门课程的学分，数据表中所有行的”学分”值都要更新，否则会出现同一门课程学分不同的情况。
(3) 插入异常：
假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有”学号”关键字，课程名称和学分也无法记录入数据库。
(4) 删除异常：
假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。
因此，把选课关系表SelectCourse改为如下三个表：
这样的数据库表是符合第二范式的，消除了数据冗余、更新异常、插入异常和删除异常。
另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。
第三范式 3NF：每一个非主属性既不部分依赖于也不传递依赖于关键字，也就是在第二范式的基础上消除传递依赖（A＞B＞C）。
假定学生关系表为：
关键字为单一关键字：
因为存在如下决定关系：
这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：
即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递依赖。
它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。
把学生关系表分为如下两个表：
这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。
BCNF：在第三范式的基础上进一步消除主属性对于码的部分依赖和传递依赖。BCNF需要符合3NF，并且，主属性不依赖于主属性。
假设仓库管理关系表为
且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：
所以，(仓库ID,存储物品ID)和(管理员ID,存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：
即存在关键字段决定关键字段的情况，所以其不符合BCNF范式。它会出现如下异常情况：
(1) 删除异常：
当仓库被清空后，所有”存储物品ID”和”数量”信息被删除的同时，”仓库ID”和”管理员ID”信息也被删除了。
(2) 插入异常：
当仓库没有存储任何物品时，无法给仓库分配管理员。
(3) 更新异常：
如果仓库换了管理员，则表中所有行的管理员ID都要修改。
因此，把仓库管理关系表分解为二个关系表：
这样的数据库表是符合BCNF范式的，消除了删除异常、插入异常和更新异常。
但是也有例外。又如，有这样一个配件管理表：
有以下约束要求：
（1）一个仓库有多名职工；
（2）一个职工仅在一个仓库工作；
（3）每个仓库里一种型号的配件由专人负责，但一个人可以管理几种配件；
（4）同一种型号的配件可以分放在几个仓库中。
分析表中的依赖关系，可以得到：
（1）职工号 -> 仓库号;
（2）（仓库号，配件号）-> 数量
（3）（仓库号，配件号）-> 职工号
（4）（职工号，配件号）-> 数量
可以看到，候选键有：（职工号，配件号）(仓库号，配件号)。所以，职工号，配件号，仓库号均为主属性，数量为非主属性。显然，非主属性是直接依赖于候选键的。所以此表满足第三范式。
而我们观察一下主属性：（仓库号，配件号）-> 职工号；职工号 -> 仓库号。显然仓库号对于候选键（仓库号，配件号）存在传递依赖，所以不符合BCNF.
解决这个问题的办法是分拆为两个表
但这样做会导致依赖（仓库号，配件号）-> 职工号丢失。
虽然，不满足BCNF，也会导致一些冗余和一致性的问题。但是，将表分解成满足BCNF的表又可能丢失一些依赖。所以，一般情况下不会强制要求关系表要满足BCNF。
第四范式 4NF：当一个表中的非主属性互相独立时（3NF），这些非主属性不应该有多值。若有多值就违反了第四范式。
有这样一个用户联系方式表
CUSTOMERID为用户ID，PHONE为用户的固定电话，CELL为用户的移动电话。
本来，这是一个非常简单的第3范式表。主键为CUSTOMERID，不存在传递依赖。但在某些情况下，这样的表还是不合理的。比如说，用户有两个固定电话，两个移动电话。这时，表的具体表示如下：
| CUSTOMERID | PHONE | CELL |
|---|---|---|
| 1000 | 88281234 | 149088888888 |
| 1000 | 88381234 | 149099999999 |
由于PHONE和CELL是互相独立的，而有些用户又有两个和多个值。这时此表就违反第四范式。
在这种情况下，此表的设计就会带来很多维护上的麻烦。例如，如果用户放弃第一行的固定电话和第二行的移动电话，那么这两行会合并吗？等等
解决问题的方法为，设计一个新表
这样就可以对每个用户处理不同类型的多个电话号码，而不会违反第四范式。
显然，第四范式的应用范围比较小，因为只有在某些特殊情况下，要考虑将表规范到第四范式。所以在实际应用中，一般不要求表满足第四范式。
第五范式 5NF：是最终范式。消除了4NF中的连接依赖。
第五范式有以下要求：
（1）必须满足第四范式
（2）表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键。
第五范式是在第四范式的基础上做的进一步规范化。第四范式处理的是相互独立的多值情况，而第五范式则处理相互依赖的多值情况。
有一个销售信息表
SALEPERSON代表销售人员，VENDOR代表供和商，PRODUCT则代表产品。
在某些情况下，这个表中会产生一些冗余。可以将表分解为
参考 1：超键，主键，候选键，外键
参考 2：五大范式
参考 3：依赖关系