数据库学习第二季大结局：以一些实例看数据模式规范化-CSDN博客

为什么需要数据模式规范化规则

数据规范化的主要目的是减少数据冗余以及避免更新数据异常。使用以下某百货超市购物数据库为例，

输入图片说明

我们把这个数据库记为：R(InvoiceNo （PK），Date，CustomerNo，CustomerName，CustomerAddress，Clerk No.，Clerk Name，ProductNo，Discount，Unit price, Qty)

在此数据库中，由于一个客户购买多种商品，在没有规范化的情况下，由于客户购买的商品有很多，因此客户每购买一个商品就要存储一次客户以及营业员信息，这对于存储空间来说是极大的浪费。另一个方面，若客户信息更改（比如John的住址改了），则针对其中每一条含有客户John的信息都要作出相应的修改，操作繁琐并且容易出现错误（比如某一个记录忘记改了导致前后数据不一致的情况），因此，要对这样的数据库进行规范化。这样的数据模式不符合任何范式，被称为0NF。

从0NF到3NF：“The key, the whole key, and nothing but the key”

从0NF到3NF是数据模式规范化的基本过程。1NF全称叫第一范式，2NF全称叫第二范式等等。此外，高阶的范式有个前提条件即为它们必须首先满足比它们低阶的范式。一般来说，完成了3NF之后的数据模式设计就可以应用到对于规范化不是很严格的应用情形中去了。

1NF：解决属性组问题

观察上述例子，比较容易发现的一个问题是由于交易与产品之间的一对多关系（一次交易购买多个产品），客户每购买一样商品则信息就重复。出现这样的原因是交易的主码（Primary key，简称PK）与产品属性组的候选主码（这个情况下为ProductNo）没有关系。因此如果把表示商品信息的属性组单独列表，然后将商品对应的交易号作为外键（Foreign key，简记FK）指向交易记录，则商品信息发生的变化就不会影响客户的记录。因此，将类似属性组都拆出来建表之后满足1NF的数据模式设计为：

R1(InvoiceNo(PK), Date, CustomerNo, Clerk No.)
R2(InvoiceNo(PK, FK), ProductNo(PK)，Discount, Unit price, QTY)
R3(InvoiceNo(PK, FK), CustomerNo(PK), CustomerName, CustomerAddress)
R4(InvoiceNo(PK, FK), Clerk No., Clerk Name)

则在此情况下，新的数据模式设计符合1NF的要求。

2NF：解决非主键对主键的部分依赖问题

仔细观察满足1NF的数据库，仍能发现不少问题。比如，以R2这张表为例，冗余数据仍然存在。

输入图片说明

导致数据冗余的原因是这张表含有两个主键，由于产品其他信息仅仅依赖于产品号（而非交易号），因此构成了其他变量对于表中主键的部分依赖关系（没有依赖全部的主键）。所以，这里还存在一定的优化空间。解决方案为将每个交易中的交易号、产品号和产品数量单列出来，构成交易-产品关系并记录其数量。对于其他不符合2NF要求的表也可以做类似操作，最后结果为：

R1(InvoiceNo(PK), Date, CustomerNo(FK), Clerk No.(FK))
R2(ProductNo(PK), Discount, Unit price)
R3(InvoiceNO(PK, FK),	ProductNo(PK, FK), Qty)
R4(Clerk No.(PK), Clerk Name)
R5(CustomerNo(PK), CustomerName, CustomerAddress)

3NF：解决主键与非主键之间的条件依赖问题

事实上，上述数据模式在解决多值依赖和部分依赖的问题之后，由于非主键变量之间不互相存在一对一关系，该规范化过程也顺便满足了3NF的要求。但是，假设由于某种原因我们将R1表设计如下：

R1(InvoiceNo(PK), Date, CustomerNo(FK), CustomerName(FK), Clerk No.(FK))

由于客户还有地址这个属性，因此客户属性组并没有全部存在于这个表中，该表是符合1NF要求的。由于每个变量由InvoiceNo这个主键唯一确定，因此2NF也是满足的。但是，由于从交易号可以推理出客户号，由客户号又能推理出客户姓名，因此交易号、客户号和客户姓名之间存在条件依赖，即交易号->客户号->客户姓名，这是3NF所不允许存在的，解决方案为将这个设计改成：

R1(InvoiceNo(PK), Date, CustomerNo(FK), Clerk No.(FK))
R2(CustomerNo(PK), CustomerName)

BCNF：解决多值非主键决定主键的问题

从0NF-3NF，原则上数据库基本的规范化要求已经满足。但是有的时候3NF也会存在明显问题，比如表

R(StudentID(PK, FK), MajorID(PK, FK), AdvisorID(FK), GPA) 输入图片说明

这张表是符合3NF的，但是由于每个教授对应于一个专业（即给出教授ID他在哪个专业是确定的），因此对于相同专业的记录，教授号就存在被重复存储的可能性，这是不满足BCNF要求的。BCNF又称3.5NF，是一般工业级数据库设计的基本要求。

这个问题的解决方式为：

R1(StudentID(PK, FK),  AdvisorID(FK), GPA)
R2(AdvisorID(PK), MajorID)

对于百货商店这个数据模式来说，由于2NF后的结果通过了3NF检查，且不存在非主键决定主键的现象，因此这个设计也是满足BCNF范式的。

4NF：解决主键之间可能存在的多值依赖问题

从0NF到BCNF，非主键之间的关系、主键和非主键的关系以及非主键和主键的关系都已经被考虑清楚了，然而主键之间的关系还并未加以考虑。因此，BCNF级别的规范仍然可能出现比较不常见的问题，以下数据模式就是一个例子。

输入图片说明

此例中，由于店名、披萨名和区域名都是主键，若确定了披萨名和区域名，则店名是唯一确定的。因此披萨名和区域名的组合对于店名构成多值依赖，造成对于同一家点的同一种披萨，投送区域可能会被重复记录。因此它的解决方案为拆分成以下两张表：

输入图片说明

对于百货商店这个数据模式，由于不存在两个以上的变量同为主键，因此此数据模式也是符合4NF的。

去规范化

由上述数据模式规范化的过程可知，数据规范化的过程虽然减少了存储冗余以及可能的数据操作异常，然而构成的代价确实产生了更多的数据表，这就会造成查询多组数据时选择操作需要连接表，降低了查询操作的速度。因此，对于频繁查询的高阶范式应当去规范化到BCNF甚至是3NF，以加快查询的速度。

参考资料

1.Fourth normal form, Wikipedia, https://en.wikipedia.org/wiki/Fourth_normal_form

王珊和萨师煊，《数据库系统概论》第5版，高等教育出版社，2016年2月，北京
Week 6 lecture slides and video, INFO90002 Database & Information Modeling, in University of Melbourne, Semester 2, 2017

——————————————数据库学习第二季MySQL高级篇全剧终————————————————

转载于:https://my.oschina.net/Samyan/blog/1581506