mysql设计
mysql设计
数据库设计,会经常使用到实体-关系图(E-R图).
基本概念
实体(entity)
- 矩形框：表示实体，在框中记入实体名.
属性(attribute)
- 椭圆形框：表示实体或联系的属性，将属性名记入框中。
联系(relation)
- 菱形框：表示联系，在框中记入联系名。
基数约束(cardinality constraint)
- 用来表示实体可以有多少实例与另一实体的实例存在联系。
- 基数约束共有四种形态：
此为形态一，即强制多个对应，表示一个实体A对应多个实体B。
此为形态二，即可选多个对应，表示一个实体A对应0个或多个实体B。
此为形态三，即强制单个对应，表示一个实体A对应一个实体B。
此为形态四，即可选单个对应，表示一个实体A对应0个或1个实体B。
我们知道联系是双向的，所以实际ER建模中常见的版本长这样：
理解这个联系的方法是从两个方向进行解读，“实体A对应0个或1个实体B，实体B对应一个或多个实体A”。
复合属性(composite attribute)
部分属性具有复合的特点，比如地址属性。地址可能包含了省份，城市，街道等子属性。
ER图上这类属性的属性名应当标记圆括号，然后扩展为多个子属性。可参考下面这个商店实体定义：
多值属性(multivalued attribute)
部分属性具有多值的特点，比如一个职工可能有多个电话号码。
ER图上这类属性用双层椭圆标识，可参考下面这个职工实体定义：
派生属性(derives attribute)
部分属性可从其他属性或者其他数据(如当前日期)派生出来，这类属性在ER图上用虚线椭圆标识。
可参考下面这个士多店实体定义：
上图中士多店的YearsInBusiness属性表示店铺开张了多少年，这个属性可以结合当前日期与OpeningDate属性算到，因此用虚线椭圆标识。
可选属性(optional attribute)
部分属性可能有也可能没有取值，比如说职工奖金。
ER图上这类属性通过在属性名后面添加(0)标识，可参考下面这个职工实体定义：
联系的进一步描述
a. 可以在联系中表明联系中的最大最小基数，如下图所示：
在上面这个例子中，每个学生具体对应到了2-6间教室；同时每间教室对应到了5-40名学生。
b. 也可以在联系中说明联系中的角色。这在一元联系中尤为常见，如下图所示：
每个人只能送给其他人一份礼物，但可以收到0或多份礼物。
关联实体(associated entity)
关联实体示用于描述M：N联系的一个替代方式，用一个内部有菱形的矩形表示，它没有唯一属性也没有部分唯一属性，且通常来说没有任何属性。
如下两个图可以说是等价的：
关联实体基本都是在多元联系的场景下用到，后面的高级话题部分会讲。
弱实体(week entity)
通常来说，实体至少要有一个唯一属性。因为这样才能精确定位到需要处理的记录。但在ER建模这一层，也并非总是如此。
举例来说，假如现在需要为某个连锁酒店管理系统进行ER建模。该公司在全国各地都开有酒店。现在需要记录下各地酒店的房间使用情况。
可以将房间使用相关信息作为酒店的建模一个多值复合属性，如下图所示：
这样做算是对的，但是并没有体现出部分码地位，也就是说各RoomID在各Building的唯一性。同时，很多时候需要将房间实体化与其他实体相联系。 比如每个房间对应的清洁工。
引入弱实体机制后，便可顺利解决这两个问题。如下图所示：
两个地方要注意一下，一是弱实体的“主码”称为部分码，码名下方用虚线标记；
再一个就是弱实体必须至少有一个属主实体，它们之间的联系需用双框菱形标识。弱实体部分码同其属主实体候选码的组合可以唯一定位到任何一个弱实体记录。
相同实体之间具有多个M:N关系
某人为一个学生选课系统进行ER建模，得到如下结果：
假如需求中有说明：一个同学一门课只能选一次，那这样的设计没问题。可是如果需求中说明，一个同学可以选一门课几次(可能是挂了好几次)， 这样的设计就有问题了。
对此，正确的做法之一是使用有两个属主实体的弱实体：
或者为每次预定生成一个唯一的id，如下图所示：
三元(或更多)关系
在ER图中，联系一般是将两个实体关联起来，又或者自己关联自己。但是也有些时候，需求方需要同时将多个实体联系起来。这怎么办呢？ 要知道表示联系的菱形有且只有两个接口。
答曰：使用关联实体。下面这个ER图中，使用了关联实体描述了某工厂的供货商，生产产品，零件三方联系：
但如果现在需求又变更了，需要给关联增加某些属性，比如说供货商每次提供的货物量，这个ER图就不能用了。
因为这样就没办法区分同一家供应商为同一产品提供等数量的同一零件的不同实例了。解决的办法是把关联实体改成一般的实体，并增设一个唯一标识符：
ER模型到关系表的映射
将常规实体映射为关系
对常规实体来说，每个常规属性对应到关系表中的一列，而某单值且唯一的列则映射为主码，标记下划线。
如下实体：
将映射为关系：
将具有复合属性的实体映射为关系
这类映射中，复合属性的各子属性会映射到的新的关系中，但是复合属性名本身不会。
如下实体：
将映射为关系：
虽然关系中没有出现符合属性名了，但数据库上层的前端应用可能会利用到复合属性名。也就是ER图在各个阶段都有可能用到，不是说映射为关系后就没啥事了。
将具有唯一复合属性的实体映射为关系
这类映射中，将会形成一个复合主码，其成员为复合属性的各子属性。
如下实体：
将映射为关系：
将具有可选属性的实体映射为关系
这类映射中，需要将可选属性对应的列标记一个(O)。
如下实体：
将映射为关系：
一对多(1：M)联系的映射
这类映射的规则为：在由1：M联系中属于M侧的实体所映射得到的关系中设置一个外码，这个外码对应于由1侧的实体映射得到的关系中的主码。
如下ER模型：
将映射为关系：
注意，外码命名不一定要和它对应的主码一致，应根据实际情况决定。
多对多(M：N)联系映射
这类映射的规则为：除了具有多对多联系的两个实体之外，联系本身也需要映射为关系。联系对应的关系中将有两个外码，分别对应两个实体的主码， 同时这两个外码构成新关系的主码。
比如下面这个ER模型：
将映射为关系：
一对一(1：1)联系的映射
这类映射和1：M的很相似。原则上外键设在任何一个实体的关系中都OK，但如果一对一联系中的基数约束是强制单个和可选单个这种类型， 则最好将外键设置在可选多的一侧。因为这样可以保证关系中不会出现太多空值。
比如下面这个ER模型：
将映射为关系：
将具有若干候选码的实体映射为关系
这类映射中，主码依然标记划线，而非主码唯一属性则标记(U)。
如下实体：
将映射为关系：
将具有多值属性的实体映射为关系
这类映射中，需要为多值属性创建一个新的关系。新的关系中包含一个外码，对应到主实体的主码。同时属性值和外码构成新的关系的复合主码。
如下实体：
将映射为关系：
将具有派生属性的实体映射为关系
派生属性不需要做什么特别处理，那是前端的事情，哈哈。
一对多(1：M)一元联系的映射
这类映射的规则为：实体映射得到的关系中包含一个外码，对应到关系自身的主码。
如下ER模型：
将映射为关系：
需要注意的是，该映射中外键名和主键名是不同的，以区分它和主码。事实上关系中也不允许出现名称相同的两列。
多对多(M：N)一元联系的映射
这类映射的规则为：除了实体本身需要映射为关系之外，多对多联系需要映射为另一个关系。新的关系中将有两个外码，它们均对应到实体主码。 且这两个外码又组合为新关系的复合主码。
如下ER模型：
将映射为关系：
这里同样要注意外键名要避免和主键名重复。
将弱实体映射为关系
弱实体映射和常规一对多联系映射一样需要在弱实体(M侧实体)中建立一个对应到属主实体(1侧实体)的外码。 然而区别是弱实体中的主码是弱实体自身的部分码+外码构成的复合主码，而后者的主码仅是M侧实体自己的主码。
如下ER模型：
将映射为关系：
当然，如果联系是一对一，则弱实体的主码就是那个对应到其属主实体的外码而没有部分码了。
如下ER模型：
将映射为关系：
三元联系的映射
这类映射和多对多联系的映射比较相似。如下ER模型：
可映射为：
这里提示下，三元联系的情况，联系肯定是多对多对多的。因为如果这三元中有一个为一，那么三元联系就应转成两个二元的一对多联系。
为什么要进行数据库设计
| 优良的设计 | 糟糕的设计 |
|---|---|
| 减少数据冗余 | 存在大量数据冗余 |
| 避免数据维护异常 | 存在数据插入、更新、删除异常 |
| 节约存储空间 | 浪费大量存储空间 |
| 高效的访问 | 访问数据低效 |
数据库设计前提条件
- 实体及实体之间的关系是什么(1对1、1对多、多对多)?
- 实体所留住的属性有哪些?
- 那些属性或属性的组合可以唯一标识一个实体。
名词解释
| 名词 | 解释 |
|---|---|
| 关系 | 一个关系对应一张表 |
| 元组 | 表中的一行记录即为一个元组 |
| 属性 | 表中的字段即为一个属性. |
| 候选码 | 表中的某个属性组,它可以唯一确定一个元组. |
| 主码 | 一个关系有多个候选码,选定其中一个为主码. |
| 域 | 属性的取值范围 |
| 分量 | 元组中的一个属性值. |
数据库范式
第一范式
不匹配第一范式的情况
重复组
重复组通常会出现在会计账上，每一笔记录可能有不定个数的值。举例来说：
'数量' 就是所谓的重复组了，而在这种情况下这份数据就不匹配第一范式。 想要消除重复组的话，只要把每笔记录都转化为单一记录即可：
| 顾客 | 日期 | 数量 |
|---|---|---|
| Pete | Monday | 19.00 |
| Pete | Monday | -28.20 |
| Pete | Wednesday | -84.00 |
| Sarah | Friday | 100.00 |
| Sarah | Friday | 150.00 |
| Sarah | Friday | -40.00 |
缺乏唯一识别码
一样是在交易这个例子中，同一天同一个人买了同样的数量，这样的交易做了两次：
| 顾客 | 日期 | 数量 |
|---|---|---|
| Pete | Monday | 19.00 |
| Pete | Monday | 19.00 |
如上所示，这两笔交易可以说是一模一样，也就是说如果只靠这些数据我们没有办法分辨这两笔记录。 我们之所以说它不匹配第一范式，是因为上面这样的表示法欠缺一个唯一识别码，可以是一个字段， 也可以是一组字段，而且可以保证在这个数据中唯一识别码不会重复出现。要将它正规化到匹配第一范式的原则只需要加入一个唯一识别码即可：
| 交易RID | 顾客 | 日期 | 数量 |
|---|---|---|---|
| 1 | Pete | Monday | 19.00 |
| 2 | Pete | Monday | 19.00 |
单一字段中有多个有意义的值
在单一字段中存放多个值是违反第一范式的做法，下面这个就是很好的例子，它把多个值用逗号分开来表示：
| 人 | 不喜欢的食物 |
|---|---|
| Jim | Liver, Goat's cheese |
| Alice | Broccoli |
| Norman | Pheasant, Liver, Peas |
以这样的设计看来，想要知道有什么人不喜欢某样特定的东西是很不容易的。不过可以把这个数据表转化成下面这种匹配第一范式的型式：
| 人 | 不喜欢的食物 |
|---|---|
| Jim | Liver |
| Jim | Goat's cheese |
| Alice | Broccoli |
| Norman | Pheasant |
| Norman | Liver |
| Norman | Peas |
第二范式
它的规则是要求数据表里的所有数据都要和该数据表的键（主键与候选键）有完全依赖关系：每个非键属性必须独立于任意一个候选键的任意一部分属性。 如果有哪些数据只和一个键的一部分有关的话，就得把它们独立出来变成另一个数据表。如果一个数据表的键只有单个字段的话，它就一定匹配第二范式。
一个数据表匹配第二范式当且仅当
- 它匹配第一范式
- 所有非键的字段都一定是候选键全体字段的函数
有一个数据表记录了设备组件的信息，如下所示：
| 组件 ID (主键) | 价格 | 供应商ID (主键) | 供应商名称 | 供应商住址 |
|---|---|---|---|---|
| 65 | 59.99 | 1 | Stylized Parts | VA |
| 73 | 20.00 | 1 | Stylized Parts | VA |
| 65 | 69.99 | 2 | ACME Industries | CA |
这个数据表的每个值都是单一值，所以它匹配第一范式。因为同一个组件有可能由不同的供应商提供，所以得把组件 ID 和供应商 ID 合在一起组成一个主键。
组件(关键词)和价格之间的关系很正确：同一个组件在不同供应商有可能会有不同的报价，所以价格确实和主键完全相关(完全依赖)。
另一方面，供应商的名称和住址就只和供应商 ID 有关(部分依赖)，这不匹配第二范式的原则。 仔细看就会发现 "Stylized Parts" 这个名称和 "VA" 这个住址重复出现了两次；要是它改名了或是被其他公司并购了怎么办？ 这时候最好把这些数据存到第二个数据表中：
| 供应商 ID (主键) | 名称 | 住址 |
|---|---|---|
| 1 | Stylized Parts | VA |
| 2 | ACME Industries | CA |
这么一来，原本的 "组件来源" 数据表就得要做相对应的改动：
| 组件 ID (主键主键) | 价格 | 供应商 ID（主键、非关键词） |
|---|---|---|
| 65 | 59.99 | 1 |
| 73 | 20.00 | 1 |
| 65 | 69.99 | 2 |
第三范式
第三范式是指满足第二范式并且关系（表）中的非主属性（非关键字段）不存在对候选键的传递依赖的性质， 也指每个非主属性都独立于其他非主属性，并依赖于候选键。第三范式指数据库中不能存在传递函数依赖关系。