数据库设计-规范化与范式¶
规范化¶
There can be following anomalies while performing a database operation:
insert: data is known but can not be inserted
update: updating data requires modifications in multiple tuples (rows)
delete: deleting some data causes some other data to be lost
第一范式(1NF)¶
- 定义
- Attribute values have to be atomic
- Each record should be unique and have a primary key as identifier
- 目标
- 为了要排除 重复组 的出现
- 例子
- 重复组(不符合 1NF 属性原子性的限制)
顾客 日期 数量 Pete Monday 19.00 -28.20 Pete Wednesday -84.00 Sarah Friday 100.00 150.00 -40.00 “数量”就是所谓的重复组了,而在这种情况下这份数据就不符合第一范式。想要消除重复组的话,只要把每笔记录都转化为单一记录即可:
顾客 日期 数量 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
Note
当前所有的 RDBMS 中,都已经在建表的时候强制满足第一范式。 但是仍然可以设计出在骨子里违反第一范式的数据表。
- 单一字段中有多个有意义的值
人 不喜欢的食物 Jim Liver, Goat’s cheese Alice Broccoli Norman Pheasant, Liver, Peas 以这样的设计看来,想要知道有什么人不喜欢某样特定的东西是很不容易的。
- 用很多字段来表达同一个事实
人 喜欢的颜色 不喜欢的食物 (1) 不喜欢的食物 (2) 不喜欢的食物 (3) Jim Green Liver Goat’s cheese Alice Fuchsia Broccoli Norman Blue Pheasant Liver Peas Emily Yellow 就算我们能确定每个人不喜欢吃的食物最多不会超过三样,这还是一个很糟的设计。 举例来说,我们想要知道所有不喜欢同一种食物的人的组合的话,这就不是件容易的事, 因为食物有可能出现在任何一个字段,也就是说每一次的查询都要去检查 9 (3 x 3) 组不同的字段组合。
- 参考
第二范式(2NF)¶
- 定义
- 属性都要和键(主键与候选键)有完全依赖关系: 每个非键属性必须独立于任意一个候选键的任意一部分属性。 如果有哪些属性只和一个键的一部分有关的话,就得把它们独立出来变成另一个数据表。
- 满足 1NF
- Every non-key attribute functionally should depend on the primary key
- 目标
- 转化为二范式就是消除部分依赖。
- 所谓部分依赖即 <a, b> 主键,属性 X 只依赖于 a
- 例子
组件来源表
组件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 有关(部分依赖),这不符合第二范式的原则。
为满足 2NF 将部分依赖的供应商属性单独拆分到一张表:
供应商
供应商 ID (主键) 名称 住址 1 Stylized Parts VA 2 ACME Industries CA 原本的 “组件来源” 数据表就得要做相对应的改动:
组件来源
组件 ID (主键主键) 价格 供应商 ID(主键、非关键词) 65 59.99 1 73 20.00 1 65 69.99 2 Note
检查数据表里的每个字段,确认它们是不是都和主键完全相关, 这样才能知道这个数据表是不是符合第二范式; 如果不是的话,就把那些不完全相关的字段移到独立的数据表里。 接下来的步骤是要确保所有不是键的字段都和彼此没有相依关系,这就叫做第三范式。
- 参考
第三范式(3NF)¶
- 定义
- 要求所有非主键属性都只和候选键有相关性,也就是说非主键属性之间应该是独立无关的。
- All non key attributes should only depends on a primary key
- 目标
- 消除传递依赖关系
- 传递依赖:
A -> B, B -> C 则 A -> C
即 B 依赖于 A,C 依赖于 B,则 C 传递依赖于 A
- 例子
机械组件
组件编号 (主键) 制造商名称 制造商地址 1000 Toyota Park Avenue 1001 Mitsubishi Lincoln Street 1002 Toyota Park Avenue - 问题在于每提到一次制造商名称就要多存一次它的地址,而这就不符合第三范式的原则。
- 制造商地址依赖于制造商所以 非键属性之间存在依赖关系 不符合 3NF 的约束
- 存在传递依赖 <组件ID> -> <制造商名称> -> <制造商地址>, <组件ID> -> <制造商地址>
本例中制造商地址很明显地不该被列在这个关系里面,因为和组件本身比起来,制造商地址应该和制造商比较有关系; 正确的做法应该是把独立成为一个新的数据表:
制造商
制造商名称 (主键) 制造商地址 Toyota Park Avenue Mitsubishi Lincoln Street 机械组件
组件编号 (主键) 制造商名称 1000 Toyota 1001 Mitsubishi 1002 Toyota 另一例子: 订单 (Order)
Order Number (主键) Customer Name Unit Price Quantity Total 1000 David $35.00 3 $105.00 1001 Jim $25.00 2 $50.00 1002 Bob $25.00 3 $75.00 非主键字段完全依赖于主键订单编号,也就是说唯一的订单编号能导出唯一非主键字段值,符合第二范式。
第三范式要求非主键字段之间不能有依赖关系,显然本例中小计依赖于非主键字段“单价”和“数量”,不符合第三范式。
小计不应该放在这个数据表里面,只要把单价乘上数量就可以得到小计了;
如果想要符合第三范式的话,就把小计
total
列去掉- 不过在做查询的时候,本来用
SELECT Order.Total FROM Order
就要改成用SELECT UnitPrice * Quantity FROM Order
了
- 参考