数据库课件6db关系数据库理论.ppt

第六章 关系数据理论,任何一个二目关系模式R（A，B）一定属于BCNF吗？ 一个全是主属性的关系模式一定可以达到第几范式？ 一个全码的关系模式一定可以达到第几范式？ 关系模式R（A，B，C），有函数依赖集FAB C,BC A,则最高达到第几范式？,应用系统设计的共同问题： 如何把现实世界表达成合适的数据库模式,即设计数据库的逻辑结构 以关系模型为背景讨论： 针对一个具体问题,如何构造合适的数据模式? 构造几个关系? 每个关系由哪些属性组成? 等等。 这些直接决定数据库的运行效率,关系DB设计理论（规范化理论）研究的是关系模式中各属性之间的数据依赖关系及其对关系模式性能的影响，探讨“好”的关系模式应该具备的性质，以及达到“好”的关系模式的设计算法。 规范化理论 -解决如何设计一个适合的关系数据库系统的问题 判断关系模式优劣的理论标准 帮助我们预测可能出现的问题 关系模型有严格的数学理论基础 可以向其它模型转换 具有普遍理论意义,关系模式的形式化定义,关系模式由五部分组成，即它是一个五元组： R(U, D, DOM, F) R： 关系名 U： 组成该关系的属性名集合 D： 属性组U中属性所来自的域 DOM：属性向域的映象集合 F： 属性间数据的依赖关系集合,6.1问题的提出,关系的规模 1.一个关系中信息量与关系的列数成正比,2.关系操作 高效： 关系运算 、 ；单表 低效： 、 ；多表,增加关系中的列数是不是可以减少联接操作，是不是属性越多越好？,3. 举例1： Class (clno, dept, major) S (sno, sname,sex,birthdate,clno,address) C (cno, cname,score,pcno) SC (sno, cno, p1 , p2 ,p3) max(clno, dept, major sno, sname,sex, birthdate, address cno, cname, score, pcno , p1 , p2 , p3),共18个属性,共15个属性,数据冗余 设每个字段10 Byte S： 有 50000个记录 50000603106 Byte Class：有1000个记录 1000303104 Byte C：有500个记录 500402104 Byte SC：有5000040个记录 500004050 1108 Byte 共：31063104 2104 1108 = 10305104 1.0305108 max： 5000040150300106 3108 同一数据在DB中多次无控制冗余存放造成的。,Max表存在的问题,Max表存在的问题（续）,插入异常：合法数据无法入库。 关系 max码：（ sno ,cno ） 一个新生入校时，已是学生，但没有选课，这时不能入库，且查询学生时无法查到。 删除异常：删除操作造成有效数据的丢失。 如：有门课程只有一人选修，此人毕业时，将删除其所有选课程的信息。这时查询课程信息时将无法得到该课程。 修改异常：一个数据的修改，引起数据库中多处数据的更新。 将“数据库原理”课程的名称改为“数据库系统基础”时，将需修改多个记录 这些异常现象在有四个表时不会发生，所以数据库结构不是属性越多越好。,注意：,插入异常： 该插入的数据未插入 不该插入的数据被插入 删除异常： 该删除的数据未删除 不该删除的数据被删除,4 举例2 考虑为管理职工的工资信息而设计一个关系模式，该模式有什么问题？,插入异常： 如果没有职工具有8级工资，则8级工资的工资数额就难以插入。 删除异常： 如果仅有职工赵明具有4级工资，如果将赵明删除，则有关4级工资的工资数额信息也随之删除了。 数据冗余： 职工很多，工资级别有限，每一级别的工资数额反复存储多次。 更新异常： 如果将5级工资的工资数额调为620，则需要找到每个具有5级工资的职工，逐一修改。,结论： 该关系模式不是一个好的模式。 “好”的模式： 不会发生插入异常、删除异常、更新异常， 数据冗余应尽可能少。 原因：由存在于模式中的某些数据依赖引起的: 工号姓名 工号级别 级别工资,Max表存在问题原因,分析可知：这与数据库文件的结构中各属性间的关系 有关。 sno 决定 sname , sex, clno, address, dept, major Clno 决定 dept,major Cno 决定 cname, score, pcno sno,cno 决定 p1 p2 p3,愿望,体现客观世界的信息 一个好的关系模式应冗余尽可能少； 一个好的关系模式应避免插入、删除异常； 异常原因是关系模式中存在不合适的属性间联系； 解决策略是消去不合适联系 ； 方法采用模式分解消去不合适的属性间联系。,负面影响 ：需要通过表连接做相关信息查询，系统开销大！,6.2 规范化,规范化理论正是用来改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余问题，把不好的关系模式变成好的关系模式。,数据依赖,数据依赖 函数依赖(Functional Dependency,FD) 多值依赖(Multivalued Dependency,MVD) X 数据依赖是针对数据模式，而不是特定的实例 是属性间的关联 是一种约束,假设给定 X 属性的值，就知道 Y的值，那么 X 函数决定 Y，记作XY,函数依赖,函数依赖的定义： 若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等， 而在Y上的属性值不等， 则称 “X函数确定Y” 或 “Y函数依赖于X”，记作XY。 称X为决定因素。 若Y不函数依赖于X, 则记为XY。,函数依赖,例如： SDC（SNO(学号),Sname(姓名),Sage(年龄),Sdept(系别)，Dean(系主任)，CNO（课程号），Grade（成绩） SDC中的函数依赖： SNO Sname SNO Sage SNOSDEPT，SDEPT DEAN, （SNO,CNO） GRADE,说明,函数依赖不是指关系模式R的某个或某些关系实例满足的约束条件，而是指R的所有关系实例均要满足的约束条件。 函数依赖是语义范畴的概念。只能根据数据的语义来确定函数依赖。 例如“姓名年龄”这个函数依赖只有在不允许有同名人的条件下成立 函数依赖反映了一种语义完整性约束,说明（续）,函数依赖与属性间的联系类型有关,关系内属性间的联系 11 （one to one）关系 一一对应关系：y=f(x) 单值函数 例：sno: sname 1：1（Students） 一个学号只对应一个学生(S) 1：n （one to many）一对多的关系 clno:sno 1：n 一个班级内对应多个学生(S) m：n（many to many）多对多关系 在sc 中 sno:cno m：n 多对多的关系 （SC）,根据函数依赖的定义，可找出下面规律： 在一个关系模式中，如属性X，Y有1：1联系， 则存在函数依赖XY、YX，可记作XY X、Y是1：m联系，则存在YX，但XY X、Y是n:m联系，则X、Y之间不存在任何函数依赖,说明（续）,函数依赖,函数依赖的导出 通过关系中各属性的实际意义直接定义 通过Amstrong公理推导 通过有代表性的关系值反推,函数依赖举例,检验：AC？CA？ABD？ 辨识： 模式上成立的依赖：依赖在模式的所有关系实例上都成立,函数依赖练习,找出上面关系可能满足的函数依赖,AB，AC，BC,A(B，C)，(A，B)C,关系模式上的函数依赖是语义范畴的概念，我们只能根据语义来确定函数依赖！,函数依赖的分类,平凡的函数依赖与非平凡的函数依赖 如果XY，且YX，则称XY为平凡的函数依赖。 即：如果对于函数依赖 A1A2An Ai， Ai是A中的一个，则该函数依赖就是平凡的函数依赖 SNOSNO是平凡的函数依赖 如果XY，且YX，则称XY为非平凡的函数依赖。 如： snosname，sno sname 即：B中至少有一个属性不在A中 说明：对任何R，一些平凡函数依赖总是成立的。,函数依赖的分类,完全函数依赖与部分函数依赖 在关系模式R(U)中，如果XY，并且对于X的任何一个真子集X，都有 X Y, 则称Y完全函数依赖于X，记作X F Y。 若XY，但Y不完全函数依赖于X，则称Y部分函数依赖于X，记作X P Y。,范例,关系模式SDC（SNO ，Sname ，Sage,SDept，DEAN，CNO,GRADE） 主码：（SNo，CNo） 函数依赖： (SNo，CNo） Grade Sno SName，(Sno，Cno) SName Sno SDept，（Sno，Cno） SDept SDept DEAN,注意： 只有当决定因素是组合属性时，讨论部分依赖才有意义,传递函数依赖与直接函数依赖 在R(U)中,如果XY,(YX),YX,YZ,则称Z对X传递函数依赖,记做：,提问：找出SDC（SNO,Sname,Sage,SDept，DEAN，CNO，GRADE）中的传递函数依赖。,提问：找出关系模式 (工号、姓名、级别、工资)中的 传递依赖。,说明：加上yx，是避免 x y，否则x z。,直接,x z,函数依赖的分类,传递,设有关系模式R（职工名，项目名，工资，部门名，部门经理），若规定每位职工可参加多个项目，各领一份工资；每个项目只属于一个部门管理；每个部门只有一个经理。 思考：关系模式R的基本函数依赖？ （项目，职工）工资 项目部门 部门经理,6.2.2 码,码 (形式化定义) 定义：设有关系模式R(U，F)，X为U的子集， 若X U，则X为R的一个候选码(Candidate Key)。 最简单情况单个属性为码； 最极端情况全部属性为码，称为全码。,f,码,定义 主码：若R(U , F)有多个候选码，则可以从中选定一个作为R的主码。 主属性：包含在每一个候选码中的属性，称作主属性。 非主属性：不包含在每一个候选码中的属性，称作非主属性。 全码：关系模式的码由整个属性组构成。,范例,找出下面关系模式的主码： SDC(SNO , SNAME , Sage,SDEPT , Dean , CNO , GRADE) 函数依赖：,异常的原因 数据依赖的约束 解决方法 数据库设计的规范化分解 范式是对关系的不同数据依赖程度的要求，是规范化过程中为不同程度的规范化要求而设立的不同标准。,6.2.3范式(Normal Forms),规范化 各种不同的范式都是以对关系模式的属性间允许的数据依赖加以限制的形式表示的。 一个关系满足某个范式所规定的一系列条件时,它就属于该范式。 可以用规范化要求来设计数据库。 也可以用来验证设计结果的合理性，用其指导优化过程。 1NF2NF3NFBCNF4NF,范式(Normal Forms),当且仅当一个关系R（U）中，每一个元组的每一个属性只含有一个值（不可再分的基本数据元素，其原子值）时，该关系属于第一范式。即不能以集合、序列等作为属性值。 要求属性是原子的。,范式第一范式(1NF),关系模型中的关系模式至少是 1NF,下面R为非1NF（非规范关系模式）,转换 将非1NF1NF 去掉嵌套属性上层 R 重写行交叉处的值,1NF(),分量是否需要再分，与具体应用有关。如果用到值的一部分，则需要进一步分割。 如果只是查询出生日期，则它满足1NF。 如果查询两人生日是否相同，则只比较月、日，需要将生日分解，就不满足1NF。,1NF,关系模式SDC(SNO , SNAME , SDEPT , Dean , CNO , GRADE) 码：（SNO，CNO）,1NF,关系模式SDC1NF，但它有如下不良特性：,插入异常：若学生没有选课，则他的个人信息及所在系的信息就无法插入。,删除异常：若删除学生的选课信息，则有关他的个人信息及所在系的信息也随之删除了。,更新异常：如果学生转系，若他选修了k门课，则需要修改k条记录。,数据冗余：如果一个学生选修了k门课，则有关 他的所在系的信息重复。,对于关系R，若R1NF，且每一个非主属性完全函数依赖于码，则R2NF。 不能部分依赖于码 例：关系模式SDC(SNO , SNAME , SDEPT , Dean , CNO , GRADE) SDC2NF？ 步骤：先根据函数依赖确定候选码； 再观察每个非主属性是否完全依赖于码。 SNAME，SDEPT，DEAN 部分依赖于码(SNO，CNO) SDC2NF,6.2.4第二范式(2NF),2NF,改造 1.分解：非主属性有两种，一种完全依赖于码，一种部分依赖于码。将SDC分解为： 学生与课程的联系：SC(SNO , CNO , Grade) 学生实体：SDM(SNO , SNAME , SDEPT , DEAN) 2.分析分解后的函数依赖： SC： 2NF SDM：SNOSNAME，SNOSDEPT， SDEPT DEAN，SNODEAN,冗余减少 如果一个学生选修了k门课，则有关他的所在系的信息不重复 避免了原修改麻烦 如果某学生转系，若他选修了k门课，则只需要修改SDM中一条记录。 避免了原插入异常 若学生没有选课，则他的个人信息及所在系的信息可插入到SDM 避免了原删除异常 删除学生的选课信息（SC），则有关他的个人信息及所在系的信息（SDM）不随之删除,效果:解决了部分插入异常和更新异常,2NF,快速热身 关系模式R（A，B，C，D），码为AB，给出它的一个函数依赖集，使得R属于1NF而不属于2NF。,2NF,S_L_C(SNO,SDEPT,SLOC,CNO,Grade),每个系的同学住在一处 该模式属于2NF吗？,Sno,Cno,Grade,Sdept,Sloc,SLC不是一个好的关系模式,插入异常 假设Sno95102，SdeptIS，SlocN的学生还未选课，因课程号是主属性，因此该学生的信息无法插入SLC。 删除异常 假定某个学生本来只选修了3号课程这一门课。现在因身体不适，他连3号课程也不选修了。因课程号是主属性，此操作将导致该学生信息的整个元组都要删除。,SLC不是一个好的关系模式,数据冗余度大 如果一个学生选修了10门课程，那么他的Sdept和Sloc值就要重复存储了10次。 修改复杂 例如学生转系，在修改此学生元组的Sdept值的同时，还可能需要修改住处（Sloc）。如果这个学生选修了K门课，则必须无遗漏地修改K个元组中全部Sdept、Sloc信息。,模式分解 SC(SNO,CNO,G) S_L(SNO,SDEPT,SLOC),分解后的函数依赖图,2NF,结论： （1）从1NF关系中消除非主属性对码的部分函数依赖得到2NF （2）如果关系R的主码是单属性或全体属性均为主属性，则R属于2NF Q:任何二元关系，是否一定属于2NF？,2NF,分析SDM(SNO , SNAME , SDEPT , DEAN) 不良特性 插入异常：如果系中没有学生，则有关系的信息就无法插入SDM 删除异常：如果学生全部毕业了，则在删除学生信息的同时有关系的信息也随之删除了 更新异常：如果学生转系，不但要修改SDEPT，还要修改Dean，如果换系主任，则该系每个学生元组都要做相应修改 数据冗余：每个学生都存储了所在系的系主任的信息 说明:若R2NF，则R仍然具有增、删、改的异常！,对于关系R，若R2NF，且每个非主属性都不传递依赖于码，则R3NF。 关系模式R中，若不存在这样的码X，属性组Y及非主属性Z(Z Y)，使得下式成立， XY , Y X ，YZ 则称R3NF,6.2.5第三范式(3NF),非主属性对候选码的传递fd。 候选码：SNO 非主属性： SNAME , SDEPT , DEAN fd：SNOSNAME，SNOSDEPT， SDEPT DEAN ， SDEPT SNO； SNO DEAN,传递,SDM的异常原因及策略,SDM 3NF,3NF规范化投影分解,1.消去非主属性对码的传递fd策略是使中间“传递者”不能传递,2.将SDM(SNO,SNAME,SAGE，SDEPT,DEAN)分解为： 学生实体：STUDENT(SNO ,SNAME,SAGE,SDept) 系的实体：DEPT(SDept , DEAN) 3. 分解后3NF？ STUDENT3NF DEPT 3NF,效果 冗余有条件地减少 系主任的信息只有一份。 避免了原修改麻烦 同学转系只要修改SDEPT，不要修改Dean，如果换系主任，则该系每个学生元组不要做相应修改 避免了原插入异常 系的信息单独维护（DEPT），无论系中没有学生 避免了原删除异常 如果学生全部毕业了，则只需删除STUDENT学生信息，不影响系的信息,1：若R1NF，且其候选码为单个属性， 则R2NF 2：若R2NF，且至多存在一个非主属性， 则R3NF 3：任何二元关系模式必为3NF。,3NF的推论,快速热身 关系模式R（A，B，C，D），码为AB，给出它的一个函数依赖集，使得R属于2NF而不属于3NF,课堂练习 Supplyer(SpNo,City,Area,Phone) SpNo供应商编号 Area城市所在地区 其中，SpNo是主码 函数依赖有哪些？ 属于2NF吗？ 属于3NF吗？,分析评价 部分fd和传递fd是冗余及操作异常的重要根源。 3NF不存在非主属性对候选码的部分fd和传递fd。 3NF消去了大部分冗余及操作异常。 并非所有的3NF都能完全消除冗余及操作异常。,示例 STC(S , T , C)，每位老师只教授一门课，每门课有若干老师。某学生选定一门课，就对应一位老师。则它的一组函数依赖为：,T C （S，T） C;（S，C） T 候选码为？,( S，T)和 (S，C) 思考 STC 3NF ？,yes!,非主属性：无,STC(S , T , C)的不良特性(并非所有3NF都存在不良特性) 插入异常：如果没有学生选修某位老师的任课，则该老师担任课程的信息就无法插入；学生未选修课程，则无C和T信息，学生信息进入不了DB。 删除异常：删除学生选课信息，会删除掉老师的任课信息 更新异常：如果老师所教授的课程有所改动，则所有选修该老师课程的学生元组都要做改动 数据冗余：每位学生都存储了有关老师所教授的课程的信息,原因 存在主属性对候选码的部分fd。 TC （S，T） C 存在主属性对候选码的传递fd。 （S，C）T TC （S，C） C,p,t,关系模式R中，对于属性组X，Y，若XY且Y X时X必含有码，则R BCNF,范式Boyce/Codd范式(BCNF),范式Boyce/Codd范式(BCNF),stc(s,t,c) /学生，教师，课程 tc /每位教师只上一门课 s,ct s,tc /每门课有若干位教师 T C，而T不含有码,改造:消去主属性对候选码的部分fd 将STC (S , T , C)分解为 ST（S，T） BCNF TC（T，C） BCNF,冗余得到了较好控制 很多学生选同一教师的一门课程时，课程信息只存一次。 修改麻烦避免了 课程改名后，只在TC中改一元组（对同一教师）。 插入异常避免了 ST中可以插入未选修课程的学生信息。 TC中可以插入无学生选修的课程和教师信息。 删除异常避免了 删去学生信息在ST中进行，教师及课程信息在TC中不受影响。,效果,最高范式 BCNF是基于函数依赖的最高范式 但不是数据库模式设计的最高范式 属于BCNF的关系中必定满足： 所有的非主属性对每一个码都是完全依赖的； 所有的主属性对每一个不包含它自己的码都是完全依赖的； 没有任何属性完全依赖于非码的任何一组属性 排除了任何属性对码的传递依赖与部分依赖,范式Boyce/Codd范式(BCNF),BCNF,思考 SCO(S , C , ORDER)，表示学生选修课程的名次，同一门课程的各个学生的名次没有并列。 其函数依赖有： (S，C) ORDER， (C，ORDER) S。 SCO属于BCNF吗？,BCNF,例：关系模式SNC(SNO,SNAME,CNO,SCORE),SNAME无重复 分析：SNC的候选码 （SNO，CNO）和（SNAME，CNO） 非主属性： SCORE 1NF? 2NF? 3NF?