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

范式理论及对实践指导意义概述。范式：1NF 、 2NF 、 3NF 、 BCNF 、 4NF 、 5NF

实例分析及 1NF 、 3NF 的认识误区 关系模型下的树结构表达 供应商和系名问题

范式的局限－对冗余的进一步讨论 单表行间冗余 多表间冗余

2.1 范式理论及对实践指导意义概述1 ）范式理论形成：

1971 年，由 1970 年首先提出“大型共享数据库数据的关系模型”的关系数据库之父 Edgar Frank Codd 相继提出了三级规范化形式 1NF-3NF1974 年， E.F.Codd 和 Boyce 共同提出 BCNF1977 Ronald Fagin 提出了第四范式 以后又相继提出了 5NF （ Project-Join Normal Form (PJ/NF)) 、 DKFN （ Domain/Key Normal Form ）和 6NF

2 ）各范式之间关系： 1NF2NF 3NF BCNF 4NF 5NF DKNF6NF

3 ）规范化方法：一个属于低一级的范式的关系模式可以通过模式分解转换成属于高一级范式的关系模式，这个过程称为关系模式的规范化。

4 ）规范化目的：消除关系中的数据冗余

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

插入，更新和删除异常实例：假设存在下列关系，包含学生和系的基本信息：学号 姓名 所在系 系主任001 zhang 数学 Mr Li

002 wang 数学 Mr Li

003 zhou 数学 Mr Li

004 feng 计算机 Mr chen

005 dong 计算机 Mr chen

该关系存在插入，更新和删除异常。

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

5 ）规范化理论对实践的指导意义

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

可避免异常：关系规范化理论对设计者有指导意义的是消除可避免异常引起的数据冗余。冗余和范式关系：一般消除了一个关系中的数据冗余（除外键

引用为必要的数据冗余外），该关系也就符合了范式要求。

一个关系符合范式要求，一般就不会产生数据冗余，但必须注意的是范式可以消除一个关系中的（单行）数据冗余，但不能消除一个表的行间冗余和多个关系之间的数据冗余。

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

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

2 ） 1NF 的第一层次的解释

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

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

2.2.2 2NF 及对实践的指导意义

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

单据号 单据日期 品号 品名 数量b001 2003-2-1 G01 A 10

b001 2003-2-1 G02 B 12

b002 2004-2-7 G02 B 20{ 单据号，品号 } 为关系的主码，“单据日期”为非主属性，“单据号”“单据日期”，即非主属性“单据日期”部分依赖于码 , 这种设计的数据冗余显而易见。把关系分解为：单据摘要（单据号 * 、单据日期）和单据明细（单据号 * ，品号 * ，品名，数量），通过单据号建立关联。

例 . 单据的单表设计就不属于第 2 范式

思考练习 :

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

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

例 . 学生学科成绩的关系模型设计

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

不属于 3NF 必然存在冗余

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

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

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

2.2.4 BCNF- 扩充的第三范式

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

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

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

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

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

一个商场的商品分类 : 商品分类

服装男装

西装休闲装

女装套装职业装休闲装

童装电器

进口国产

日用品

2.3 实例分析：2.3.1 正确理解 1NF- 树结点的数据表设计：

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

关系模型设计：

冗余分析：

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

分析代码包含信息：“代码”属性不是原子项，它至少包含了下列两个信息（所以不符合 1NF ）： 本级代码 上级代码，而上级代码又包含了上述两个信息。

1NF 规范化：主要任务是把代码项分解为原子项： 思考一：把代码分解成本级代码和上级代码两项，由于上级

代码仍包含本级代码和上级代码，所以不是原子项。这个思路不可行

思考二：由于“代码”实际包含了一个树结构信息，参考数据结构中方法，可以把代码分解成下列原子项：id ： 流水号，主键code ： 本级代码pid ： 父结点 id

对上述关系模型的分析：

code name id code pid 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 。

两种设计的比较：范式的意义

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

一）冗余

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

二）扩展能力及空间利用率

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

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

四）算法比较1 ）  规范化设计较非规范化设计简单的算法：

对某个结点是否为叶结点的判断通过判断 select 结果是否为空，第一种设计

select 的 where 条件较复杂获取所有叶结点都需要使用子查询，但设计一子查询中要引

用主查询中的列获取某个结点从根结点开始的完整路径都可以使用 select 获得，但设计一更复杂

2 ）规范化设计与非规范化设计复杂性相近的算法

结点的迁移、复制和交换例 1 ：把 0101 下的所有结点复制到 0102 的

结点下结点的增加、删除和修改逐级求和的实现例 2 ：逐级求和的演示

例 1 ：把 0101 下的所有结点复制到 0102 的结点下code name id code pid name

0 001 服装 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 休闲装

code amt id code pid 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

例 2 ：各结点包含数量的逐级求和

3 ）规范化设计劣于非规范化设计的算法 取某结点的所有子结点获取结点所在层数某级代码长度加长获得结点在设计一中的 code

注：可以在第二种设计中增加一个 level 列表示一个结点所在的层数，这样可以使第 1 到第３个算法变得和非规范化设计一样简单

例 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)))

设计二：取出所有叶结点即获取 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 ，上

述语句可作相应扩展。

设计一算法：取代码为 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)

结点的迁移、复制和交换例：把男装下所有子结点（西装和休闲装）

复制到女装下算法：扫描要复制的子结点，若是第一层子

结点，依次产生新的代码，若非第一层子结点

设计一：则代码为上一层新增的代码 + 要复制的最后一级代码。

设计二：复制被复制代码， pid 为上级代码的 id

设计一：由代码总长得到代码级数设计二：比较困难，循环搜索父结点，直到根结点，所以 n 级代码要对整表扫描 n 次后才能获得其级数 n 。

vlev=1;vid= 当前结点 id;vpid= 当前结点 pid;while (vpid!=0) // 有父结点{

整表扫描找到当前结点的父结点（ id=vpid的行）：

vid=id;vpid=pid;vlev=vlev+1;

}return vlev

某级代码长度加长基本方法是对存在的某级代码全部左补“ 0” 设计一：若分级代码被其他关系外键引用，除容易

产生更新异常外，对代码表本身的修改简单。 设计二：关键是怎样获得所有某级别的代码，然后左补“ 0” ，也就是本问题的解决依赖于上一问题的解决。

可以在表中增加“级数”（ level ）字段解决规范化设计的这一缺陷，具体设计和分析见 2.4 中行间冗余的例 1

分级代码的还原（用到了根结点）

设计一：直接取 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 均适用 )

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

结论：

2.3.2 3NF 在实践中应用问题一）问题提出

引用数据发生变更的处理： 例 1 ：系名问题：学校系名变更，要求学生各时期的

系名仍使用当时的系名。一个学生在就学期间可能对应多个系名。

例 2 ：供应商问题：商品的供应商名称发生变更，要求变更前进货单中的供应商仍保留原来名称。与系名问题不同的是一个进货单只可能对应一个供应商名。

上述两种情况，若在学生关系或商品关系中仅以系编号或供应商编号引用系信息或供应商信息，符合 3NF ，但一旦对应信息发生变更（如供应商名称），原信息被覆盖（丢失）。

二）供应商问题的常见解决方案方案：把可能变更的且要保留的引用数据放入引用表中，如把供应商编号和进货时的供应商名称同时存入进货单中。分析（是否符合范式）：（后者认识上有误区）1 ）若不发生系供应商名称变更，则由于存在传递依赖

“进货单号供应商号供应商名称”，上述设计不符合 3NF 。

2 ）误区：当供应商名称发生变更，由于进货单中存放的是进货时供应商名，所以（ 1 ）中传递依赖不再成立，所以符合 3NF 。

3 ）正确的判断：若供应商名发生变更，事实上成立传递依赖：“进货单号（供应商号，进货日期）供应商进货时名称”，所以关系仍不符合了 3NF 。

冗余分析：存在两个方面的冗余 对引用信息变更部分：变更前的信息重复（下例中

的红色部分） 对引用信息未变更部分：若引用信息发生变更的频

率极低，显然增加的列对大多数引用信息未发生变更的情况是纯粹的冗余。（下例中绿色部分）

* 供应商编号 供应商名称…

A00011 海尔冰箱厂A00012 长虹电器有限

公司A00013 益民食品厂

• A00012 原名长虹电器厂

* 进货单号 供应商编号 供应商名称…

D001 A00011 海尔冰箱厂D002 A00011 海尔冰箱厂D003 A00012 长虹电器厂D004 A00012 长虹电器厂D005 A00011 海尔冰箱厂D006 A00012 长虹电器厂D007 A00012 长虹电器有限

公司

三）符合范式的解决方案目标：使设计符合所有范式要求。基本方法：使用供应商表和供应商名称变更表记录供应商所有曾用名。

1 ）方案一：关系模型设计： 供应商（ * 供应商编号，供应商当前名称，……） 供应商名称变更表（ * 供应商编号， * 名称版本号，变更日期，

供应商原名称） 进货单摘要（ * 单号，日期，供应商编号，名称版本号，……）

模型设计说明： 变更表记录供应商的原名称，而新名称总是存放在供应商表

中。 供应商名称变更表合理假设同一天供应商名称不会变更两次，

所以可以以（日期，供应商编号）为码。但考虑到引用的便捷性，加入一个版本号（流水号，对每一个供应商从 1 开始，每变更一次加 1 ）。

概要设计：

新增进货单：仅引用供应商表，名称版本号取 0

供应商名称变更： （供应商信息维护的一个部分）（ 1 ）名称变更：供应商名称变更表新增一行，版本号为上一变

更版本号 +1

（ 2 ）更新当前供应商名称：以新供应商名称替换供应商表中供应商名称。

（ 3 ）更改进货单对供应商名称的引用：所有该供应商且供应商名称版本号 =0 的进货单，其供应商名称版本号取（ 1 ）中产生的版本号。

查询进货单（含供应商）：若供应商名称版本号 =0 ，则供应商名称引用供应商表，否则引用供应商名称变更表。（练习 select ）

设计特点：

符合所有范式若原系统设计没有考虑供应商名称变更问题，此设计对原设计影响较小。供应商名称变更较少发生的情况下，本设计对系统的性能（查询效率）基本无影响。

2 ）方案二：

关系模型设计：供应商（ * 供应商编号，地址，电话，……）供应商名称变更表（ * 供应商编号， * 供应商

名称版本号，名称设定日期，供应商名称）进货单（ * 单号，日期，供应商编号，版本号，…）

模型设计说明供应商名称全部放在“供应商名称变更表”

概要设计：新增进货单：仅引用供应商变更表中名称版本号最大的行。（练习 select ）供应商名称变更： （包括供应商信息维护） 新增供应商或名称变更：供应商名称变更表新增一

行，版本号从 0 开始，或为上一变更版本号 +1

查询进货单（含供应商）：全部通过供应商编号和名称版本号引用供应商名称变更表。

设计特点：符合所有范式若原系统设计没有考虑供应商名称变更问题，此设计对原设计影响较大。实现较方案一简单若进货单查询中要包括供应商的其他信息，不论供应商名称是否发生过变更，都要连接供应商表和供应商名称变更表，较方案一多连接一个表。

3 ）方案三

关系模型设计：供应商名称变更表（ * 供应商编号， * 供应

商信息版本号，设定日期，供应商名称，电话，地址，…）

进货单（ * 单号，日期，供应商编号，版本号，…）

模型设计说明：供应商的所有属性均放在“供应商名称变更表”中 。

设计特点：较方案二的特点是去掉供应商表，把供应商信息全部放到变更表中，一但供应商名称变化，该供应商其他信息要复制一遍，是缺点，但也是优点，即具有了变更供应商其他属性的能力，对信息变更不常发生的情况下， 重复的数据量很小。适合名称变化少量发生的情况。思考和练习：分析是否符合 NF ，进行概要设计。

四）系名问题记录变更：系名的变更的记录方法可类似地采用供应商名称变更的方案 1 － 3 的处理方法。引用问题：一个进货单与供应商名称为 1 － 1关系，可以用（供应商编号，供应商名称版本号）引用供应商名，但学生在读期间，系名可能发生多次变更，所以学生对系名可能为 1 －多关系 系名信息不可能保存在学生表中 通过学生中系编号将引用到系名变更表中多个系名

例：有下列两个表：department

* 设置日期 * 系编号 系名称1998-2-1 B001 数学系1998-2-1 B002 物理系1998-2-1 C001 计算机系1999-5-1 C001 软件学院2000-6-10 C001 信息学院

students

* 学号 姓名 系编号98101 王海 C001

98102 李明 C001

98201 周涛 B001

98202 吴名 B001

98301 孙新 B002

完成下列 select 语句：• 获得各系的最新系名• 由于要输出学号为 98101 的学生 1999-7-1 的

成绩单，如何获得当时的该学生所在系名？

获得各系的最新系名：

语句一：使用 max聚集函数select deptid,deptname from department a

where setdate=(select max(setdate) from department where deptid=a.deptid group by deptid)

语句二：使用 all 操作符select deptid,deptname from department a

where setdate>=all (select setdate from department where deptid=a.deptid)

获得 1999-7-1 学生 98101 所在系名：

关键点：获得 1999-7-1 日前的最后一次设置的所有系名，实际在上例语句一子查询中加条件 setdate<=‘1999-7-1’ 即可：select deptname from department a where setdate=(select max(setdate) from department where deptid=a.deptid and setdate<='1999-7-1' group by deptid)

在上述查询中限制为 98101 学生所在系：select deptname from department a where setdate=(select max(setdate) from department where deptid=a.deptid and setdate<=‘1999-7-1’ group by deptid)-- 红色部分语句同上

and deptid in (select deptid from students where id='98101')

思考和练习：使用 all 查询 1999-7-1 所有系名

在子查询中加条件：将出现 1999-7-1 后设置的系名“信息学院”：select deptid,deptname from department a

where setdate>=all (select setdate from department where deptid=a.deptid and setdate<'1999-7-1')

在主查询中加条件：系号为 C001 最后的设置日期大于 1999-7-1 ，所以该系名不出现：select deptid,deptname from department a

where setdate>=all (select setdate from department where deptid=a.deptid )

and setdate<='1999-7-1'

正确的做法是在子查询和主查询中均加此条件。

引用方法小结：供应商问题中进货单也可以用上述方法获得供应商名称，但由于查询使用了子查询（对包括供应商的进货单的查询将更复杂），当数据量很大时，效率明显降低。所以建议使用版本号的引用方法。引用信息表达的是某一时刻的特征，如供应商问题中进货单对供应商名称的引用，可采用“编号 +版本号”作为外键引用的方法得到名称。引用信息表达的是某一时期的特征，如系名问题中学生信息引用各时期的系名，则必须结合日期用查询语句得到该日期的系统。

2.4 范式的局限－对冗余的进一步讨论

一）突破范式限制有时为了提高运行的时间效率需要突破范式限制。突破范式限制一定需要在空间上付出代价，以空间换时间。在下列情况下，我们需要考虑突破范式限制： 被动优化：当运行的响应时间要求很高时，而规范

设计又达不到要求时 主动优化：较少的空间和算法代价换取了较大的速度提升（下面的例 1 ）

二）合理冗余1 ）多表冗余

范式无法去除多表冗余。多表冗余的必要性：多个关系之间的数据冗余的必要性要权衡空间，时间和安全性等各方面的考虑： 出于安全性考虑的完全相同的数据表（热备份）。 出于计算时间（效率）上考虑的月结转表（用单独

的表保存各月的累计数，每月计算一次，作为下月的初始值，见下例 2 ）。

2 ）单表的行间冗余定义：所谓行间冗余指某行某列数据可由本行或其他行的数据计算得到。如存在子结点的树结点的数据为对应下层子结点数据之和。行间冗余可能违反范式（下例 1 ），也可能不违反范式（下例 2 ），后者属于范式无法排除的冗余。冗余必要性：通过与无冗余设计在性能、安全性和算法复杂性等方面比较后评价其必要性。冗余的代价：系统一般要提供冗余数据的生成程序及冗余数据正确性的检测程序。

例 1 ：在 2.3.1 实例中增加结点在树中的层数Level 后是否符合范式？

必要性：原设计要获得第 n 层结点的层数 n 要对整表扫描 n遍，当树结点层次很深时，执行效率低。可行性：增加层数列 level ，在结点加入时（总是在父结点已知的情况下）为其赋值，其值 level= 父结点 level+1 。改进后优点：以简单的 level 维护算法取代了相对复杂的求 level 的算法，直接通过 level 获得层数，效率极大提高。代价：产生了冗余及数据不一致的风险。

是否符合 3NF:关键问题：从语意上判定是否成立传递依赖关系：idpidlevel ，主要是判定后者是否成立不成立理由：由于 pid （父结点）确定后， level 值取决于 id=pid （父结点）的 pid （父结点）取值，即 level 似乎并不完全取决于 pid （自身的父结点），换句话说， pid 值（父结点）不变，而 id=pid 行的 pid 值（父结点的父结点）变化， level 值就会变化。成立理由：在数据表数据不变的前提下，即结点的父子关系不变，显然 pidlevel 。AB 的验证方法：在数据表合理取值的任何情况下，所有满足 A=A0 的行的 B 列值唯一确定结论： pidlevel 成立，理由是父结点相同的结点的结点级别相同。所以如此设计不符合 3NF 。

例 2 ：账表中的余额，符合范式但有行间冗余

下列为 2008 年收支表： （常见于财务、家庭记帐、银行帐户等）日期 摘要 收入 支出 余额01-01 上期余额 140001-05 工资 8000 940001-07 存款 5000 440001-10 水电煤 400 400001-11 超市购物 200 380001-15 ……

余额 = 上行余额 + 本行收入 - 本行支出

分析：设计：收支表（流水号 * ，日期，摘要，收入，支出，余额），其中余额为冗余数据符合范式：注意由余额的计算方法，不成立下列依赖：“流水号（收入，支出）余额” ，即“余额”并不完全依赖于本元组的“收入”和“支出”数据，所以符合所有范式。思考和练习：写出对收支表的增删改的算法，注意余额的产生 去除“余额”列，如何实现对收支表的查询？和含

“余额”的设计从收支表的维护和查询方面进行比较。（提示：使用多表冗余，用单独的表保存月初余额）