A first course in database systems(3)

《A first course in database systems(3)》由会员分享，可在线阅读，更多相关《A first course in database systems(3)（123页珍藏版）》请在金锄头文库上搜索。

1、3TheRelationalDataModelWhatarerelationaldatamodels?HowtoconvertE/Rmodelsintorelationaldatamodels?Howtoassure(保证保证)certainformconditionbydecomposing(分解分解)relationalmodel? 3.1BasicsoftheRelationalModelRelation:Atwo-dimensionaltablecalledarelation.titleyearlengthfilmTypeStar WarsWaynes WorldMighty Duck

2、s19771992199112495104colorcolorcolorMoviesAttributes(columnheaders)Tuples(rows)3.1BasicsoftheRelationalModelStars-intitleyearstarNameStar Wars1977Carrie FisherStar Wars1977Mark HamillWaynes World1992Dana Carvey3.1.1AttributesAttribute(属性属性):Namesforthecolumnsoftherelation,describethemeaningofentries

3、inthecolumnbelow.SuchaslengthofMovies.Anattributehaveaname.Anytwoattributesofarelationcanthavesamename.3.1.2Schemas(模式模式)Relation schema=relationnameandsetofattributes.Example:Movies(title,year,length,filmtype) orMovies(title:string,year:int,length:int,filmtype:string) Database=collectionofrelations

4、.Database schema=setofallrelationschemasinthedatabase.3.1.3TuplesTuples(元组元组):Therowsofarelation,otherthantheheaderrowcontainingtheattributes,arecalledtuples.Theremaybenotupleinarelation.Atuplehasonecomponent(分量分量)foreachattributeoftherelation. Howtodescribeatuple?Usecommastoseparatecomponents,andus

5、eparentheses(圆括号圆括号)tosurroundthetuple.Example:(StarWars,1977,124,color)Weshouldalwaysusetheorderinwhichtheattributeswerelistedintherelationschema.3.1.3TuplesThemappingoftuplesandobjects:ArelationaclassAtupleaobjectAcomponentofatupleapropertyofaobjectThedifferenceoftuplesandobjects:Objectshaveidenti

6、ties(标识标识),whiletupleshavenot.Aclasscouldhavetwodifferentobjectswiththesamevaluesinallattributes,butatuplecantappearmorethanonceinarelation.3.1.4DomainsDomains:Adomainisanelementary(基本的基本的)type,suchasinteger,char(n),date,time.Eachattributeofarelationisadomain,thatis,aparticularelementarytype.Eachcom

7、ponentofanytuplemustbeatomic(原子的原子的).Atomic:Cannotbebrokenintosmallercomponents3.1.5EquivalentRepresentationsofaRelationWecanreordertheattributesofarelation,withoutchangingtherelation.Wecanreorderthetuplesofarelation,withoutchangingtherelation. 3.1.6RelationInstancesTherelationshipbetweenschemaandtu

8、ples:Theschemaofarelationisrelativelystatic,whilethetupleschangeovertime.Relationinstances(实例实例):Asetoftuplesforarelationisaninstanceoftherelation.Theinstanceofarelationchangesovertime.Thesetoftuplesthatareintherelation“now”is“currentinstance(当前实例当前实例)”.3.1BasicsoftheRelationalModelExercises:p643.1.

9、1 3.2FromE/RDiagramstoRelationalDesignsHowtoconvertanE/Rdiagramtoarelation? Mainidea:ConversionfromentitysetstorelationsConversionfromrelationshipstorelationsHandlingweakentitysets3.2.1FromEntitySetstoRelationsEntityset-relation.Attributes-attributes.MoviestitleyearlengthfilmtypeStars-inOwnsStarsStu

10、diosnameaddressnameaddressMovies(title,year,length,filmType)Stars(name,address)Studios(name,address)3.2.2FromE/RRelationshipstoRelationsHowtoconvertmultiwayrelationshipstorelations?Convertmultiway(多元多元)relationshipstobinary(二元二元)relationshipsfirstly. Howtoconvertbinaryrelationshipstorelations?Accord

11、ingtomultiplicityoftherelationship:1.Amany-manyrelationshipRfromE1toE2ConvertRtoarelationwhoseattributesarecomposedoftheattributesofRandthekeyattributesofE1andE2.Example:Enrollment(注册注册)betweenStudentsandCoursesEnrollment(Sno,Cno,score)3.2.2FromE/RRelationshipstoRelationsMoviestitleyearlengthfilmtyp

12、eStars-inOwnsStarsStudiosnameaddressnameaddressMovies(title,year,length,filmType)Stars(name,address)Studios(name,address)Stars-in(title,year,starName)3.2.2FromE/RRelationshipstoRelations2.Amany-onerelationshipRfromE1toE2Rcanbeconvertedtoarelation,butnotmustbe.AddthekeyattributesofE2totherelationE1.E

13、xample:BelongbetweenDepartmentsandEmployeesEmployees(EmpID,name,.)Departments(DeptID,name)Belong(EmpID,DeptID),DeptID3.2.2FromE/RRelationshipstoRelationsMoviestitleyearlengthfilmtypeStars-inOwnsStarsStudiosnameaddressnameaddressMovies(title,year,length,filmType)Stars(name,address)Studios(name,addres

14、s)Stars-in(title,year,starName)Movies(title,year,length,filmType,studiosName)3.2.2FromE/RRelationshipstoRelations3.Aone-onerelationshipRfromE1toE2Rcanbeconvertedtoarelation,butusuallynot.Therearetwoways:AddthekeyattributesofE2totherelationE1.AddthekeyattributesofE1totherelationE2.Anone-onerelationsh

15、ipsmaybemergedintoanother.Example:HeaderbetweenDepartmentsandEmployeesEmployees(EmpID,name,.,leadDept)Departments(DeptID,name)Employees(EmpID,name,.)Departments(DeptID,name,leaderID)3.2.4HandlingWeakEntitySetsConversionfromweakentitysetstorelations ：ConvertweakentitysetEtoarelationwhoseattributesare

16、composedoftheattributesofEandthekeyattributesofF.CannotconvertRtoarelation.ERF3.2.4HandlingWeakEntitySetsStudios(name,address)Crews(studioName,number)CrewsnumberUnit-ofStudiosnameaddress3.2.4HandlingWeakEntitySetsMoviesStarsStudiosSigndateSalarynameaddrtitleyearnameaddrStar-ofMovie-ofStudio-ofContra

17、ctsContracts(starName,studioName,title,year,salary,signdate)3.3ConvertingSubclassstructurestoRelationslSubclasses:ThreeApproaches 1.Object-oriented :Onerelationpersubsetofsubclasses,withallrelevantattributes.2.Use nulls :Onerelation;entitieshaveNULLinattributesthatdontbelongtothem.3.E/R style :Onere

18、lationforeachsubclass:Keyattribute(s).Attributesofthatsubclass.3.3ConvertingSubclassstructurestoRelationslExample:MoviestitleyearlengthfilmtypeMurder-Mysteriesisaweapon3.3ConvertingSubclassstructurestoRelationslObject-orientedmovies ( title, year, length, FilmType)Murder-Mysteries (title, year, leng

19、th, FilmType, weapon)lUse nullsmovies (title, year, length, FilmType, weapon)lE/R stylemovies ( title, year, length, FilmType)Murder-Mysteries (title, year, weapon)DepartmentSalesmanCustomerSalesorderProductSalesitemsalesforrunsforasheaderworksforunit-ofcontainsdeptidnameempididnonamegenderphonename

20、provcityphonecompanyordernosigndatecustidlinenosalescostquantitytypespecpricedescprodidfactoryconverttorelationsAmarketingmanagementsystemCustomer(custid,name,prov,city,phone,unit)Product(prodid,factory,type,spec,price,desc)Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,name,headerid

21、)Salesorder(orderno,signdate,empid,custid)Salesitem(orderno,lineno,prodid,singlecost,quantity)3.2FromE/RDiagramstoRelationalDesignsExercises:P753.2.13.2.23.4FunctionalDependencies(函数依赖函数依赖)用用形形式式化化方方法法研研究究一一个个关关系系中中各各属属性性之之间间的语义关系。的语义关系。Mainidea:MeaningofFDsKeysSuperkeysFindkeysfromE/Rdiagrams3.4.1D

22、efinitionofFunctionalDependencyX-Aisanassertion(断言断言)aboutarelationRthatwhenevertwotuplesofRagreeonalltheattributesofX,thentheymustalsoagreeontheattributeA.Say“X-Aholds(成立成立)inR.”Say“XfunctionallydetermineA.” tuXAIf t and uagreehere,Then theymust agreehere3.4.1DefinitionofFunctionalDependency若若关关系系R

23、的的任任意意两两个个元元组组在在属属性性A1、A2、An上上的的值值一一致致(即即有有相相同同分分量量值值)，则则这这两两个个元元组组在在属属性性B上上的的值值必必也也一一致致，称称属属性性A1A2An函函数数决决定定B，或称属性或称属性B函数依赖于函数依赖于A1A2An。记为：记为：A1A2AnBExample：InStudents(sno,sname,gender)snosnamesnogenderconsider：snamegender？3.4.1DefinitionofFunctionalDependencyLetusconsidertherelationstudents(sno,sn

24、ame,cno,score) snosname？and：snoscore？or：snocnoscore?3.4.1DefinitionofFunctionalDependencyCorollary(推论推论)1:A1A2AnB1A1A2AnB2A1A2AnBmiff(ifandonlyif)A1A2AnB1B2BmCorollary2:AA3.4.1DefinitionofFunctionalDependencyExample3.12：LetusconsidertherelationMovies(title,year,length,filmType,studioName,starName)ti

25、tleyearlengthfilmTypestudioNamestarNameStarWarsStarWarsStarWarsMightyDucksWaynesWorldWaynesWorld 197719771977199119921992 1241241241049595 colorcolorcolorcolorcolorcolor FoxFoxFoxDisneyParamountParamount CarrieFisherMarkHamillHarrisonFordEmilioEstevezDanaCarveyMikeMeyers titleyearlengthfilmTypestudi

26、oNameconsider：titleyearstarName?3.4.1DefinitionofFunctionalDependencyConsiderthefunctionaldependenciesinrelation student ( stuid, name, gender, depid, dorm, courseid, coursename, score ).stuidcourseidscorestuidnamegenderdepiddormdepiddorm,courseidcoursename如何分析一个具体关系中的函数依赖如何分析一个具体关系中的函数依赖：根据属性之间的语义关

27、系分析函数依赖。根据属性之间的语义关系分析函数依赖。应考虑所有可能的属性组合。应考虑所有可能的属性组合。尽可能使函数依赖式的左面最小化，而右面最大化。尽可能使函数依赖式的左面最小化，而右面最大化。注注意意：函函数数依依赖赖是是针针对对关关系系模模式式，而而不不是是针针对对特特定定实实例，故只从关系实例不能确切断定函数依赖。例，故只从关系实例不能确切断定函数依赖。stuidnamegender？namegender?stuidscore?stuidcourseid?stuidcourseidscore?3.4.1DefinitionofFunctionalDependencyThreecases

28、ofassertingfunctionaldependencies:IftwotupleshavethesamevaluesintheirAcomponents,andtheyalsohavethesamevaluesintheirBcomponents,thenABholds.IftwotupleshavethesamevaluesintheirAcomponents,buttheyhavenotthesamevaluesintheirBcomponents,thenABdoesnothold.IfanytwotuplescanthavethesamevaluesintheirAcompon

29、ents,thenABholds. 3.4.1DefinitionofFunctionalDependency常见术语：常见术语：部分函数依赖部分函数依赖：若：若XY，且存在，且存在X的真子集的真子集X，XY，则称，则称Y对对X部分函数依赖。部分函数依赖。完全函数依赖完全函数依赖：若：若XY，且，且Y对对X不是部分函数依赖，不是部分函数依赖，则称则称Y对对X是完全函数依赖。是完全函数依赖。传递函数依赖传递函数依赖：若：若XY，YZ，且，且YX不成立，不成立，则称则称Z对对X是传递函数依赖。是传递函数依赖。3.4.2KeysofRelationsKey:Wesayasetofoneormorea

30、ttributesA1,A2,.,AnisakeyforarelationRif:A1,A2,.,AnfunctionallydetermineallotherattributesoftherelationR.(superkey)Nopropersubset(真子集真子集)ofA1,A2,.,AncanfunctionallydetermineallotherattributesofR.(minimal) 3.4.2KeysofRelationsKeyoftherelationMovies ( title, year, length, filmtype, studioName, starNam

31、e ):title,year?title,year,starName?IfABholdsinR(A,B,C),thekeysofR?3.4.2KeysofRelationsPrimarykey:Sometimesarelationhasmorethanonekey,andoneofthekeysisdesignatedastheprimarykey.Candidate(候选候选)key:Allkeysofarelation. 3.4.3Superkey(超键超键)Superkeys:Superset(超集超集)ofakey.Asetofattributesthatcontainsakey.Th

32、oseattributesfunctionallydetermineallotherattributesoftherelation.Therelationshipbetweenkeysandsuperkeys:Keysaresuperkeys.title,year,starNameSomesuperkeysarenotkeys.title,year,length,starName3.4.4DiscoveringKeysforRelationsHowtoexpressakeyofarelationschema?UnderlinetheattributesoftheprimarykeyHowtod

33、eterminethekeyfortherelationcomingfromanentityset?Thekeyfortherelationisthekeyattributesofthisentityset.Example:Movies(title,year,length,fileType)Stars(name,address)Studios(name,address)3.4.4DiscoveringKeysforRelationsHowtodeterminethekeyfortherelationRcomingfromabinaryrelationship?Accordingtomultip

34、licity(多重性多重性)oftherelationship,therearethreecases:many-many:thekeysofbothconnectedentitysetsarethekeyattributesforR.many-onefromE1toE2:thekeyattributesofE1arekeyattributesofR,butthoseofE2arenot.one-one:thekeyattributesforeitheroftheconnectedentitysetsarekeyattributesofR.Example:StarsIn(title,year,s

35、tarName)Owns(title,year,studioName)canbemergedintherelationMovies:Movies(title,year,length,fileType,studioName) DepartmentSalesmanCustomerSalesorderProductSalesitemsalesforrunsforasheaderworksforunit-ofcontainsdeptidnameempididnonamegenderphonenameprovcityphonecompanyordernosigndatecustidlinenosales

36、costquantitytypespecpricedescprodidfactoryCustomer(custid,name,prov,city,phone,unit)Product(prodid,factory,type,spec,price,desc)Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,name,headerid)Salesorder(orderno,signdate,empid,custid)Salesitem(orderno,lineno,prodid,singlecost,quantity)3.

37、5RulesAboutFunctionalDependenciesWhatarerulesaboutfunctionaldependencies?Whyweneedthem?Supposewearetoldofasetofdependenciesthatarelationsatisfies,wecandeduce(演绎出演绎出)otherdependenciesbyrulesaboutfunctionaldependencies.Theabilitytodiscoveradditionaldependenciesisessentialwhenwediscussthedesignofgoodre

38、lationschemas.3.5RulesAboutFunctionalDependenciesExample3.17:p90IfwearetoldthatarelationR(A,B,C)satisfiesthefunctionaldependenciesABandBC,thenwecandeduce(推论推论)thatRalsosatisfiesthedependencyAC.Proof:Set(a,b1,c1)and(a,b2,c2)asthetuplesofR.SinceAB,thenb1=b2SinceBC,thenc1=c2So:ACisholdinR.3.5RulesAbout

39、FunctionalDependenciesSomeimportantrulesaboutfunctionaldependencies:Thesplitting/combiningrule(分解分解/合并规则合并规则)Thetrivial-dependencyrule(平凡依赖规则平凡依赖规则)Thetransitiverule(传递规则传递规则)Armstrongsaxioms (公理公理)3.5RulesAboutFunctionalDependencies两个函数依赖集之间的关系：两个函数依赖集之间的关系：设设T是关系是关系R上的函数依赖集，若对上的函数依赖集，若对R的所有实例，的所有实

40、例，函数依赖函数依赖XY都成立，则称都成立，则称T“逻辑蕴含逻辑蕴含”XY。(XY可由可由T推导出来推导出来)设设S是关系是关系R上的另一函数依赖集，若对上的另一函数依赖集，若对R的所有实的所有实例，例，S中的所有函数依赖均成立，则称函数依赖集中的所有函数依赖均成立，则称函数依赖集S“蕴含于蕴含于”函数依赖集函数依赖集T。(S可由可由T推导出来推导出来)若函数依赖集若函数依赖集S“蕴含于蕴含于”T，同时，同时T“蕴含于蕴含于”S，则函数依赖集则函数依赖集S“等价于等价于”函数依赖集函数依赖集T。3.5RulesAboutFunctionalDependenciesTherelationship

41、betweentwosetsoffunctionaldependencies:IfeveryrelationinstancethatsatisfiesallthedependenciesinasetoffunctionaldependenciesTalsosatisfiesallthedependenciesinasetoffunctionaldependenciesS,wesaythatSfollowsfrom(蕴蕴含于含于)T.IfSfollowsfromTandTfollowsfromS,thenSandTareequivalent(等价等价).3.5RulesAboutFunction

42、alDependencies蕴含和等价关系的作用：蕴含和等价关系的作用：可用等价的更简单的函数依赖集代替复杂的函可用等价的更简单的函数依赖集代替复杂的函数依赖集。数依赖集。可在一个函数依赖集可在一个函数依赖集S中增加蕴含其中的其它中增加蕴含其中的其它函数依赖。函数依赖。可判断一个函数依赖是否蕴含于已知的函数依可判断一个函数依赖是否蕴含于已知的函数依赖集。赖集。 3.5.1TheSplitting/CombiningRuleSplitting/CombiningRule:A1A2AnB1B2BmisashorthandforA1A2AnB1A1A2AnB2A1A2AnBmThereisnospl

43、ittingruleorcombiningruleforleftsides.3.5.1TheSplitting/CombiningRuleExample：InMoviestitleyearlengthfilmTypestudioNameisequivalentto：titleyearlengthtitleyearfileTypetitleyearstudioNameBut，snocnoscorecannotsplitthetheleftsideinto：snoscorecnoscore3.5.2TrivialDependenciesTrivialdependency(平凡依赖平凡依赖):Afu

44、nctionaldependencyA1A2.AnBissaidtobetrivialifBisoneoftheAs.Everytrivialdependencyholdsineveryrelation.AdependencyA1A2.AnB1B2.BmisTrivialiftheBsareasubsetoftheAs.NontrivialifatleastoneoftheBsisnotamongtheAs.Completelynontrivial(完全非平凡完全非平凡)ifnoneoftheBsisalsooneoftheAs.Example:title yearyear lengthisn

45、ontrivialtitle yearlengthiscompletelynontrivial3.5.2TrivialDependenciesThetrivial-dependencyrule:Wecanremovefromtherightsideofafunctionaldependencythoseattributesthatappearontheleft.Thatis:ThefunctionaldependencyA1A2.AnB1B2.BmisequivalenttoA1A2.AnC1C2.Ck,wheretheCsareallthoseBsthatarenotalsoAs. ABCt

46、uIftanduagree intheAsThentheymust agreeintheBsSosurelytheyagreeintheCs3.5.3ComputingtheClosureofAttributes属性的闭包属性的闭包 设设S是关系是关系R上的函数依赖集，上的函数依赖集，A=A1,A2,An是是R上的属性集，上的属性集，则属性集则属性集A可函数决定的最大属性集合称做可函数决定的最大属性集合称做A的闭包，的闭包，记做：记做：A+(或或A+s)。这个集合如何计算？这个集合如何计算？这种计算有何用途？这种计算有何用途？ 3.5.3ComputingtheClosureofAttribu

47、tesTheclosure(闭包闭包)ofattributes:SupposeA1,A2,.,AnisasetofattributesandSisasetoffunctionaldependencies.TheclosureofA1,A2,.,AnunderthedependenciesinSisthesetofattributesBsuchthateveryrelationthatsatisfiesallthedependenciesinsetSalsosatisfiesA1A2.AnB.Thatis,A1A2.AnBfollowsfromthedependenciesofS.Wedenot

48、e(表示表示)theclosureofasetofattributesA1,A2,.,AnbyA1,A2,.,An+.A1,A2,.,AnarealwaysinA1,A2,.,An+.IfA1A2.AnX,thenX B.3.5.3ComputingtheClosureofAttributes计算属性的闭包：计算属性的闭包：对于给定的函数依赖集对于给定的函数依赖集S，和属性集，和属性集A=A1,A2,An1.设属性集设属性集X是是A的闭包，将的闭包，将X初始化为初始化为A1,A2,An，即为闭包的最小集合。，即为闭包的最小集合。2.对于对于S中的每个函数依赖式：中的每个函数依赖式：B1B2Bm

49、C。如果如果B1,B2,Bm都在都在X中，而中，而C不在不在X中，则把中，则把C加加入入X中。中。3.重复第重复第2步，直到遍历完步，直到遍历完S中所有函数依赖，而没有中所有函数依赖，而没有新属性能加入到新属性能加入到X中。中。4.属性集属性集X即为属性集即为属性集A在函数依赖集在函数依赖集S下的闭包下的闭包A+。 3.5.3ComputingtheClosureofAttributesExample3.19:p93LetusconsiderarelationwithschemaR(A,B,C,D,E,F)andasetoffunctionaldependenciesS:ABC,BCAD,DE

50、,CFBPleasecomputerA,B+.Solution：FirstX(1)=A,BSinceABC，soX(2)=A,B,CSinceBCAD，soX(3)=A,B,C,DSinceDE，soX(4)=A,B,C,D,ENomoredependencycanbeused,thusA，B+=A,B,C,D,E3.5.3ComputingtheClosureofAttributesWhywecomputetheclosureofattributes?SupposewehavearelationRwithasetofdependenciesS,andadependencyA1A2.AnB.W

51、ecantestwhetherthedependencyfollowsfromthedependenciesofS.ComputerA1,A2,.,An+usingthesetofdependenciesS.IfBisinA1,A2,.,An+,thenA1A2.AnBfollowsfromS.IfBisnotinA1,A2,.,An+,thenA1A2.AnBdoesnotfollowfromS.A1A2.AnB1B2.BmfollowsfromsetofdependenciesSiffAllofB1,B2,.,BmareinA1,A2,.,An+. 3.5.3ComputingtheClo

52、sureofAttributesExample3.20:LetusconsiderarelationwithschemaR(A,B,C,D,E,F)andasetoffunctionaldependenciesS:ABC,BCAD,DE,CFBPleasetestwhetherABDandDAfollowfromthedependenciesofS? 3.5.3ComputingtheClosureofAttributesClosuresandkeys:A1,A2,.,An+isthesetofallattributesifandonlyifA1,A2,.,Anisasuperkeyforth

53、erelation.3.5.5TheTransitiveRuleThetransitive(传递传递)rule:Cascade(级联级联)twofunctionaldependencies.IfA1A2.AnB1B2.BmandB1B2.BmC1C2.CkholdinrelationR,thenA1A2.AnC1C2.CkalsoholdsinR.Example:MovieStudio ( title, year, length, fileType, studioName, studioAddr)title,yearstudioNamestudioNamestudioAddrthentitle

54、,yearstudioAddr 3.5.6ClosingSetsofFunctionalDependenciesWecanderiveanewsetofdependenciesfromasetofgivendependenciesusingrulesaboutfunctionaldependencies.Anysetofgivendependenciesfromwhichwecaninferallthedependenciesforarelationwillbecalledabasis(基基)forthatrelation.Arelationmayhaveseveralbases.Ifnopr

55、opersubsetofthedependenciesinabasiscanalsoderivethecompletesetofdependencies,thenwesaythebasisisminimal.Arelationmayhaveseveralminimalbases.3.5.6ClosingSetsofFunctionalDependenciesExample3.22:p98ConsiderarelationwithschemaR(A,B,C)andasetofdependenciesFD:AB,AC,BA,BC,CA,CBThentheminimalbasesare:AB,BA,

56、BC,CB,AB,BC,CAandsoon.3.5.6ClosingSetsofFunctionalDependenciesArmstrongsaxioms:Reflexivity(自反律自反律).IfB1,B2,Bm A1,A2,An,thenA1A2AnB1B2Bm.(trivial)Augmentation(增长律增长律).IfA1A2AnB1B2Bm,thenA1A2AnC1C2CkB1B2BmC1C2CkforanysetofattributesC1,C2,Ck.Transitivity(传递律传递律).IfA1A2AnB1B2BmandB1B2BmC1C2Ck,thenA1A2An

57、C1C2Ck.3.5.7ProjectingFunctionalDependenciesSupposearelationhasschemaR(U)andasetofdependenciesF. IfRisdecomposedinto S(U1) and T(U2), then we need findwhatfunctionaldependenciesstillholdinS.3.5.7ProjectingFunctionalDependenciesAlgorithm:Consider each set of attributes X that iscontained in the set o

58、f attributes of S.ComputeX+.ThenforeachattributeBsuchthat1)BisanattributeofS,2)BisinX+,and3)BisnotinX,thefunctionaldependencyXBholdsinS.3.5.7ProjectingFunctionalDependencies Example:LetRhaveschemaR(A,B,C,D),andsupposethefunctionaldependenciesABandBCaregivenforR.LetS(A,C)beoneoftherelationinadecompos

59、itionofR.PleasecomputethedependenciesthatholdinS.Solution:A+=A,B,C,thenACholdsinS.C+=CA,C+=A,B,C3.5.7ProjectingFunctionalDependencies Example:ConsiderR(A,B,C,D,E)decomposedintoS(A,B,C)andanotherrelation.LetthefunctionaldependenciesofRbeAD,BEandDEC.PleasecomputethedependenciesthatholdinS.Solution:A+=

60、A,DB+=B,EC+=CA,B+=A,B,C,D,E,thenABCholdsinSA,C+=A,C,DB,C+=B,C,EA,B,C+=A,B,C,D,EExercise3.5.1ConsiderarelationwithschemaR(A,B,C,D)andfunctionaldependenciesABC,CDandDA.a)Whatareallthenontrivialfunctionaldependencies that follow from thegivendependencies?b)WhatareallthekeysofR?c)Whatareallthesuperkeysf

61、orRthatarenotkeys?solution：(a)ComputingtheClosuresofallthesubsetofattributes:A+=AB+=BC+=C,D,ACADD+=D,AA,B+=A,B,C,DABCDA,C+=A,C,DACDA,D+=A,DB,C+=B,C,D,ABCADB,D+=B,D,A,CBDACC,D+=C,D,ACDAA,B,C+=A,B,C,DABCDA,B,D+=A,B,D,CABDCA,C,D+=A,C,DB,C,D+=B,C,D,ABCDAA,B,C,D+=A,B,C,D(b)Allkeys：A,B,B,C,B,D(c)Allsuperk

62、eys：A,B,B,C,B,DA,B,C,A,B,D,B,C,D,A,B,C,D3.5RulesAboutFunctionalDependenciesExercise:P1003.5.2i)3.6DesignofRelationalDatabaseSchemasCausesofredundancy(冗余冗余)：Afactisrepeatedinmorethanonetuple. Themostcommoncausefortheredundancy:attempsto group into one relation both single-value andmultivaluedproperti

63、esofanobject.sno snameaddresscno score depid dhead S1S1S1S2S3S3S4S4 N1N1N1N2N3N3N4N4A1A1A1A2A3A3A4A4C1C2C3C2C1C3C1C2 AABBAABA D1D1D1D1D2D2D2D2 M1M1M1M1M2M2M2M2 Example:StudentHowtosolvethisredundancy?1)Exploreinmoredetailtheproblemsthatarisewhenourschemaisflawed(有缺陷的有缺陷的).2)Introduce the idea of “de

64、composition”, breaking arelationschemaintotwosmallerschemas.3)Introduce“Boyce-Coddnormalform”,or“BCNF”,acondition on a relation schema that eliminates theseproblems.4)Explain how to assure the BCNF condition bydecomposingrelationschemas.5)Relax BCNF requirement slightly, and introduce“Thirdnormalfor

65、m”,or“3NF”.6)Introduce“1NF”and“2NF”. 3.6DesignofRelationalDatabaseSchemas3.6.1Anomalies(异常异常)Anomaly:即不符合规范的设计，导致操作数据库时，出现影响即不符合规范的设计，导致操作数据库时，出现影响数据一致性的现象。数据一致性的现象。Goalofrelationalschemadesignistoavoidanomalies.Theprincipalkindsofanomalies:Redundancy:Informationmayberepeatedunnecessarilyinseveraltu

66、pels.Update Anomalies(更更 新新 异异 常常 ): We may changeinformation in one tuple but leave the sameinformationunchangedinanother.DeletionAnomalies(删删除除异异常常). validfactislostwhenatupleisdeleted.3.6.1Anomalies(异常异常)sno snameaddresscno score depid dhead S1S1S1S2S3S3S4S4 N1N1N1N2N3N3N4N4A1A1A1A2A3A3A4A4C1C2C3

67、C2C1C3C1C2 AABBAABA D1D1D1D1D2D2D2D2 M1M1M1M1M2M2M2M2 Example:Student3.6.2DecomposingRelations Decomposingrelations:GivenarelationRwithschemaA1,A2,.,An,wemaydecomposeRintotworelationsSandTwithschemasB1,B2,.,BmandC1,C2,.,Ck,suchthatA1,A2,.,An=B1,B2,.,BmC1,C2,.,Ck.ThetuplesinrelationSaretheprojections

68、ontoB1,B2,.,BmofallthetuplesinR.ThetuplesinrelationTaretheprojectionsontoC1,C2,.,CkofallthetuplesinR. 3.6.2DecomposingRelations Projection:ForeachtupletinthecurrentinstanceofR,takethecomponentsoftintheattributesB1,B2,.,Bm.Keeponlyonecopyofeachtuple. 3.6.1Anomalies(异常异常)sno snameaddresscno score depi

69、d dhead S1S1S1S2S3S3S4S4 N1N1N1N2N3N3N4N4A1A1A1A2A3A3A4A4C1C2C3C2C1C3C1C2 AABBAABA D1D1D1D1D2D2D2D2 M1M1M1M1M2M2M2M2 Example:Student3.6.2DecomposingRelationsExample:StudentEnrollment分解后的两个关系减分解后的两个关系减少了异常。少了异常。这两个关系应可通过这两个关系应可通过连接连接(join)运算恢复运算恢复为原有的关系。为原有的关系。snosnameaddressdepiddheadS1S2S3S4 N1N2N3

70、N4A1A2A3A4D1D1D2D2M1M1M2M2snocnoscoreS1S1S1S2S3S3S4S4 C1C2C3C2C1C3C1C2AABBAABA3.6.3Boyce-CoddNormalForm 范式范式(NormalForm)是什么：是什么：关系模式所满足的不同程度的要求关系模式所满足的不同程度的要求(规范形式规范形式)。若关系模式若关系模式R的每个分量均是不可再分的数据的每个分量均是不可再分的数据项，则项，则R满足第一范式，又记作：满足第一范式，又记作：R1NF。在第一范式在第一范式(1NF)的基础上，逐步加强条件，分的基础上，逐步加强条件，分别有：别有：2NF,3NF,BCN

71、F,4NF,5NF。一个较低级别的关系模式，可通过分解的方式，一个较低级别的关系模式，可通过分解的方式，转换成若干个较高级别的关系模式，这种过程叫转换成若干个较高级别的关系模式，这种过程叫做做关系规范化关系规范化。3.6.3Boyce-CoddNormalForm UnderBCNF,theanomalies(异常异常)canbeguaranteednottoexist.WesayarelationRisinBCNFifwheneverX -A isanontrivialFDthatholdsinR,X isasuperkey.Remember: nontrivial meansAisnota

72、memberofsetX.Remember,asuperkeyisanysupersetofakey(notnecessarilyapropersuperset).3.6.3Boyce-CoddNormalForm RinBCNF:ThereisnonontrivialdependencyinR.(nonebuttrivialdependency)The left side of every nontrivial functionaldependencymustcontainakey.(superkey)3.6.3Boyce-CoddNormalForm Example:Isstudent(s

73、no,sname,address,cno,score,depid,dhead)inBCNF？Keys：sno,cnoNontrivialfunctionaldependencies:snosnameaddressdepiddheadDecomposeintotworelations:enrollment(sno,cno,score)andstudent(sno,sname,address,depid,dhead),aretheyinBCNF？Method：a)findoutallkeysb)checktheleftsideofallnontrivialfunctionaldependencie

74、s3.6.3Boyce-CoddNormalForm Anytwo-attributerelationisinBCNFSupposing that the attributes are A and B,therearefourcasestoconsider:1)Therearenonontrivialfunctionaldependencies.2)ABholds,butBAdoesnothold.Aistheonlykey,andeachnontrivialdependencycontainskeyontheleft.3)BAholds,butABdoesnothold.Bistheonly

75、key,andeachnontrivialdependencycontainskeyontheleft.4)Both AB and BA hold, then both A and B arekeys.Surelyanydependencyhasatleastoneoftheseontheleft.3.6.3Boyce-CoddNormalFormTheremaybeseveralkeysforarelation,andtheBCNFconditiononlyrequiressome keybecontainedintheleftsideofanynontrivialdependency. 3

76、.6.4DecompositionintoBCNFWecanbreakanyrelationschemaintoacollectionofsubsetofitsattributeswiththefollowingimportantproperties:1)ThesesubsetsaretheschemasofrelationsinBCNF.2)The data in the original relation isrepresented faithfully by the data in therelations that are the result of thedecomposition,

77、thatis,weneedtobeabletoreconstruct the original relation exactlyfromthedecomposedrelations.3.6.4DecompositionintoBCNFDecompositionstrategy(策略策略):1)FindaBCNF-violatingdependencyA1A2.AnB1B2.Bm.2)DecomposetherelationRintotworelations:R1(A1,A2,.An,B1,B2.Bm)R2(A1,A2,.,An,alltheotherattributesofR)3)Repeat

78、decompositionprocesstillallrelationsareinBCNF.3.6.4DecompositionintoBCNF Example:student(sno,sname,address,cno,score,depid,dhead)isnotinBCNF.1snosnameaddressdepiddheadisaBCNFviolation.2Decomposestudentinto:student(sno,sname,address,depid,dhead)enrollment(sno,cno,score)分解消除了非键属性对键的部分依赖。分解消除了非键属性对键的部分

79、依赖。3depiddheadisaBCNFviolationinstudent.4Decomposestudentinto:department(depid,dhead)student(sno,sname,address,depid)分解消除了非键属性对键的传递依赖。分解消除了非键属性对键的传递依赖。3.6.5RecoveringInformationfromaDecomposition snocnoscoresnameaddressdepiddheadS1S1S1S2S3S3S4S4 C1C2C3C2C1C3C1C2AABBAABAN1N1N1N2N3N3N4N4A1A1A1A2A3A3A4

80、A4D1D1D1D1D2D2D2D2M1M1M1M1M2M2M2M2snosnameaddressdepiddheadS1S2S3S4 N1N2N3N4A1A2A3A4D1D1D2D2M1M1M2M2snocnoscoreS1S1S1S2S3S3S4S4 C1C2C3C2C1C3C1C2AABBAABA如果分解是基于函数如果分解是基于函数依赖进行的，则分解后的依赖进行的，则分解后的关系应能够采用关系应能够采用“连接连接”的方式，准确重构原有的的方式，准确重构原有的关系关系。3.6.5RecoveringInformationfromaDecomposition snocnoS1S2C1C1sn

81、ocnoscoreS1S2C1C1ABsnocnoscoreS1S1S2S2C1C1C1C1ABAB如果分解不是基于函如果分解不是基于函数依赖进行的，则分解后数依赖进行的，则分解后的关系不能采用的关系不能采用“连接连接”的方式，准确重构原有的的方式，准确重构原有的关系关系。cnoscoreC1C1AB3.6.6ThirdNormalFormSome relations are not in BCNF, but they can notdecomposefurther.Example3.32：Booking(movie,theater,city)Atuple(m,t,c)meansthatthe

82、moviewithtitlemiscurrentlybeingshownattheatertincityc.Dependencies:一座城市有多个影院；每个影院对应唯一的城市。一座城市有多个影院；每个影院对应唯一的城市。每个影院可放映多部影片；每部影片可在多个影院放映。每个影院可放映多部影片；每部影片可在多个影院放映。同一个城市的两个影院不会放映同一部影片。同一个城市的两个影院不会放映同一部影片。so：theatercitymoviecitytheaterkeys:movie,cityandmovie,theatertheatercityisaBCNFviolation.3.6.6Third

83、NormalFormCanwedecomposeBookingintotheater,cityandtheater, movie?Supposethesetworelationsasfollow:theatercityGuildParkMenloParkMenloParktheatermovieGuildParkTheNetTheNettheatercitymovieGuildParkMenloParkMenloParkTheNetTheNetReconstructBookingbyjoining:moviecitytheaterdoesnotholdinthisBooking.3.6.6Th

84、irdNormalFormThirdnormalform:ArelationRisinthirdnormalform(3NF),iffWheneverA1A2.AnBisanontrivialdependency,eitherA1,A2,.,Anisasuperkey,orBisamemberofsomekey(keyattribute).INBooking(movie,theater,city),theatercityisBCNFviolation，butcityisamemberofkeymovie,city,sotheBookingisin3NF. 3.6.6ThirdNormalFor

85、mTherearetwoimportantpropertiesofadecomposition:1.Recoverability(可恢复性可恢复性):itshouldbepossibletoprojecttheoriginalrelationsontothedecomposedschema,andthenreconstructtheoriginal.2.DependencyPreservation(保持保持):itshouldbepossibletocheckintheprojectedrelationswhetherallthegivenFDsaresatisfied.3.6.6ThirdN

86、ormalFormWecanget(1)withaBCNFdecomposition.Wecangetboth(1)and(2)witha3NFdecomposition.Butwecantalwaysget(1)and(2)withaBCNFdecomposition.3.6.71NFand2NF Firstnormalform(1NF):Everycomponentofeverytupleisanatomicvalue.Secondnormalform(2NF):Itpermitstransitivedependenciesinarelationbutforbids(禁止禁止)anontr

87、ivialdependencywithaleftsidethatisapropersubset(真子集真子集)ofakey.ForanynontrivialdependencyA1A2.AnB, if B is not a member of anykey,A1A2.Anisnotapropersubsetofanykey。3.6.71NFand2NF1Findthehighestnormalformofstudent(sno,cno,score,depid,dorm)1NF2FindthehighestnormalformofMovies(title,year,length,filmType

88、,studioName,starName)1NF3FindthehighestnormalformofMovieStudio(title,year,length,filmType,studioName,studioAddr)2NFExample：P1173.6.1a) R(A,B,C,D)withfunctionaldependenciesABC,CD,DA：a)IndicatealltheBCNFviolations.b)Decomposetherelations,asnecessary,intocollectionofrelationsthatareinBCNF.c)Indicateall

89、the3NFviolations.Solution：keys：A,B,B,C,B,DBCNFviolations: CAD,DADecomposeRinto: R1(A,C,D),R2(B,C)DecomposeR1into: R11(A,D),R12(C,D)Inprojectedrelations，dependencyABCisnotsatisfied.3NFviolations: None Ris3NF Exercises:P1173.6.1b)c)3.6.23.6.33.7MultivaluedDependencies本节的主要内容：本节的主要内容：属性独立性引发的冗余属性独立性引发的

90、冗余如何定义多值依赖如何定义多值依赖多值依赖规则多值依赖规则 第四范式第四范式如何分解关系使之满足如何分解关系使之满足4NF范式之间的关系。范式之间的关系。 3.7MultivaluedDependenciesCausesofmultivalueddependencies:Twoattributesorsetsofattributesareindependentofoneanother.Attributeindependencemaybringonredundancy.Itcannotberepresentedbyfunctionaldependencies.Multivalueddepend

91、enciesandfunctionaldependencies:Multivalued dependencies are the generalization offunctionaldependencies,andfunctionaldependenciesarespecialcaseofmultivalueddependencies.Every functional dependency is a multivalueddependency.3.7.1AttributesIndependenceandItsConsequentRedundancySixtuplesrepresenttwoa

92、ddressesandthreemoviesofastar.Thequantityoftuples=n(addresses)*m(movies)Thereisobviousredundancy.Yetthereisnonontrivialfunctionaldependencies,soitisinBCNF.Twomultivaluedattributesorrelationshipsinarelationwillbringonredundancy,buttherelationisinBCNF.Example:Star(name,street,city,title,year)namestree

93、tcitytitleyearCarrieFisherCarrieFisherCarrieFisherCarrieFisherCarrieFisherCarrieFisher123MapleSt.5LocustLn.123MapleSt.5LocustLn.123MapleSt.5LocustLn.HollywoodMailbuHollywoodMailbuHollywoodMailbuStarWarsStarWarsEmpireStrikesBackEmpireStrikesBackReturnofJediReturnofJedi1977197719801980198319833.7.1Att

94、ributesIndependenceandItsConsequentRedundancyAttributeindependence:同同一一关关系系中中的的两两个个不不交交叉叉的的属属性性集集均均为为多多值值，且且相相互互没没有有关关联联，需需要要将将两两个个属属性性集集的的所所有有属性值的各种组合一一列出，属性值的各种组合一一列出，导致严重冗余。导致严重冗余。 Twosetsofattributeshavenorelationshipinarelation,andtheyhavesetsofvaluesthatappearinallpossiblecombinations.3.7.2Def

95、initionofMultivaluedDependencies定义：定义：若若关关系系R(A,B,Z)中中的的元元组组，在在属属性性A上上取取某某一一确确定定值值时时，在在属属性性B上上有有一一对对应应值值(集集)，且且该该值值(集集)与与属属性性Z无无关关，则则称称属属性性A与与属属性性B间间存存在在多多值值依依赖赖，记为：记为：AB说明：说明：1当当A上的值确定后，上的值确定后，B上的上的值集值集亦随之确定。亦随之确定。2对于任意的对于任意的(a,z1)和和(a,z2)，均有相同的，均有相同的B集。集。（与（与Z无关）无关）3.7.2DefinitionofMultivaluedDepe

96、ndenciesAmultivalued dependency(MVD)onR (A,B,Z),A-B ,saysthatiftwotuplesofRagreeonalltheattributesofA,thentheircomponentsinBmaybeswapped(交交换换),andtheresultwillbetwotuplesthatarealsointherelation.i.e.,foreachvalueofA,thevaluesofBareindependentofthevaluesofZ.3.7.2DefinitionofMultivaluedDependencies在关系

97、在关系R(A,B,Z)中，存在多值依赖中，存在多值依赖AB，即即若存在若存在R中的两个元组中的两个元组t和和u在属性在属性A上取值上取值一致，分别记为一致，分别记为t(a,b1,z1)和和u(a,b2,z2),则则元组元组v(a,b2,z1)及元组及元组w(a,b1,z2)必亦属于必亦属于R。3.7.2DefinitionofMultivaluedDependenciestuvc2aaAB其它其它b1tuvwAB其它其它namestreetcitytitleyearCarrieFisherCarrieFisherCarrieFisherCarrieFisherCarrieFisherCarri

98、eFisherCarrieFisherCarrieFisherCarrieFisher123MapleSt.5LocustLn.123MapleSt.5LocustLn.123MapleSt.5LocustLn.HollywoodMailbuHollywoodMailbuHollywoodMailbuStarWarsStarWarsEmpireStrikesBackEmpireStrikesBackReturnofJediReturnofJedi197719771980198019831983若当若当A值确定时，无值确定时，无论论Z值如何，均对应唯一值如何，均对应唯一的的B值，则特化为函数依值

99、，则特化为函数依赖。所以，函数依赖是赖。所以，函数依赖是多值依赖的特例。多值依赖的特例。有多值依赖有多值依赖namestreetcity成立成立；同时同时 name titleyear成立成立c2ab13.7.2DefinitionofMultivaluedDependenciesSomecasesofmultivalueddependency:When the values of As attributes are fixed, then thevaluesofBsattributescanbecombinedwiththevaluesofalltheotherattributesfreel

100、y,thenABholds.IfABholds,thenABholds.IfalltheattributesofRareamongAsandBs,thenABholds.department classstudent01010101011111111212S1S2S3S4S5Example:departmentclass?3.7.3ReasoningAboutMultivaluedDependencies平凡依赖规则：平凡依赖规则：若多值依赖若多值依赖AB在关系在关系R上成立，则上成立，则AC成成立立，其其中中C是是B属属性性集集加加上上A的的一一个个或或几几个个属属性性；同同时时AD成成立立

101、，其其中中D是是B属性集中不属于属性集中不属于A的属性。的属性。传递规则：传递规则：若若多多值值依依赖赖AB和和BC在在关关系系R上上成成立，则立，则ACB也成立。也成立。多值依赖没有分解合并规则多值依赖没有分解合并规则例例如如：namestreetcity成成立立；但但namestreet不成立。不成立。3.7.3ReasoningAboutMultivaluedDependencies两个新规则：两个新规则：函函数数依依赖赖规规则则：每每个个函函数数依依赖赖都都是是一一个个多多值值依赖：若依赖：若AB，则，则AB。注意：反之不然。注意：反之不然。互互补补(Complementation)规

102、规则则：若若AB是是R的多值依赖，的多值依赖，则则 AZ亦亦成立成立其中其中Z是是R中不属于中不属于A和和B的所有其它属性。的所有其它属性。故：由故：由namestreetcity可知可知nametitleyear成立成立3.7.4FourthNormalForm非平凡多值依赖：非平凡多值依赖：关系关系R(A,B,Z)中中AB是非平凡多值依是非平凡多值依赖，当且仅当：赖，当且仅当：a)B中属性不在中属性不在A中，即中，即ABb)A和和B未包含未包含R中所有属性，即中所有属性，即ABU（或（或Z）第四范式第四范式(4NF)的定义的定义：关系关系R(A,B,Z)满足满足4NF，当且仅当：，当且仅当

103、：若若AB是是R上的非平凡多值依赖，则上的非平凡多值依赖，则A是是R的超键。的超键。 3.7.4FourthNormalForm关系关系R满足满足4NF的两种情形的两种情形：R中不存在非平凡多值依赖。中不存在非平凡多值依赖。(只有平凡多值依赖只有平凡多值依赖)R中所有非平凡多值依赖的左面包含某个键。中所有非平凡多值依赖的左面包含某个键。注注意意：键键和和超超键键只只与与函函数数依依赖赖有有关关，增增加加多多值值依依赖并不改变键。赖并不改变键。每每个个函函数数依依赖赖都都是是一一个个多多值值依依赖赖，违违背背BCNF定定违背违背4NF；满足满足4NF定满足定满足BCNF。4NF在在BCNF基基础

104、础上上，进进一一步步消消除除非非平平凡凡、非非函函数依赖性质的多值依赖。数依赖性质的多值依赖。3.7.4FourthNormalFormExample:Star(name,street,city,title,year)ThereisanontrivialmultivalueddependencynamestreetcityTheleftsideisnotasuperkey,thentherelationisnotin4NF.ButitisinBCNF.Example:Star(name,gender,street,city,title,year)key:name,street,city,tit

105、le,yearfunctionaldependency：namegendermultivalueddependency：namegenderstreetcitynamegendertitleyearStarisnotin2NF.3.7.5DecompositionintoFourthNormalFormThe4NFdecompostionalgorithmisquiteanalogous to the BCNF decompositionalgorithm.1)Finda4NF-violatingmultivalueddependencyA1A2.AnB1B2.Bm.2)Decomposeth

106、erelationRintotworelations:R1(A1,A2,.,An,B1,B2,.,Bm)R2(A1,A2,.,An,alltheotherattributesofR)3)Repeatdecompositionprocesstillallrelationsarein4NF.3.7.5DecompositionintoFourthNormalFormExample:Star(name,street,city,title,year)1.namestreetcityisa4NFviolation2.decomposeStarinto:R1(name,street,city)R2(nam

107、e,title,year)Thereisno4NFviolationanymore.namestreetcityCarrieFisherCarrieFisher123MapleSt.5LocustLn.HollywoodMailbunametitleyearCarrieFisherCarrieFisherCarrieFisherStarWarsEmpireStrikesBackReturnofJedi1977198019833.7.6RelationshipsAmongNormalFormsRelationsin4NFRelationsinBCNFRelations in3NFRelation

108、sin2NFRelationsin1NFMultivalueddependenciesFunctionaldependenciesAtomicity 对于一个特定关对于一个特定关系，应关注其满足的系，应关注其满足的最高范式，或违背的最高范式，或违背的最低范式。最低范式。property3NFBCNF4NFEliminates redundancy due to functional dependenciesmostyesyesEliminatesredundancyduetomultivalueddependenciesnonoyesPreserves functional dependenc

109、iesyesmaybemaybePreserves multivalued dependenciesmaybemaybemaybe3.7Exercise商品销售业务管理系统的关系设计符合最高范式是什么？商品销售业务管理系统的关系设计符合最高范式是什么？Customer(custid,name,prov,city,phone,unit)Product(prodid,factory,type,spec,price,desc)Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,name,headerid)Salesorder(

110、orderno,signdate,empid,custid)Salesitem(orderno,lineno,prodid,singlecost,quantity)3.7ExerciselBased on the meaning above, find the highestnormalformofeachofthefollowingrelations.Report1(empid,name,custid,custname)Report2(empid, idno, name, gender, phone, deptid,deptname,isheader)/isheader表示是否为部门经理表示

111、是否为部门经理Report3(orderno,signdate,empid,custid,prodid,singlecost,quantity)Report4(empid,name,orderno,signdate,amount)/amount表示一个订单的总金额表示一个订单的总金额Report5(empid,custid,prodid)Report6(empid,orderno,signdate,custid,amount)/amount表示一个订单的总金额表示一个订单的总金额3.7ExerciseP1263.7.13.7.3Consider:3.7.73.8数据库设计数据库设计数据库设计是

112、系统设计的重要部分数据库设计是系统设计的重要部分软件工程中详细介绍系统设计的步骤和方法软件工程中详细介绍系统设计的步骤和方法系统设计的主要步骤：系统设计的主要步骤：系统的需求分析系统的需求分析系统的初步设计系统的初步设计系统的详细设计系统的详细设计系统实施系统实施系统的测试系统的测试系统的运行与维护系统的运行与维护系统设计的主要内容系统设计的主要内容系统的功能设计系统的功能设计系统的数据库设计系统的数据库设计3.8数据库设计数据库设计数据库设计各阶段的主要工作：数据库设计各阶段的主要工作：系统需求分析：系统需求分析：收收集集信信息息，了了解解需需求求，确确定定边边界界，用用数数据据流流图图表表

113、示示系系统统的的功功能能与数据的关系，建立数据字典。与数据的关系，建立数据字典。系统的初步设计系统的初步设计用特定的方法用特定的方法(E/R模型模型)完成数据库的概念结构设计。完成数据库的概念结构设计。系统的详细设计系统的详细设计用特定的方法用特定的方法(关系模型关系模型)完成数据库的逻辑结构设计。完成数据库的逻辑结构设计。系统实施系统实施 确确定定数数据据库库在在物物理理设设备备上上的的存存贮贮结结构构和和存存取取方方法法，完完成成数数据据库库的物理结构设计，建立数据库。的物理结构设计，建立数据库。系统的测试系统的测试 输输入入测测试试数数据据，检检验验设设计计的的正正确确性性，评评价价设设

114、计计的的时时间间和和空空间间效效率。率。系统的运行与维护系统的运行与维护 系系统统的的安安全全性性、完完整整性性控控制制，系系统统的的转转储储及及恢恢复复，系系统统性性能能的的监督、分析及改进。监督、分析及改进。3.9设计实例设计实例教学管理信息系统教学管理信息系统系统的总需求系统的总需求课程设置课程设置设设置置并并管管理理课课程程信信息息，包包括括课课程程号号、课课程程名名、学学分分数数、课程性质、开课系及课程性质、开课系及教学大纲教学大纲教学计划管理教学计划管理录录入入并并管管理理教教学学计计划划，包包括括计计划划编编号号、级级号号、系系、专专业业及及计计划划内内容容（课课程程性性质质：必

115、必修修、必必选选、专专业业选选修修、公公共共选选修修，课程号，学期号）课程号，学期号）学生信息管理学生信息管理录录入入并并管管理理学学生生信信息息：包包括括学学号号、姓姓名名、班班级级、专专业业及及院系院系教师信息管理教师信息管理录录入入并并管管理理教教师师信信息息：包包括括教教工工号号、姓姓名名、所所属属教教研研室室及院系及院系3.9设计实例设计实例教学管理信息系统教学管理信息系统授课任务书管理授课任务书管理根根据据教教学学计计划划下下达达各各系系本本学学期期的的教教学学任任务务：包包括括课课程程号号、课课程程名名、应应授授课课班班级级、学学生生人人数数，由由各各系系填填写写授授课课教教师师名名并并返还教务处。返还教务处。排课排课根根据据授授课课任任务务书书中中的的班班级级、人人数数、教教师师及及教教室室信信息息排排课课表，要求教师、教室及班级不冲突。表，要求教师、教室及班级不冲突。学生选课学生选课根根据据学学号号，提提供供必必要要的的选选课课信信息息，记记录录学学生生的的选选课课结结果果，打印课表。打印课表。成绩录入成绩录入根根据据教教工工号号，允允许许录录入入相相关关课课程程的的学学生生成成绩绩，打打印印成成绩绩单。单。成绩查询成绩查询