数据库逻辑模型和关系模型.ppt

1 PrenticeHall 2002 Chapter6 LogicalDatabaseDesignandtheRelationalModel JIANGMINCOMP256 2 PrenticeHall 2002 Overview ObjectiveistotranslatetheconceptualdatamodelintoalogicaldatabasedesignthatcanbeimplementedonachosenDBMSNotationforrelationaldatamodelIntegrityConstraintsTransformingERDtorelationalmodelNormalizationandNormalForm NF 3 PrenticeHall 2002 RelationalDataModel RepresentsdataintheformoftablesDataStructureDataorganizedintheformoftableswithrowsandcolumnsDataManipulationOperationstomanipulatethedatastoredinthetablesDataIntegrityFacilitiestospecifybusinessrulesthatmaintaintheintegrityofthedatawhenmanipulated 4 PrenticeHall 2002 RelationalDataModel RelationNamedtwodimensionaltableofdataSetofnamedcolumnsArbitrarynumberofrows 5 PrenticeHall 2002 RelationalDataModel Relationshowingsampledata Fields Attributes columns Tuples Records Rows 6 PrenticeHall 2002 RelationalNotation EMPLOYEE1 Emp ID Name Dept Name Salary DEPARTMENT Dept Name Location EMPLOYEE1 7 PrenticeHall 2002 CorrespondencewithERModel Relations tables correspondwithentitytypesandwithmany to manyrelationshiptypesRowscorrespondwithentityinstancesandwithmany to manyrelationshipinstancesColumnscorrespondwithattributesNOTE Thewordrelation inrelationaldatabase isNOTthesamesamethewordrelationship inERmodel 8 PrenticeHall 2002 KeyFields Primarykey Msaccess Anattribute orcombination thatuniquelyidentifieseachrowinarelationForeignkey eg Representsrelationshipbetween2tablesAttribute orcombination inarelationthatistheprimarykeyofanotherrelationinsamedatabaseCompositekeyKeyconsistsofmorethanoneattribute 9 PrenticeHall 2002 Figure6 5 Schemaforfourrelations PineValleyFurniture 10 PrenticeHall 2002 NotalltablesqualifyasrelationsRequirements MSaccess Everyrelationhasauniquename Attributes columns intableshaveuniquenamesTheorderofthecolumnsisirrelevantTheorderoftherowsisirrelevantEveryattributevalueisatomic notmultivalued notcomposite Everyrowisunique can thavetworowswithexactlythesamevaluesforalltheirfields 11 PrenticeHall 2002 Everyattributevalueisatomic notmultivalued notcomposite 12 PrenticeHall 2002 13 PrenticeHall 2002 Eg2 14 PrenticeHall 2002 RelationalDatabase ConsistsofanynumberofrelationsStructuredescribedthroughuseofconceptualschemaTWOmethodsforexpressingaschema ShorttextstatementsusingrelationalnotationGraphicallikeERDbutwithattributeslistedwithintherectangleforeachrelation 15 PrenticeHall 2002 CUSTOMER ORDER ORDER LINE PRODUCT Exampleschemafromtextbook p212 16 PrenticeHall 2002 TextualNotationCUSTOMER Customer ID Customer Name Address City State Zip ORDER Order ID Order Date Customer ID ORDER LINE Order ID Product ID Quantity PRODUCT Product ID Product Description Product Finish Unit Price On Hand NoteCustomer IDisFKeyonORDERBothOrder IDandProduct IDareFkeyonORDER LINE butdifficulttoshow 17 PrenticeHall 2002 DiagramNotation LikePowerDesignerorOracleDesigner 2000 18 PrenticeHall 2002 IntegrityConstraints PurposeistofacilitatemaintainingaccuracyandintegrityofdatainthedatabaseDomainConstraintsEntityIntegrityReferentialIntegrityOperationalConstraints 19 PrenticeHall 2002 domainconstraint Specificationofthesetofvaluesthatcanbeassigneddomainnamemeaningordescriptiondatatypesize length decimalplaces etc allowablevalues listorrange DomainConstraints 20 PrenticeHall 2002 EntityIntegrity Designedtoensureeveryrelationhasauniqueprimarykeyasawayofidentifyingaspecificrow rowuniquenessproperty Noprimarykeyattribute orcomponent canbeNULL 21 PrenticeHall 2002 NULLvalue Therearecaseswhenanattributecannotbeassignedavalue Thereisnovalueapplicableeg PersonalFAXnumberThevalueisnotknownwhenrowiscreatedeg GPAfornewlyregisteredstudentNULLisemptyormissingorabsentvalueNotthesameasblankorzero eg meaningifGPAisNULLversus0 00 22 PrenticeHall 2002 ReferentialIntegrity DesignedtomaintainconsistencyofrelationshipsbetweentablesEachforeignkeyvaluemustmatchaprimarykeyvalueintheotherrelation orelsetheforeignkeyvaluemustbenullParent Childeg cannotcreateanOrderunlessCustomerexists 23 PrenticeHall 2002 Figure5 5 Referentialintegrityconstraints PineValleyFurniture Referentialintegrityconstraintsaredrawnviaarrowsfromdependenttoparenttable 24 PrenticeHall 2002 Well StructuredRelations Arelationthatcontainsminimaldataredundancyandallowsuserstoinsert delete andupdaterowswithoutcausingdatainconsistenciesGoalistoavoidanomaliesInsertionAnomaly addingnewrowsforcesusertocreateduplicatedataDeletionAnomaly deletingrowsmaycausealossofdatathatwouldbeneededforotherfuturerowsUpdateAnomaly changingdatainarowforceschangestootherrowsbecauseofduplication 25 PrenticeHall 2002 Example Figure5 2b Question Isthisarelation Answer Yes uniquerowsandnomultivaluedattributes Question What stheprimarykey Answer Composite Emp ID Course Title 26 PrenticeHall 2002 AnomaliesinthisTable Insertion can tenteranewemployeewithouthavingtheemployeetakeaclassDeletion ifweremoveemployee140 weloseinformationabouttheexistenceofaTaxAccclassUpdate givingasalaryincreasetoemployee100forcesustoupdatemultiplerecords 27 PrenticeHall 2002 EMPLOYEE1 EMP COURSE EMPLOYEE2 28 PrenticeHall 2002 DataNormalization PrimarilyatooltovalidateandimprovealogicaldesignsothatitsatisfiescertainconstraintsthatavoidunnecessaryduplicationofdataTheprocessofdecomposingrelationswithanomaliestoproducesmaller well structuredrelations 29 PrenticeHall 2002 FunctionalDependenciesandKeys FunctionalDependency Thevalueofoneattribute thedeterminant determinesthevalueofanotherattributeCandidateKey Auniqueidentifier OneofthecandidatekeyswillbecometheprimarykeyE g perhapsthereisbothcreditcardnumberandSS inatable inthiscasebotharecandidatekeysEachnon keyfieldisfunctionallydependentoneverycandidatekey 30 PrenticeHall 2002 Normalization NormalizationProcessofdecomposingrelationswithanomaliestoproducesmallerwell structuredrelationsNormalFormAstateofarelationthatresultsfromapplyingrulesregardingfunctionaldependencies orrelationshipsbetweenattributes tothatrelation 31 PrenticeHall 2002 Normalization Firstnormalform Secondnormalform Thirdnormalform Boyce Coddnormalform Fourthnormalform Fifthnormalform Removemulti valuedattributes Removeremaininganomalies Removemulti valueddependencies Removepartialdependencies Removetransitivedependencies Removeremainingfunctionaldependencies 32 PrenticeHall 2002 Definition constraintbetweentwoattributesortwosetsofattributesThevalueofoneattribute thedeterminant determinesthevalueofanotherattributeA BBisfunctionallydependantonAAiscalledthedeterminantSIN Name Birthdate Address ISBN Title AuthorEmp ID Course Name Date Completedeg FunctionalDependencies 33 PrenticeHall 2002 CandidateKey Definition Attribute orcombination thatuniquelyidentifiesarowinarelationOneofthecandidatekeyswillbecometheprimarykeyE g perhapsthereisbothcreditcardnumberandSS inatable inthiscasebotharecandidatekeys 34 PrenticeHall 2002 CandidateKey Mustsatisfysubsetofprimarykeyproperties UniqueIdentificationImpliesthateachnonkeyattributeisfunctionallydependentonthatkeyNon redundancyNoattributeinthekeycanbedeletedwithoutdestroyingthepropertyofuniqueidentification 35 PrenticeHall 2002 CandidateKeyMustsatisfysubsetofprimarykeyproperties UniqueIdentificationImpliesthateachnonkeyattributeisfunctionallydependentonthatkeyNon redundancyNoattributeinthekeycanbedeletedwithoutdestroyingthepropertyofuniqueidentification 36 PrenticeHall 2002 EMPLOYEE1 FunctionalDependencies EMPLOYEE2 37 PrenticeHall 2002 Arelationwhichcontainsnomulti valuedattributes noarrays 1NF FirstNormalForm 38 PrenticeHall 2002 Figure5 10 Mappingamultivaluedattribute a 39 PrenticeHall 2002 40 PrenticeHall 2002 2NF SecondNormalForm Arelationthatisin1NFandhaveeverynon keyattributefullyfunctionallydependentontheprimarykeyeg EMPLOYEE2PartialfunctionaldependencyOneormorenon keyattributesaredependentonpart butnotall theprimarykey 41 PrenticeHall 2002 Fig5 23 b FunctionalDependenciesinEMPLOYEE2 Dependencyonentireprimarykey Dependencyononlypartofthekey EmpID CourseTitle DateCompleted EmpID Name DeptName Salary Therefore NOTin2ndNormalForm 42 PrenticeHall 2002 Gettingitinto2ndNormalForm Seep193 decomposedintotwoseparaterelations Botharefullfunctionaldependencies 43 PrenticeHall 2002 EMPLOYEE1 EMP COURSE EMPLOYEE2 44 PrenticeHall 2002 Guarantee2NFifrelationis1NFandeitherPrimarykeyisasingleattributeNonon keyattributesinrelation everyattributeispartofthekey 45 PrenticeHall 2002 3NF ThirdNormalForm Arelationthatisin2NFandhasnotransitivedependenciespresentTransitiveDependencyFunctionaldependencybetweentwoormorenon keyattributes 46 PrenticeHall 2002 3NF ThirdNormalForm Figure6 24 TransitiveDependency 47 PrenticeHall 2002 Figure5 24 b Relationwithtransitivedependency CustID NameCustID SalespersonCustID RegionAllthisisOK 2ndNF 48 PrenticeHall 2002 3NF ThirdNormalForm 49 PrenticeHall 2002 Figure5 25 b Relationsin3NF Now therearenotransitivedependencies Bothrelationsarein3rdNF CustID NameCustID Salesperson Salesperson Region 50 PrenticeHall 2002 OtherNormalForms fromAppendixB Boyce CoddNFAlldeterminantsarecandidatekeys thereisnodeterminantthatisnotauniqueidentifier4thNFNomultivalueddepe