第二章 范式及其对数据库设计的 指导意义

1. 第二章 范式及其对数据库设计的指导意义 • 范式理论及对实践指导意义概述。 • 范式：1NF、2NF、3NF、BCNF、4NF、5NF • 实例分析及1NF、3NF的认识误区 • 关系模型下的树结构表达 • 供应商和系名问题 • 范式的局限－对冗余的进一步讨论 • 单表行间冗余 • 多表间冗余

2. 2.1 范式理论及对实践指导意义概述 1）范式理论形成： • 1971年，由1970年首先提出“大型共享数据库数据的关系模型”的关系数据库之父 Edgar Frank Codd相继提出了三级规范化形式1NF-3NF • 1974年，E.F.Codd和Boyce共同提出BCNF • 1977 Ronald Fagin提出了第四范式 • 以后又相继提出了5NF（Project-Join Normal Form (PJ/NF)) 、DKFN（Domain/Key Normal Form）和6NF

3. 2）各范式之间关系：1NF2NF 3NF BCNF 4NF 5NF DKNF6NF 3）规范化方法：一个属于低一级的范式的关系模式可以通过模式分解转换成属于高一级范式的关系模式，这个过程称为关系模式的规范化。

4. 4）规范化目的：消除关系中的数据冗余 由于数据冗余引发的问题： • 浪费了存储资源，并且重复的数据占用的空间随数据量的递增而递增。 • 由于数据的重复，为保证数据的一致性，将增加数据维护（插入、更新和删除）的代价，从而降低了系统的开发和运行效率 • 各种意外还是可能造成重复数据的不一致，从而降低了系统的稳定性和可靠性。 • 是产生插入，更新和删除异常根源（见下例）

5. 插入，更新和删除异常实例： 假设存在下列关系，包含学生和系的基本信息： 学号 姓名 所在系 系主任 001 zhang 数学 Mr Li 002 wang 数学 Mr Li 003 zhou 数学 Mr Li 004 feng 计算机 Mr chen 005 dong 计算机 Mr chen 该关系存在插入，更新和删除异常。

6. 插入异常：当新成立一个系但还没有学生时，产生插入异常。插入异常：当新成立一个系但还没有学生时，产生插入异常。 • 删除异常：当一个系的学生被全部删除后，系信息也被删除。 • 更新异常：当系名称或系主任发生变化，必须同时更新这个系所有学生记录，若漏改一个，就产生更新异常。

7. 5）规范化理论对实践的指导意义 • 异常分类：关系设计不规范引起插入，更新和删除异常有的可以通过严密的算法避免发生，有的则不能避免。在上例中，插入和删除异常不可避免，而更新异常却可以避免。 • 不可避免异常：若数据库的设计中存在不可避免的异常时，需求将无法实现，设计者会自觉地消除这些异常。在上例中，一般会增加一个“系（系名，系主任）”关系来排除不可避免的插入和删除异常。这时，规范化设计成为设计师自觉的行动

8. 可避免异常：关系规范化理论对设计者有指导意义的是消除可避免异常引起的数据冗余。可避免异常：关系规范化理论对设计者有指导意义的是消除可避免异常引起的数据冗余。 • 冗余和范式关系： • 一般消除了一个关系中的数据冗余（除外键引用为必要的数据冗余外），该关系也就符合了范式要求。 • 一个关系符合范式要求，一般就不会产生数据冗余，但必须注意的是范式可以消除一个关系中的（单行）数据冗余，但不能消除一个表的行间冗余和多个关系之间的数据冗余。

9. 2.2 范式2.2.1 1NF及对实践的指导意义1）定义 • 1NF的定义1：若关系中所有属性是不可再分的基本项（原子项），即关系中的属性不能是组合属性，称关系属于或服从第一范式。 • 1NF的定义2：关系模式R中不能含有任何重复的数据项。（Robert D. Schnneider 规划与建立高性能SQL Server 6.5数据库） • 第一范式是关系数据模式必须遵循的规范，其他规范均建立在此基础之上。 • 关系的一切数学理论均基于关系模式服从1NF。

10. 2）1NF的第一层次的解释 • 如一个学生的成绩包括数学，语文，外语等，则成绩不能作为学生关系中的一 个属性。 • 要使其符合1NF,必须把数学，语文，外语成绩直接作为学生关系的属性。 • 由于关系数据库中表中列之间的关系相互并列,本身不支持层次结构或数组,所以表面上看,只要是二维表,就一定符合1NF

11. 3）1NF的第二层次的解释 • 不要或没有必要把若干属性或代码组合成一个组合属性或组合代码放在一个数据列中。这同样违反1NF 。 • 这样做的风险是数据库系统对组合属性中某属性的可操作性(子串)一定不如对列的可操作性。 • 解决上述问题的方法也不要简单地把组合属性分解成列,当这些属性有扩充的可能时,应单独建立一个关系。（后面有详例分析）

12. 4）1NF的第三层次的解释 • 1NF要求在一行中不能有重复组，不管是重复的列还是列中含有的重复信息都不允许。(数据库设计) • 如不要把数学成绩，语文成绩和外语成绩作为学生关系中的属性，因为，一旦增加一门课程，该关系就必须作修改。 • 正确的做法是把成绩独立出来，形成的关系模型为：成绩（学号，学科号，成绩） • 类似的如在学生关系中有联系电话属性，而每一个学生可能有不确定的电话数量，则增加属性“电话1”、“电话2”…，同样不符合1NF要求，正确的做法是增加关系：R(学号，电话号码)

13. 2.2.2 2NF及对实践的指导意义 • 关系属于第一范式并且每一个非主属性完全依赖于码，则称关系属于第二范式。 • 由于非主属性均函数依赖于码，所以第二范式去除了非主属性对码的部分依赖。 • 假设(x,y)为关系R(U)的码，有非主属性z，yz，由于y不包含码，所以关系的行中y值必有重复， yz，z值与y值同步重复。 • 一般可以把R(U)分解成R1(U-{z})和R2(y,z)，使子关系均属于2NF，同时消除了冗余。

14. 例.单据的单表设计就不属于第2范式 单据号 单据日期 品号 品名 数量 b001 2003-2-1 G01 A 10 b001 2003-2-1 G02 B 12 b002 2004-2-7 G02 B 20 • {单据号，品号}为关系的主码，“单据日期”为非主属性，“单据号”“单据日期”，即非主属性“单据日期”部分依赖于码,这种设计的数据冗余显而易见。 • 把关系分解为：单据摘要（单据号*、单据日期）和单据明细（单据号*，品号*，品名，数量），通过单据号建立关联。

15. 思考练习: • 分别完成单据的两种设计下的下列操作的设计，并进行比较： • 单据明细的增加，删除和修改 • 单据的增加和删除。 • 空白单据的处理

16. 例.学生学科成绩的关系模型设计 若把学生学科成绩设计成（学号，姓名，学科号，学科名，成绩），该关系就不符合第二范式。（“学号”，“学科号”）为该关系的主键（码），非主属性中，除“成绩”完全依赖于主键，“姓名”和“学科名”不完全依赖于主键，即仅分别完全依赖于主键的子集“学号”和“学科号”。 要使其符合2NF，必须把上述关系分解成三个关系：学生(学号,姓名,…)、学科(学科号,学科名,…)和成绩(学号, 学科号, 成绩)。

17. 2.2.3 3NF及对实践的指导意义 • 关系属于第一范式且关系中不存在非主属性Z传递函数依赖于码，则称关系属于第三范式。 • 在传递函数依赖XYZ的定义中隐含YX和ZY，但3NF定义中的传递函数依赖允许YX （YX）,由此才能保证2NF 3NF。 • 假如不放宽传递函数依赖条件，则关系：学生选课（学号，课程号，课程名）不符合2NF但符合3NF，因为我们找不到属性组Y，使唯一的非主属性“课程名”传递函数依赖于码(学号,课程号)，即(学号,课程号) Y课程名。而定义条件放宽后，可取Y=“课程号”。 • 第三范式是在关系中去除非主属性对码的传递函数依赖。

18. 不属于3NF必然存在冗余 • X，Y，Z为关系R(U)的属性组，且X为码， XYZ，由于Y+>X，所以Y必不包含码，在关系的行中Y值必有重复，而YZ，所以Z值同步重复。 • 一般可以把R(U)分解成R1(U-{Z})和R2(Y,Z)，使子关系均属于3NF，同时消除了冗余。

19. 如在学生关系中增加所在“系”和“系主任”属性，则该关系就不符合第三范式，因为由依赖关系“学号系系主任”，中间就存在了传递函数依赖，学号系主任。如在学生关系中增加所在“系”和“系主任”属性，则该关系就不符合第三范式，因为由依赖关系“学号系系主任”，中间就存在了传递函数依赖，学号系主任。 学号 姓名 系 系主任 001 wang 数学 Li 002 Feng 数学 Li 003 Cheng 数学 Li 004 Huang 物理 Xu 显然这种设计存在数据冗余

20. 正确的做法是在学生关系中增加“系编号”属性，同时增加一个关系：系（系编号，系名，系主任） 。 学号 姓名 系号 系号 系 系主任 001 wang 01 01 数学 Li 002 Feng 01 02 物理 Xu 003 Cheng 01 004 Huang 02 • 单据中包含商品代码外,还包括商品属性,同样不符合第三范式,因为存在下列传递函数依赖:(单据号,单据明细序号)商品代码商品属性。

21. 2.2.4 BCNF-扩充的第三范式 • 关系模式R(U,F)1NF,若XY (YX),X必包含码,则R(U,F) BCNF。 • BCNF是在3NF基础上去除主属性对码的部分和传递依赖。 • X，Y为关系R(U)的属性组， XY，且X不包含码，在关系的行中X值必有重复，而XY，所以Y值同步重复。 • 一般可以把R(U)分解成R1(U-{Y})和R2(X,Y)，使子关系均属于BCNF，同时消除了冗余。

22. 属于3NF但不属于BCNF的例： 学生选课（学号，课程号，教师编号，成绩），假设一个教师只上一门课。则：教师编号课程号，但教师编号不是关系的码，所以此关系不属于BCNF。 学号 课程号 教师编号 成绩 001 数据库 Li 89 001 C语言 Zhang 76 002 数据库 Li 99 003 数据库 Li 87 显然这种设计存在数据冗余

23. (学号，课程号)和(学号，教师编号)均为关系的码，所以学号、课程号和教师编号为主属性，成绩为非主属性，显然，成绩不传递函数依赖于上述两个码，所以关系属于3NF。 (学号，课程号)和(学号，教师编号)均为关系的码，所以学号、课程号和教师编号为主属性，成绩为非主属性，显然，成绩不传递函数依赖于上述两个码，所以关系属于3NF。 把上述关系分解成：学生选课（学号，教师，成绩）和教师任课（教师，课程）关系就符合BCNF。 学号 教师 成绩教师 课程 001 Li 89 Li 数据库 001 Zhang 76 Zhang C语言 002 Li 99 003 Li 87

24. 思考和练习: • 是否可以分解成：学生选课（学号，课程，成绩）和教师任课（教师，课程）？说明原因。 • 不可以，无法确定每个学生所上课程的教师 • 在一个教师只任一门课的前提下，原设计可确定每个学生所上课程及教师，若此前提不成立，则同样无法确定每个学生所上课程的教师

25. 2.2.7 各范式关系小结 • 1NF基础上去除非主属性对码的部分依赖成为2NF • 2NF基础上去除非主属性对码的传递依赖成为3NF • 3NF基础上去除主属性对码的部分和传递依赖成为BCNF • BCNF基础上去除非平凡和非函数依赖的多值依赖成为4NF • 4NF基础上去除不是由侯选码蕴涵的连接依赖成为5NF • 5NF4NF BCNF  3NF 2NF 1NF • 低一级模式可以通过模式分解转换为高一级模式

26. 2.3 实例分析：2.3.1 正确理解1NF-树结点的数据表设计： 一个商场的商品分类: 商品分类 服装 男装 西装 休闲装 女装 套装 职业装 休闲装 童装 电器 进口 国产 日用品

27. 关系模型设计： 代码（主码） 名称 01 服装 0101 男装 010101 西装 010102 休闲装 0102 女装 010201 套装 010202 职业装 010203 休闲装 0103 童装 02 电器 0201 进口 0202 国产 03 日用品

28. 冗余分析： • 在上述设计中增加“上级代码”、“代码级数”、“是否为叶结点”等列，显然，这些列的数据为冗余数据，因为这些数据值完全可以由“代码”计算得到。 • 这些列并不传递或部分依赖于码 • 似乎产生了一种既有数据冗余但又符合所有范式的模式

29. 对上述关系模型的分析： • 分析代码包含信息：“代码”属性不是原子项，它至少包含了下列两个信息（所以不符合1NF）： • 本级代码 • 上级代码， 而上级代码又包含了上述两个信息。 • 1NF规范化：主要任务是把代码项分解为原子项： • 思考一：把代码分解成本级代码和上级代码两项，由于上级代码仍包含本级代码和上级代码，所以不是原子项。这个思路不可行 • 思考二：由于“代码”实际包含了一个树结构信息，参考数据结构中方法，可以把代码分解成下列原子项： id： 流水号，主键 code： 本级代码 pid： 父结点id

30. code name  id codepid name 0 0 01 服装 1 01 0 服装 0101 男装 2 01 1 男装 010101 西装 3 01 2 西装 010102 休闲装 4 02 2 休闲装 0102 女装 5 02 1 女装 010201 套装 6 01 5 套装 010202 职业装 7 02 5 职业装 010203 休闲装 8 03 5 休闲装 0103 童装 9 03 1 童装 02 电器 10 02 0 电器 0201 进口 11 01 10 进口 0202 国产 12 02 10 国产 03 日用品 13 03 0 日用品 • 为以后算法实现的方便，左边设计第一行为根结点，并且code类型必须使用var char避免空格。以下假设上列数据对应两个表：classes_1和classes_2。

31. 两种设计的比较：范式的意义 • 两种设计的可行性：原始设计中关系虽然不符合1NF，但此设计中代码包含了分类树的所有结构信息，所以设计方案是可行的。规范后的设计通过pid建立结点的父子关系同样包含了树的所有结构信息，所以也是可行的。 • 比较方法：从冗余、扩展能力及空间利用率、结点引用和各种算法四个方面进行比较

32. 一）冗余 • 每一个结点只需要知道其父结点代码，就可以构建一棵树，而根据第一种设计，其每一个结点均包含了其所有祖先的结点代码，这就是冗余的信息。 • 设计一冗余的信息被隐藏在一个列中，并且随树结构层数的增加而增加 • 设计二没有冗余

33. 二）扩展能力及空间利用率 • 第一种设计表示的树结点的层数受code列长度的限制，而第二种设计则没有这种限制。 • 为了能适应结点层数的扩展，第一种设计不得不加大code列的长度，由于code列为主码，从效率角度考虑，通常其数据类型会首先考虑使用char型，所以在实际的代码后会存在大量的空格。 • 在每一级代码长度不一样的情况下，第二种设计的code列同样会产生少量的空格，但由于code列不是主码，可以把其类型定义为varchar解决这个问题。

34. 三）结点的引用 • 第一种设计若直接选择code列主码，则一旦代码进行修改，通过外键引用该表的关系也要做修改。 • 第二种设计由于其他关系通过id列引用该表，所以当code列修改后，通过外键引用该表的关系无需做修改。 • 作为外码，引用第二种设计的id对空间的占用比引用第一种设计code对空间占用要小。 • 设计一也可人为地增加一个主码，但客观上又造成新的空间占用。

35. 四）算法比较1）规范化设计较非规范化设计简单的算法：四）算法比较1）规范化设计较非规范化设计简单的算法： • 对某个结点是否为叶结点的判断 • 通过判断select结果是否为空，第一种设计select的where条件较复杂 • 获取所有叶结点 • 都需要使用子查询，但设计一子查询中要引用主查询中的列 • 获取某个结点从根结点开始的完整路径 • 都可以使用select获得，但设计一更复杂

36. 2）规范化设计与非规范化设计复杂性相近的算法2）规范化设计与非规范化设计复杂性相近的算法 • 结点的迁移、复制和交换 例1：把0101下的所有结点复制到0102的结点下 • 结点的增加、删除和修改 • 逐级求和的实现 例2 ：逐级求和的演示

37. 例1：把0101下的所有结点复制到0102的结点下 code name  id codepid name 0 0 01 服装 1 01 0 服装 0101 男装 2 01 1 男装 010101 西装 3 01 2 西装 01010101 全毛 4 01 3 全毛 01010102 化纤 5 02 3 化纤 010102 休闲装 6 02 2 休闲装 0102 女装 7 02 1 女装 010201 套装 8 01 7 套装 010202 职业装 9 02 7 职业装 010203 休闲装 10 03 7 休闲装 010204 西装 11 04 7 西装 01020401 全毛 12 01 11 全毛 01020402 化纤 13 02 11 化纤 010205 休闲装 14 05 7 休闲装

38. 例2：各结点包含数量的逐级求和 code amt  id codepid amt 0 0 01 900 1 01 0 900 0101 300 2 01 1 300 010101 100 3 01 2 100 010102 200 4 02 2 200 0102 390 5 02 1 390 010201 120 6 01 5 120 010202 130 7 02 5 130 010203 140 8 03 5 140 0103 210 9 03 1 210 02 290 10 02 0 290 0201 140 11 01 10 140 0202 150 12 02 10 150 03 300 13 03 0 300

39. 3）规范化设计劣于非规范化设计的算法 • 取某结点的所有子结点 • 获取结点所在层数 • 某级代码长度加长 • 获得结点在设计一中的code 注：可以在第二种设计中增加一个level列表示一个结点所在的层数，这样可以使第1到第３个算法变得和非规范化设计一样简单

40. 例3：一些算法的实现或思路 • 设计一：判定code=codev结点是叶结点 select * from classes_1 where code like codev+’%’ and len(code)>len(codev)为空集。 • 设计二：判定id=idv结点是叶结点 select * from classes_2 where pid=idv为空集。 • 设计一：取出所有叶结点 select * from classes_1 a where not exists (select * from classes_1 where code like rtrim(a.code)+'%' and len(rtrim(code))>len(rtrim(a.code)))

41. 设计二：取出所有叶结点 即获取id不出现在父结点集中的结点 • 全体父结点集的id： select pid from classes_2 where pid is not null • 所有叶结点：select * from classes_2 where id not in (select pid from classes_2 where pid is not null) • 设计二：获得结点路径 select isnull(a.name,'')+'/'+ isnull(b.name,'')+'/'+isnull(c.name,'') from classes_2 a,classes_2 b,classes_2 c where c.pid=b.id and b.pid=a.id • 表达式中有部分值为null，则整个表达式值为null，有些DBMS会把null处理为空串。 • 该语句对最大级数为1，2，3均适用，若级数大于3，上述语句可作相应扩展。

42. 设计一算法：取代码为codev结点的所有子结点 select * from classes_1 where code like codev+”%” • 设计二算法：取id=idv结点的所有子结点 select * from classes_2 where pid=idv or pid in (select id from classes_2 where pid=idv)

43. 结点的迁移、复制和交换 • 例：把男装下所有子结点（西装和休闲装）复制到女装下 • 算法：扫描要复制的子结点，若是第一层子结点，依次产生新的代码，若非第一层子结点 • 设计一：则代码为上一层新增的代码+要复制的最后一级代码。 • 设计二：复制被复制代码，pid为上级代码的id

44. 设计一：由代码总长得到代码级数 • 设计二：比较困难，循环搜索父结点，直到根结点，所以n级代码要对整表扫描n次后才能获得其级数n。 vlev=1;vid=当前结点id;vpid=当前结点pid; while (vpid!=0) //有父结点 { 整表扫描找到当前结点的父结点（id=vpid的行）： vid=id;vpid=pid; vlev=vlev+1; } return vlev

45. 某级代码长度加长 • 基本方法是对存在的某级代码全部左补“0” • 设计一：若分级代码被其他关系外键引用，除容易产生更新异常外，对代码表本身的修改简单。 • 设计二：关键是怎样获得所有某级别的代码，然后左补“0”，也就是本问题的解决依赖于上一问题的解决。 • 可以在表中增加“级数”（level）字段解决规范化设计的这一缺陷，具体设计和分析见2.4中行间冗余的例1

46. 分级代码的还原（用到了根结点） • 设计一：直接取code • 设计二：select isnull(a.code,'')+ isnull(b.code,'')+isnull(c.code,'') from classes_2 a,classes_2 b,classes_2 c where c.pid=b.id and b.pid=a.id order by 1; • code类型必须使用varchar避免空格，该语句对最大级数为1，2，3均适用)

47. 结论： • 一般不符合范式就会有数据冗余，上例并不是例外，因为“代码”列本身存在冗余，如所有“0101”的子结点均重复包含了其上级代码“0101” 。 • 不属于某个范式甚至是1NF的关系设计不一定就是一个不好的设计，但如果使它规范化，那可能是一个更好的设计。 • 一个看上去比较复杂的设计，并不一定会给以后的实现带来更多的复杂性。

48. 2.3.2 3NF在实践中应用问题 一）问题提出 • 引用数据发生变更的处理： • 例1：系名问题：学校系名变更，要求学生各时期的系名仍使用当时的系名。一个学生在就学期间可能对应多个系名。 • 例2：供应商问题：商品的供应商名称发生变更，要求变更前进货单中的供应商仍保留原来名称。与系名问题不同的是一个进货单只可能对应一个供应商名。 • 上述两种情况，若在学生关系或商品关系中仅以系编号或供应商编号引用系信息或供应商信息，符合3NF，但一旦对应信息发生变更（如供应商名称），原信息被覆盖（丢失）。

49. 二）供应商问题的常见解决方案 • 方案：把可能变更的且要保留的引用数据放入引用表中，如把供应商编号和进货时的供应商名称同时存入进货单中。 • 分析（是否符合范式）：（后者认识上有误区） 1）若不发生系供应商名称变更，则由于存在传递依赖“进货单号供应商号供应商名称”，上述设计不符合3NF。 2）误区：当供应商名称发生变更，由于进货单中存放的是进货时供应商名，所以（1）中传递依赖不再成立，所以符合3NF。 3）正确的判断：若供应商名发生变更，事实上成立传递依赖：“进货单号（供应商号，进货日期）供应商进货时名称”，所以关系仍不符合了3NF。

50. 冗余分析：存在两个方面的冗余 • 对引用信息变更部分：变更前的信息重复（下例中的红色部分） • 对引用信息未变更部分：若引用信息发生变更的频率极低，显然增加的列对大多数引用信息未发生变更的情况是纯粹的冗余。（下例中绿色部分） • *供应商编号 供应商名称 … • A00011 海尔冰箱厂 • A00012 长虹电器有限公司 • A00013 益民食品厂 • A00012原名长虹电器厂 *进货单号 供应商编号 供应商名称 … D001 A00011 海尔冰箱厂 D002 A00011 海尔冰箱厂 D003 A00012 长虹电器厂 D004 A00012 长虹电器厂 D005 A00011 海尔冰箱厂 D006 A00012 长虹电器厂 D007 A00012 长虹电器有限公司