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

会计学1范式及其对数据库设计的指导意义22.1问题的引出手工表格设计原则：尽可能地减少重复：我们把学生基本信息表以班级为单元组织，这样班级信息就可放在表头，避免了每个学生都出现重复的班级信息。避免重复也就减少了填写表格的工作量及出现不一致差错的可能性。达到上述目标的方法是进行多表的设计：学生信息表和班级信息表。这些设计原则就是关系型数据库设计的范式中所要求的内容，上述三部分叙述对应范式中的冗余、各种异常和模式分解。第1页/共76页32.2 范式理论概述1）范式理论形成：1971年，由1970年首先提出“大型共享数据库数据的关系模型”的关系数据库之父EdgarFrankCodd相继提出了三级规范化形式1NF-3NF1974年，E.F.Codd和Boyce共同提出BCNF1977RonaldFagin提出了第四范式以后又相继提出了5NF（Project-JoinNormalForm(PJ/NF))、DKFN（Domain/KeyNormalForm）和6NF第2页/共76页42）各范式之间关系：1NF2NF3NFBCNF4NF5NFDKNF6NF3）规范化方法：一个属于低一级的范式的关系模式可以通过模式分解转换成属于高一级范式的关系模式，这个过程称为关系模式的规范化。第3页/共76页54）规范化目的：消除关系中的数据冗余由于数据冗余引发的问题：浪费了存储资源，并且重复的数据占用的空间随数据量的递增而递增。由于数据的重复，为保证数据的一致性，将增加数据维护（插入、更新和删除）的代价，从而降低了系统的开发和运行效率各种意外还是可能造成重复数据的不一致，从而降低了系统的稳定性和可靠性。是产生插入，更新和删除异常根源（见下例）第4页/共76页6插入，更新和删除异常实例：假设存在下列关系，包含学生和系的基本信息：学号 姓名 所在系 系主任001 zhang 数学 MrLi002 wang 数学 MrLi003 zhou 数学 MrLi004 feng 计算机 Mrchen005 dong 计算机 Mrchen该关系存在插入，更新和删除异常。第5页/共76页7插入异常：当新成立一个系但还没有学生时，产生插入异常。删除异常：当一个系的学生被全部删除后，系信息也被删除。更新异常：当系名称或系主任发生变化，必须同时更新这个系所有学生记录，若漏改一个，就产生更新异常。第6页/共76页85）规范化理论对实践的指导意义异常分类：关系设计不规范引起插入，更新和删除异常有的可以通过严密的算法避免发生，有的则不能避免。在上例中，插入和删除异常不可避免，而更新异常却可以避免。不可避免异常：若数据库的设计中存在不可避免的异常时，需求将无法实现，设计者会自觉地消除这些异常。在上例中，一般会增加一个“系（系名，系主任）”关系来排除不可避免的插入和删除异常。这时，规范化设计成为设计师自觉的行动第7页/共76页9可避免异常：关系规范化理论对设计者有指导意义的是消除可避免异常引起的数据冗余。冗余和范式关系：一般消除了一个关系中的数据冗余（除外键引用为必要的数据冗余外），该关系也就符合了范式要求。一个关系符合范式要求，一般就不会产生数据冗余，但必须注意的是范式可以消除一个关系中的（单行）数据冗余，但不能消除一个表的行间冗余和多个关系之间的数据冗余。第8页/共76页102.3 1NF及对实践的指导意义第一范式是关系数据模式必须遵循的规范，其他规范均建立在此基础之上关系的一切数学理论均基于关系模式服从1NF是唯一没有严格定义的范式，容易被忽视和误解第9页/共76页112.3.1 1NF的最常见的表述及认识上的误区若一个表中所有列是不可再分的数据项（原子项），就称这样的设计属于或服从第一范式如一个学生的成绩包括“高等数学”、“英语”和“计算机科学”等，则成绩不能作为学生关系中的一个属性。要使其符合1NF,必须把“高等数学”、“英语”和“计算机科学”成绩直接作为学生关系的属性。由于关系数据库中表中列之间的关系相互并列,本身不支持层次结构或数组,所以表面上看,只要是二维表,就一定符合1NF第10页/共76页12若一个表中不包含任何重复的数据项，称这样的设计属于或服从第一范式。所谓重复的数据项是指这些数据项具有相同性质并且数据项数量具有变化的预期由“学号”、“姓名”、“高等数学”、“英语”和“计算机科学”构成的二维表，在课程可能增加和变化的情况下，它仍然不符合1NF。2.3.2 1NF另一种表述和全面理解1NF第11页/共76页13如此设计的弊端是一旦增加一门课程，该二维表的结构就必须作修改。这种设计隐含了结构上的不稳定性，将会给据此设计的系统造成不断重复的修改。正确的做法是把成绩独立出来，形成的一个成绩表，包含“学号”、“学科”和“成绩”数据项。第12页/共76页14全面理解1NF不要把若干数据组合成一个字符串或组合列作为数据表的一个列，这同样违反1NF这样做的风险是数据库管理系统对组合列中某个数据(通常为字符串的子串)的可操作性和操作效率通常比对列的操作要差如一般数据库管理系统不支持把一个列的子串作为外键第13页/共76页15常见的设计和需求：“学号”列往往包含了学生的年级、专业和班级信息，一个“会计科目号”的列包含了从一级科目开始的各级科目的科目号信息。规范的设计：把学号改成学生在班级中的序号，通常也就是“学号”的后两位，在学生信息表中增加年级、专业和班号的列满足需求：在界面上或打印时若需要输出用户要求的“学号”，可由这些列组合生成。既满足了1NF的要求，又满足的用户需求。年级、专业和班级号这些列对学生表是必需的，在概念数据模型中，它反映的是学生和专业及班级的关系第14页/共76页162.4函数依赖函数依赖是定义第二范式到BCNF范式的基础概念假设A，B分别包含了一个表的某些列，若A中列的列值能唯一确定B中列的列值，则我们称B函数依赖于A，记为：AB。若B不函数依赖于A，记为A+>B第15页/共76页17非平凡的函数依赖当B包含的列均在A中，则必然有B函数依赖于A，称为平凡的函数依赖，反之，成为非平凡的函数依赖。平凡的函数依赖没有任何的实际意义，所以后面除特别指出，函数依赖都指非平凡的函数依赖第16页/共76页18部分和完全函数依赖B函数依赖于A，但同时函数依赖于A中的部分列，我们称B部分函数依赖于A，否则称B完全函数依赖于A非平凡的完全的函数依赖才是有意义的并且能反映函数依赖之本质的函数依赖如果一个表的所有列均完全函数依赖于某些列，那么，这些列就一定构成该表的候选码第17页/共76页19传递函数依赖假设A，B，C分别由一个表的某些列构成，AB，但B+>A，BC，那么自然得出AC，我们把通过B“传递”得到的C对A的函数依赖称为传递函数依赖。第18页/共76页202.52NF及对实践的指导意义关系（表）的每一个非主属性列均完全函数依赖于任一候选码，这样的设计属于或服从第二范式。由于非主属性均函数依赖于码，所以第二范式去除了非主属性对码的部分依赖。假设(x,y)为关系R(U)的码，有非主属性z，yz，由于y不包含码，所以关系的行中y值必有重复，yz，z值与y值同步重复。一般可以把R(U)分解成R1(U-{z})和R2(y,z)，使子关系均属于2NF，同时消除了冗余。第19页/共76页21 单据号 单据日期 品号 品名 数量

b001 2003-2-1 G01 A 10 b001 2003-2-1 G02 B 12 b002 2004-2-7 G02 B 20{单据号，品号}为关系的主码，“单据日期”为非主属性，“单据号”“单据日期”，即非主属性“单据日期”部分依赖于码,这种设计的数据冗余显而易见。把关系分解为：单据摘要（单据号*、单据日期）和单据明细（单据号*，品号*，品名，数量），通过单据号建立关联。例.单据的单表设计就不属于第2范式第20页/共76页22 若把学生学科成绩设计成（学号，姓名，学科号，学科名，成绩），该关系就不符合第二范式。（“学号”，“学科号”）为该关系的主键（码），非主属性中，除“成绩”完全依赖于主键，“姓名”和“学科名”不完全依赖于主键，即仅分别完全依赖于主键的子集“学号”和“学科号”。 要使其符合2NF，必须把上述关系分解成三个关系：学生(学号,姓名,…)、学科(学科号,学科名,…)和成绩(学号,学科号,成绩)。例.学生学科成绩的关系模型设计第21页/共76页232.6 3NF及对实践的指导意义关系（表）中不存在非主属性列传递函数依赖于任一候选码，这样的设计属于或服从第三范式在传递函数依赖XYZ的定义中隐含YX和ZY，但3NF定义中的传递函数依赖允许YX（但YX）,由此才能保证2NF3NF。假如不放宽传递函数依赖条件，则关系：学生选课（学号，课程号，课程名）不符合2NF但符合3NF，因为我们找不到属性组Y，使唯一的非主属性“课程名”传递函数依赖于码(学号,课程号)，即(学号,课程号)Y课程名。而定义条件放宽后，可取Y=“课程号”。第三范式是在关系中去除非主属性对码的传递函数依赖。第22页/共76页24不属于3NF必然存在冗余X，Y，Z为关系R(U)的属性组，且X为码，XYZ，由于Y+>X，所以Y必不包含码，在关系的行中Y值必有重复，而YZ，所以Z值同步重复。一般可以把R(U)分解成R1(U-{Z})和R2(Y,Z)，使子关系均属于3NF，同时消除了冗余。第23页/共76页25如在学生关系中增加所在“系”和“系主任”属性，则该关系就不符合第三范式，因为由依赖关系“学号系系主任”，中间就存在了传递函数依赖，学号系主任。 学号 姓名 系 系主任 001 wang 数学 Li 002 Feng 数学 Li 003 Cheng 数学 Li 004 Huang 物理 Xu

显然这种设计存在数据冗余第24页/共76页26正确的做法是在学生关系中增加“系编号”属性，同时增加一个关系：系（系编号，系名，系主任）。 学号 姓名 系号 系号 系 系主任 001 wang 01 01 数学 Li 002 Feng01 02 物理 Xu 003 Cheng01 004 Huang02单据中包含商品代码外,还包括商品属性,同样不符合第三范式,因为存在下列传递函数依赖:(单据号,单据明细序号)商品代码商品属性。第25页/共76页272.7BCNF-扩充的第三范式关系模式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，同时消除了冗余。第26页/共76页28属于3NF但不属于BCNF的例：

学生选课（学号，课程号，教师编号，成绩），假设一个教师只上一门课。则：教师编号课程号，但教师编号不是关系的码，所以此关系不属于BCNF。 学号 课程号 教师编号 成绩

001 数据库 Li 89 001 C语言 Zhang 76 002 数据库 Li 99 003 数据库 Li 87 显然这种设计存在数据冗余第27页/共76页29 (学号，课程号)和(学号，教师编号)均为关系的码，所以学号、课程号和教师编号为主属性，成绩为非主属性，显然，成绩不传递函数依赖于上述两个码，所以关系属于3NF。

把上述关系分解成：学生选课（学号，教师，成绩）和教师任课（教师，课程）关系就符合BCNF。

学号 教师 成绩

教师 课程 001 Li 89 Li 数据库 001 Zhang 76 ZhangC语言 002 Li 99

003 Li 87

第28页/共76页30各范式关系小结1NF基础上去除非主属性对码的部分依赖成为2NF2NF基础上去除非主属性对码的传递依赖成为3NF3NF基础上去除主属性对码的部分和传递依赖成为BCNFBCNF基础上去除非平凡和非函数依赖的多值依赖成为4NF4NF基础上去除不是由侯选码蕴涵的连接依赖成为5NF5NF4NFBCNF3NF2NF1NF低一级模式可以通过模式分解转换为高一级模式第29页/共76页31一个商场的商品分类:

商品分类 服装 男装 西装 休闲装 女装 套装 职业装 休闲装 童装 电器 进口 国产 日用品2.8范式应用实例分析

2.8.1正确理解1NF－树结点的数据表设计第30页/共76页32代码（主码） 名称 01 服装 0101 男装 010101 西装 010102 休闲装 0102 女装 010201 套装 010202 职业装 010203 休闲装 0103 童装 02 电器 0201 进口 0202 国产 03 日用品 关系模型设计：第31页/共76页33分析代码包含信息：“代码”属性不是原子项，它至少包含了下列两个信息（所以不符合1NF）：本级代码上级代码，而上级代码又包含了上述两个信息。1NF规范化：主要任务是把代码项分解为原子项：思考一：把代码分解成本级代码和上级代码两项，由于上级代码仍包含本级代码和上级代码，所以不是原子项。这个思路不可行思考二：由于“代码”实际包含了一个树结构信息，参考数据结构中方法，可以把代码分解成下列原子项：

id： 流水号，主键 code： 本级代码 pid： 父结点id对上述关系模型的分析：第32页/共76页34code name id code

pid name

0 001 服装 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类型必须使用varchar避免空格。以下假设上列数据对应两个表：classes_1和classes_2。第33页/共76页35两种设计的比较：范式的意义两种设计的可行性：原始设计中关系虽然不符合1NF，但此设计中代码包含了分类树的所有结构信息，所以设计方案是可行的。规范后的设计通过pid建立结点的父子关系同样包含了树的所有结构信息，所以也是可行的。比较方法：从冗余、扩展能力及空间利用率、结点引用和各种算法四个方面进行比较第34页/共76页36一）冗余每一个结点只需要知道其父结点代码，就可以构建一棵树，而根据第一种设计，其每一个结点均包含了其所有祖先的结点代码，这就是冗余的信息。设计一冗余的信息被隐藏在一个列中，并且随树结构层数的增加而增加设计二没有冗余第35页/共76页37二）扩展能力及空间利用率

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

第36页/共76页38三）结点的引用

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

第37页/共76页39四）算法比较

1）

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

结点的迁移、复制和交换例1：把0101下的所有结点复制到0102的结点下结点的增加、删除和修改逐级求和的实现例2：逐级求和的演示

第39页/共76页41例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 休闲装第40页/共76页42code amt id code

pid amt

0 001 900 1 01 0 9000101 300 2 01 1 300010101 100 3 01 2 100010102 200 4 02 2 200 0102 390 5 02 1 390010201 120 6 01 5 120010202 130 7 02 5 130010203 140 8 03 5 1400103 210 9 03 1 21002 290 10 02 0 2900201 140 11 01 10 1400202 150 12 02 10 15003 300 13 03 0 300例2：各结点包含数量的逐级求和第41页/共76页433）规范化设计劣于非规范化设计的算法

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

第42页/共76页44例3：一些算法的实现或思路设计一：判定code=codev结点是叶结点 select*fromclasses_1wherecodelikecodev+’%’andlen(code)>len(codev)为空集。设计二：判定id=idv结点是叶结点 select*fromclasses_2wherepid=idv为空集。设计一：取出所有叶结点 select*fromclasses_1awherenotexists (select*fromclasses_1wherecodelikertrim(a.code)+'%'andlen(rtrim(code))>len(rtrim(a.code)))第43页/共76页45设计二：取出所有叶结点 即获取id不出现在父结点集中的结点全体父结点集的id：selectpidfromclasses_2wherepidisnotnull所有叶结点：select*fromclasses_2whereidnotin(selectpidfromclasses_2wherepidisnotnull)设计二：获得结点路径 selectisnull(,'')+'/'+isnull(,'')+'/'+isnull(,'')fromclasses_2a,classes_2b,classes_2cwherec.pid=b.idandb.pid=a.id表达式中有部分值为null，则整个表达式值为null，有些DBMS会把null处理为空串。该语句对最大级数为1，2，3均适用，若级数大于3，上述语句可作相应扩展。第44页/共76页46设计一算法：取代码为codev结点的所有子结点 select*fromclasses_1wherecodelikecodev+”%”设计二算法：取id=idv结点的所有子结点 select*fromclasses_2wherepid=idvorpidin(selectidfromclasses_2wherepid=idv)第45页/共76页47结点的迁移、复制和交换例：把男装下所有子结点（西装和休闲装）复制到女装下算法：扫描要复制的子结点，若是第一层子结点，依次产生新的代码，若非第一层子结点设计一：则代码为上一层新增的代码+要复制的最后一级代码。设计二：复制被复制代码，pid为上级代码的id第46页/共76页48设计一：由代码总长得到代码级数设计二：比较困难，循环搜索父结点，直到根结点，所以n级代码要对整表扫描n次后才能获得其级数n。

vlev=1;vid=当前结点id;vpid=当前结点pid; while(vpid!=0)//有父结点 { 整表扫描找到当前结点的父结点（id=vpid的行）： vid=id;vpid=pid; vlev=vlev+1; } returnvlev第47页/共76页49某级代码长度加长基本方法是对存在的某级代码全部左补“0”设计一：若分级代码被其他关系外键引用，除容易产生更新异常外，对代码表本身的修改简单。设计二：关键是怎样获得所有某级别的代码，然后左补“0”，也就是本问题的解决依赖于上一问题的解决。可以在表中增加“级数”（level）字段解决规范化设计的这一缺陷，具体设计和分析见2.4中行间冗余的例1第48页/共76页50分级代码的还原（用到了根结点）设计一：直接取code设计二：selectisnull(a.code,'')+isnull(b.code,'')+isnull(c.code,'')fromclasses_2a,classes_2b,classes_2cwherec.pid=b.idandb.pid=a.idorderby1;code类型必须使用varchar避免空格，该语句对最大级数为1，2，3均适用)第49页/共76页51一般不符合范式就会有数据冗余，上例并不是例外，因为“代码”列本身存在冗余，“010101”是对“0101”的冗余，“0101”是对“01”的冗余。不属于某个范式甚至是1NF的关系设计不一定就是一个不好的设计，但如果使它规范化，那可能是一个更好的设计。一个看上去比较复杂的设计，并不一定会给以后的实现带来更多的复杂性。结论：第50页/共76页522.8.2 3NF在实践中应用问题

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

一）把可能发生变更的数据放入引用表中

把供应商编号和进货时的供应商名称同时存入进货单中。分析（是否符合范式）：（后者认识上有误区）1）若不发生系供应商名称变更，则由于存在传递依赖“进货单号供应商号供应商名称”，上述设计不符合3NF。2）误区：当供应商名称发生变更，由于进货单中存放的是进货时供应商名，所以（1）中传递依赖不再成立，所以符合3NF。3）正确的判断：若供应商名发生变更，事实上成立传递依赖：“进货单号（供应商号，进货日期）供应商进货时名称”，所以关系仍不符合了3NF。第52页/共76页54冗余分析：存在两个方面的冗余对引用信息变更部分：变更前的信息重复（下例中的红色部分）对引用信息未变更部分：若引用信息发生变更的频率极低，显然增加的列对大多数引用信息未发生变更的情况是纯粹的冗余。（下例中绿色部分）*供应商编号供应商名称 …A00011 海尔冰箱厂A00012 长虹电器有限公司A00013 益民食品厂A00012原名长虹电器厂

*进货单号 供应商编号供应商名称 …D001 A00011 海尔冰箱厂D002 A00011 海尔冰箱厂D003 A00012 长虹电器厂D004 A00012 长虹电器厂D005 A00011 海尔冰箱厂D006 A00012 长虹电器厂D007 A00012 长虹电器有限公司第53页/共76页55二）使用供应商名称变更表记录供应商所有曾用名关系模型设计：供应商（*供应商编号，供应商当前名称，……）供应商名称变更表（*供应商编号，*名称版本号，变更日期，供应商原名称）进货单摘要（*单号，日期，供应商编号，名称版本号，……）模型设计说明：变更表记录供应商的原名称，而新名称总是存放在供应商表中。供应商名称变更表合理假设同一天供应商名称不会变更两次，所以可以以（日期，供应商编号）为码。但考虑到引用的便捷性，加入一个版本号（流水号，对每一个供应商从1开始，每变更一次加1）。第54页/共76页56概要设计：新增进货单：仅引用供应商表，名称版本号取0供应商名称变更：（供应商信息维护的一个部分）（1）名称变更：供应商名称变更表新增一行，版本号为上一变更版本号+1（2）更新当前供应商名称：以新供应商名称替换供应商表中供应商名称。（3）更改进货单对供应商名称的引用：所有该供应商且供应商名称版本号=0的进货单，其供应商名称版本号取（1）中产生的版本号。查询进货单（含供应商）：若供应商名称版本号=0，则供应商名称引用供应商表，否则引用供应商名称变更表。（练习select）第55页/共76页57设计特点：符合所有范式若原系统设计没有考虑供应商名称变更问题，此设计对原设计影响较小。供应商名称变更较少发生的情况下，本设计对系统的性能（查询效率）基本无影响。第56页/共76页58三）使用供应商名称表记录所有供应商的名称关系模型设计：供应商（*供应商编号，地址，电话，……）供应商名称变更表（*供应商编号，*供应商名称版本号，名称设定日期，供应商名称）进货单（*单号，日期，供应商编号，版本号，…）模型设计说明供应商名称全部放在“供应商名称变更表”第57页/共76页59概要设计：新增进货单：仅引用供应商变更表中名称版本号最大的行。（练习select）供应商名称变更：（包括供应商信息维护）新增供应商或名称变更：供应商名称变更表新增一行，版本号从0开始，或为上一变更版本号+1查询进货单（含供应商）：全部通过供应商编号和名称版本号引用供应商名称变更表。第58页/共76页60设计特点：符合所有范式若原系统设计没有考虑供应商名称变更问题，此设计对原设计影响较大。实现较方案一简单若进货单查询中要包括供应商的其他信息，不论供应商名称是否发生过变更，都要连接供应商表和供应商名称变更表，较方案一多连接一个表。第59页/共76页61四）使供应商信息表包含该供应商所有的历史变更信息关系模型设计：供应商名称变更表（*供应商编号，*供应商信息版本号，设定日期，供应商名称，电话，地址，…）进货单（*单号，日期，供应商编号，版本号，…）模型设计说明：供应商的所有属性均放在“供应商名称变更表”中。第60页/共76页62设计特点：较方案二的特点是去掉供应商表，把供应商信息全部放到变更表中，一但供应商名称变化，该供应商其他信息要复制一遍，是缺点，但也是优点，即具有了变更供应商其他属性的能力，对信息变更不常发生的情况下，重复的数据量很小。适合名称变化少量发生的情况。思考和练习：分析是否符合NF，进行概要设计。第61页/共76页6系名问题记录变更：系名的变更的记录方法可类似地采用供应商名称变更的方案1－3的处理方法。引用问题：一个进货单与供应商名称为1－1关系，可以用（供应商编号，供应商名称版本号）引用供应商名，但学生在读期间，系名可能发生多次变更，所以学生对系名可能为1－多关系系名信息不可能保存在学生表中通过学生中系编号将引用到系名变更表中多个系名第62页/共76页64例：有下列两个表：department*设置日期*系编号系名称1998-2-1B001数学系1998-2-1B002物理系1998-2-1C001计算机系1999-5-1C001软件学院2000-6-10C001信息学院students*学号 姓名 系编号98101 王海 C00198102 李明 C00198201 周涛 B00198202 吴名 B00198301 孙新 B002完成下列select语句：获得各系的最新系名由于要输出学号为98101的学生1999-7-1的成绩单，如何获得当时的该学生所在系名？第63页/共76页65获得各系的最新系名：语句一：使用max聚集函数 selectdeptid,deptnamefromdepartmenta wheresetdate=(selectmax(setdate)fromdepartmentwheredeptid=a.deptidgroupbydeptid)语句二：使用all操作符 selectdeptid,deptnamefromdepartmenta wheresetdate>=all(selectsetdatefromdepartmentwheredeptid=a.deptid)第64页/共76页66获得1999-7-1学生98101所在系名：关键点：获得1999-7-1日前的最后一次设置的所有系名，实际在上例语句一子查询中加条件setdate<=‘1999-7-1’即可：

selectdeptnamefromdepartmentawheresetdate=(selectmax(setdate)fromdepartmentwheredeptid=a.deptidandsetdate<='1999-7-1'groupbydeptid)在上述查询中限制为98101学生所在系：

selectdeptnamefromdepartmenta wheresetdate=(selectmax(setdate)fromdepartmentwheredeptid=a.deptidandsetdate<=‘1999-7-1’groupbydeptid) --红色部分语句同上 anddeptidin(selectdeptidfromstudentswhereid='98101')第65页/共76页67引用方法小结：供应商问题中进货单也可以用上述方法获得供应商名称，但由于查询使用了子查询（对包括供应商的进货单的查询将更复杂），当数据量很大时，效率明显降低。所以建议使用版本号的引用方法。引用信息表达的是某一时刻的特征，如供应商问题中进货单对供应商名称的引用，可采用“编号+版本号”作为外键引用的方法得到名称。引用信息表达的是某一时期的特征，如系名问题中学生信息引用各时期的系名，则必须结合日期用查询语句得到该日期的系统。第66页/共76页682.9 范式的局限－对冗余的进一步讨论使一个不符合范式的设计符合范式，通常可以消除冗余满足范式，冗余还是可能出现在表的单行、行间和多表之间能以较少的空间代价和算法代价换取较大的系统运行效率的提高，这样的冗余是合理的第67页/共76页692.9.1 范式无法消除的冗余－计算列问题在学生表中加入“选中”标志列，该列的数值是依据某个算法由学号中的各位数字计算得到这个列并不传递函数依赖或部分函数依赖于码“学号”