1第第3 3章章 数据库设计和规范化数据库设计和规范化2本章的内容简介本章的内容简介第一节：关系数据库设计的理论依据第一节：关系数据库设计的理论依据第二节：数据库设计概述第二节：数据库设计概述第三节：系统规划第三节：系统规划第四节：需求分析第四节：需求分析第五节：概念结构设计第五节：概念结构设计第六节：逻辑结构设计第六节：逻辑结构设计第七节：物理结构设计第七节：物理结构设计第八节：数据库的实施第八节：数据库的实施第九节：数据库的运行和维护第九节：数据库的运行和维护3关系数据库设计是对数据进行组织化和结构化的过程，核心问题是关系模型的设计核心问题是关系模型的设计。关系模式设计的好坏将直接影响到数据库设计的成败，而关系规范化理论关系规范化理论则是指导关系模式设计的标准。 43.1 关系数据库设计的理论依据关系数据库设计的理论依据3.1.1 问题引入问题引入关系数据库设计理论关系数据库设计理论所要研究的就是所要研究的就是针对某一针对某一个具体问题，如何构造一个适合于它的数据模个具体问题，如何构造一个适合于它的数据模式，即构造几个关系模式，每个关系模式应该式，即构造几个关系模式，每个关系模式应该由哪些属性组成等。由哪些属性组成等。下面通过实例来说明采用下面通过实例来说明采用不同的数据库模式不同的数据库模式将将产生产生不同的效果不同的效果。51） 假设有一组实体和实体之间的联系：假设有一组实体和实体之间的联系：2）针对上述）针对上述E-R图可能设计出以下两种数据模式：图可能设计出以下两种数据模式：只有一个关系模式：只有一个关系模式： 即将两个实体及实体之间的联系全部放到一个关系模式中。即将两个实体及实体之间的联系全部放到一个关系模式中。 学生学生选课选课课程（课程（学号，姓名，性别，年龄，课程号，学号，姓名，性别，年龄，课程号， 课程名，学分，成绩课程名，学分，成绩）用三个关系模式：用三个关系模式： 学生（学生（学号，姓名，性别，年龄学号，姓名，性别，年龄） 课程（课程（课程号，课程名，学分课程号，课程名，学分） 选课（选课（学号，课程号，成绩学号，课程号，成绩）例题分析例题分析学生学生课程课程选课选课 学号学号 年龄年龄 姓名姓名 课程号课程号 学分学分课程名课程名 成绩成绩m m n n63）设计方案比较）设计方案比较第一种设计方案可能有下述问题：第一种设计方案可能有下述问题：数据冗余：数据冗余：课程名，学分课程名，学分 等重复出现等重复出现 修改异常：修改异常：某一元组修改学分，其他不变某一元组修改学分，其他不变=同一课程，学分同一课程，学分 不同，造成了数据潜在的不一致性不同，造成了数据潜在的不一致性 插入异常：插入异常：系刚成立，尚未招收学生，主关键字为空，则课程系刚成立，尚未招收学生，主关键字为空，则课程 信息都无法录入。信息都无法录入。删除异常：删除异常：一个系的学生毕业了，删除这些学生的记录，则课一个系的学生毕业了，删除这些学生的记录，则课 程等相关信息也删除掉了。程等相关信息也删除掉了。 第二种设计方案不存在上述问题。第二种设计方案不存在上述问题。数据冗余消除了，插入、删除、修改异常消除了。数据冗余消除了，插入、删除、修改异常消除了。但查询效率比较低。但查询效率比较低。 7 4）问题解决方法）问题解决方法在数据库设计中，如何把现实世界表示成关在数据库设计中，如何把现实世界表示成关系模式，如何变成一个好的关系模式是数据系模式，如何变成一个好的关系模式是数据库规范化理论所研究的问题。库规范化理论所研究的问题。数据库数据库规范化理论规范化理论包含以下两方面的内容：包含以下两方面的内容： 1. 函数依赖函数依赖 2. 范式范式83.1.2 规范化规范化 规范化理论就是研究如何指导产生一个好规范化理论就是研究如何指导产生一个好的关系模式。的关系模式。数据依赖：是一个关系内部属性与属性之数据依赖：是一个关系内部属性与属性之间的一种约束关系。间的一种约束关系。这种约束关系是通过这种约束关系是通过属性间值的相等与否体现出来的数据间的属性间值的相等与否体现出来的数据间的相互关系。相互关系。数据依赖包括函数依赖和多值数据依赖包括函数依赖和多值依赖。依赖。函数依赖函数依赖是最重要也是是最重要也是最基本的一种数据最基本的一种数据依赖依赖。 91、 函数依赖例例如如:在在关关系系职职工工（职职工工号号，姓姓名名，性性别别，年年龄龄），职职工工的的“职职工工号号”确确定定了了，其其姓姓名名、性性别别和和年年龄龄也也随随之之确确定定了了。因因而而称称“职职工工号号”为为决决定定因因素素，它它“函函数数决决定定”姓姓名名等等属属性性的的内内容容，而而姓姓名名等等属属性性的的内内容容则则“函函数依赖数依赖”于于“职工号职工号”，可记做：，可记做： 职工号职工号(姓名，性别，年龄姓名，性别，年龄) 其其中中U表表示示属属性性组组的的集集合合，F表表示示属属性性组组间间的的函函数数依依赖的集合。对于上述的职工关系，可以写为：赖的集合。对于上述的职工关系，可以写为： U职工号，姓名，性别，年龄职工号，姓名，性别，年龄 F职工号职工号姓名，职工号姓名，职工号性别，职工号性别，职工号年龄年龄10例：对学生课程关系模式的函数依赖例：对学生课程关系模式的函数依赖 1.1.学号学号学生姓名学生姓名, , 课程号课程号课程名称课程名称, , ( (学号学号, ,课程号课程号)成绩成绩 2.2.学生姓名学生姓名 学号学号, , 教师名教师名 课程号课程号, , 学号学号 教师名教师名11关系数据库中函数依赖的分类关系数据库中函数依赖的分类1. 平凡函数依赖和非平凡函数依赖平凡函数依赖和非平凡函数依赖2. 完全函数依赖和部分函数依赖完全函数依赖和部分函数依赖3. 传递函数依赖传递函数依赖12平凡函数依赖与非平凡函数依赖1. 如果如果XY，但，但Y不包含于不包含于X，则称，则称XY是非是非平平凡的函数依赖凡的函数依赖。如不作特别说明，我们总是讨。如不作特别说明，我们总是讨论非平凡函数依赖。论非平凡函数依赖。2. 如果如果Y不函数依赖于不函数依赖于X，则记作，则记作X Y。3. 如果如果XY，并且，并且YX，则记作，则记作XY。XYXY13 XY XY为平凡函数依赖为平凡函数依赖 XW, WYXW, WY为非平凡函数依赖为非平凡函数依赖XYW设关系设关系X,Y,WX,Y,W为关系为关系R R中的三个属性组，中的三个属性组，属性关系如下图所示，问属性关系如下图所示，问XY, XW, XY, XW, WYWY各属于上述何种函数依赖。各属于上述何种函数依赖。14例：对学生课程关系模式例：对学生课程关系模式 1.1.平凡函数依赖：平凡函数依赖： ( (学号学号, ,课程号课程号)学号学号, , ( (学号学号, ,课程号课程号)课程号课程号 2.2.非平凡函数依赖：非平凡函数依赖： ( (学号学号, ,课程号课程号)成绩成绩15完全函数依赖与部分函数依赖1.若XY且X Y，则有X F Y，即Y完全函数依赖于X。 2.若XY，X Y，则有X P Y,即Y部分函数依赖于X。XYnm16例：对学生课程关系模式例：对学生课程关系模式 1.1.完全函数依赖：完全函数依赖： ( (学号学号, ,课程号课程号) ) 成绩成绩 2.2.部分函数依赖：部分函数依赖： ( (学号学号, ,课程号课程号) ) 课程名课程名FP17传递函数依赖与非传递函数依赖1.1.若若X X Y Y，Y Y Z Z，但但YXYX，则则有有X X Z Z为为传递函数依赖。传递函数依赖。2.2.请请注注意意上上述述定定义义中中的的条条件件YXYX。如如果果不不加加上上这这一一限限制制，当当XYXY时时允允许许YXYX，则则X XY Y。而而在在X XY Y的的条条件件下下，YZYZ就就等等于于XZXZ。这这样样X X就就直直接接函函数数决决定定Z Z，而而不不是是通通过过Y Y传传递递决定决定Z Z了，即了，即非传递函数依赖。非传递函数依赖。18例例: : 在学生关系在学生关系( (学号学号, ,所在系所在系, ,系主任系主任) ) 有：学号有：学号 所在系，所在系， 所在系所在系 系主任系主任 则：系主任传递函数依赖于学号则：系主任传递函数依赖于学号192 、范式规规范范化化理理论论是是E.F.CoddE.F.Codd首首先先提提出出的的。他他认认为为，一一个个关关系系数数据据库库的的关关系系都都应应满满足足一一定定规规范范，才能构造好的数据模式。才能构造好的数据模式。范式：范式：关系模式满足的确定约束条件称为范关系模式满足的确定约束条件称为范式，式，根据满足约束条件的级别不同，范式由根据满足约束条件的级别不同，范式由低到高分为低到高分为1NF，2NF，3NF，BCNF，4NF，5NF等。等。 规范化过程：规范化过程：把一个低一级范式的关系模把一个低一级范式的关系模式分解为高一级范式的关系模式的过程。式分解为高一级范式的关系模式的过程。20各种范式之间存在联系：某一关系模式R为第n范式，可简记为RnNF。211NF的定义：如果一个关系模式的定义：如果一个关系模式R的所有属的所有属性都是性都是不可分的基本数据项不可分的基本数据项，则，则R1NF。第一范式是对关系模式的第一范式是对关系模式的最起码的要求最起码的要求。不满足第一范式的数据库模式不能称为关不满足第一范式的数据库模式不能称为关系数据库。系数据库。但是满足第一范式的关系模式并不一定是但是满足第一范式的关系模式并不一定是一个好的关系模式。一个好的关系模式。第一范式（1NF）22例：学生例：学生选课选课课程课程（学号，姓名，性别，年龄，课程号，课程名，学分，成绩）（学号，姓名，性别，年龄，课程号，课程名，学分，成绩） 符合第一范式。符合第一范式。23定义：若定义：若R1NF，且它的每一非主属，且它的每一非主属性性完全依赖完全依赖于主键，则于主键，则R2NF。第二范式（2NF）24例：模式的评价和优化分解例：模式的评价和优化分解关系：库存（关系：库存（仓库号仓库号, ,设备号设备号, ,数量数量, ,地点）地点）分析：分析：1NF1NF，但非，但非2NF2NF。因为非主属性。因为非主属性数量数量完完 全依赖于关键字。非主属性全依赖于关键字。非主属性地点地点部分部分 依赖于关键字。即有仓库号依赖于关键字。即有仓库号地点。地点。存在问题：存在问题：1.1.数据冗余；数据冗余； 2.2.插入异常插入异常; ; 3. 3.删除异常删除异常; ; 4. 4.修改异常。修改异常。（如：一个仓库若只有一种设备，则删除设备（如：一个仓库若只有一种设备，则删除设备删除仓库）删除仓库）25解决的方法：解决的方法：是用投影分解把关系模式仓库是用投影分解把关系模式仓库 分解为两个关系模式：分解为两个关系模式： 将将部分函数依赖关系的决定方部分函数依赖关系的决定方和和非主属性非主属性从关系模式中从关系模式中 提出，单独构成一个关系模式。提出，单独构成一个关系模式。 将余下属性加上码（仍要保留部分函数依赖的决定方属将余下属性加上码（仍要保留部分函数依赖的决定方属 性，起分解出来的新关系之间的关联作用）构成另一关性，起分解出来的新关系之间的关联作用）构成另一关 系模式。系模式。分解：分解：库存库存( (仓库号仓库号, ,设备号设备号，数量，数量) ) 仓库仓库( (仓库号仓库号，地点，地点) )26定义：若定义：若R2NF，且每一非主属性不，且每一非主属性不传递依赖于主键，则传递依赖于主键，则R3NF。第三范式（3NF）27例：模式的评价和优化分解仓库（仓库（仓库号仓库号, ,仓库面积仓库面积, ,所在省所在省, ,所在城市）所在城市）分析：分析：2NF2NF，但非，但非3NF3NF。因为仓库号。因为仓库号所在城所在城 市，所在城市市，所在城市所在省。所以非主属所在省。所以非主属 性性所在省所在省传递依赖于主码传递依赖于主码仓库号仓库号。存在问题：存在问题：1.1.数据冗余；数据冗余； 2.2.插入异常插入异常; ; 3. 3.删除异常删除异常; ; 4. 4.修改异常。修改异常。（如：在山东济南要设一个仓库，想先存入有关所在城（如：在山东济南要设一个仓库，想先存入有关所在城 市信息，但无仓库号，则不能。）市信息，但无仓库号，则不能。）28解决的方法：解决的方法：是用投影分解把关系模式仓库分是用投影分解把关系模式仓库分 解为两个关系模式：解为两个关系模式： 将传递依赖的属性分解出来。将传递依赖的属性分解出来。分解：分解：仓库（仓库（仓库号仓库号，仓库面积，所在城市），仓库面积，所在城市） 城市（城市（所在城市所在城市，所在省），所在省）仓库号仓库号所在城市所在城市，所在城市所在城市所在省所在省。仓库号仓库号所在省。所在省。 非非3NF291NF1NF2NF2NF3NF3NF消除非主属性对码的部分函数依赖消除非主属性对码的部分函数依赖消除非主属性对码的传递函数依赖消除非主属性对码的传递函数依赖各种范式之间的关系各种范式之间的关系30例例: :关系模式关系模式SLC(SLC(SnoSno，SdeptSdept，SlocSloc，CnoCno， Grade)Grade)， SlocSloc为学生住处，假设每个系的学为学生住处，假设每个系的学生住在同一个地方。生住在同一个地方。函数依赖包括：函数依赖包括： (Sno, Cno) Grade Sno Sdept Sdept Sloc 综合例题解析综合例题解析Sno Sloc(Sno, Cno) Sdept(Sno, Cno) Sloc 31SnoCnoGradeSdeptSlocSLC SLC是否满足第一范式1NF？1NF1NF函数依赖图：函数依赖图：SLC的主码为的主码为 (Sno, Cno)SLC是否满足第一范式？是否满足第一范式？SLC是否为一个好的关系模式？是否为一个好的关系模式？32SLC是否是一个好的关系模式？(1) (1) 插入异常插入异常假设假设SnoSno9510295102，SdeptSdeptISIS，SlocSloc1 1号楼的学号楼的学生还未选课，因课程号是主属性，因此该学生的生还未选课，因课程号是主属性，因此该学生的信息无法插入信息无法插入SLCSLC。(2) (2) 删除异常删除异常 假定某个学生本来只选修了假定某个学生本来只选修了3 3号课程这一门课。号课程这一门课。现在因身体不适，他连现在因身体不适，他连3 3号课程也不选修了。因号课程也不选修了。因课程号是主属性，此操作将导致该学生信息的整课程号是主属性，此操作将导致该学生信息的整个元组都要删除。个元组都要删除。SLC(Sno，Sdept，Sloc，Cno， Grade)33(3) (3) 数据冗余度大数据冗余度大 如果一个学生选修了如果一个学生选修了1010门课程，那么他门课程，那么他的的SdeptSdept和和SlocSloc值就要重复存储了值就要重复存储了1010次。次。(4) (4) 修改复杂修改复杂 例如学生转系，在修改此学生元组的例如学生转系，在修改此学生元组的SdeptSdept值的同时，还可能需要修改住处值的同时，还可能需要修改住处（SlocSloc）。如果这个学生选修了）。如果这个学生选修了K K门课，门课，则必须无遗漏地修改则必须无遗漏地修改K K个元组中全部个元组中全部SdeptSdept、SlocSloc信息。信息。 SLC(Sno，Sdept，Sloc，Cno， Grade)34 SLC是否满足第二范式2NF ？问题：问题： 非主属性非主属性SdeptSdept和和SlocSloc部分函数依赖于码部分函数依赖于码(Sno, (Sno, Cno)Cno)。解决方法解决方法 SLCSLC分解为两个关系模式，以消除这些部分函数分解为两个关系模式，以消除这些部分函数依赖。依赖。 SCSC（SnoSno，CnoCno，GradeGrade） SLSL（SnoSno，SdeptSdept，SlocSloc）SLC(Sno，Sdept，Sloc，Cno， Grade)35 1NFSnoCnoGradeSdeptSlocSLC362NF2NF函数依赖图函数依赖图：SnoCnoGradeSCSLSnoSdeptSloc37 2NF SLC(Sno, Sdept, Sloc, Cno, Grade) 1NF SLC(Sno, Sdept, Sloc, Cno, Grade) 2NF SC（Sno， Cno， Grade） 2NF SL（Sno， Sdept， Sloc） 2NF将一个将一个1NF关系分解为多个关系分解为多个2NF的关系，并不的关系，并不能完全消除关系模式中的各种异常情况和数据能完全消除关系模式中的各种异常情况和数据冗余。冗余。38 SLC是否满足第三范式3NF ？问题：问题：2NF2NF关系模式关系模式SL(SL(SnoSno, Sdept, Sloc), Sdept, Sloc)中中函数依赖：函数依赖： SnoSdeptSnoSdept SdeptSloc SdeptSloc SnoSlocSnoSlocSlocSloc传递函数依赖于传递函数依赖于SnoSno，即，即SLSL中存在非主属中存在非主属性对码的传递函数依赖。性对码的传递函数依赖。SLSnoSdeptSloc39解决方法解决方法 采用投影分解法，把采用投影分解法，把SLSL分解为两个关系模分解为两个关系模式，以消除传递函数依赖：式，以消除传递函数依赖： SDSD（SnoSno，SdeptSdept） DLDL（SdeptSdept，SlocSloc）SD的码为Sno， DL的码为Sdept。SL(Sno, Sdept, Sloc)40 3NF3NF函数依赖图图：SD的码为Sno， DL的码为Sdept。SnoSdeptSDSdeptSlocDL41 3NF则，则， SC（Sno， Cno， Grade） 3NF SL(Sno, Sdept, Sloc) 2NF SL(Sno, Sdept, Sloc) 3NF SD（Sno， Sdept） 3NF DL（Sdept， Sloc） 3NFSLC(Sno，Sdept，Sloc，Cno， Grade)42 下课了。休息一会儿。休息一会儿。