第5章-关系数据库设计课件

第5 5章 关系数据库设计数据库原理及应用郑轶（20102010）1整体概述THEFIRSTPARTOFTHEOVERALLOVERVIEW,P L E A S E S U M M A R I Z E T H E C O N T E N T第一部分2目 录n5.1 5.1 问题的提出 n5.2 5.2 函数依赖 n5.3 5.3 关系模式的分解 n5.4 5.4 范式及其规范化35.1 5.1 问题的提出n关系数据库逻辑设计n针对具体问题，如何构造一个适合于它的数据模式n数据库逻辑设计的工具关系数据库的规范化理论n例如：与之前的在线书店数据库的关系模式不同，我们将图书的信息保存在一个单独的关系模式BookInfo中：nBookInfo(ISBN,title,author_id,author_name,serial,press,price,category_id,category_tag)4BookInfoBookInfoISBNISBNTitleTitleAuthorAuthor_id_idAuthorAuthor_name_nameSerialSerialPressPressPricePriceCategoCategory_idry_idCategorCategory_tagy_tag7-04-001968.xo.719概率论与数理统计001盛骤1高教出版社5.801数学7-04-001968.xo.719概率论与数理统计002谢世干2高教出版社5.801数学7-04-001968.xo.719概率论与数理统计003潘承义3高教出版社5.801数学7-111.06887-4可靠性模型与应用004蒋仁言1机械出版社19.001数学7-111.06887-4可靠性模型与应用005左明健2机械出版社19.001数学7-5327-1224.91.717基督山伯爵006大仲马1译文出版社18.002文学7-5327-1224.91.321三个火枪手006大仲马1译文出版社16.702文学7-5327-0924.81.489乱世佳人007米切尔1译文出版社16.102文学7-200-02999-8王子与贫儿008马克.土温1北京出版社13.002文学5异常问题n插入异常 无法插入新的类别“计算机”。n删除异常 如果所有“数学”类别的图书都售出，在删除这些图书信息的同时，会将该类别也删除。n更新异常 例如，更新“盛骤、谢世干、潘承义”三人所著图书的价格。n信息冗余 例如，图书类别造成大量冗余。n异常现象产生的根源：关系模式中属性之间存在的数据依赖关系。64.2 4.2 函数依赖n关系模式的形式化定义n关系模式由五部分组成，即它是一个五元组：R(U,D,DOM,F)R(U,D,DOM,F)nR：关系名nU：组成该关系的属性名集合nD：属性组U中属性所来自的域nDOM：属性向域的映象集合nF F：属性间数据的依赖关系集合n由于D和DOM对模式设计关系不大,因此我们在本章中把关系模式看作是一个三元组：R RU,FU,F。n当且仅当U上的一个关系 r 满足F时,r称为关系模式RU,F的一个关系。7函数依赖的定义n函数依赖（Functional Dependency,FD)n定义5-15-1 设有关系模式R(U),XR(U),X和Y Y是属性集U的子集，若对R(U)R(U)的任意一个可能的关系r r，r r中不可能存在两个元组在X X上的属性值相等，而在Y Y上的属性值不等，则称X X函数确定Y Y，或Y Y函数依赖于X X，记作XYXY。n例如：isbntitleisbntitleisbnpressisbnpressisbnpriceisbnpricen注：函数依赖是指R的一切关系均要满足的约束条件。n若XY,则X叫做决定因素(Determinant)。n若XY，YX，则记作XY。n若Y不函数依赖于X,则记作X Y。8重要术语(一)n平凡的函数依赖与非平凡的函数依赖n在关系模式R(U)中，对U中的子集X，Y，如果XY，但Y X，则称XY是非平凡的函数依赖。n若Y X，则称XY是平凡的函数依赖。isbntitleisbntitleauthor_id author_nameauthor_id author_namecategory_id category_tagcategory_id category_tagn完全函数依赖与部分函数依赖n在R(U)中,如果XY，并且不存在X的任何一个真子集X,满足X Y,则称Y对X完全函数依赖,记作:。n否则，则称Y对X部分函数依赖,记作：。n(isbn,author_id)serial(isbn,author_id)serial9重要术语(二)n传递依赖n在R(U)中,如果XY,YX，YZ，但YX不成立,则称Z对X传递函数依赖。nisbn category_idisbn category_idcategory_id category_tagcategory_id category_tagnisbn category_tagisbn category_tagnBookInfoBookInfo的决定组合（isbnisbn，author_id,category_idauthor_id,category_id）n码 定义5.25.2 设K K为R RU,FU,F中的属性或属性组合,若K U，则称K为R的一个候选码（Candidate key）。若R中有多个候选码，则选定其中的一个做为主码（Primary key）。n包含在任何一个码里面的属性是主属性，反之是非主属性。n若U中的属性全部为主属性，则称为全码。10重要术语(三)n定义5.35.3 设F是函数依赖集，被F逻辑蕴涵的函数依赖全体构成的集合，称为函数依赖集F的闭包（closure），记为F+。n例如，从F=AB,BC可以推导出AC，所以AC是F+中的成员。nArmstrong公理：n自反律：若YX，则XY。n增长律：若XY，则XZYZ。n传递律：若XY,YZ,则XZ。115.3 5.3 关系模式的分解n为消除关系模式 R 中的异常，把 R 中的属性分开，构成两个新的关系模型。n分解的三个准则：n关系模式分解后原有的信息不丢失。称为无损连接。n分解后保持原有函数依赖仍然成立。n既保持函数依赖，又具有无损连接。n例:SL（Sno，Sdept，Sloc）F=SnoSdept,SdeptSloc,SnoSloc存在插入异常、删除异常、冗余度大和修改复杂等问题。n分解后的Ri向R恢复是通过自然连接来实现的。12方法1 1 SL SL SL1 SL2SL1 SL2_ _ _ _ _ Sno Sdept Sloc Sno Sdept Sloc Sno Sloc Sdept SlocSno Sloc Sdept Sloc _ _ _ _ _95001 CS A 95001 CS A 95001 A CS A95001 A CS A 95002 IS B 95002 IS B 95002 B IS B95002 B IS B 95003 MA C 95003 MA C 95003 C MA C95003 C MA C 95004 IS B 95004 IS B 95004 B95004 B PH APH A95005 PH A 95005 PH A 95005 A95005 A 结论：丢失信息，不具无损连接性。13方法22无损连接的分解 SL SL SL1 SL2SL1 SL2_ _ _ _ _ Sno Sdept Sloc Sno Sdept Sloc Sno Sdept Sno SlocSno Sdept Sno Sloc _ _ _ _ _95001 CS A 95001 CS A 95001 CS 95001 A95001 CS 95001 A 95002 IS B 95002 IS B 95002 IS 95002 B95002 IS 95002 B 95003 MA C 95003 MA C 95003 MA 95003 C95003 MA 95003 C 95004 IS B 95004 IS B 95004 IS95004 IS 95004 B95004 B 95005 PH A 95005 PH A 95005 PH 95005 PH 95005 A95005 A结论：不丢失信息，但不保持函数依赖性，未能消除异常。14方法33无损连接&函数依赖的分解 SL SL SL1 SL2SL1 SL2_ _ _ _ _ Sno Sdept Sloc Sno Sdept Sloc Sno Sdept Sdept SlocSno Sdept Sdept Sloc _ _ _ _ _95001 CS A 95001 CS A 95001 CS CS A95001 CS CS A 95002 IS B 95002 IS B 95002 IS IS B95002 IS IS B 95003 MA C 95003 MA C 95003 MA MA C95003 MA MA C 95004 IS B 95004 IS B 95004 IS95004 IS PH APH A 95005 PH A 95005 PH A 95005 PH 95005 PH 结论：不丢失信息，具无损连接性，同时保持函数依赖性 15BookInfoBookInfo的分解n例如：BookinfoU=ISBN,title,author_id,author_name,serial,press,price,category_id,category_tagF=ISBN title,author_id author_name,(ISBN,author_id)serial,ISBN category_id,category_id category_tagn设关系R分解为R1、R2，如果它们的公共属性集是R1或R2的主码，那么分解时无损的。165.4 5.4 范式及其规范化n范式是符合某一种级别的关系模式的集合。n关系数据库中的关系必须满足一定的要求。满足不同程度要求的为不同范式。n某一关系模式R为第n范式，可简记为RnNFn各种范式之间的关系：175.4.1 5.4.1 第一范式（1NF1NF）n如果一个关系模式R的所有属性都是不可分的基本数据项，则R1NF。n第一范式是对关系模式的最起码的要求。不满足第一范式的数据库模式不能称为关系数据库。n例1：联系人（姓名，性别，电话）如果一个联系人有家庭电话和公司电话，那么就没有达到 1NF。n要符合 1NF 我们只需把列（电话）拆分，即：联系人（姓名，性别，家庭电话，公司电话）。n但是满足第一范式的关系模式并不一定是一个好的关系模式。181NF1NF例2:关系模式 SLC(Sno,Sdept,Sloc,Cno,Grade)Sloc为学生住处，假设每个系的学生住在同一个地方。nSLC满足1NF。n函数依赖包括：n(Sno,Cno)f Grade SLC的码为(Sno,Cno)nSno Sdeptn(Sno,Cno)P SdeptnSno Slocn(Sno,Cno)P SlocnSdept Sloc非主属性Sdept和Sloc部分函数依赖于码(Sno,Cno)19存在问题(1)(1)插入异常假设Sno95102，SdeptIS，SlocN的学生还未选课，因课程号是主属性，因此该学生的信息无法插入SLC。(2)(2)删除异常 假定某个学生本来只选修了3号课程这一门课。现在因身体不适，他连3号课程也不选修了。因课程号是主属性，此操作将导致该学生信息的整个元组都要删除。(3)(3)数据冗余度大 如果一个学生选修了10门课程，那么他的Sdept和Sloc值就要重复存储10次。(4)(4)修改复杂 例如学生转系，在修改此学生元组的Sdept值的同时，还可能需要修改住处（Sloc）。如果这个学生选修了K门课，则必须无遗漏地修改K个元组中全部Sdept、Sloc信息。20问题及解决n原因:Sdept、Sloc部分函数依赖于码。n解决方法:SLC分解为两个关系模式，以消除这些部分函数依赖。nSC（Sno，Cno，Grade）nSL（Sno，Sdept，Sloc）n函数依赖图：SnoCnoGradeSCSLSnoSdeptSloc215.4.2 5.4.2 第二范式（2NF2NF）n若关系模式R1NF，并且每一个非主属性都完全函数依赖于R的码，则R2NF。n所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。n如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。n为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是属性完全依赖于主键。n例：nSLC(Sno,Sdept,Sloc,Cno,Grade)1NFnSLC(Sno,Sdept,Sloc,Cno,Grade)2NFnSL（Sno，Sdept，Sloc）2NFnSC（Sno，Cno，Grade）2NF22存在问题n2NF的SL（Sno,SDept,SLoc）仍存在问题：n插入异常：如果某个系刚成立，目前没有在校学生，则无法插入这个系的信息。n删除异常：如果某个系的学生全毕业了，删除学生的同时，将系的信息也删掉了。n数据冗余大：每个系的学生住在同一个地方，关于系的住处的信息被重复存放。n问题所在：SL中的SLoc传递函数依赖于Sno。235.4.3 5.4.3 第三范式（3NF3NF）n关系模式R，非主键列必须直接依赖于主键，不能存在传递依赖，则称R3NF。n简单的说，第三范式是为了消除数据库中关键字之间的依赖关系。nSL(Sno,Sdept,Sloc)2NFn SL(Sno,Sdept,Sloc)3NFn解决方法：采用投影分解法，把SL分解为两个关系模式，以消除传递函数依赖：nSD（Sno，Sdept）3NFn DL（Sdept，Sloc）3NF245.4.4 BCNF5.4.4 BCNF范式n定义 设关系模式R1NF，如果对于R的每个函数依赖XY，若Y不属于X，则X必含有候选码，那么RBCNF。nBCNF在3NF的基础上又排除了主属性对码的部分依赖和传递依赖。nBCNF范式是修正的第三范式，每个表中只有一个候选键。n3NF3NF与BCNFBCNF的关系n如果关系模式RBCNF，必定有R3NFn如果R3NF，且R只有一个候选码，则R必属于BCNF。n一个满足BCNFBCNF的关系模式有：n所有非主属性都完全函数依赖侯选码。n所有主属性都完全函数依赖于每个不包含它的侯选码。n没有任何属性完全函数依赖于非码的任何一组属性。255.4.4 BCNF5.4.4 BCNF范式例：在关系模式STJ（S，T，J）中，S表示学生，T表示教师，J表示课程。n每一教师只教一门课。每门课由若干教师教，某一学生选定某门课，就确定了一个固定的教师。某个学生选修某个教师的课就确定了所选课：(S，J)T，(S，T)J，TJnSTJ3NFn(S，J)和(S，T)都可以作为候选码nS、T、J都是主属性nSTJBCNFnTJ，T是决定属性集，T不是候选码n解决方法：将STJ分解为二个关系模式：SJ(S，J)BCNF，TJ(T，J)BCNFn没有任何属性对码的部分函数依赖和传递函数依赖。265.4.5 5.4.5 规范化过程n一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式集合，这种过程就叫关系模式的规范化。n关系模式规范化的基本步骤：275.5 5.5 多值依赖n函数依赖的概念是帮助排除关系中存在冗余数据的元组。即使关系达到 3NF 或 BCNF，数据冗余现象仍可能存在。n例:学校中某一门课程由多个教师讲授，他们使用相同的一套参考书。n关系模式Teaching(C,T,B)课程名称 C教师姓名 T参考书 B物理数学李 勇王 军赵 大张 平 普通物理学光学原理 物理数学分析微分方程高等代数285.5 5.5 多值依赖nTeachingBCNF:nTeach具有唯一候选码(C，T，B)，即全码nTeaching模式中存在的问题n数据冗余度大n插入操作复杂n删除操作复杂n修改操作复杂n产生原因:存在多值依赖普通物理学光学原理物理普通物理学光学原理物理数学分析微分方程高等代数数学分析微分方程高等代数李 勇李 勇李 勇王 军王 军王 军赵 大赵 大赵 大张 平张 平张 平 物 理物 理物 理物 理物 理物 理数 学数 学数 学数 学数 学数 学 参考书B B教员T T课程C C295.5.1 5.5.1 多值依赖的概念n定义 设R(U)是一个属性集U上的一个关系模式，X、Y和Z是U的子集，并且ZUXY，多值依赖 XYXY成立当且仅当对R的任一关系r，r在（X，Z）上的每个值对应一组Y的值，这组值仅仅决定于X值而与Z值无关。n例：Teaching（C,T,B）nC Tn对于C的每一个值，T有一组值与之对应，而不论B取何值。n平凡多值依赖和非平凡的多值依赖n若XY，而Z，则称 XY为平凡的多值依赖n否则称XY为非平凡的多值依赖n解决方法：模式分解。30提问与解答环节Questionsandanswers31感谢参与本课程，也感激大家对我们工作的支持与积极的参与。课程后会发放课程满意度评估表，如果对我们课程或者工作有什么建议和意见，也请写在上边结束语32谢谢聆听THANKYOUFORLISTENING演讲者：XX时间：202X.XX.XX33