第5章数据库设计理论 引言 如何设计数据库模式 设计一个好的关系数据库模式（概念模式）——逻辑设计 凭经验设计？ 什么是好的关系数据库模式？ 好的关系数据库模式应该包括多少关系模式？☆ 每个关系模式应该包含哪些属性？ 借助数学工具规定设计的理论和方法——规范化 学生管理数据库设计实例 事实情况 一个系有若干名学生 一个学生只属于一个系 一个系只有一名系主任 一个学生可以选修多门课程 一门课程可由多名学生选修 每个学生学了每门课程有一个成绩 属性 学生SNO 系DN 系主任DM 课程CN 成绩G 单一关系模式☆ UN(SNO,DN,DM,CN,G) 关系键、候选键、主键 (SNO,CN) 存在的问题 插入异常 删除异常 修改异常 冗余太大 模式分解☆ S(SNO,DN) D(DN,DM) SC(SNO,CN,G) 关系规范化 用几个结构简单的关系模式取代结构复杂的关系模式 关系模式的分解 关系数据库模式：“不好”→“好” 数据依赖 关系模式中的数据依赖 关系＝关系模式＋关系实例 关系是属性的笛卡尔积的一个子集 描述关系模式的五元组R(U,D,DOM,F) F：属性间数据的依赖关系集合 描述关系模式的简化三元组R(U,F) 数据依赖的概念 什么是数据依赖 通过一个关系中属性间的相等与否体现出来的数据间的相互关系 是现实世界属性间相互联系的抽象 是数据内在的性质，是语义的体现 有哪些数据依赖 函数依赖 多值依赖 …… 怎么个函数依赖 学生关系Student(学号Sno,姓名Sn,所在系Dn) 一旦学号确定，姓名和所在系也就唯一地确定下来了 属性间的这种依赖关系类似于数学中的函数 Sno函数决定Sn和Dn Sn和Dn函数依赖于Sno 记作Sno→Sn，Sno→Dn R(U,F)的实例☆ U={Sno,Dn,Dm,Cn,G} F={Sno→Dn,Dn→Dm,(Sno,Cn)→G} 与函数依赖相关的概念 函数依赖 定义 X,Y是R的两个属性集合(子集) 当任何时刻R中的任意两个元组中的X属性值相同时，则它们的Y属性值也相同 R中不存在两个元组，它们在X上的属性值相同，而在Y上的属性值不同 X函数决定Y Y函数依赖于X 记作X→Y X叫做决定因素(决定属性集) 例子 S(SNO,SN,AGE,SEX,DEPT) SNO决定函数(SN,AGE,SEX,DEPT) 函数(SN,AGE,SEX,DEPT)依赖于SNO SNO→(SN,AGE,SEX,DEPT) 说明 函数依赖指R的所有关系实例均要满足的约束条件 如果X→Y,Y→X,则X←→Y（XY） 若Y不函数依赖于X，则记为X !→ Y（X  Y） 函数依赖与属性间的联系类型有关 一对一联系：X←→Y 多对一联系：X→Y 多对多联系：不存在依赖关系 可从属性间的联系类型来分析属性间的函数依赖 平凡函数依赖 当属性集合Y是属性集合X的子集时(Y(X) 存在函数依赖X→Y 一组属性函数决定它的所有子集 非平凡函数依赖 Y不是X的子集(YX) 但X→Y 完全函数依赖 定义 X’是X的真子集 X必须是组合属性 X→Y 对每一个X’都有X’ !→ Y 则函数X完全函数决定Y Y完全(Full)函数依赖于X X f→ Y（XY） 例子 SC(SNO,CNO,G) (SNO,CNO)→G SNO !→ G CNO !→ G (SNO,CNO) f→ G 部分函数依赖 定义 X’是X的真子集 X→Y 对每一个X’都有X’→Y Y不完全依赖于X Y对X的函数依赖是部分(Part)的 XY（X p→ Y） 例子 UN(SNO,CN,G,DN,DM) (SNO,CN)→DN SNO→DN (SNO,CN) p→ DN 传递函数依赖 定义 X,Y,Z是R互不相同的属性集合 X→Y，Y !→ X Y→Z 则称属性集合Z传递(Transfer)函数依赖于X X t→ Z（XZ） 例子1 UN(SNO,CN,G,DN,DM) SNO→DN DN !→ SNO DN→DM SNO t→ DM 反例 若X→Y，Y→X 则X←→Y 若Y→Z 则X→Z 直接函数依赖 例子2 S(SNO,SN,DN) SNO←→SN SN→DN SNO→DN 小结 函数依赖是完整性约束的一种特殊形式 函数依赖分为 完全函数依赖 部分函数依赖 传递函数依赖 函数依赖是规范化理论的依据 函数依赖是规范化程度的准则 关系键(码)的形式化定义 定义 关系模式R(A1,A2,…,An) X是属性的集合，X({A1,A2,…,An} 若X f→ {A1,A2,…,An}，即全体属性完全函数依赖于X 则X是R的候选关系键(候选键) 从多个候选键中选中一个作为主键 关系键的性质 每个关系必有键 主键具有标识元组的唯一性。主键是用来唯一标识关系中的元组的 关系键具有最小性。若抽去主键中任意一属性，则主键就失去标识的唯一性 主键中的任一属性值不能取空 属性键 全键 整个属性组合是关系键 主属性(键属性) 包含在任何一个候选键中的属性 非主属性(非键属性) 不包含在任何候选键中的属性 例子 S(SNO,SN,AGE,SEX,DEPT) SNO,SN是主属性 AGE,SEX,DEPT是非主属性 SC(SNO,CNO,G) SNO,CNO是主属性 G是非主属性 PWA(演奏者P,作品W,听众A) 之间为多对多关系，无函数依赖 全属性集(P,W,A)是键、主键、全键 P,W,A都是主属性 规范化 范式(NF, Normal Formula) 定义 符合某种级别的关系模式的集合 如果一个关系满足某个特定的约束值，则称它属于某种特定的范式 各级范式的要求 第一范式：关系满足只包含原子值的约束 第二范式：每个非主属性都完全函数依赖于R的每个键 第三范式：每个非主属性都不传递依赖于R的任何键 BC范式：R中每个决定因数都是候选键 第四范式：对于R的每个非平凡多值依赖X→→Y，X都含有候选码 第五范式：投影—连接范式 各级范式的关系 5NF(4NF(BCNF(3NF(2NF(1NF 如果关系满足某个范式要求，也会满足级别较低的所有范式的要求 较高层次的范式比较低层次的范式具有更合乎要求 规范化 将一个低一级范式的关系模式通过投影运算转化为若干个高一级范式的关系模式的集合的过程 第一范式(1NF) 定义 若关系模式R的所有属性都是不可分的基本数据项 则R(1NF 说明 1NF是关系模式的最起码要求 若R(1NF，则R不是关系数据库 例子 UN(Sno,Cn,Dn,Dm,G) (Sno,Cn)为关系键、候选键、主键 函数依赖关系 (SNO,CN) f→ G Sno→Dn (SNO,CN) p→ DN Sno→Dm (SNO,CN) p→ DM Dn→Dm Sno t→ Dm 存在的问题 插入异常 删除异常 冗余太大 修改异常 第二范式(2NF) 定义 在关系模式R(1NF的基础上 且每个非主属性都完全依赖于R的每个关系键 则R(2NF 例子 S(SNO,SN,AGE,SEX,DEPT)(1NF 如果姓名SN无重名 ∵SNO→(SN,AGE,SEX,DEPT) SN→(SNO,AGE,SEX,DEPT) SNO,SN分别为候选键 AGE,SEX,DEPT是非主属性 AGE,SEX,DEPT完全依赖于每个键 ∴S(2NF 注意 如果关系R的全体属性都是R的主属性，或者R的所有键仅含有一个属性，那么R(2NF。Why？ 从1NF中消除非主属性对键的部分函数依赖，则可获得2NF关系 2NF的规范化 设定R(A1,A2,…,An)(2NF 意味着{A1,A2,…,An}含有不相交的子集X和Y，X是键属性，Y是非键属性，且X p→ Y Z表示R中既不属于X又不属于Y的那些属性集合，则R(X,Y,Z) ∵X p→ Y,将X表示为X’X” X’→Y 可用两个投影R(X’,Y)和R(X,Z)代替R(X,Y,Z)，无损连接性 R(X’,Y)(2NF 若R(X,Z)(2NF,对其再进行投影分解 例子 UN(SNO,CN,G,DN,DM)(1NF (SNO,CN)是唯一主键 非主属性对键的函数依赖有 (SNO,CN) f→ G (SNO,CN) p→ DN ∵SNO→DN (SNO,CN) p→ DM ∵SNO t→ DM ∵存在非主属性对键的部分依赖关系，∴UN(2NF 采用投影分解运算来提高关系模式的范式等级 投影成两个关系 SC(SNO,CN,G) SD(SNO,DN,DM) SC(2NF,SD(2NF 三个问题的解决 插入异常有所改善 删除异常仍然存在 冗余得到改善 第三范式(3NF) 定义 若R(2NF 且每个非主属性都不传递依赖于R的任何键 则R(3NF 说明 每个非主属性既不部分依赖，也不传递依赖于R的任何键 从1NF→2NF：消除非主属性对键的部分函数依赖 从2NF→3NF：消除非主属性对键的传递函数依赖 3NF的规范化 R(X,Y,Z,W) W包括既不属于X,Y，也不属于Z的其余属性 若X→Y,Y !→ X,Y→Z，则X t→ Z 投影为R(X,Y,W)和R(Y,Z) 则R(Y,Z)(3NF 若R(X,Y,W)还不属于3NF，继续进行投影分解 例子 UN(SNO,CN,G,DN,DM) SC(SNO,CN,G) (2NF (3NF SD(SNO,DN,DM) (2NF (3NF 方法1 投影分解☆ SD(SNO,DN) (3NF D(DN,DM) (3NF SC(SNO,CN,G) (3NF 三种弊病的解决程度：均解决 分析 保持了原来的两个完全依赖关系SNO→DN,DN→DM（函数依赖保持性） 具有无损连接性 方法2 投影分解 SD(SNO,DN) SM(SNO,DM) SC(SNO,CN,G) 三种弊病的解决程度：均存在 分析 分解可恢复 具有无损连接性 只用了原先的一个完全依赖SNO→DN 没用另一个完全依赖DN→DM，未保持原函数依赖 却使用了原隐含的传递依赖SNO→DM 方法3 投影分解 SM(SNO,DM) D(DN,DM) SC(SNO,CN,G) 三种弊病的解决程度：不是无损分解 分析 分解为不可恢复 只用了一个完全依赖DN→DM 未用另一个完全依赖SNO→DN 却使用了传递依赖SNO→DM 3NF分解小结 既具有无损连接性 又保持函数依赖性 Boyce-Codd范式(BCNF) 3NF的不完善性 3NF排除了非主属性对键的部分函数依赖和传递函数依赖 把能够分离的属性尽可能地分解为单独的关系 但3NF没有限制有些主属性对键具有的函数依赖 例子 SCG(SNO,SN,CN,G) 假设SN没有重名 关系键 (SNO,CN) (SN,CN) 函数依赖 SNO←→SN SNO→SN SN→SNO (SNO,CN)→G (SN,CN)→G 主属性 SNO,SN,CN 非主属性 G 非主属性G不传递依赖于任何键 ∴SCG(3NF 但存在主属性对键的部分依赖 (SNO,CN) p→ SN ∵SNO→SN (SNO,CN) p→ SNO ∵SN→SNO ∴SCG有冗余，会修改异常 改SNO BCNF定义 若R(1NF 如果对于R的每个函数依赖X→Y，X必含有候选键 R中的每个(决定因素)决定属性集X都是候选键 则R(BCNF 说明 在满足BCNF的关系中，除候选键之外没有其他的决定因素(决定属性集) 满足BCNF的关系将消除任何属性(主属性或非主属性)对键的部分依赖或传递依赖 属于BCNF的关系必然属于3NF，但属于3NF的关系却不一定属于BCNF BCNF的规范化 若R(BCNF R表示为R(X,Y,Z) X,Y,Z是属性的集合，X→Y 用R的投影R(X,Y)和R(X,Z)代替R(X,Y,Z) 最坏可用二元关系结束投影 例子1 SCG(SNO,SN,CN,G) 关系键 (SNO,CN) (SN,CN) 主属性 SNO,SN,CN 非主属性 G 函数依赖关系 SN←→SNO (SNO,CN)→G (SN,CN)→G (SNO,CN) p→ SNO (SNO,CN) p→ SN SCG(3NF STC(BCNF 存在异常 规范化 S(SNO,SN) SC(SNO,CN,G) 或 SC(SN,CN,G) S(BCNF SC(BCNF 消除异常 例子2 STC(STUDENT,TEACHER,COURSE) 元组语义 每个教师只教一门课程，每门课程可由若干名教师任教 某个学生选定某门课，就对应一个固定教师 特定的学生由特定的教师讲授特定的课程 STC关系实例☆ 函数依赖关系 T→C (S,C)→T (S,T)→C 主属性 S,C,T 非主属性 (None) STC(3NF STC(BCNF 存在异常 规范化 ST(S,T)(BCNF TC(T,C)(BCNF 消除异常 BCNF小结 BCNF在概念上要比3NF简单 3NF涉及到：主键、传递函数、完全依赖 BCNF没有涉及 3NF规范化的过程：1NF→2NF→3NF BCNF规范化的过程：1NF→BCNF 3NF和BCNF是在函数依赖条件下对关系模式分解所能达到的分离程度所进行的测度 若数据库模式中的所有关系模式都属于BCNF，就已经彻底分离了函数依赖 3NF可能存在主属性对键的部分依赖和传递依赖 当关系具有几个（重叠）的候选键时，应3NF→BCNF 多值依赖与第四范式 第五范式 关系模式的规范化 规范化小结 对关系的最基本要求是满足1NF，但存在多种弊病，应该进行规范化 规范化的基本思想是逐步消除数据依赖中的不合适部分，使得数据库模式中的各个关系模式达到某种程度的分离 让一个关系只描述一个实体或实体间的联系 规范化实质上是概念的单一化，用一个关系表示一个实体 关系模式规范化的步骤☆ 对1NF关系进行投影，消去非主属性对键的部分函数依赖，产生一组2NF关系 对2NF关系进行投影，消去非主属性对键的传递函数依赖，产生一组3NF关系 对3NF关系进行投影，消去决定因素不是键的函数依赖，产生一组BCNF关系 消除主属性对键的部分依赖 消除主属性对键的传递依赖 消除主属性对非主属性的依赖 关系模式的分解 规范化准则：取原始关系的投影，消去决定因素不是候选键的函数依赖 通过投影分解运算，把低一级范式的关系模式分离为若干个高一级范式的关系模式 规范化的投影分解不是唯一的 要求分解“既保持函数依赖”，又具有“无损连接性” 例子1 SD(SNO,DEPT,LOC) 存在函数依赖 SNO→DEPT DEPT→LOC SNO t→ LOC 关系键 SNO 主属性 SNO 非主属性 DEPT,LOC SD(2NF SD(3NF 分解方法1 关系模式 S(SNO) D(DEPT) L(LOC) (5NF 丢失了联系 分解方法2 关系模式 SL(SNO,LOC) DL(DEPT,LOC) 分析 隐含传递函数依赖 非无损连接性 分解方法3 关系模式 SD(SNO,DEPT) SL(SNO,LOC) 分析 无损连接性 隐含传递函数依赖 分解方法4 关系模式 SD(SNO,DEPT) DL(DEPT,LOC) 分析 无损连接性 保持了函数依赖 例子2 学生成绩管理数据库 学生成绩登记表☆ 函数依赖关系 学号→(姓名，性别，专业，年级) 课号→(课名，学分，学时，师号) (学号，课号)→成绩 师号→教师 UN(学号,姓名,性别,专业,年级,课程成绩)(1NF 消去重复组 学生(学号,姓名,性别,专业,年级,课号,课名,学分,学时,教师,师号,成绩) 关键字(学号,课号) (1NF 消去部分函数依赖 部分依赖 (学号,课号) p→ (姓名,性别,专业,年级) (学号,课号) p→ (课名,学分,学时,师号,教师) 消去部分依赖 (学号)→(姓名,性别,专业,年级) (课号)→(课名,学分,学时,师号,教师) (学号,课号)→成绩 投影成三个子关系模式 学生(学号,姓名,性别,专业,年级) 课程(课号,课名,学分,学时,师号,教师) 成绩(学号,课号,成绩) (2NF 消去传递函数依赖 传递依赖 课号→师号 师号→教师 课号 t→ 教师 消去传递依赖 (课号)→(课名,学分,学时,师号) (师号→教师) 投影成两个子关系模式 课程(课号,课名,学分,学时,师号) 教师(师号,教师) (3NF 最后投影结果 学生(学号,姓名,性别,专业,年级) 课程(课号,课名,学分,学时,师号) 教师(师号,教师) 成绩(学号,课号,成绩)