第四章 关系数据库设计理论.ppt

《第四章 关系数据库设计理论.ppt》由会员分享，可在线阅读，更多相关《第四章 关系数据库设计理论.ppt（36页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第四章 关系数据库设计理论n针对一个具体问题，如何构造一个适合于它的数据模式，即应该构造几个关系，每个关系有哪些属性组成等数据库逻辑设计问题。4-1第四章 关系数据库设计理论 41数据依赖411关系模式的形式化定义412函数依赖与存储异常413有关概念42范式4.2.11NF4.2.22NF4.2.33NF4.3关系模式的规范化4-241 数据依赖 411关系模式的形式化定义关系由五元组表示：R（U，D，DOM，F），其中：nR：关系名nU：属性名集合nD：域nDOM：属性向域的映象集合nF：属性间的数据依赖关系集影响数据库模式的主要是U和F，因此，关关系系简简化化三三元元组为组为R（U，F）

2、4-3412 函数依赖与存储异常n数数据据依依赖赖：通过一个关系中属性间值的相等与否体现出来的数据间的相互关系的抽象，是数据内在的性质，是语义的体现。n函函数数依依赖赖：是属性之间的一种关系，它意味着，如如果果我我们们给给定定了了一一个个属属性性的的值值,我我们们就就可可以以获获得另一个属性的值得另一个属性的值.n如果关系中属性存在很多函数依赖关系，则影响关系性质，带来很多问题。4-4412 函数依赖与存储异常例如：描述一个学生关系Student(Sno,Sdept,Mname,Cname,Grade)它包含如下一些函数依赖：(1)一个学生只在一个系SnoSdept(2)一个系只有一个系主任S

3、deptMname(3)每个学生每门课有一个成绩(Sno,Cname)Grade4-5412 函数依赖与存储异常这个关系模式存在这个关系模式存在4个问题个问题:1.数据冗余太大,如一个系主任姓名重复出现,一个学生一门课程有一个Mname,浪费大量存储空间.2.更新异常:由于冗余太大，更新数据付出很大代价来维护DB完整性，会有数据不一致的危险，如更换系主任,必须改每个元组.3.插入异常:如一个系刚成立,尚无学生,则无法把系信息存入4.删除异常:如某系学生全毕业,学生全删,则系信息也丢了.鉴于以上种种,Student不是一个好的模式,4-6412 函数依赖与存储异常n一一个个”好好”的的模模式式应

4、应当当不不会会发发生生插插入入异异常常,更更新异常新异常,删除异常删除异常,数据冗余应尽可能少数据冗余应尽可能少.n此模式之所以会产生上述问题之所以会产生上述问题,是由于存在于模式中的某些数据依赖引起的n规范化来改造模式。4-7413 有关概念n规范化理论致力于解决关系模式中不合适的数据依赖问题n函数依赖和多值依赖是最重要的数据依赖4-8413 有关概念 1.函数依赖定义：设R(U)是属性集U上的关系模式。X，Y是U的子集。若对R(U)上任意一个可能的关系r，r中不可能存在两个元组，它们在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y或Y函数依赖于X，记作XY.例：R：student

5、(Sno,Sdept,Mname,Cname,Grade)X:Sdept,Y:Mname可得X相同，则Y一定相同，即SdeptMname4-9413 有关概念2.平凡函数依赖与非平凡函数依赖定义：XY,但YX，则称XY是非平凡的函数依赖。反之为平凡函数依赖。n我们只讨论非平凡函数依赖例：上例中X：（Sno,Cname）Y：Grade，（Sno,Cname）Grade，Grade（Sno,Cname）所以该依赖为非平凡函数依赖若（Sno,Cname）Cname，属于平凡函数依赖，显然一定成立4-10413 有关概念3.完全函数依赖n定义：在R(U)中，如果XY，并且对于X的任意一个真子集X，都有

6、XY,则称Y对X完全函数依赖，记作XY；若XY，但Y不完全函数依赖于X，则称Y对X部分函数依赖，记作XY；例：上例中X:(Sno,Cname)Y:GradeX:SnoX不决定Y，所以X完全函数决定Y，如果X：(Sno,Sdept)Y：Mname，其中：X为Sdept，则XY成立，我们说X部分决定YFp4-11413 有关概念4.传递函数依赖定义：在R(U)中，如果XY，（YX）YX,YZ,则称Z对X传递函数依赖；例：上例中SnoSdeptSdeptMname则SnoMname4-12413 有关概念5.码（关键字）定义:设K为R中的属性或属性组合，若KU，则K为R的侯选码(Candidatek

7、ey).若侯选码多于一个，则选定其中的一个为主码(Primarykey).例：student中SnoU（完全决定），则Sno为主关键字FF4-13第四章 关系数据库设计理论 41数据依赖411关系模式的形式化定义412函数依赖与存储异常413有关概念42范式4.2.11NF4.2.22NF4.2.33NF4.3关系模式的规范化4-1442范式 n规范化是把有问题的关系转化为两个或多个没有这些问题的关系的过程n规范化可用作检查关系合乎需要的程度和正确性的指南n范式是符合某一种级别的关系模式的集合.n关系数据库中的某些关系必须满足一定的要求.满足不同要求的为不同范式.n目前有六种范式:1NF(最最

8、低低),2NF,3NF,BCNF,4NF,5NF(最高最高)4-154.2.1 1NF n如果一个关系模式R的所有属性都是不可分的基本数据项,则R为1NF.n不不满满足足1NF的的数数据据库库模模式式不不能能成成为为关关系系数数据据库库.但满足但满足1NF不一定是好关系不一定是好关系.4-164.2.1 1NF例.SCL(Sno,Sdept,Sloc学生住处,Cno,Grade)假设每个系学生住在同一地方.该关系满足1NF，但此关系中存在函数依赖：(Sno,Cno)-Grade,Sno-Sdept,(Sno,Cno)-Sdept,Sno-Sloc,(Sno.Cno)-Slot,Sdept-Sl

9、ocn存在许多部分依赖4-174.2.1 1NF存在问题存在问题:(1)插入异常若要插入Sno=95102,Sdept=IS,Sloc=N但未选课,则无法插入.(2)删除异常如95022只选了3号课,现不选了,则该生信息全无.(3)冗余大某生选10门课,则Sdept和Sloc存10次4)修改复杂如某生从数学系转到信息系,要改多次Sdept,Sloc,SLC不是好模式n出现问题原因：出现问题原因：Sdept,Sloc对关键字的部分函对关键字的部分函数依赖数依赖4-184.2.2 2NF 将SCL拆分为SC(Sno,Cno,Grade)SL(Sno,Sdept,Sloc)拆分后,非主属性都完全依赖

10、于关键字了一定程度解决了上述问题:(1)在SL中可插入未选课学生(2)删除选课学生,只删sc,不涉及SL中Sno记录(3)不论Sno选多少课,Sdept与Sloc只在SL中存一次(4)某生从数学系转至信息系只改SL即可4-194.2.2 2NFn2NF定定义义:若关系模式R1NF,并且每一个非主属性都完全函数依赖于R的码,则R2NFn但属于但属于2NF关系不一定是关系不一定是”好好”关系关系.如SC、SL均为2NF，但SL中存在:nSnoSdeptnSdeptSlocnSnoSlocSloc传递依赖于Sno，即存在非主属性对Key的传递函数依赖.4-204.2.2 2NFSL关系中仍存在异常:

11、(1)插入异常:某系暂无在校生,则无系信息.(2)删除异常:某系学生全毕业,则系信息被删除(3)冗余:每系学生一个住处,重复(4)修改复杂:学校调整住处,住处全部修改n出现问题原因：SLOC传递依赖于SNO4-214.2.3 3NF n要消除传递依赖,将SL拆分SD(Sno,Sdept)DL(Sdept,Sloc)n显然,分解后的关系模式中即没有非主属性对关键字的部分依赖,也没有非主属性对关键字的传递依赖,4-224.2.3 3NF在一定程度上解决了上述4个问题(1)DL中可插入无在校生的系(2)某系学生毕业了只删SD,DL中仍有该系信息(3)关于系的住址信息只在中DL存储一次(4)当学校调整

12、住处时只修改DL中Sloc即可4-234.2.3 3NFn3NF定义定义:n一个关系如果在第二范式中,并且没有传递依赖,则该关系在第三范式中.n(抽象抽象)定义定义:n如果模式R中不存在候选关键字X,属性组Y以及非主属性Z(Z不包含于Y),使得XY,YZ,YX成立,则R3NF4-244.2.3 3NFn候选关键字:n若关系中某一属性组的值能唯一标识一个元组,则称该属性组为候选关键字n一个关系可有多个候选关键字,其中有一个主关键字4-254.2.3 3NF其他范式其他范式满足满足3NF的不一定是好的关系的不一定是好的关系(一般满足一般满足3NF即可即可)nBCNF:如果一个关系的每个决定因素都是

13、候选关键字,则BCNFn4NF:一个关系属于BCNF,且没有多值依赖,则属于4NFn5NF:无连接依赖无连接依赖不讨论4-26第四章 关系数据库设计理论 41数据依赖411关系模式的形式化定义412函数依赖与存储异常413有关概念42范式4.2.11NF4.2.22NF4.2.33NF4.3关系模式的规范化4-274.3关系模式的规范化 n一一个个关关系系只只要要其其分分量量都都是是不不可可分分的的数数据据项项,它它就是规范化关系就是规范化关系.但这只是最基本的规范化但这只是最基本的规范化.n关系模式规范化：关系模式规范化：n一一个个低低一一级级范范式式的的关关系系模模式式通通过过模模式式分分

14、解解可可以以转换为若干个高一级范式的关系模式集合转换为若干个高一级范式的关系模式集合4-284.3关系模式的规范化消除非关键字属性对关键字的部分函数依赖消除非关键字属性对关键字的部分函数依赖消除非关键字属性对关键字的传递函数依赖消除非关键字属性对关键字的传递函数依赖消除主属性对关键字的传递和部分函数依赖消除主属性对关键字的传递和部分函数依赖消除非平凡且非函数依赖的多组依赖消除非平凡且非函数依赖的多组依赖消除不是由候选码所蕴含的联接依赖消除不是由候选码所蕴含的联接依赖1NF2NF3NFBCNFBCNF4NF5NF4-29Normalization ExampleOneoftheorderform

15、sacustomerusesisshownbelow.Order#_OrderDate_CustomerID_CustomerName_CustomerAddress_CustomerCity_State_Zip_ItemItemItemItemItem(s)IDDescriptionPriceQuantity TotalPrice_OrderTotal _Alistingofthefieldsis:Order#OrderDateCustomerIDCustomerNameCustomerAddressCustomerCityStateZipItemIDItemDescriptionItemP

16、riceItemQuantityItem(s)TotalPriceOrderTotalRepeats4-30Normalization Example(cont.)Amodelerchoosestoremovetherepeatinggroupsandcreatestwotablesasshownbelow.Thisplacesthedatainfirstnormalform.OrderTableOrder#OrderDateCustomerIDCustomerNameCustomerAddressCustomerCityStateZipOrderTotalOrder-ItemTableOrd

17、er#ItemIDItemDescriptionItemPriceItemQuantityItem(s)TotalPrice4-31Normalization Example(cont.)AmodelerchecksthatattributesdescribetheentirePrimaryKey.Asanoption,theitempricemaybekeptattheOrder-Itemlevelintheeventadiscountordifferentpriceisgivenfortheorder.TheItemtablemayidentifytheretailprice.TheOrd

18、erTotalandItem(s)TotalPricearederiveddataandmayormaynotbeincluded.Thisplacesthedatainsecondnormalform.OrderTableOrder#OrderDateCustomerIDCustomerNameCustomerAddressCustomerCityStateZipOrderTotalOrder-ItemTableOrder#ItemIDItemPrice(sale)ItemQuantityItem(s)TotalPriceItemTableItemIDItemDescriptionItemP

19、rice(retail)4-32Normalization Example(cont.)Thesetablesarenowinthirdnormalform.Iftheitemsalepriceisalwaysthesameastheretailprice,thentheitempriceonlyneedstobekeptintheitemtable.TheOrderTotalandItem(s)TotalPricearederiveddataandmayormaynotbeincluded.AmodelerchecksthatattributesonlydescribethePrimaryK

20、ey.OrderTableOrder#OrderDateCustomerIDOrderTotalOrder-ItemTableOrder#ItemIDItemPrice(sale)ItemQuantityItem(s)TotalPriceItemTableItemIDItemDescriptionItemPrice(retail)CustomerTableCustomerIDCustomerNameCustomerAddressCustomerCityStateZip4-33Normalization Example(cont.)ORDERORDERORDERCUSTOMER#DATEIDPK

21、,SAFK12002-02-27100122002-04-241002CUSTOMERCUSTCUSTCUSTCUSTCUSTCUSTIDNAMEADDRESSCITYSTATEZIP PK,SA1001MALONEY100BrownSt.DaytonOH454791002JONES12MainSt.SanDiegoCA92127NotethatItemsTotalPrice&Order_Totalarenotshowninthismodel.HowareItemsTotalPrice&Order_Totalhandled?ORDER_ITEMORDERITEMSALEITEM#IDPRICE

22、QUANTITYPKFKFK1500115.00215002300.0012500115.001ITEMITEMITEMRETAILIDDESCRIPTIONPRICEPK5001PS20ElectricPencilSharpener15.005002MFC140Multi-FunctionPrinter300.00Thetablesareshownbelowin3NFwithPK-FKrelationships.4-34Derived Data ExampleEMPLOYEEEMPLOYEEEMPLOYEEDEPTNUMBERNAMENUMPK,SANNFKUPI22416JONES1002

23、30547SMITH100182455NOMAR100217435NECHES100123451MILLER1002DEPARTMENTDEPTDEPTNUMNAMEPK,SANN,NDUPI1001ENGINEERING1002EDUCATIONNORMALIZEDCarryingthecountofthenumberofemployeesinadepartmentisanormalformsviolation.ThenumberofemployeescanbedeterminedfromtheEmployeetable.DEPARTMENTDEPTDEPTEMPLOYEENUMNAMECO

24、UNTPK,SANN,NDDerivedDataUPI1001ENGINEERING21002EDUCATION3DENORMALIZEDEMPLOYEEEMPLOYEEEMPLOYEEDEPTNUMBERNAMENUMPK,SANNFKUPI22416JONES100230547SMITH100182455NOMAR100217435NECHES100123451MILLER10024-35System Assigned Primary KeysUseSystemAssignedPKsinsteadofintelligent(decomposable)datavaluesasPrimaryK

25、eystopossiblysavestoragespace.TheIDENTITYcolumnfeatureinTeradatamaybeusedtogenerateanumericvalue.ThePKappearsasaForeignKeyinothertables,andcostsstorageineachone.Inthisexample,theProductNumberCOSTS:Additional200KBintheProducttable50,000 x4bytesIfeachstorecarries10,000products,ProductNumberSAVES:240MB

26、intheStoreProducttable(8bytesx30Mrows)7440MBintheStoreProductDailyHistorytableEACHMonth(8bytesx930Mrows)STOREStoreNumberPKInteger(3000rows)PRODUCTProductProductNumberCodePK,SANN,NDIntegerChar(12)(50,000rows)Additionalcolumnof4bytesisaddedtoProducttableasPK,SA.STOREPRODUCTStoreProductNumberNumberPKFKFK(30,000,000rows)STOREPRODUCTDAILYHISTORYStoreProductNumberNumberDatePKFK(31days=930,000,000rows)ProductNumbersaves8bytesperrowinthesetables.4-36