资源描述
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(canthavetworowswithexactlythesamevaluesforalltheirfields),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,orelsetheforeignkeyvaluemustbenullParentChildeg.cannotcreateanOrderunlessCustomerexists,23,PrenticeHall,2002,Figure5-5:Referentialintegrityconstraints(PineValleyFurniture),Referentialintegrityconstraintsaredrawnviaarrowsfromdependenttoparenttable,24,PrenticeHall,2002,Well-StructuredRelations,Arelationthatcontainsminimaldataredundancyandallowsuserstoinsert,delete,andupdaterowswithoutcausingdatainconsistenciesGoalistoavoidanomaliesInsertionAnomalyaddingnewrowsforcesusertocreateduplicatedataDeletionAnomalydeletingrowsmaycausealossofdatathatwouldbeneededforotherfuturerowsUpdateAnomalychangingdatainarowforceschangestootherrowsbecauseofduplication,25,PrenticeHall,2002,ExampleFigure5.2b,QuestionIsthisarelation?,AnswerYes:uniquerowsandnomultivaluedattributes,QuestionWhatstheprimarykey?,AnswerComposite:Emp_ID,Course_Title,26,PrenticeHall,2002,AnomaliesinthisTable,InsertioncantenteranewemployeewithouthavingtheemployeetakeaclassDeletionifweremoveemployee140,weloseinformationabouttheexistenceofaTaxAccclassUpdategivingasalaryincreasetoemployee100forcesustoupdatemultiplerecords,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#inatableinthiscasebotharecandidatekeysEachnon-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)determinesthevalueofanotherattributeABBisfunctionallydependantonAAiscalledthedeterminantSINName,Birthdate,Address?ISBNTitle,AuthorEmp_ID,Course_NameDate_Completedeg,FunctionalDependencies,33,PrenticeHall,2002,CandidateKey,Definition:Attribute(orcombination)thatuniquelyidentifiesarowinarelationOneofthecandidatekeyswillbecometheprimarykeyE.g.perhapsthereisbothcreditcardnumberandSS#inatableinthiscasebotharecandidatekeys,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),1NFFirstNormalForm,38,PrenticeHall,2002,Figure5-10:Mappingamultivaluedattribute,(a),39,PrenticeHall,2002,40,PrenticeHall,2002,2NFSecondNormalForm,Arelationthatisin1NFandhaveeverynon-keyattributefullyfunctionallydependentontheprimarykeyeg.EMPLOYEE2PartialfunctionaldependencyOneormorenon-keyattributesaredependentonpart(butnotall)theprimarykey,41,PrenticeHall,2002,Fig5.23(b)FunctionalDependenciesinEMPLOYEE2,Dependencyonentireprimarykey,Dependencyononlypartofthekey,EmpID,CourseTitleDateCompleted,EmpIDName,DeptName,Salary,Therefore,NOTin2ndNormalForm!,42,PrenticeHall,2002,Gettingitinto2ndNormalForm,Seep193decomposedintotwoseparaterelations,Botharefullfunctionaldependencies,43,PrenticeHall,2002,EMPLOYEE1,EMP_COURSE,EMPLOYEE2,44,PrenticeHall,2002,Guarantee2NFifrelationis1NFandeitherPrimarykeyisasingleattributeNonon-keyattributesinrelation(everyattributeispartofthekey),45,PrenticeHall,2002,3NFThirdNormalForm,Arelationthatisin2NFandhasnotransitivedependenciespresentTransitiveDependencyFunctionaldependencybetweentwoormorenon-keyattributes,46,PrenticeHall,2002,3NFThirdNormalForm,Figure6-24,TransitiveDependency,47,PrenticeHall,2002,Figure5-24(b)Relationwithtransitivedependency,CustIDNameCustIDSalespersonCustIDRegionAllthisisOK(2ndNF),48,PrenticeHall,2002,3NFThirdNormalForm,49,PrenticeHall,2002,Figure5.25(b)Relationsin3NF,Now,therearenotransitivedependenciesBothrelationsarein3rdNF,CustIDNameCustIDSalesperson,SalespersonRegion,50,PrenticeHall,2002,OtherNormalForms(fromAppendixB),Boyce-CoddNFAlldeterminantsarecandidatekeysthereisnodeterminantthatisnotauniqueidentifier4thNFNomultivalueddependencies5thNFNo“losslessjoins”Domain-keyNFThe“ultimate”NFperfecteliminationofallpossibleanomalies,51,PrenticeHall,2002,HigherNormalForms,5NFFifthNormalFormDealswith“loss-lessjoins”DKNFDomainKeyNormalFormUltimatethattakesintoaccountallpossibledependenciesandconstraintsdependsuponthekey,thewholekey,andnothingbutthekey,sohelpyouCodd,52,PrenticeHall,2002,Borrowbook,53,PrenticeHall,2002,Borrowbook,1NFRemovemultivaluedattributes,54,PrenticeHall,2002,Borrowbook,2NFRemovepartialdependencies,student,55,PrenticeHall,2002,Borrowbook,2NFRemovepartialdependencies,book,56,PrenticeHall,2002,student,3NFRemovetransitivedependencies,Borrowbook,book,57,PrenticeHall,2002,PROJECT,58,PrenticeHall,2002,PROJECT,PROJECTRemovepartialdependencies,PROJECT-EMPLOYEERemovepartialdependencies,HOURS-ASSGNED,EMPLOYEE,EMPLOYEE,DEPARTMENT,59,PrenticeHall,2002,PROJECT,PROJECT,HOURS-ASSGNED,EMPLOYEE,Removepartialdependencies,60,PrenticeHall,2002,PROJECT,EMPLOYEE,DEPARTMENT,Removetransivedependencies,PROJECT,HOURS-ASSGNED,
展开阅读全文