数据库设计教程chap08

Chapter8NormalizationTransparencies1©PearsonEducationLimited,2004Chapter8-ObjectivesHowtablesthatcontainredundantdatacansufferfromupdateanomalies,whichcanintroduceinconsistenciesintoadatabase.Therulesassociatedwiththemostcommonlyusednormalforms,namelyfirst(1NF),second(2NF),andthird(3NF).Theidentificationofvarioustypesofupdateanomaliessuchasinsertion,deletion,andmodificationanomalies.2©PearsonEducationLimited,2004Chapter8-ObjectivesHowtablesthatbreaktherulesof1NF,2NF,and3NFarelikelytocontainredundantdataandsufferfromupdateanomalies.Howtorestructuretablesthatbreaktherulesof1NF,2NF,and3NF.3©PearsonEducationLimited,2004NormalizationAtechniqueforproducingasetoftableswithdesirablepropertiesthatsupporttherequirementsofauserorcompany.4©PearsonEducationLimited,2004DataredundancyandupdateanomaliesMajoraimofrelationaldatabasedesignistogroupcolumnsintotablestominimizedataredundancyandreducefilestoragespacerequiredbybasetables.ProblemsassociatedwithdataredundancyareillustratedbycomparingtheStaffandBranchtableswiththeStaffBranchtable.5©PearsonEducationLimited,2004Dataredundancyandupdateanomalies6©PearsonEducationLimited,2004Dataredundancyandupdateanomalies7©PearsonEducationLimited,2004DataredundancyandupdateanomaliesStaffBranchtablehasredundantdata;thedetailsofabrancharerepeatedforeverymemberofstaff.Incontrast,thebranchinformationappearsonlyonceforeachbranchintheBranchtableandonlythebranchnumber(branchNo)isrepeatedintheStafftable,torepresentwhereeachmemberofstaffislocated.8©PearsonEducationLimited,2004DataredundancyandupdateanomaliesTablesthatcontainredundantinformationmaypotentiallysufferfromupdateanomalies.Typesofupdateanomaliesincludeinsertiondeletionmodification9©PearsonEducationLimited,2004Firstnormalform(1NF)Only1NFiscriticalincreatingappropriatetablesforrelationaldatabases.Allsubsequentnormalformsareoptional.Atableinwhichtheintersectionofeverycolumnandrecordcontainsonlyonevalue.10©PearsonEducationLimited,2004Branchtableisnotin1NF11©PearsonEducationLimited,2004ConvertingBranchtableto1NF12©PearsonEducationLimited,2004Secondnormalform(2NF)2NFonlyappliestotableswithcompositeprimarykeys.Atablethatisin1NFandinwhichthevaluesofeachnon-primary-keycolumncanbeworkedoutfromthevaluesinallthecolumnsthatmakeuptheprimarykey.13©PearsonEducationLimited,2004TempStaffAllocationtableisnotin2NF14©PearsonEducationLimited,2004FunctionaldependencyTheparticularrelationshipsthatweshowbetweenthecolumnsofatablearemoreformallyreferredtoasfunctionaldependencies.Functionaldependencydescribestherelationshipbetweencolumnsinatable.15©PearsonEducationLimited,2004FunctionaldependencyForexample,consideratablewithcolumnsAandB,whereBisfunctionallydependentonA(denotedA

B).IfweknowthevalueofA,wefindonlyonevalueofBinalltherecordsthathasthisvalueofA,atanymomentintime.16©PearsonEducationLimited,2004Secondnormalform(2NF)Formaldefinitionof2NFisatablethatisin1NFandeverynon-primary-keycolumnisfullyfunctionaldependentontheprimarykey.FullfunctionaldependencyindicatesthatifAandBarecolumnsofatable,BisfullydependentonAifBisfunctionallydependentonAbutnotonanypropersubsetofA.17©PearsonEducationLimited,2004ConvertingTempStaffAllocation

tableto2NF18©PearsonEducationLimited,2004Thirdnormalform(3NF)Atablethatisin1NFand2NFandinwhichallnon-primary-keycolumncanbeworkedoutfromonlytheprimarykeycolumn(s)andnoothercolumns.19©PearsonEducationLimited,2004StaffBranchtableisnotin3NF20©PearsonEducationLimited,2004Thirdnormalform(3NF)Theformaldefinitionof3NFisatablethatisin1NFand2NFandinwhichnonon-primary-keycolumnistransitivelydependentontheprimarykey.21©PearsonEducationLimited,2004Thirdnormalform(3NF)Forexample,consideratablewithA,B,andC.IfBisfunctionaldependentonA(A

B)andCisfunctionaldependentonB(B

C),thenCistransitivelydependentonAviaB(providedthatA