9--ch7--关系数据库规范化理论.ppt

邸书灵信息科学与技术学院计算机系2010 11 17 数据库原理TheoryofDatabase TheoryofDatabase2 01 53 问题的提出 Re 关系模式 五元组 R U D DOM F R 关系名 U 组成该关系的属性名集合 D 属性组U中属性所来自的域 DOM 属性向域的映象集合 F 属性间数据的依赖关系集合 TheoryofDatabase3 01 53 问题的提出 Cont 关系模式R U D DOM F 简化为一个三元组 R U F 当且仅当U上的一个关系r满足F时 r称为关系模式R U F 的一个关系 TheoryofDatabase4 01 53 问题的提出 Cont 例 建立一个描述学校教务的数据库 包括 学生的学号 Sno 所在系 Sdept 系主任姓名 Mname 课程名 Cname 成绩 Grade 建立单一的关系模式 R U F U Sno Sdept Mname Cname Grade TheoryofDatabase5 01 53 问题的提出 Cont U Sno Sdept Mname Cname Grade 存在的问题1 数据冗余太大2 更新异常 UpdateAnomalies 3 插入异常 InsertionAnomalies 4 删除异常 DeletionAnomalies TheoryofDatabase6 01 53 存在的问题 U Sno Sdept Mname Cname Grade 问题1 数据冗余太大学生所在的系Sdept及其系主任姓名Mname多次重复出现 系Sdept及其系主任姓名Mname不仅随着每个学生Sno重复出现 而且随着每个学生Sno选的每门课Cname重复出现 TheoryofDatabase7 01 53 存在的问题 Cont U Sno Sdept Mname Cname Grade 存在的问题1 数据冗余太大2 更新问题 UpdateAnomalies 3 插入异常 InsertionAnomalies 4 删除异常 DeletionAnomalies TheoryofDatabase8 01 53 存在的问题 Cont U Sno Sdept Mname Cname Grade 问题2 更新问题 UpdateAnomalies 由于数据冗余问题 当更新数据时 需要很高的代价来保证数据库的一致性 如更换系主任后 必须修改与该系学生有关的每一个元组中系主任姓名Mname TheoryofDatabase9 01 53 存在的问题 Cont U Sno Sdept Mname Cname Grade 存在的问题1 数据冗余太大2 更新问题 UpdateAnomalies 3 插入异常 InsertionAnomalies 4 删除异常 DeletionAnomalies TheoryofDatabase10 01 53 存在的问题 Cont U Sno Sdept Mname Cname Grade 问题3 插入异常 InsertionAnomalies 如果学号Sno是码属性 则当一个系新成立但尚无学生时 其系Sdept及系主任姓名Mname就不能保存至数据库中 TheoryofDatabase11 01 53 存在的问题 Cont U Sno Sdept Mname Cname Grade 存在的问题1 数据冗余太大2 更新问题 UpdateAnomalies 3 插入异常 InsertionAnomalies 4 删除异常 DeletionAnomalies TheoryofDatabase12 01 53 存在的问题 Cont U Sno Sdept Mname Cname Grade 问题4 删除异常 DeletionAnomalies 如果某个系的学生全部毕业了 在删除该系学生信息的同时 该系Sdept及系主任姓名Mname也被删除了 TheoryofDatabase13 01 53 总结 结论R U F U Sno Sdept Mname Cname Grade 不是一个好的模式 好 的模式 不会发生插入异常 删除异常 更新异常 数据冗余应尽可能少 原因关系模式不够 规范 TheoryofDatabase14 01 53 规范化 函数依赖码范式 NormalForm 1NF2NF3NFBCNF多值依赖4NF TheoryofDatabase15 01 53 第7章关系数据库规范化理论 7 1函数依赖7 2关系规范化7 3关系模式的分解准则 TheoryofDatabase16 01 53 函数依赖 FunctionalDependency 函数依赖平凡函数依赖与非平凡函数依赖完全函数依赖与部分函数依赖传递函数依赖 TheoryofDatabase17 01 53 函数依赖 Cont 定义 如果有一个关系模式R A1 A2 An X和Y为 A1 A2 An 的子集 那么对于关系R中的任意一个X值 都只有一个Y值与之对应 则称X函数决定Y 或Y函数依赖于X 记做 X Y TheoryofDatabase18 01 53 函数依赖 Cont 例 Student Sno SName Sdept Sage Sno SNameSno SdeptSno Sage例 SC Sno Cno Grade Sno Cno Grade TheoryofDatabase19 01 53 一些术语和符号 1 如果X Y 但Y不包含于X 则称X Y是非平凡的函数依赖 2 如果X Y 但Y包含于X 则称X Y是平凡的函数依赖 如不作特别说明 我们总是讨论非平凡函数依赖 3 如果Y不函数依赖于X 则记作X Y 4 如果X Y 则称X为决定因子 5 如果X Y 并且Y X 则记作X Y nontrivial非平凡的 trivial平凡的 TheoryofDatabase20 01 53 一些术语和符号 Cont 6 如果X Y 并且对于X的一个任意真子集X 都有X Y 则称Y完全函数依赖于X 记作 7 如果X Y Y X Y Z 则称Z传递函数依赖于X 记作 如果X Y成立 则称Y部分函数依赖于X 记作 fulldependency partialdependency transitivedependency TheoryofDatabase21 01 53 示例 例 关系模式 SC Sno Sname Cno Credit Grade 则函数依赖关系有 Sno Sname Sno Cno Sname Sno Cno Grade TheoryofDatabase22 01 53 示例 Cont 例 关系模式 S Sno Sname Dept Dept master 函数依赖关系有 由于 SnoDept DeptDept master所以有 SnoDept master Sname Sno t TheoryofDatabase23 01 53 第7章关系数据库规范化理论 7 1函数依赖7 2关系规范化7 3关系模式的分解准则 TheoryofDatabase24 01 53 关系规范化 关系模式中的码范式 TheoryofDatabase25 01 53 关系规范化 关系模式满足不同标准化程度称为满足不同范式 1971 1972年E F Codd系统提出了1NF 2NF 3NF 后来又和他人一起提出了BCNF 4NF 5NF 范式的种类 第一范式 1NF 第二范式 2NF 第三范式 3NF BC范式 BCNF 第四范式 4NF 第五范式 5NF TheoryofDatabase26 01 53 范式 各种范式之间存在联系 某一关系模式R为第n范式 可简记为R nNF 一个低一级范式的关系模式 通过模式分解可以转换为若干个高一级范式的关系模式的集合 这种过程就叫规范化 TheoryofDatabase27 01 53 第一范式 1NF 1NF的定义如果一个关系模式R的所有属性都是不可分的基本数据项 则R 1NF 第一范式是对关系模式的最起码的要求 不满足第一范式的数据库模式不能称为关系数据库 但是满足第一范式的关系模式并不一定是一个好的关系模式 TheoryofDatabase28 01 53 关系模式S L C Sno Sdept Sloc Cno Grade 例 关系模式S L C Sno Sdept Sloc Cno Grade Sloc为学生住处 假设每个系的学生住在同一个地方 函数依赖包括 Sno Cno FGradeSno Sdept Sno Cno PSdeptSno Sloc Sno Cno PSlocSdept Sloc TheoryofDatabase29 01 53 S L C不是一个好的关系模式 S L C Sno Sdept Sloc Cno Grade 码 Sno Cno 1 数据冗余度大 2 删除异常 3 插入异常 4 更新复杂 TheoryofDatabase30 01 53 S L C不是一个好的关系模式 Cont 原因Sdept Sloc部分函数依赖于码 解决方法S L C分解为两个关系模式 以消除这些部分函数依赖SC Sno Cno Grade S L Sno Sdept Sloc TheoryofDatabase31 01 53 第二范式 2NF 2NF的定义定义 若R 1NF 且每一个非主属性完全函数依赖于码 则R 2NF S L C Sno Sdept Sloc Cno Grade 1NFS L C Sno Sdept Sloc Cno Grade 2NF 答案 S L C Sno Sdept Sloc Cno Grade 2NF 原因 存在部分依赖 Sno Cno PSdept Sno Cno PSloc 1NF且没有部分依赖 TheoryofDatabase32 01 53 第二范式 2NF Cont 解决办法 采用投影分解法将一个1NF的关系分解为多个2NF的关系 可以在一定程度上减轻原1NF关系中存在的插入异常 删除异常 数据冗余度大 修改复杂等问题 S L C Sno Sdept Sloc Cno Grade 2NFSC Sno Cno Grade 2NFS L Sno Sdept Sloc 2NF TheoryofDatabase33 01 53 分解办法 首先 对于组成主码的属性集合的每一个子集 用它作为主码构成一个表 然后 将依赖于这些主码的属性放置到相应的表中 最后 去掉只由主码的子集构成的表 TheoryofDatabase34 01 53 分解 S L C Sno Sdept Sloc Cno Grade 对于S L C表 首先分解为如下形式的三张表 S L Sno C Cno S C Sno Cno 然后 将依赖于这些主码的属性放置到相应的表中S L Sno Sdept Sloc C Cno S C Sno Cno Grade 最后 去掉只由主码的子集构成的表 最终分解为 S L Sno Sdept Sloc S C Sno Cno Grade TheoryofDatabase35 01 53 第二范式 2NF Cont 但是 将一个1NF关系分解为多个2NF的关系 并不能完全消除关系模式中的各种异常情况和数据冗余 S L Sno Sdept Sloc 2NF 1 数据冗余 2 删除异常 3 插入异常 4 更新复杂 TheoryofDatabase36 01 53 第三范式 3NF 3NF的定义定义 关系模式R中每一个非主属性既不部分依赖于码也不传递依赖于码 则称R 3NF 或 R 2NF且非主属性不传递函数依赖于码 1NF且没有部分依赖没有传递依赖 2NF且没有传递依赖 TheoryofDatabase37 01 53 第三范式 3NF Cont 例 2NF关系模式S L Sno Sdept Sloc 函数依赖 Sno SdeptSdept SnoSdept Sloc因此 Sno SlocS L中存在非主属性对码的传递函数依赖 S L 3NF t TheoryofDatabase38 01 53 第三范式 3NF Cont 解决方法 采用投影分解法 把S L分解为两个关系模式 以消除传递依赖 分解过程 1 对于不是候选码的每个决定因子 从表中删去依赖于它的所有属性 2 新建一个表 新表中包含在原表中所有依赖于该决定因子的属性 3 将决定因子作为新表的主码 TheoryofDatabase39 01 53 第三范式 3NF Cont 2NF关系模式S L Sno Sdept Sloc 分解为 S D Sno Sdept D L Sdept Sloc 分解后的关系模式S D与D L中不再存在传递依赖 都是 3NF的 TheoryofDatabase40 01 53 第三范式 3NF Cont S L C Sno Sdept Sloc Cno Grade 1NFS L C Sno Sdept Sloc Cno Grade 2NFSC Sno Cno Grade 2NFS L Sno Sdept Sloc 2NFS D Sno Sdept 3NFD L Sdept Sloc 3NF 3NF 3NF TheoryofDatabase41 01 53 第三范式 3NF Cont 采用投影分解法将一个2NF的关系分解为多个3NF的关系 可以在一定程度上解决原2NF关系中存在的插入异常 删除异常 数据冗余度大 修改复杂等问题 将一个2NF关系分解为多个3NF的关系后 仍然不能完全消除关系模式中的各种异常情况和数据冗余 TheoryofDatabase42 01 53 小结 问题的提出规范化函数依赖码范式1NF2NF3NF 为什么学习关系理论 方法 模式分解 存在的问题 解决方案 TheoryofDatabase43 01 53 规范化举例 关系模式 Student 学号 姓名 导师号 导师名 课程号 课程说明 成绩 语义 一名学生只有一个导师 学生可选多门课 将其规范化成3NF的 TheoryofDatabase44 01 53 1 此表是1NF 其函数依赖为 学号 姓名 学号 导师号 学号 导师名 课程号 课程说明 学号 课程号 成绩主码为 学号 课程号 存在部分函数依赖关系 不是2NF 2 将其分解为2NF学生 学号 姓名 导师号 导师名 课程 课程号 课程说明 成绩 学号 课程号 成绩 Student 学号 姓名 导师号 导师名 课程号 课程说明 成绩 TheoryofDatabase45 01 53 3 判是否为3NF学生 学号 姓名 导师号 导师名 其函数依赖为 学号 姓名 学号 导师号 导师号 导师名 因此 学号 导师名 即存在传递依赖 因此 学生 学号 姓名 导师号 导师名 不是3NF消除依赖于决定者的属性 把它们放在一个单独表中 学生 学号 姓名 导师号 导师 导师号 导师名 t Student 学号 姓名 导师号 导师名 课程号 课程说明 成绩 是3NF 是3NF TheoryofDatabase46 01 53 4 至此 原关系分解为 课程 课程号 课程说明 是3NF成绩 学号 课程号 成绩 是3NF学生 学号 姓名 导师号 导师名 不是3NF学生 学号 姓名 导师号 是3NF导师 导师号 导师名 是3NF课程 课程号 课程说明 是3NF成绩 学号 课程号 成绩 是3NF学生 学号 姓名 导师号 是3NF导师 导师号 导师名 是3NF Student 学号 姓名 导师号 导师名 课程号 课程说明 成绩 TheoryofDatabase47 01 53 第7章关系数据库规范化理论 7 1函数依赖7 2关系规范化7 3关系模式的分解准则 TheoryofDatabase48 01 53 模式分解 S L C Sno Sdept Sloc Cno Grade 分解方案一SC Sno Cno Grade S L Sno Sdept Sloc S L C Sno Sdept Sloc Cno Grade 分解方案二SC Sno Cno Sloc S L Sno Sdept Grade 好 不好 TheoryofDatabase49 01 53 模式分解 对于一个模式的分解是有多个方案的 但无论哪个方案都要满足分解后产生的模式要与原模式 等价 等价的三种不同含义 分解具有连接无损性 LosslessJoin 分解具有函数依赖保持性 DependencyPreservation 分解既要具有连接无损性 又要具有函数依赖保持性 TheoryofDatabase50 01 53 关系模式的分解准则 模式分解要满足 模式分解具有无损连接性 模式分解能够保持函数依赖 无损连接是指分解后的关系通过自然连接可以恢复成原来的关系 即通过自然连接得到的关系与原来的关系相比 既不多出信息 又不丢失信息 保持函数依赖分解是指在模式的分解过程中 函数依赖不能丢失的特性 即模式分解不能破坏原来的语义 TheoryofDatabase51 01 53 naturaljoin S num name project p title S1Smithp1accountS1Smithp2billing增加的元组S2Jonesp1accountS3Smithp1account增加的元组S3Smithp2billing 元组增加 信息丢失不具有连接无损性 TheoryofDatabase52 01 53 Employee S num name project p title Employee S num name project Project project p title Employee S num name project Project project p title S1Smithp1p1accountS2Jonesp1p2billingS3Smithp2naturaljoin S num name project p title S1Smithp1accountS2Jonesp1accountS3Smithp2billing 具有连接无损性 TheoryofDatabase53 01 53 Sno Sdept Mname Sno Sdept Sdept Mname分解方案一 Sno Sdept Mname 分解方案二 Sno Sdept 和 Sno Mname 分解方案三 Sno Sdept 和 Sdept Mname 只能做笛卡尔积元组增加 信息丢失不具有连接无损性 原来的依赖不存在了Sdept Mname 不具有依赖保持性 既具有连接无损性 又具有依赖保持性 TheoryofDatabase54 01 53 关系模式的分解准则 Cont S D L Sno Dept Loc 函数依赖 Sno Dept Dept Loc不是第三范式 三种分解方案 方案1 S L Sno Loc D L Dept Loc 方案2 S D Sno Dept S L Sno Loc 方案3 S D Sno Dept D L Dept Loc 这三种分解方案得到的关系模式都是第三范式的 那么如何比较这三种方案的好坏呢 由此在将一个关系模式分解为多个关系模式时除了提高规范化程度之外 还需要考虑其他的一些因素 TheoryofDatabase55 01 53 关系模式的分解准则 Cont 将一个关系模式R分解为若干个关系模式 R1 R2 Rn意味着将存储在一张二维表r中的数据分散到了若干个二维表r1 r2 rn中 分解应该不丢失信息 即能通过对关系r1 r2 rn的自然连接运算重新得到关系r中的所有信息 TheoryofDatabase56 01 53 关系模式的分解准则 Cont 事实上 将关系r投影为r1 r2 rn时不会丢失信息 关键是对r1 r2 rn做自然连接时可能产生一些r中原来没有的元组 从而无法区别哪些元组是r中原来有的 即数据库中应该存在的数据 哪些是不应该有的 在这个意义上就丢失了信息 TheoryofDatabase57 01 53 关系模式的分解准则 Cont 以上三种分解方案是否都满足分解要求呢 假设此关系模式的数据如表所示 此关系用r表示 TheoryofDatabase58 01 53 关系模式的分解准则 Cont 若按方案1将S D L投影到S L和D L的属性上 得到如左边两个表所示的关系 做自然连接得到结果如右表所示 S L Sno Loc D L Dept Loc TheoryofDatabase59 01 53 关系模式的分解准则 Cont 结论 方案1不是无损的 分解前 方案1连接后 TheoryofDatabase60 01 53 关系模式的分解准则 Cont 无损连接性将关系模式R分解为个关系模式 R1 R2 Rn若对于R中的任何一个可能的r 都有r r1 r2 rn即r在R1 R2 Rn上的投影的自然连接等于r 则称关系模式R的这个分解具有无损连接性 TheoryofDatabase61 01 53 关系模式的分解准