(數(shù)據(jù)庫原理課件)Chapter6-Advanced-database-Design

DatabaseDesign.SchoolofsoftwareChapter6AdvancedDatabaseDesigns任課教師：何婧hejing@DatabaseDesign.Schoolofso1DatabaseDesign.SchoolofsoftwareObjectivesHowtodescribeDerivedattribute.Recognizewhenadatabasemodelrequiresarecursiverelationship.Howtodescribecomplexrelationship.HowtoidentifyandsolveproblemsinanERmodelcalledconnectiontraps.Recognizewhenitisappropriatetousegeneralization/specializationhierarchies.DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.1AttributesAttributecanbeclassifiedasbeing:simpleorcomposite(復(fù)雜)single-valuedormulti-valuedorderived（來源）DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributeAttributethatrepresentsavaluethatisderivablefromvalueofarelatedattribute,orsetofattributes,notnecessarilyinthesameentity.

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattribute(SQLServer)createtableemp(empidintnotnullprimarykey,salarymoneynotnull,bonusmoneynotnulldefault0,

total_salaryas(salary+bonus));DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributeinsertintoemp(empid,salary,bonus)values(100,$5000.00,$1000.00);insertintoemp(empid,salary,bonus)values(101,$3000,$500);select*fromemp;100 5000.0000 1000.0000 6000.0000101 3000.0000 500.0000 3500.0000DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributecreatetableemp_tax(taxidintnotnullprimarykey,empidintnotnullforeignkeyreferencesemp(empid),accumulation_fundmoney,medicaremoney,

salarymoney)DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributecreatetriggertrg_for_emp_taxonemp_taxafterinsertasbegindeclare@total_salarymoney,@salary_resultmoney;select@total_salary=total_salaryfromemp,insertedwhereemp.empid=inserted.empid;select@salary_result=@total_salary-accumulation_fund-medicarefrominserted;updateemp_taxsetsalary=@salary_result;end;DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributeinsertintoemp_tax(taxid,empid,accumulation_fund,medicare)values(001,100,$120,$80);select*fromemp_tax;100 120.0000 80.0000 5800.0000DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.2RecursiveRelationshipsExhibit5-1:EnrollmentDatabaseDesignOriginalDesignoftheEnrollmentDatabaseDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareMentorRecursiveRelationshipsExhibit5-2:MentorRelationshipsfor EnrollmentDatabaseInstructor11mentorsinstructors22and33.Instructor33mentorsinstructor44.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareImplementingaRecursiveRelationshipExhibit5-3:DataRelationshipsforMentor RecursiveRelationshipAforeignkey(INSTRUCTOR$id)isaddedtotheINSTRUCTORtable,whichisacopyoftheprimarykeyofthatsametable.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareERDiagramwitha

RecursiveRelationshipExhibit5-4:EnrollmentDatabaseDiagram withRecursiveRelationshipWiththeforeignkeyadded,thereisnowaone-to-manyrelationshipbetweeninstructors.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareBill-of-MaterialsExhibit5-5:Bill-of-MaterialsExampleAbill-of-materialsisastructurethatshowsarelationshipsbetweenproducts.Thisisamany-to-manyrelationship:aproductcanhavemanysub-products,andaproductcanbeasub-productinmanylargerproducts.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareBill-of-MaterialsinTableFormExhibit5-6:BillofMaterialsTableTryingtorepresentamany-to-manyrecursiverelationshipdoesnotworksinceyoucouldnotdeterminehowmanycomponentcolumnswouldbeneeded,andtherewillbealargenumberofemptycells.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareERDiagramforaMany-to-Many

RecursiveRelationshipExhibit5-7:Bill-ofMaterialsERDiagramAswithallmany-to-manyrelationships,amany-to-manyrecursiverelationshipisrepresentedwithanassociativetable.Inthiscase,however,theassociativetableislinkedbacktothesametable(PRODUCT),withbothhalvesofitsprimarykeybeingforeignkeysfromthePRODUCTtable.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareMany-to-Many

RecursiveRelationshipDataExhibit5-8:Bill-of-MaterialsDatabaseTablesDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.3ComplexrelationshipExhibit5-14:SummerReadingFun Step-by-StepDesignMultiplicityisthenumber(orrange)ofpossibleoccurrencesofanentitytypeinann-aryrelationshipwhenother(n-1)valuesarefixed.

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexrelationship

–individualexamplesDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexrelationship

–multiplicityDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware

Complexrelationship–ERdiagramDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware

Complexrelationship–representationastablesDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聯(lián)系之間存在重疊，如何表達(dá)聯(lián)系之間的聯(lián)系？實例：職工參加項目，并在此過程中可能使用機(jī)器ComplexRelationship-聚集職工項目參加并使用姓名工種名稱類型機(jī)器名型號方案1工時機(jī)器DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聚集職工項目參加姓名工種名稱類型機(jī)器名型號方案2工時使用機(jī)器e1在j1項目中使用什么機(jī)器？DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聚集是一種抽象，通過它聯(lián)系被作為高層實體集實體集A與B以及它們的聯(lián)系可被看成實體集，并與另一實體集C發(fā)生聯(lián)系聚集ABR1R2CDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聚集職工項目參加姓名工種名稱類型機(jī)器名型號工時使用機(jī)器DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聚集DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.4ProblemswithERmodelsProblemsmayarisewhendesigninganERmodelcalledconnectiontraps.(連接陷阱)Oftenduetoamisinterpretationofthemeaningofcertainrelationships.Twomaintypesofconnectiontrapsarecalledfantraps

andchasm

traps.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareProblemswithERmodelsFantrap（扇形陷阱）Twoentitieshavea1:*relationshipthatfanoutfromathirdentity,butthetwoentitiesshouldhaveadirectrelationshipbetweenthemtoprovidethenecessaryinformation.

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAnexampleofafantrapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareResolvingthefantrapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareResolvingthefantrapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareProblemswithERmodelsChasmtrap（深坑陷阱）Amodelsuggeststheexistenceofarelationshipbetweenentities,butthepathwaydoesnotexistbetweencertainentityoccurrences.

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAnexampleofachasmtrapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareResolvingthechasmtrapDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.5Supertype/SubtypeHierarchiesExhibit5-9:EquipmentEntityandDataThefirstfourcolumnsapplytoallequipmentbutthelastfourapplytoonlysomeequipment,whichresultsinemptycells.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareEquipmentSupertype/SubtypeHierarchyExhibit5-10:EquipmentSupertype/Subtype HierarchySupertypeEntity:Generalentitywiththecommonfield.SubtypeEntites:specializedentitieswithuniquefields.PartialSpecialization:Instancesofthesupertypedon’thavetobelongtoasubtype.TotalSpecialization:Instancesofthesupertypemustbelongtoasubtype.Disjointrule:Supertypemaybelongto,atmost,onesubtype.Overlaprule:Supertypemaybelongtomultiplesubtypes.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareSupertype/SubtypeExampleExhibit5-11:STUDENT,FACULTYand STAFFtablesAllthreetablessharefourcommonfields.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwarePersonSupertype/SubtypeHierarchyStep1:TablesStep2:FieldsandKeysStep3:RecognizeCommonFieldsStartbyidentifyingthreedifferenttables.-Addthefieldsanddesignatetheprimarykeys.-Makesurethatthefieldsactuallystorethesamedataforeachentity.Exhibit5-12:PersonSupertype/Subtype HierarchyDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwarePersonSupertype/SubtypeHierarchyStep4:CreateSupertype/SubtypeHierarchyStep5:DetermineTotal/PartialSpecializationandDisjoint/OverlapRuleExhibit5-12:PersonSupertype/Subtype HierarchyTotalSpecializationOverlapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareMoreComplex

Supertype/SubtypeExampleExhibit5-13:Athlete/SGAHierarchySubtypesnotonlycontaindifferentdata,butcanalsobeinvolvedindifferentrelationshipswithothertables.Here,anathletestudentsubtypeisinvolvedinarelationshipwithateam.PartialSpecializationOverlapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAllStafftableholdingdetailsofallstaff

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareStaffentitywithsubclassesrepresentingjobrolesDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareSharedsubclassandasubclasswithitsownsubclassDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareVehicleentityintovehicletypes

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesignExample:

SummerReadingFunExhibit5-14:SummerReadingFun Step-by-StepDesignProblemStatementTheSummerReadingFunprogramisaneducationalprogramintendedtohelpchildrenimprovetheirreadingskillsoverthesummer.Youarehelpingtheprogramdirectorsdesignadatabasethatwillimprovetheirprogrammanagement.Tomaketheprogramseemlesslikeschool,therearenotests.Studentsimprovetheirskillsbyreadingbooksratherthantakingtests.Bookareselectedaccordingtothereadingskilltheywillhelpstudentsimprove.Foreachskill,Thedatabasemuststoreanidentifierandadescription.Someskillhaveprerequisites.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesignExample:

SummerReadingFunExhibit5-14:SummerReadingFun Step-by-StepDesignProblemStatementThetitleofeachbookandauthor’sfirstandlastnamesmustbestored.Booksneedtobeclassifiedaseitherfictionornonfiction.Forfictionbooks,thestylemustbestored.Fornonfictionbooks,thetopicisstored.Theskilladdressedbyeachbookneedtobestored.Abookmayaddressseveralskills.Certaininformationabouteachstudentsmustbetracked,includingfirstandlastnames,grade,andschool.Keeptrackofwhatbookseachstudenthasread.Thedateonwhichstudentcompletesthebook.Trackinformationaboutwhichskillseachstudenthasgained.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesignExample:

SummerReadingFunExhibit5-14:SummerReadingFun Step-by-StepDesignStep1:TablesTheproblemstatementreferstofiveentities:students,books,skills,fictionbooksandnon-fictionbooks.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesignExample:

SummerReadingFunStep2:RelationshipsFictionandNon-fictionaresubtypesofbooks.Sincetherearenoothertypesofbooks,wehavetotalspecialization.Thedisjointruleisineffectsinceabookcannotbebothfictionandnon-fiction.Astudenthasmanyskillsandaskillisrelatedtomanystudents.Astudentreadsmanybooksandabookisreadbymanystudents.Abookcanbeusedtodevelopmanyskillsandaskillcanbedevelopedinmanybooks.Exhibit5-14:SummerReadingFun Step-by-StepDesignDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesignExample:

SummerReadingFunStep3:ResolveMany-to-Many RelationshipsAssociativeEntitiesarecreatedtoresolvethemany-to-manyrelationships.Thereisalsoamany-to-manyrecursiverelationshipbetweenskills:someskillsarerelatedtootherskillsasprerequisites.TherecursiverelationshipbetweenskillsisresolvedwiththePREREQassociativeentity.Exhibit5-14:SummerReadingFun Step-by-StepDesignDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesignExample:

SummerReadingFunStep4:FieldsThefieldsidentifiedintheproblemstatementareaddedtothetables.Exhibit5-14:SummerReadingFun Step-by-StepDesignDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesignExample:

SummerReadingFunStep5:KeysPrimarykeysarecreatedforSTUDENT,SKILL,andBOOK.Primarykeysfromtablesrelatedtoassociativeentitiesarecopiedintotheassociativetablesasforeignkeys:thepairofforeignkeysbecomestheprimarykeyoftheassociativetables.TheprimarykeyintheBOOKsupertypetableiscopiedintothesubtypetablesasforeignkeys,whichalsoserveastheprimarykeysofthosetables.Exhibit5-14:SummerReadingFun Step-by-StepDesignDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesignExample:

SummerReadingFunDataTypesExhibit5-15:SummerReadingFunERDiagram WithDataTypesDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesign:

SwamplandRealEstateExhibit5-16:SwamplandRealEstateStep-by-StepDesignStep1:TablesTheproblemstatementreferstosevenentities:property,agency,area,outlet,clientandthesingle-familyandcondopropertytypes.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesign:

SwamplandRealEstateStep2:RelationshipsClientsarerelatedtopropertiesintwoways:asabuyerandasaseller.Singlefamilyandcondoaresubtypesofbooks.Sincethereareothertypesofproperty,wehavepartialspecialization.Thedisjointruleisineffectsinceapropertycannotbebothsinglefamilyandcondo.Clientscanalsoreferotherclientstotheagency,sothereisarecursiverelationshipbetweenclients.Exhibit5-16:SwamplandRealEstateStep-by-StepDesignDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesign:

SwamplandRealEstateStep3:ResolveMany-to-Many RelationshipsThemany-to-manyrelationshipbetweenpropertiesandoutletsisresolvedbyaddingthePROPERTYOUTLETassociativetable.Exhibit5-16:SwamplandRealEstateStep-by-StepDesignDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesign:

SwamplandRealEstateStep4:FieldsThefieldsidentifiedintheproblemstatementareaddedtothetables.Exhibit5-16:SwamplandRealEstateStep-by-StepDesignDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesign:

SwamplandRealEstateStep5:KeysEachone-to-manyrelationshipisrepresentedbyduplicatingtheprimarykeyfromtheonesideoftherelationshipintothetableonthemanysideasaforeignkey.Theprimarykeysofthetableslinkedtotheassociativeareduplicatedintotheassociatetableasforeignkeys.Thesetwoforeignkeysarecombinedwiththeaddatetobetheprimarykeyoftheassociativetable.Theclientrecursiverelationshipisresolvedbyduplicatingtheprimarykeyintothesametableasaforeignkey.TheprimarykeyinthePROPERTYsupertypetableiscopiedintothesubtypetablesasforeignkeys,whichalsoserveastheprimarykeysofthosetables.Exhibit5-16:SwamplandRealEstateStep-by-StepDesignDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesign:

SwamplandRealEstateExhibit5-17:SwamplandRealEstate ERDiagramDataTypesDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwarePracticeExercise6:

EventPlanningSheetExhibit5-18:TheEventPlanningSheetDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareExhibit5-19:ARecipeCardPracticeExercise6:

RecipeCardDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwarePracticeExercise6:

SecondRecipeCardExhibit5-20:ASecondRecipeCardDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareChapter6AdvancedDatabaseDesigns任課教師：何婧hejing@DatabaseDesign.Schoolofso63DatabaseDesign.SchoolofsoftwareObjectivesHowtodescribeDerivedattribute.Recognizewhenadatabasemodelrequiresarecursiverelationship.Howtodescribecomplexrelationship.HowtoidentifyandsolveproblemsinanERmodelcalledconnectiontraps.Recognizewhenitisappropriatetousegeneralization/specializationhierarchies.DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.1AttributesAttributecanbeclassifiedasbeing:simpleorcomposite(復(fù)雜)single-valuedormulti-valuedorderived（來源）DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributeAttributethatrepresentsavaluethatisderivablefromvalueofarelatedattribute,orsetofattributes,notnecessarilyinthesameentity.

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattribute(SQLServer)createtableemp(empidintnotnullprimarykey,salarymoneynotnull,bonusmoneynotnulldefault0,

total_salaryas(salary+bonus));DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributeinsertintoemp(empid,salary,bonus)values(100,$5000.00,$1000.00);insertintoemp(empid,salary,bonus)values(101,$3000,$500);select*fromemp;100 5000.0000 1000.0000 6000.0000101 3000.0000 500.0000 3500.0000DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributecreatetableemp_tax(taxidintnotnullprimarykey,empidintnotnullforeignkeyreferencesemp(empid),accumulation_fundmoney,medicaremoney,

salarymoney)DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributecreatetriggertrg_for_emp_taxonemp_taxafterinsertasbegindeclare@total_salarymoney,@salary_resultmoney;select@total_salary=total_salaryfromemp,insertedwhereemp.empid=inserted.empid;select@salary_result=@total_salary-accumulation_fund-medicarefrominserted;updateemp_taxsetsalary=@salary_result;end;DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAttributesDerivedattributeinsertintoemp_tax(taxid,empid,accumulation_fund,medicare)values(001,100,$120,$80);select*fromemp_tax;100 120.0000 80.0000 5800.0000DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.2RecursiveRelationshipsExhibit5-1:EnrollmentDatabaseDesignOriginalDesignoftheEnrollmentDatabaseDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareMentorRecursiveRelationshipsExhibit5-2:MentorRelationshipsfor EnrollmentDatabaseInstructor11mentorsinstructors22and33.Instructor33mentorsinstructor44.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareImplementingaRecursiveRelationshipExhibit5-3:DataRelationshipsforMentor RecursiveRelationshipAforeignkey(INSTRUCTOR$id)isaddedtotheINSTRUCTORtable,whichisacopyoftheprimarykeyofthatsametable.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareERDiagramwitha

RecursiveRelationshipExhibit5-4:EnrollmentDatabaseDiagram withRecursiveRelationshipWiththeforeignkeyadded,thereisnowaone-to-manyrelationshipbetweeninstructors.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareBill-of-MaterialsExhibit5-5:Bill-of-MaterialsExampleAbill-of-materialsisastructurethatshowsarelationshipsbetweenproducts.Thisisamany-to-manyrelationship:aproductcanhavemanysub-products,andaproductcanbeasub-productinmanylargerproducts.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareBill-of-MaterialsinTableFormExhibit5-6:BillofMaterialsTableTryingtorepresentamany-to-manyrecursiverelationshipdoesnotworksinceyoucouldnotdeterminehowmanycomponentcolumnswouldbeneeded,andtherewillbealargenumberofemptycells.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareERDiagramforaMany-to-Many

RecursiveRelationshipExhibit5-7:Bill-ofMaterialsERDiagramAswithallmany-to-manyrelationships,amany-to-manyrecursiverelationshipisrepresentedwithanassociativetable.Inthiscase,however,theassociativetableislinkedbacktothesametable(PRODUCT),withbothhalvesofitsprimarykeybeingforeignkeysfromthePRODUCTtable.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareMany-to-Many

RecursiveRelationshipDataExhibit5-8:Bill-of-MaterialsDatabaseTablesDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.3ComplexrelationshipExhibit5-14:SummerReadingFun Step-by-StepDesignMultiplicityisthenumber(orrange)ofpossibleoccurrencesofanentitytypeinann-aryrelationshipwhenother(n-1)valuesarefixed.

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexrelationship

–individualexamplesDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexrelationship

–multiplicityDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware

Complexrelationship–ERdiagramDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware

Complexrelationship–representationastablesDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聯(lián)系之間存在重疊，如何表達(dá)聯(lián)系之間的聯(lián)系？實例：職工參加項目，并在此過程中可能使用機(jī)器ComplexRelationship-聚集職工項目參加并使用姓名工種名稱類型機(jī)器名型號方案1工時機(jī)器DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聚集職工項目參加姓名工種名稱類型機(jī)器名型號方案2工時使用機(jī)器e1在j1項目中使用什么機(jī)器？DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聚集是一種抽象，通過它聯(lián)系被作為高層實體集實體集A與B以及它們的聯(lián)系可被看成實體集，并與另一實體集C發(fā)生聯(lián)系聚集ABR1R2CDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聚集職工項目參加姓名工種名稱類型機(jī)器名型號工時使用機(jī)器DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware聚集DatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.4ProblemswithERmodelsProblemsmayarisewhendesigninganERmodelcalledconnectiontraps.(連接陷阱)Oftenduetoamisinterpretationofthemeaningofcertainrelationships.Twomaintypesofconnectiontrapsarecalledfantraps

andchasm

traps.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareProblemswithERmodelsFantrap（扇形陷阱）Twoentitieshavea1:*relationshipthatfanoutfromathirdentity,butthetwoentitiesshouldhaveadirectrelationshipbetweenthemtoprovidethenecessaryinformation.

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAnexampleofafantrapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareResolvingthefantrapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareResolvingthefantrapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareProblemswithERmodelsChasmtrap（深坑陷阱）Amodelsuggeststheexistenceofarelationshipbetweenentities,butthepathwaydoesnotexistbetweencertainentityoccurrences.

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAnexampleofachasmtrapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareResolvingthechasmtrapDatabaseDesign.SchoolofsoDatabaseDesign.Schoolofsoftware6.5Supertype/SubtypeHierarchiesExhibit5-9:EquipmentEntityandDataThefirstfourcolumnsapplytoallequipmentbutthelastfourapplytoonlysomeequipment,whichresultsinemptycells.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareEquipmentSupertype/SubtypeHierarchyExhibit5-10:EquipmentSupertype/Subtype HierarchySupertypeEntity:Generalentitywiththecommonfield.SubtypeEntites:specializedentitieswithuniquefields.PartialSpecialization:Instancesofthesupertypedon’thavetobelongtoasubtype.TotalSpecialization:Instancesofthesupertypemustbelongtoasubtype.Disjointrule:Supertypemaybelongto,atmost,onesubtype.Overlaprule:Supertypemaybelongtomultiplesubtypes.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareSupertype/SubtypeExampleExhibit5-11:STUDENT,FACULTYand STAFFtablesAllthreetablessharefourcommonfields.DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwarePersonSupertype/SubtypeHierarchyStep1:TablesStep2:FieldsandKeysStep3:RecognizeCommonFieldsStartbyidentifyingthreedifferenttables.-Addthefieldsanddesignatetheprimarykeys.-Makesurethatthefieldsactuallystorethesamedataforeachentity.Exhibit5-12:PersonSupertype/Subtype HierarchyDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwarePersonSupertype/SubtypeHierarchyStep4:CreateSupertype/SubtypeHierarchyStep5:DetermineTotal/PartialSpecializationandDisjoint/OverlapRuleExhibit5-12:PersonSupertype/Subtype HierarchyTotalSpecializationOverlapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareMoreComplex

Supertype/SubtypeExampleExhibit5-13:Athlete/SGAHierarchySubtypesnotonlycontaindifferentdata,butcanalsobeinvolvedindifferentrelationshipswithothertables.Here,anathletestudentsubtypeisinvolvedinarelationshipwithateam.PartialSpecializationOverlapDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareAllStafftableholdingdetailsofallstaff

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareStaffentitywithsubclassesrepresentingjobrolesDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareSharedsubclassandasubclasswithitsownsubclassDatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareVehicleentityintovehicletypes

DatabaseDesign.SchoolofsoDatabaseDesign.SchoolofsoftwareComplexDesignExample:

SummerReadingFunExhibit5-14:SummerReadingFun Step-by-StepDesignProblemStatementTheSummerReadingFunprogramisaneducationalprogramintendedtohelpchildrenimprovetheirreadingskillsoverthesummer