数据库规范化三个范式应用实例

　　规范化为什么重要？目前很多的数据库由于种种原因还没有被规范化。本文中解释了其中一些原因，并用不同形式的范式（normal form）规范化了一个保险公司的理赔表。在这个过程中表的改变以及添加的一些附加表使数据库效率更高、错误更少、更容易维护。
　　数据库的规范化是优化表的结构和把数据组织到表中的实践，这样做数据才能更明确。规范化使你能够改变业务规则、需求和数据而不需要重新构造整个系统。

　　通过改变存储数据的方式--仅仅改变一丁点--并改变访问这些信息的程序，你就可以消除很多错误或垃圾数据出现的机会并减轻更新信息所必要的工作量。

　　公司现实存在的一个问题可以用一句话概括"我们一般都这样做"。我们一般像采用那种方式存储信息；我们一般允许人们把任何信息写入<insert field name>；我们一般采用那种方式编程。这通常是一件坏事，特别是对于年轻的和正在学习的公司来说。但是，当有新的系统和更好的完成任务的途径的时候，有时"采用那种方式任务完成得很好"这句话可能需要重新探讨和修改。规范化数据就是公司常常采用的有益的方式之一。

　　尽管对于COBOL程序（例如任何COBOL程序员都熟悉的文件布局）使用数据来说，把它们（数据）存储在关系数据库中与存储在平面文件中很相似，但是存储在平面文件中的方法并不是完成任务的必要的最好的途径，特别是由于你不了解两者之间的差别或害怕改变，而简单地把过去的观念带入到现在的方式。

　　注意：Dictionary.com是这样定义规范化的："使其标准，特别使导致它符合某种标准或规范。"或"某种标准的强制接受"。Webopedia认为规范化是"在关系数据库设计中，组织数据以最小化冗余的过程。规范化通常包括把一个数据库分成两个或多个表并定义表之间的关系。其目标是隔离数据，这样添加、删除和修改某个字段只需要在一个表中进行，接着可以通过定义的关系传递到数据库中剩余的表中"。我更喜欢这个定义。

　　术语

　　在你了解现实世界中的一个保险公司的例子之前，你需要了解一些在讨论中会用到的术语。处理数据库的时候，特别是在处理规范化问题的时候，下面一部分讲到的一组新的关键字很有作用：

　　· 关系（Relation）：从本质上说，关系是一个包含行和列的二维表或数组。

　　· 关联（Relationship）：关联是不同表之间的数据彼此联系的方法。关联同时存在于形成不同实体的数据项之间和表实体本身之间，构成了数据库规范化的基本核心问题。数据关联有三种基本的类型，对它们有所了解是很重要的：

　　　一对一（1:1）：一对一关联意味着任何给定的每个（而不是大多数）实例严密地与另一个实体的一个实例对应。每个人只有一个正确的指纹就是唯一的。每个电话号码准确地与一个付帐的独立私人客户对应（不是公司）。美国的每个人都只有一个社会保障号码。

　　　一对多（1:M）：一对多关联意味着给定实体的一个实例可以可以与另一个实体的零个实例、一个实例或者多个实例关联。每个人可能没有小孩、有一个小孩或多个小孩。每个人可能没有汽车、有一辆汽车或多辆汽车。
　
　　　多对多（M:N）：多对多关联（给定实体的零个、一个或多个实例与另一个实体的零个、一个或多个实例关联）是一种直接模拟很复杂的关联，它经常被分解为多个1:M关联。由于多个家庭混合在一起，一个或多个小孩可能没有父母亲（孤儿）、一个父母（单亲家庭），多于一个父母（两个仍然在一起或者离婚的两个父母、或者离婚了又复婚了的父母）。房屋或财产可以转让给一个人或多个人，而这些人（一个或多个）在遗嘱上可能又一个或多个房屋或财产。

　　· 属性（Attribute）：属性被认为是程序或数据库中的某些组件的可以修改的特性或特征，它可以被设置为不同值或者关系或表中的列。

　　· Tuple：Tuple是关系数据库或非关系数据库中的排序了的一组值或值属性：关系中的一行。

　　· 删除异常：删除异常指由于其它数据故意的删除而导致的数据矛盾或未预料到的数据（信息）丢失。

　　· 插入异常：插入异常指由于数据的缺少或缺乏导致没有能力把信息添加到数据库。

　　· 更新异常：更新异常指由于数据冗余或者冗余数据的不完整更新造成的数据矛盾。

　　· 关系的分解：关系的分解指把一个关系分解成多个关系，从而使关系符合更高的范式。

　　· 数据冗余：数据冗余指数据库中没有必要的数据重复。

　　· 数据完整性：数据完整性指数据库中数据的一致性。保证数据完整性很重要，只有这样用户才知道他们依赖的数据是正确的、他们查询的结果以及程序才是精确的和符合期望的。

　　· 原子值：原子值是一个值，它既不是能被进一步拆分的一组值，也不是一个重复的组。每个列都有一个完整的值，但是只有一个值--这个值不能被分解为多个部分，它要么被数据库使用，要么被使用数据库的用户访问的信息。

　　· 参考完整性规则：参考完整性规则指存储在非空的外部健中的值必须是某种关系中的关键数据项。

　　· 外部健：外部健是一个关系中的一组属性（一个或多个列），它同时也是某种（相同的或其它的）关系中的主键。它是关系之间的逻辑链接。参考自己关系的外部健称为递归外部健。

　　· 功能依赖：功能依赖意味着一行中某个属性的值由该行中另一个属性的值决定。这通常出现在主键（使某行唯一的信息片断）与该行的其它信息之间。城市和州的组合依赖于Zip（邮政）代码，即使给定的一个州中有很多Zip代码与某个城市关联。美国的每个合法的人员身份依赖于他的社会保障号码。

　　· 决定性：功能依赖左边的属性决定行中其它属性的值（Zip代码决定了城市和州；社会保障号码决定了人的身份；执照号码和州决定了汽车的拥有者）。

　　· 实体完整性规则：实体完整性规则指某一行的关键属性可能为空（如果你在某个城市就有一个Zip代码；如果你有一辆汽车就有一个执照号码）。

　　· 约束：约束是一种规则，它限定了数据库中的值。电话号码必须是数字的；美元数量必须是数字的；state必须是合法的州或省；country必须是合法的国家；日期不能是2月31号。

　　现在你已经知道了很多相关的术语了，我们可以看看相关术语中规范会的意义了。下面的例子并不是典型的雇员―经理―部门示例，也不是学生―教授―课程提供示例。我将演示一个假设的保险公司的数据库。数据库中的表比本示例中用到的要复杂得多，但是与人们遇到的比较相近。

　　图1显示了理赔（claim）表的非规范化定义。尽管在某个保险公司的数据库中的表比它多得多，但是这些表为我们提供了一些背景，通过它我们可以看到规范化和其分支。请记住每个章节中的示例都只有部分列，这样就简化了示例并使你轻易地看到发生变化的东西。

CLAIM_NUM、 OCCURANCE_NUM 、 CLAIM_STATUS、 ACCDNT_YR、 ACCDNT_DT、 REPORTED_DT、 ENTERED_DT、 CLAIM_DT1、 CLAIM_DT2、 CLAIM_DT3 、 CLAIM_DT4、 CLAIM_DT4 、 CLAIM_DT5 、 CLAIM_DT6 、 CLAIM_DT7、 CLAIM_DT8 、 CLAIM_DT9 、 CLAIM_DT10、 CLOSED_DT 、 DEATH_DT、 ASSIGNED_DT、 ADJSTER_CD 、ADJUSTER_NAME 、 AGENT_CD 、 AWARD_CD 、 CAUSE_CD 、 CAUSE_DESC、 LOCATION 、 SITE 、 COVERAGE_CD 、 COVERAGE_DESC、 DED_RECOV、 DEDUCTIBLE_REMAIN 、 PAID_1 、 RESERVED_1 、 PAID_2 、 RESERVED_2 、 PAID_3 、 RESERVED_3 、 PAID_4 、 RESERVED_4 、 PAID_5 、 RESERVED_5 、 PAID_6 、 RESERVED_6 、 PAID_7 、 RESERVED_7 、 PAID_8 、 RESERVED_8、 PAID_9 、 RESERVED_9 、 PAID_10 、 RESERVED_10 、 LEGAL_FLG、 KEY1、 KEY2、 KEY3、 KEY4、 KEY5、 KEY6、 KEY7、 KEY8、 KEY9、 KEY10、 SEVERITY_CD 、 POLICY_NUM 、 PAYMENT_NUM 、 SSN、 STATE、 ACTVY_DT、 ENTRY_DT、 ADMIN_CD、ADMIN_DESC、 REOPEN_DT、 INSURED_NAME、 INSURED_ADDRESS、 INSURED_PHONE、 INSURED_CITY、 INSURED_STATE、 INSURED_ZIP、 CLAIMANT_NAME、 CLAIMANT_ADDRESS、 CLAIMANT_CITY、 CLAIMANT_STATE、 CLAIMANT_ZIP、 CLAIMANT_PHONE、 SPECIAL_DT_1 、 SPECIAL_DT_2、 SPECIAL_DT_3、 SPECIAL_DT_4 、 SPECIAL_DT_5、 SPECIAL_DT_6 、 SPECIAL_DT_7 、SPECIAL_DT_8 、SPECIAL_DT_9 、 SPECIAL_DT_10 、 GROSS_PD、 POLICY_ID

图1：未规范化的理赔表的列



　　第一范式（1NF）

　　把数据库或数据库的表转换为第一范式一般都相当简单。第一范式要求消除数据中重复的组，这是通过建立相关数据的单独表来实现的。它通过观察数据和表结构来确定表以完成第一范式。

　　第一范式是通过把重复的组放到每个独立的表中，把这些表通过一对多关联联系起来这种方式来消除重复组的。

　　没有重复的属性以及没有重复的一组值--这听起来足够简单了。但是，有时候由于没有其它的选择，使人们相信只有简单地给设计添加任何其它集合却很困难，但是这也是你所做的事情。

　　如果我们想使理赔表达到第一范式，我们就需要找到真正与某个理赔相关联的所有属性。到底是什么构成了理赔？

　　· 理赔要有编号。

　　· 理赔要有提出要求的人。

　　· 理赔要有报告日期。

　　· 理赔要有事故或生病日期。

　　· 理赔要有由于事故或者生病引起的某种物品保留的数量。

　　· 理赔属于或者依据某种策略编写。

　　· 理赔能够结束。

　　· 理赔能够重新开始。

　　· 理赔有某种覆盖面吗？或者某种策略有更多的的事情？

　　· 理赔有起因吗？或者事故或生病有起因吗？

　　· 支付了理赔吗？或者支付了发票吗？

　　· 理赔有社会保障号码吗？或者有时候某个社会保障号码属于提出要求的人吗？

　　· 死亡日期是个有趣的部分。理赔的人死亡了吗？没有，但是如果是生命保险，它可能与理赔相关，因此应该留着。

　　修改与理赔直接相关的列，得到的结果如图2所示：

CLAIM_NUM、 CLAIM_STATUS、 ACCIDENT_YR、 ACCIDENT_DT、 REPORTED_DT、 ENTERED_DT、 CLOSED_DT、 DEATH_DT、 ASSIGNED_DT、 ADJSTER_CD、 ADJUSTER_NAME、 AGENT_CD、 AGENT_NAME、 AWARD_CD、 AWARD_DESC、 PAYMENT_NUM、 LOCATION、 SITE、 DEDUCTIBLE_RECOVER、DEDUCTIBLE_REMAIN、 POLICY_NO、 POLICY_DESCRIPTION、 STATE、 RUN_DT、 ACTIVITY_DT、 ENTRY_DT、 REOPEN_DT、 INSURED_NAME、 INSURED_ADDRESS、 INSURED_PHONE、 INSURED_CITY、 INSURED_STATE、 INSURED_ZIP、 CLAIMANT_NAME、 CLAIMANT_ADDRESS、 CLAIMANT_CITY、 CLAIMANT_STATE、 CLAIMANT_ZIP、 CLAIMANT_PHONE、 GROSS_PD

图2：第一范式的理赔表

符合第一范式的理赔表修订版本将包含仅仅与理赔相关的信息，没有包含支付或发票、策略或事故。

Payment_num	Claim_status	Accident_dt	Accident_yr	Reported_dt	Entered_dt
123456789	Open	20-JUN-2000	2000	28-JUN-2000	29-JUN-2000
234567890	Reviewed	15-FEB-1984	1984	19-FEB-1984	20-FEB-1984
147258369	Reopened	08-APR-2003	2003	10-APR-2003	11-APR-2003
258369147	Closed	18-DEC-1980	1980	18-DEC-1980	19-DEC-1980

　　如果你有支付表，并且存储了特定理赔的保留数量以供支付其它不同的帐单，为什么不把它们存储在支付表中呢？总之，你在支付表中存储了一些信息，因此为什么不把这些内容也放在它里面而不要放在理赔表中呢？

　　如果把这些信息放入理赔表的唯一原因是某个用户在理赔时可能需要这些信息，那么理赔表和支付表可以连接（join）起来，而且信息可以来自单个理赔发生的所有支付总和。并且由于你拥有不同类型的保险策略（因此有不同类型的理赔），为什么不把所有类型的理赔的支付信息存储在一张表中呢？把所有的支付信息存储在相同的表中符合逻辑。与某种支付（属性）相关联的大多数信息是相同的，不管是那种类型的支付或那种类型的理赔。但是，不同类型的理赔的帐号信息有些不同。

　　第二范式（2NF）

　　第二范式处理冗余数据的删除问题。当某张表中的信息依赖于该表中其它的不是主键部分的列的时候，通常会违反第二范式。

　　如果新的第一范式理赔表的列如下，那么可以迅速、轻易看到的冗余数据就是被保险人的城市和州和提出理赔要求的人的城市和州。城市和州都直接依赖于Zip代码，而不依赖于与理赔相关的任何东西。

CLAIM_NUM、 CLAIM_STATUS、 ACCIDENT_YR、 ACCIDENT_DT、 REPORTED_DT、 ENTERED_DT、 CLOSED_DT、 DEATH_DT、 ASSIGNED_DT、 ADJSTER_CD、 ADJUSTER_NAME、 AGENT_CD、AGENT_NAME、 AWARD_CD、 AWARD_DESC、 LOCATION、 SITE、 DEDUCTIBLE_RECOVER、 DEDUCTIBLE_REMAIN、 POLICY_NO、 POLICY_DESCRIPTION、STATE、 RUN_DT、 ACTIVITY_DT、 ENTRY_DT、 REOPEN_DT、 INSURED_NAME、 INSURED_ADDRESS、 INSURED_PHONE、 INSURED_CITY、 INSURED_STATE、 INSURED_ZIP、 CLAIMANT_NAME、 CLAIMANT_ADDRESS、 CLAIMANT_CITY、 CLAIMANT_STATE、 CLAIMANT_ZIP

图3.第二范式的Claim

Claim_num	Claimant_name	Claimant_address	Claimant_city	Claimant_state	Claimant_zip
123456789	Jennifer Smith	1234 Main	Pittsburgh	PA	15201
234567890	Bill Smith	7852 Eagle	Pittsburgh	PA	15202
147258369	John Jones	4562 Edge	Eighty Four	PA	15330
258369147	Eleanor Stillwater	7531 West Eastern	Somerset	PA	15510

Zip_Code	City	State
15330	Eighty Four	PA
15510	Somerset	PA
15201	Pittsburgh	PA
15202	Pittsburgh	PA
15203	Pittsburgh	PA
15204	Pittsburgh	PA
15205	Pittsburgh	PA
15206	Pittsburgh	PA
15207	Pittsburgh	PA
15208	Pittsburgh	PA
15209	Pittsburgh	PA
15210	Pittsburgh	PA

　　因为Pittsburgh、Eighty Fou和Somerset、PA都不依赖于理赔，而依赖于与信息相关的Zip代码，它不是直接归属于支付表的。尽管这并不是此表的唯一问题，但是它消除了与城市、州、Zip代码依赖所引发的困难。

Claim_num	Claimant_name	Claimant_address	Claimant_zip
123456789	Jennifer Smith	1234 Main	15201
234567890	Bill Smith	7852 Eagle	15202
147258369	John Jones	4562 Edge	15330
258369147	Eleanor Stillwater	7531 West Eastern	15510

　　其它的能够迁移到其它表从而使理赔表符合第二范式的信息包括赔偿金编号和赔偿金描述组合，只需要把赔偿金编号存储在理赔表中。采用这种方法时，任何对于给定编号而对描述进行的更新需要做一些改变，它可以改变赔偿金表中的某行的某列，而且这不会发生更新异常，但是如果你更新某个影响成百上千个实体的表的某一列就可能发生更新异常了。相同的逻辑可以应用在调解人和代理人上，把他们的信息迁移到自己的表中，只需要在理赔表中存储编号列的值，这样就容易通过连接访问辅助信息了。

award_cd

award_desc

adjuster_cd

adjuster_name

agent_cd

agent_name

　　第三范式（3NF）

　　第三范式规则查找以消除没有直接依赖于第一范式和第二范式形成的表的主键的属性。我们为没有与表的主键关联的所有信息建立了一张新表。每张新表保存了来自源表的信息和它们所依赖的主键。

　　注意：通常把第三范式说成是"键，全都是键，除了键之外没有任何信息"。

CLAIM_Num、CLAIM_STATUS 、ACCIDENT_DT、REPORTED_DT、 ENTERED_DT、 CLOSED_DT 、 DEATH_DT、 ASSIGNED_DT、 ADJSTER_CD、 AGENT_CD、AWARD_CD、 LOCATION、SITE 、 DEDUCTIBLE_RECOVER、 DEDUCTIBLE_REMAIN 、 POLICY_NO 、 STATE、 RUN_DT、 ACTIVITY_DT、 ENTRY_DT、 REOPEN_DT、 INSURED_NAME、 INSURED_ADDRESS、 INSURED_PHONE、 INSURED_ZIP、 CLAIMANT_NAME、 CLAIMANT_ADDRESS、 CLAIMANT_ZIP

图4：第三范式的理赔表

　　在第三范式中可以看到理赔表的更多的变化，在该表中被保险人姓名、地址、电话号码、Zip代码更加依赖于签订的策略而不是理赔本身。因此，我们能把被保险人的信息放入策略表中。这使得理赔表的剩余信息与理赔更加直接相关，把其它所有信息放入自己的表中而保证了足够（没有遗漏）信息。这些表的一个简单的连接能够重新构造源表的信息，这也是关系代数和关系运算（关系理论和关系数据库依赖的基础）的目标。

POLICY_NO

INSURED_NAME

INSURED_ADDRESS

INSURED_PHONE

INSURED_ZIP

　　第三范式通常是人们能够得到的规范化的最高等级，它一般也是实践中规范化和标准化数据的最高层次。但是还有更多的范式。层次越高，采用简单的步骤完成的困难就越大，而它就越来越靠近理论了。

　　规范化或者没有规范化--以及你可以采用的规范化扩展--通常是相关人员综合的结果。如果有足够的重要的需求，把某片信息存储在某个位置，而它不一定严格符合某种范式的定义，这种存储也是应该被尊重的。另外，规范化的结果需要以表和数据库的使用为基础。通常，在决策支持系统或数据仓库中，由于数据仓库的时间变量组件的映象，我们会强烈渴望得到极端的没有规范化的信息（特别是在事实表中）。

　　在面向团队的环境中，这些决定都是部门的决定（或者是共同的指导决定）。希望这些指导方针能够帮助你了解规范化，帮助你作出更有见识的决定。

原文转自：http://www.ltesting.net