关系数据库的规范化理论与数据库设计.ppt

三级数据库技术 第7章关系数据库的规范化理论与数据库设计 2 本部分占总分的8 主要内容 规范化理论概述函数依赖关系模式分解数据库设计过程 3 了解关系模式设计的常见问题及应对措施 是学习本章的前提和基础函数依赖定义 逻辑蕴含 码 armstrong公理 常用范式和关系模式分解方法考试重点函数依赖 关系模式规范化程度和模式分析等价标准是重中之重 4 学习本章的目的 针对具体应用 数据要存储 需要什么样的关系模式 解决关系数据库的逻辑设计问题 如何建表 要建合适的表 需要学习规范化理论 7 1关系模式中的常见问题 6 考点1关系模式设计的常见问题 已知数据库要存储教师的姓名和地址 还要存储教师教的课程号和课程设计的关系模式 T TNAME ADDRESS CNO CNAME 其中 TNAME 教师姓名 ADDRESS 教师地址 CNO 任教课程号 CNAME 课程名称 一个教师可以教多门课程 7 该关系模式可能出现如下问题 数据冗余更新异常 多个记录更新 容易产生数据不一致 插入异常 TNAME CNO码 某个教师没上课 CNO为空 不能插入 删除异常 某个教师课程全部删除 教师的地址姓名信息全部删除 如何设计一个好的关系模式 需要学习规范化理论 8 考题 1 下列哪一条不是由于关系模式设计不当所引起的问题 A 数据冗余B 插入异常C 更新异常D 丢失修改D2009 032 由于关系模式设计不当所引起的插入异常指的是A 两个事务并发地对同一关系进行插入而造成数据库不一致B 由于码值的一部分为空而不能将有用的信息作为一个元组插入到关系中C 没经授权的用户对关系进行插入D 插入操作因为违反完整性约束条件而遭到拒绝B2008 092005 09 9 3 下列哪些条是由于关系模式设计不当引起的问题 I 数据冗余II 插入异常III 删除异常IV 丢失修改V级联回滚A 仅II和IIIB 仅I II和IIIC 仅I IV和VD 仅II III和IVB2008 044 由于关系模式设计不当所引起的更新异常指的是A 两个事务并发地对同一个数据项进行更新而造成数据库不一致B 关系的不同元组中数据冗余 更新时未能同时更新有关元组而造成数据库不一致C 未经授权的用户对数据进行了更新D 对数据的更新因为违反完整性约束条件而遭到拒绝B2007 09 10 下列关于规范化理论的叙述中 哪一条是不正确的 A 规范化理论是数据库设计的理论基础B 规范化理论最主要的应用是在数据库概念结构设计阶段C 规范化理论最主要的应用是在数据库逻辑结构设计阶段D 在数据库设计中 有时候会降低规范化程度而追求高查询性能B2009 04 2008 09 11 考点2函数依赖 例在关系模式R SNO NAME AGE 中 根据语义一个SNO值对应一个AGE值 不可能出现一个SNO值对应两个AGE值 我们称SNO函数决定AGE 写作SNO AGE或AGE函数依赖于SNO如果一个AGE对应一个SNO 但我们决不能认为AGE函数决定SNO 因为它不符合语义 12 函数依赖定义设R U 是属性集U上的一个关系模式 X和Y均为U的子集 若对于R U 的任一个可能的关系r r中不可能有两个元组在X中的属性值相等 而在Y中的属性值不等 那么称X函数决定YX Y 或Y函数依赖于XX为决定因素 13 一 函数依赖 例如 学生 Sno Sname Sdept Sage 由于在所有的元组中 Sno都是唯一的 因此 Sno函数确定Sname和Sdept 如果规定Sname不能重复 那么在所有的元组中Sname也都是唯一的 可以说 Sname函数确定Sdept 或者说 Sdept函数依赖于Sname记作 Sname Sdept 或者说 Sname和Sdept函数依赖于Sno Sno Sname Sno Sdept 14 几个术语和符号 如果X Y 则X叫做决定因素 Determinant 如果X Y Y X 则记作 X Y 如果Y不函数依赖于X 则记作 X Y 15 二 平凡函数依赖与非平凡函数依赖 如果X Y 但Y X 则称X Y是非平凡的函数依赖 如果X Y 但Y X 则称X Y是平凡的函数依赖 例 在关系SC Sno Cno Grade 中 非平凡函数依赖 Sno Cno Grade 平凡函数依赖 Sno Cno Sno Sno Cno Cno 16 三 完全函数依赖与部分函数依赖 定义5 2在关系模式R U 中 如果X Y 并且对于X的任何一个真子集X 都有X Y 则称Y完全函数依赖于X 记作 XFY若X Y 但Y不完全函数依赖于X 则称Y部分函数依赖于X 记作 XPY 17 三 完全函数依赖与部分函数依赖 例 在关系SC Sno Cno Grade 中 用X表示 Sno Cno 用Y表示Grade 那么 Sno Cno Grade但是Sno Grade Cno Grade 因此 Sno Cno FGrade 18 四 传递函数依赖 定义5 3在关系模式R U 中 如果X Y Y Z 且Y X Y X 则称Z传递函数依赖于X 注 如果Y X 即X Y 则称Z直接函数依赖于X 例 在关系Std Sno Sdept Mname 中 有 Sno Sdept Sdept MnameMname传递函数依赖于Sno 19 五 函数依赖的逻辑蕴含 设R是一个关系模式 X Y是U中属性组 F是函数依赖集 若在R的任何一个满足F中函数依赖的关系r上 都有函数依赖X Y成立 则称F逻辑蕴含X Y F推导出X Y 例如关系模式S SNO NAME AGE DNO DEAN 其属性组上的函数依赖集为F SNO NAME SNO AGE SNO DNO DNO DEAN SNO DEAN就是F所逻辑蕴含 推导出 的一个函数依赖在关系模式R中为F所逻辑蕴含的函数依赖全体称为F闭包 F 20 码 定义设K为关系模式R中的属性或属性组合 若K U 并且不存在K的真子集决定U 则K称为R的一个侯选码 CandidateKey 若关系模式R有多个候选码 则选定其中的一个做为主码 Primarykey 主属性与非主属性全码 ALLKEY 主码为关系模式所有属性 21 例 关系模式CSZ CITY ST ZIP 其属性组上的函数依赖为F CITY ST ZIP ZIP CITY 推导后 CITY ST 和 ST ZIP 为候选码 所以CITY ST ZIP都是主属性 22 Armstrong公理系统 推理规则 设F是属性组U上的一组函数依赖 有如下推理规则自反律 若Y X U 则X Y为F逻辑蕴含 推导出 增广律 若X Y为F逻辑蕴含 且Z U 则XZ YZ为F逻辑蕴含传递律 若X Y及Y Z为F所逻辑蕴含 则X Z为F所逻辑蕴含 23 例 关系模式CSZ CITY ST ZIP 其属性组上的函数依赖为F CITY ST ZIP ZIP CITY 证明 ST ZIP 是候选码1 ZIP CITYF中已知2 ST ZIP ST CITY 增广律3 ST ZIP ST CITY ZIP 增广律 24 Armstrong的推论 合并规则 X Y Y Z 则X YZ伪传递规则 X Y WY Z 则XW Z分解规则 X Y Z Y 则X Z关系模式码的确定1 找出F集合的所有决定因素 即 左边属性 组合为U12 从U1中找出最小属性组K 使K U则K为候选码 再找出其他候选码 25 考题 1 下列哪一条不属于Armstrong公理系统中的基本推理规则 A 若Y X 则X YB 若X Y 则XZ YZC 若X Y 且Z Y 则X ZD 若X Y 且Y Z 则X ZA自反律B增广律C分解律D传递律C2009 03 26 2 Armstrong公理系统中的增广律的含义 设R是一个关系模式 X Y是U中属性组 若X Y为F所逻辑蕴含 且Z U 则 1 为F所逻辑蕴含XZ ZY2008 093 下列关于部分函数依赖的叙述中 哪条是正确的 A 若X Y 且存在Y的真子集Y X Y 则Y对X部分函数依赖B 若X Y 且存在Y的真子集Y X Y 则Y对X部分函数依赖C 若X Y 且存在X的真子集X X Y 则Y对X部分函数依赖D 若X Y 且存在X的真子集X X Y 则Y对X部分函数依赖C2007 09 27 3 设有关系模式R B C M T A G 根据语义有如下函数依赖集 F B C M T B M C T M A T A B G 关系模式R的码是 1 1 取出函数依赖的左部 B M T C A 2 找出最小组合决定其他属性 M A 28 4 下列关于函数依赖的叙述中 哪一条是不正确的 A 若X Y Y Z 则X ZB 若X Y Y Y 则X Y C 若X Y X X 则X YD 若X X 则X X A传递B分解D自反律C2007 045 下列关于关系模式的码和外码的叙述中 哪些是正确的I主码可以是单个属性 也可以是属性组II外码可以是单个属性 也可以是属性组III一个关系模式的主码与该关系模式的任何一个外码的交一定为空IV一个关系模式的主码与该关系模式的所有外码的并一定包含了该关系模式的所有属性A I和IIB I II和IIIC I II和IVD I III和IVA 29 6 下列关于关系模式的码的叙述中 哪一项是不正确的 A 当候选码多于一个时 选定其中一个作为主码B 主码可以是单个属性 也可以是属性组C 不包含在主码中的属性称为非主属性D 若一个关系模式中的所有属性构成码 则称为全码C不包含在任何码中的属性 30 考点31NF 2NF 3NF BCNF 关系模式的规范化 解决插入 删除和更新异常 尽量消除数据冗余 消除不合适的数据依赖这就要求关系模式应该满足一定的条件关系模式满足不同的条件 称为不同的范式 31 1NF范式如果关系模式R的所有属性都是不可再分解的 则称R属于第一范式 简称1NF 记做R 1NF 满足1NF的关系为规范化的关系 否则为非规范化的关系1NF的模式是关系数据库的最基本要求 不是理想的模式 可能存在部分函数依赖 存在冗余和更新异常 32 例如 学生关系S 学号 姓名 成绩 其中属性成绩又分为英语 数学和语文三个数据项 由于存在属性再分 所以关系模式S不是1NF 即S 1NF例题 下列关于1NF的叙述中 哪条不正确A 满足1NF中的关系可以包含复合属性B 满足1NF中的关系不可以包含复合属性C 满足1NF的关系称为规范化关系 否则称为非规范化关系D 不满足1NF的数据库模式不能称为关系数据库A 33 2NF关系模式R 1NF 且每个非主属性完全依赖于码 则称R属于2NF判断关系是否属于2NF关系SC Sno Cno Grade Sdept F Sno Cno Grade Sno Sdept 判断步骤 1 找出所有候选码 SC只有一个候选码 Sno Cno 2 确定非主属性 Grade Sdept是关系SC的非主属性 3 看非主属性对码的函数依赖情况 Grade完全依赖码 Sdept依赖于Sno 部分依赖码 所以SC不是2NF如何变为2NF 分为两个表 SG Sno Cno Grad 和S Sno Sdept 34 例题判断关系S是否属于2NF范式S SNO NAME AGE DNO DEAN F SNO NAME SNO AGE SNO DNO DNO DEAN 1 找所有候选码 F的左部 SNO DNO 码为最小的属性集 SNO决定其他所有属性 所以SNO为码 2 显然非主属性完全依赖SNO 所以S属于2NF但关系S由于存在传递依赖 仍然存在冗余 更新 插入和删除异常如何消除传递依赖 关系分解SI SNO NAME AGE DNO DEPT DNO DEAN 变为3NF 35 3NF若关系模式R 2NF 并且每个非主属性都不传递依赖于码 则称R属于3NF每个非主属性既不部分依赖码也不传递依赖码不存在非主属性依赖于非主属性 36 考题 1 有关系模式P C S T R G 根据语义F C T ST R TR C SC G 关系模式P的规范化程度最高达到 2004 9A 1NFB 2NFC 3NFD BCNF 候选码 F每个函数依赖左部集合 C S T R ST 为码 SC为码 不存在部分依赖和传递依赖 所以是3NF 不是BCNF2 设有关系模式R A B C 根据语义有如下函数依赖F A B B C A 关系模式R的规范化程度最高达到 2006 4A 1NFB 2NFC 3NFD 4NF码为 B C A C 不存在非主属性 所以最高为3NF 4NF为多值依赖 37 BCNF若关系模式R 1NF 且对于每个非平凡函数依赖X Y X必须包含码 则R属于BCNF其中Y不一定是非主属性判断BCNF 只要决定因素包含码就可以了一个关系只有两个属性 必定为BCNF范式在函数依赖中 BCNF达到最高规范化程度 38 例CSZ CITY ST ZIP F CITY ST ZIP ZIP CITY CSZ是否属于BCNF 1 找码 F的左部 CITY ST ZIP 两两组合 CITY ST CITY ZIP ST ZIP 显示 CITY ST 和 ST ZIP 为码 没有主属性所以CSZ至少为3NF由于ZIP CITY 决定因素不包含码 所以不属于BCNF 39 考题 1 有关系模式P C S T R 根据语义有如下函数依赖集 F C T ST R TR C 1 关系模式P的规范化程度最高达到2008 09A 1NFB 2NFC 3NFD BCNF分析 找码F左部 C S T R 码为 S T S C 非主属性R3NF 2 现将关系模式P分解为两个关系模式P1 C T R P2 C S 那么这个分解A 不具有无损连接性 不保持函数依赖B 具有无损连接性 不保持函数依赖C 不具有无损连接性 保持函数依赖D 具有无损连接性 且保持函数依赖 40 2 有关系模式R S T C D G 根据语义有如下函数依赖集 F S C T C D S C G T C 2008 04 1 关系模式R的候选码A 只有1个 为 S C B 只有1个 为 S T C 有2个 为 S C 和 S T D 有2个 为 S C 和T分析 找码 F的左部 S C T 左部最小组合确定码 S C 单个属性不为码 两个属性组合 S C 为码 S T 为码C 2 关系模式规范化程度最高达到A 1NFB 2NFC 3NFD BCNF由于C D 非主属性局部依赖码 所以为1NF 41 3 有关系模式P A B C D E F G H I J F ABD E AB G B F C J C I G H 关系模式P的码是A A B C B A B D C A C D G D A B C D 分析 F的左部 ABCDG 根据答案 A B C A B D 和 A C D G 都不能决定其他属性 只有 A B C D 才能决定其他属性D 56 关系模式P的规范化程度最高达到A 1NFB 2NFC 3NFD BCNF存在非主属性部分依赖码A 42 考点4多值依赖和4NF 43 多值依赖与第四范式 4NF 例 学校中某一门课程由多个教师讲授 他们使用相同的一套参考书 关系模式Teaching C T B 课程C 教师T和参考书B 44 45 用二维表表示Teaching 46 多值依赖与第四范式 续 Teaching BCNF Teach具有唯一候选码 C T B 即全码Teaching模式中存在的问题 1 数据冗余度大 有多少名任课教师 参考书就要存储多少次 47 一 多值依赖 设R U 是一个属性集U上的一个关系模式 X Y和Z是U的子集 并且Z U X Y 多值依赖X Y成立当且仅当对R的任一关系r r在 X Z 上的每个值对应一组Y的值 这组值仅仅决定于X值而与Z值无关例Teaching C T B 对于C的每一个值 T有一组值与之对应 而不论B取何值 48 多值依赖 续 平凡多值依赖和非平凡的多值依赖若X Y 而Z 则称X Y为平凡的多值依赖否则称X Y为非平凡的多值依赖 49 多值依赖的性质 1 多值依赖具有对称性若X Y 则X Z 其中Z U X Y多值依赖的对称性可以用完全二分图直观地表示出来 2 多值依赖具有传递性若X Y Y Z 则X Z Y 50 多值依赖 续 3 函数依赖是多值依赖的特殊情况 若X Y 则X Y 4 若X Y X Z 则X Y Z 5 若X Y X Z 则X Y Z 6 若X Y X Z 则X Y Z X Z Y 51 多值依赖与函数依赖的区别 1 有效性多值依赖的有效性与属性集的范围有关若X Y在U上成立 则在W XY W U 上一定成立 反之则不然 即X Y在W W U 上成立 在U上并不一定成立多值依赖的定义中不仅涉及属性组X和Y 而且涉及U中其余属性Z 一般地 在R U 上若有X Y在W W U 上成立 则称X Y为R U 的嵌入型多值依赖 52 多值依赖与函数依赖的区别 只要在R U 的任何一个关系r中 元组在X和Y上的值满足定义函数依赖 则函数依赖X Y在任何属性集W XY W U 上成立 53 多值依赖 续 2 若函数依赖X Y在R U 上成立 则对于任何Y Y均有X Y 成立多值依赖X Y若在R U 上成立 不能断言对于任何Y Y有X Y 成立 54 第四范式 4NF 关系模式R 1NF 如果对于R的每个非平凡多值依赖X Y Y X X都含有候选码 则R 4NF X Y 如果R 4NF 则R BCNF不允许有非平凡且非函数依赖的多值依赖允许的是函数依赖 是非平凡多值依赖 注意4NF针对多值依赖 55 第四范式 续 例 Teach C T B 4NF存在非平凡的多值依赖C T 且C不是候选码用投影分解法把Teach分解为如下两个关系模式 CT C T 4NFCB C B 4NFC T C B是平凡多值依赖 56 考题 1 若关系模式R中只包含两个属性 则A R肯定属于2NF 但R不一定属于3NFB R肯定属于3NF 但R不一定属于BCNFC R肯定属于BCNF 但R不一定属于4NFD R肯定属于4NFC4NF为多值依赖2 下列关于函数依赖和多值依赖的叙述中 哪些是不正确的 I 若X Y 则X YII 若X Y 则X YIII 若Y X 则X YIV 若Y X 则X YV 若X Y Y Y 则X Y VI 若X Y Y Y 则X Y A 仅II IV和VB 仅I III和IVC 仅II和VID 仅IV和VI2009 032008 09分析 X Y是X Y的特例Y X 则X Y 则X Y若X Y Y Y 则不能推出X YC 57 3 设U是所有属性的集合 X Y Z都是U的子集 且Z U X Y 下列关于多值依赖的叙述中 哪一条是不正确的 A 若X Y 则X ZB 若X Y 则X YC 若X Y 且Y Y 则X Y D 若Z 则X YC A对称关系B特例D平凡多值依赖2008 09 58 4 如果关系模式R的属性之间不存在非平凡且非函数依赖的多值依赖 对R的规范化程度达到 1 2008 044NF5 设U为所有属性 X Y Z为属性集 Z U X Y 下列关于平凡多值依赖的叙述中 哪条是正确的 A 若X Y Z 则称X Y为平凡多值依赖B 若X Y Z 则称X Y为平凡多值依赖C 若X Y X Y 则X Y为平凡的多值依赖D 若X Y X Z 则称X Y为平凡的多值依赖A2007 09 59 6 下列关于关系模式规范化的叙述中 哪条不正确 A 若R 4NF 则必然有R 3NFB 若R 3NF 则必然R 2NFC 若R 2NF 则必然有R 1NFD 若R 1NF 则必然有R BCNFD2007 09 60 考点5关系模式分解 在关系模式规范化过程中 需要对关系模式进行分解 要求分解后的模式与原模式等价常用的等价标准有要求分解具有无损连接性的和分解是保持函数依赖的两种 61 无损连接标准设关系模式R分解为关系模式R1 R2 Rn 若对于R的任何一个可能r 都有r r1 r2 rn 即r在R1 R2 Rn上的投影的自然连接等于r 则称关系R的分解具有无损连接性的简单的说 就是分解的关系的自然连接等于原关系 62 保存函数依赖标准设关系模式R分解为关系模式R1 R2 Rn 若对于F F1 F2 Fn 则关系R的这个分解为保持函数依赖的简单的说 原关系F蕴含的所有函数依赖等于分解后关系蕴含的所有函数依赖 63 掌握关系模式分析的几个事实关系模式可以具有无损连接 可能保持函数依赖 也可能既有无损连接又保持函数依赖 1 若要求分解具有无损连接性 那么模式分解一定可以达到BCNF 2 若要求分解保持函数依赖 那么模式分解可以达到3NF 但不一定达到BCNF 3 若要求分解既具有无损连接性 又保持函数依赖 则模式分解可以达到3NF 不一定达到BCNF 64 考题 1 下列关于模式分解的叙述中 哪一 些 条是正确的 I 若一个模式分解具有无损连接性 则该分解一定保持函数依赖II 若一个模式分解保持函数依赖 则该分解一定具有无损连接性III 模式分解可以做到既具有无损连接性 又保持函数依赖IV 模式分解不可能做到既具有无损连接性 又保持函数依赖A 仅I和IIIB 仅II和IVC 仅IIID 仅IVC2009 03 65 考题 1 有关系模式P C S T R 根据语义有如下函数依赖集 F C T ST R TR C 现将关系模式P分解为两个关系模式P1 C T R P2 C S 那么这个分解A 不具有无损连接性 不保持函数依赖B 具有无损连接性 不保持函数依赖C 不具有无损连接性 保持函数依赖D 具有无损连接性 且保持函数依赖 不具有无损连接 不保持函数依赖 ST R 66 2 下列关于模式分解的叙述中 哪一条是不正确的 A 若一个模式分解保持函数依赖 则该分解一定具有无损连接性B 若要求分解保持函数依赖 那么模式分解可以达到3NF 但不一定能达到BCNFC 若要求分解既具有无损连接性 又保持函数依赖 则模式分解可以达到3NF 但不一定能达到BCNFD 若要求分解具有无损连接性 那么模式分解一定可以达到BCNFA3 关系模式分解的等价性标准主要有两个 分别为分解具有 1 和分解保持函数依赖 无损连接 67 将关系模式R分解为关系模式R1 R2 Rn 若对于R的任何一个可能r 都有r r1 r2 rn 即r在R1 R2 Rn上的投影的自然连接等于r 则称关系R的分解 1 具有无损连接性2007 09 68 考点6数据库分析与设计 数据库设计的6个阶段 需求阶段 信息需求和处理需求DFD数据流图 概念结构阶段 E R图的建立 逻辑结构设计物理结构设计数据库实施和运行维护 69 ren 概念结构阶段E R方法的步骤 1 设计局部E R图 2 设计全局E R图解决属性冲突 结构冲突 命名冲突 3 E R图的优化 70 逻辑结构设计E R模型向关系模型转换 1 实体转换为关系 属性转换为关系的属性 实体码转换为关系的码 2 1 1的联系 可以转换为一个关系 也可以与联系的任意一端实体关系模式合并 3 1 n的联系可以转换为一个独立关系 码为n端实体码 也可以与联系的n端实体关系模式合并 加入1端实体码 4 m n联系转换为一个关系模式 码为各实体码组合 5 3个或3个以上的多元联系转换为一个关系模式 模式的码由联系的实体码组成 71 E R图向关系模型转换 转换原则 实体型的属性 关系的属性实体型的码 关系的码 学生实体可以转换为如下关系模式 学生 学号 姓名 性别 年龄 所在系 一个实体型转换为一个关系模式 课程实体可以转换为如下关系模式 课程 课程号 课程名 先修课 学分 72 E R图向关系模型的转换 一个m n联系转换为一个关系模式 关系的属性 与该联系相连的各实体的码以及联系本身的属性关系的码 各实体码的组合 例 选课 联系是一个m n联系 可以将它转换为如下关系模式 其中学号与课程号为关系的组合码 选课 学号 课程号 成绩 73 E R图向关系模型的转换 一个1 n联系转化为关系模式时有2种方法1 可以转换为一个独立的关系模式 关系的属性 与该联系相连的各实体的码以及联系本身的属性关系的码 n端实体的码2 可以与n端对应的关系模式合并合并后关系的属性 在n端关系属性中加入1端关系的码和联系本身的属性合并后的码 n端实体的码 74 1 N联系转换实例 某大学管理系统中的实体 系 和 教师 之间存在着1 N的联系 其E R图如图 75 1 N联系转换实例 系实体 1端 的关系模式 系 系编号 系名 电话 系主任 教师实体 N端 的关系模式 教师 教师编号 姓名 性别 年龄 职称 将聘用 1 N联系 转换为一个独立的关系模式 聘用 教师编号 系编号 聘用日期 将聘用与N端 教师实体 关系模式合并 教师 教师编号 姓名 性别 年龄 职称 系编号 聘用日期 76 E R图向关系模型的转换