数据库系统原理(英文)-7-09.pptVIP免费

PART2RELATIONALDATABASESChapter7Relational-DatabaseDesignnormalizationofrelationalschemasApril2009DatabaseSystemConcepts-Chapter7Relational-DatabaseDesign-3MainContentsinThisChapterVII-1Whynormalizationneeded?pitfallsinrelational-databasedesign(§7.1)principlesofrelationnormalizationVII-2Functionaldependency(§7.4)VII-3Definitionsofnormalformsthefirstnormalform(§7.2),secondnormalform,thirdnormalform(§7.3.4),BCNF(§7.3.2)VII-4Decompositionproperties(§7.4.4,§7.4.5)VII-53NFdecomposition(§7.5.2)VII-6BCNFdecomposition(§7.5.1)April2009DatabaseSystemConcepts-Chapter7Relational-DatabaseDesign-4VII-1-1PitfallsinRelational-Databasedesign(§7.1)AsshowninFig.7.0.1,logicalDBSdesignconsistsofinitialrelationalschemagenerating(§2.9)relationalschemanormalizingAbadDBdesign,i.e.schemanotbeingnormalizedwell,mayresultinrepetitionofinformationinabilitytorepresentcertaininformationVII-1WhyNormalizationNeeded?April2009DatabaseSystemConcepts-Chapter7Relational-DatabaseDesign-5returnFig.7.0.2Casestudyusedinchapter7VII-1-1PitfallsinRelational-Databasedesign(cont.)April2009DatabaseSystemConcepts-Chapter7Relational-DatabaseDesign-6ForallschemaconcerningloansinFig.7.0.2Branch-schema,Borrower-schema,Loan-schemacombinethesethreerelationsintoonesinglerelationLending-schema=(branch-name,branch-city,assets,customer-name,loan-number,amount)relationlendingisshowninFig.7.1VII-1-1PitfallsinRelational-Databasedesign(cont.)Applicationarea/probleminrealworldrequirementsanalysisSpecificationoffunctionalrequirementsconceptualDBSdesignConceptualDBSschema,i.e.E-RschemalogicalDBSdesignlogicalDBSschema,i.e.relationaldataschemaphysicalDBSdesignphysicalDBSschemaaboutphysicalstoragestructureandaccessmethodDBMSindependentDBMSdependentInitialrelationalschemagenerating(§2.9)Relationalschemanormalizing(chapter7)Fig.7.0.1DBSdesignFig.7.1Samplelending(Lending-schema)relationPerryridge,Horseneck,1700000,Adams,L-31,1500t1New-York,Long-island,200000,null,null,nullt2April2009...