数据库管理系统原理与设计答案(第三版全部答案)

DATABASEMANAGEMENT

SYSTEMS

SOLUTIONSMANUAL

THIRDEDITION

RaghuRamakrishnan

UniversityofWisconsin

Madison,WI,USA

JohannesGehrke

CornellUniversity

Ithaca,NY,USA

Je Derstadt,ScottSeliko ,andLinZhu

CornellUniversity

Ithaca,NY,USA

CONTENTS

PREFACE

1INTRODUCTIONTODATABASESYSTEMS

2INTRODUCTIONTODATABASEDESIGN

3THERELATIONALMODEL

4RELATIONALALGEBRAANDCALCULUS

5SQL:QUERIES,CONSTRAINTS,TRIGGERS

6DATABASEAPPLICATIONDEVELOPMENT

7INTERNETAPPLICATIONS

8OVERVIEWOFSTORAGEANDINDEXING

9STORINGDATA:DISKSANDFILES

10TREE-STRUCTUREDINDEXING

11HASH-BASEDINDEXING

12OVERVIEWOFQUERYEVALUATION

13EXTERNALSORTING

14EVALUATIONOFRELATIONALOPERATORS

iiii172242599094102113122141166175181

iiDatabaseManagementSystemsSolutionsManualThirdEdition15ATYPICALQUERYOPTIMIZER

16OVERVIEWOFTRANSACTIONMANAGEMENT

17CONCURRENCYCONTROL

18CRASHRECOVERY

19SCHEMAREFINEMENTANDNORMALFORMS

20PHYSICALDATABASEDESIGNANDTUNING

21SECURITY197218228245258278292

PREFACE

Itisnoteveryquestionthatdeservesananswer.

PubliusSyrus,42B.C.

Ihopethatmostofthequestionsinthisbookdeserveananswer.Thesetofquestionsisunusuallyextensive,andisdesignedtoreinforceanddeepenstudents’understandingoftheconceptscoveredineachchapter.Thereisastrongemphasisonquantitativeandproblem-solvingtypeexercises.

WhileIwrotesomeofthesolutionsmyself,mostwerewrittenoriginallybystudentsinthedatabaseclassesatWisconsin.I’dliketothankthemanystudentswhohelpedindevelopingandcheckingthesolutionstotheexercises;thismanualwouldnotbeavailablewithouttheircontributions.Inalphabeticalorder:X.Bao,S.Biao,M.Chakrabarti,C.Chan,W.Chen,N.Cheung,D.Colwell,J.Derstadt,C.Fritz,V.Ganti,J.Gehrke,G.Glass,V.Gopalakrishnan,M.Higgins,T.Jasmin,M.Krish-naprasad,Y.Lin,C.Liu,M.Lusignan,H.Modi,S.Narayanan,D.Randolph,A.Ranganathan,J.Reminga,A.Therber,M.Thomas,Q.Wang,R.Wang,Z.WangandJ.Yuan.Inaddition,JamesHarringtonandMartinReamesatWisconsinandNinaTangatBerkeleyprovidedespeciallydetailedfeedback.

Severalstudentscontributedtoeachchapter’ssolutions,andanswersweresubse-quentlycheckedbymeandbyotherstudents.Thismanualhasbeeninuseforseveralsemesters.Ihopethatitisnowmostlyaccurate,butI’msureitstillcontainser-rorsandomissions.Ifyouareastudentandyoudonotunderstandaparticularsolution,contactyourinstructor;itmaybethatyouaremissingsomething,butitmayalsobethatthesolutionisincorrect!Ifyoudiscoverabug,pleasesendmemail(raghu@cs.wisc.edu)andIwillupdatethemanualpromptly.

ThelatestversionofthissolutionsmanualisdistributedfreelythroughtheWeb;gotothehomepagementionedbelowtoobtainacopy.

ForMoreInformation

ThehomepageforthisbookisatURL:

iii

DatabaseManagementSystemsSolutionsManualThirdEdition

http://www.cs.wisc.edu/~dbbook

Thispageisfrequentlyupdatedandcontainsinformationaboutthebook,pastandcurrentusers,andthesoftware.Thispagealsocontainsalinktoallknownerrorsinthebook,theaccompanyingslides,andthesoftware.Sincethesolutionsmanualisdistributedelectronically,allknownerrorsareimmediately xedandnolistoferrorsismaintained.Instructorsareadvisedtovisitthissiteperiodically;theycanalsoregisteratthissitetobenoti edofimportantchangesbyemail.

1

INTRODUCTIONTODATABASE

SYSTEMS

Exercise1.1Whywouldyouchooseadatabasesysteminsteadofsimplystoringdatainoperatingsystem les?Whenwoulditmakesensenottouseadatabasesystem?Answer1.1Adatabaseisanintegratedcollectionofdata,usuallysolargethatithastobestoredonsecondarystoragedevicessuchasdisksortapes.Thisdatacanbemaintainedasacollectionofoperatingsystem les,orstoredinaDBMS(databasemanagementsystem).TheadvantagesofusingaDBMSare:

Dataindependenceande cientaccess.Databaseapplicationprogramsarein-dependentofthedetailsofdatarepresentationandstorage.Theconceptualandexternalschemasprovideindependencefromphysicalstoragedecisionsandlogicaldesigndecisionsrespectively.Inaddition,aDBMSprovidese cientstorageandretrievalmechanisms,includingsupportforverylarge les,indexstructuresandqueryoptimization.

Reducedapplicationdevelopmenttime.SincetheDBMSprovidesseveralimpor-tantfunctionsrequiredbyapplications,suchasconcurrencycontrolandcrashrecovery,highlevelqueryfacilities,etc.,onlyapplication-speci ccodeneedstobewritten.Eventhisisfacilitatedbysuitesofapplicationdevelopmenttoolsavailablefromvendorsformanydatabasemanagementsystems.

Dataintegrityandsecurity.TheviewmechanismandtheauthorizationfacilitiesofaDBMSprovideapowerfulaccesscontrolmechanism.Further,updatestothedatathatviolatethesemanticsofthedatacanbedetectedandrejectedbytheDBMSifusersspecifytheappropriateintegrityconstraints.

Dataadministration.Byprovidingacommonumbrellaforalargecollectionofdatathatissharedbyseveralusers,aDBMSfacilitatesmaintenanceanddataadministrationtasks.AgoodDBAcane ectivelyshieldend-usersfromthechoresof ne-tuningthedatarepresentation,periodicback-upsetc.

1

2Chapter1

Concurrentaccessandcrashrecovery.ADBMSsupportsthenotionofatrans-action,whichisconceptuallyasingleuser’http://www.77cn.com.cnerscanwritetransactionsasiftheirprogramswererunninginisolationagainstthedatabase.TheDBMSexecutestheactionsoftransactionsinaninterleavedfashiontoobtaingoodperformance,butschedulestheminsuchawayastoensurethatcon ictingoperationsarenotpermittedtoproceedconcurrently.Further,theDBMSmain-tainsacontinuouslogofthechangestothedata,andifthereisasystemcrash,itcanrestorethedatabasetoatransaction-consistentstate.Thatis,theactionsofincompletetransactionsareundone,sothatthedatabasestatere ectsonlytheactionsofcompletedtransactions.Thus,ifeachcompletetransaction,executingalone,maintainstheconsistencycriteria,thenthedatabasestateafterrecoveryfromacrashisconsistent.

Iftheseadvantagesarenotimportantfortheapplicationathand,usingacollectionof lesmaybeabettersolutionbecauseoftheincreasedcostandoverheadofpurchasingandmaintainingaDBMS.

Exercise1.2Whatislogicaldataindependenceandwhyisitimportant?

Answer1.2Logicaldataindependencemeansthatusersareshieldedfromchangesinthelogicalstructureofthedata,i.e.,changesinthechoiceofrelationstobestored.Forexample,ifarelationStudents(sid,sname,gpa)isreplacedbyStudentnames(sid,sname)andStudentgpas(sid,gpa)forsomereason,applicationprogramsthatoperateontheStudentsrelationcanbeshieldedfromthischangebyde ningaviewStu-dents(sid,sname,gpa)(asthenaturaljoinofStudentnamesandStudentgpas).Thus,applicationprogramsthatrefertoStudentsneednotbechangedwhentherelationStu-dentsisreplacedbytheothertworelations.TheonlychangeisthatinsteadofstoringStudentstuples,thesetuplesarecomputedasneededbyusingtheviewde nition;thisistransparenttotheapplicationprogram.

Exercise1.3Explainthedi erencebetweenlogicalandphysicaldataindependence.Answer1.3Logicaldataindependencemeansthatusersareshieldedfromchangesinthelogicalstructureofthedata,whilephysicaldataindependenceinsulatesusersfromchangesinthephysicalstorageofthedata.WesawanexampleoflogicaldataindependenceintheanswertoExercise1.2.ConsidertheStudentsrelationfromthatexample(andnowassumethatitisnotreplacedbythetwosmallerrelations).WecouldchoosetostoreStudentstuplesinaheap le,withaclusteredindexonthesname eld.Alternatively,wecouldchoosetostoreitwithanindexonthegpa eld,ortocreateindexesonboth elds,ortostoreitasa lesortedbygpa.Thesestoragealternativesarenotvisibletousers,exceptintermsofimprovedperformance,sincetheysimplyseearelationasasetoftuples.Thisiswhatismeantbyphysicaldataindependence.

IntroductiontoDatabaseSystems3Exercise1.4Explainthedi erencebetweenexternal,internal,andconceptualsche-mas.Howarethesedi erentschemalayersrelatedtotheconceptsoflogicalandphysicaldataindependence?

Answer1.4Externalschemasallowsdataaccesstobecustomized(andauthorized)atthelevelofindividualusersorgroupsofusers.Conceptual(logical)schemasde-scribesallthedatathatisactuallystoredinthedatabase.Whilethereareseveralviewsforagivendatabase,thereisexactlyoneconceptualschematoallusers.Internal(physical)schemassummarizehowtherelationsdescribedintheconceptualschemaareactuallystoredondisk(orotherphysicalmedia).

Externalschemasprovidelogicaldataindependence,whileconceptualschemaso erphysicaldataindependence.

Exercise1.5WhataretheresponsibilitiesofaDBA?IfweassumethattheDBAisneverinterestedinrunninghisorherownqueries,doestheDBAstillneedtounderstandqueryoptimization?Why?

Answer1.5TheDBAisresponsiblefor:

Designingthelogicalandphysicalschemas,aswellaswidely-usedportionsoftheexternalschema.

Securityandauthorization.

Dataavailabilityandrecoveryfromfailures.

Databasetuning:TheDBAisresponsibleforevolvingthedatabase,inparticulartheconceptualandphysicalschemas,toensureadequateperformanceasuserrequirementschange.

ADBAneedstounderstandqueryoptimizationevenifs/heisnotinterestedinrun-ninghisorherownqueriesbecausesomeoftheseresponsibilities(databasedesignandtuning)arerelatedtoqueryoptimization.UnlesstheDBAunderstandstheper-formanceneedsofwidelyusedqueries,andhowtheDBMSwilloptimizeandexecutethesequeries,gooddesignandtuningdecisionscannotbemade.

Exercise1.6ScroogeMcNuggetwantstostoreinformation(names,addresses,de-scriptionsofembarrassingmoments,etc.)aboutthemanyducksonhispayroll.Notsurprisingly,thevolumeofdatacompelshimtobuyadatabasesystem.Tosavemoney,hewantstobuyonewiththefewestpossiblefeatures,andheplanstorunitasastand-aloneapplicationonhisPCclone.Ofcourse,Scroogedoesnotplantosharehislistwithanyone.IndicatewhichofthefollowingDBMSfeaturesScroogeshouldpayfor;ineachcase,alsoindicatewhyScroogeshould(orshouldnot)payforthatfeatureinthesystemhebuys.

4

1.Asecurityfacility.

2.Concurrencycontrol.

3.Crashrecovery.

4.Aviewmechanism.

5.Aquerylanguage.

Answer1.6Letusdiscusstheindividualfeaturesindetail.Chapter1

AsecurityfacilityisnecessarybecauseScroogedoesnotplantosharehislistwithanyoneelse.Eventhoughheisrunningitonhisstand-alonePC,arivalduckstercouldbreakinandattempttoqueryhisdatabase.Thedatabase’ssecurityfeatureswouldfoiltheintruder.

Concurrencycontrolisnotneededbecauseonlyheusesthedatabase.

Crashrecoveryisessentialforanydatabase;Scroogewouldnotwanttolosehisdataifthepowerwasinterruptedwhilehewasusingthesystem.

Aviewmechanismisneeded.Scroogecouldusethistodevelop“customscreens”thathecouldconvenientlybringupwithoutwritinglongqueriesrepeatedly.AquerylanguageisnecessarysinceScroogemustbeabletoanalyzethedarksecretsofhisvictims.Inparticular,thequerylanguageisalsousedtode neviews.

Exercise1.7Whichofthefollowingplaysanimportantroleinrepresentinginforma-tionabouttherealworldinadatabase?Explainbrie y.

1.Thedatade nitionlanguage.

2.Thedatamanipulationlanguage.

3.Thebu ermanager.

4.Thedatamodel.

Answer1.7Letusdiscussthechoicesinturn.

Thedatade nitionlanguageisimportantinrepresentinginformationbecauseitisusedtodescribeexternalandlogicalschemas.

IntroductiontoDatabaseSystems5

Thedatamanipulationlanguageisusedtoaccessandupdatedata;itisnotimportantforrepresentingthedata.(Ofcourse,thedatamanipulationlanguagemustbeawareofhowdataisrepresented,andre ectsthisintheconstructsthatitsupports.)

Thebu ermanagerisnotveryimportantforrepresentationbecauseitbringsarbitrarydiskpagesintomainmemory,independentofanydatarepresentation.Thedatamodelisfundamentaltorepresentinginformation.ThedatamodeldetermineswhatdatarepresentationmechanismsaresupportedbytheDBMS.Thedatade nitionlanguageisjustthespeci csetoflanguageconstructsavailabletodescribeanactualapplication’sdataintermsofthedatamodel.

Exercise1.8DescribethestructureofaDBMS.IfyouroperatingsystemisupgradedtosupportsomenewfunctionsonOS les(e.g.,theabilitytoforcesomesequenceofbytestodisk),whichlayer(s)oftheDBMSwouldyouhavetorewritetotakeadvantageofthesenewfunctions?

Answer1.8ThearchitectureofarelationalDBMStypicallyconsistsofalayerthatmanagesspaceondisk,alayerthatmanagesavailablemainmemoryandbringsdiskpagesintomemoryasneeded,alayerthatsupportstheabstractionsof lesandindexstructures,alayerthatimplementsrelationaloperators,andalayerthatparsesandoptimizesqueriesandproducesanexecutionplanintermsofrelationaloperators.Inaddition,thereissupportforconcurrencycontrolandrecovery,whichinteractswiththebu ermanagementandaccessmethodlayers.

ThediskspacemanagementlayerhastoberewrittentotakeadvantageofthenewfunctionsonOS les.Itislikelythatthebu ermanagementlayerwillalsobea ected.Exercise1.9Answerthefollowingquestions:

1.Whatisatransaction?

2.WhydoesaDBMSinterleavetheactionsofdi erenttransactionsinsteadofexe-cutingtransactionsoneaftertheother?

3.Whatmustauserguaranteewithrespecttoatransactionanddatabaseconsis-tency?WhatshouldaDBMSguaranteewithrespecttoconcurrentexecutionofseveraltransactionsanddatabaseconsistency?

4.Explainthestricttwo-phaselockingprotocol.

5.WhatistheWALproperty,andwhyisitimportant?

Answer1.9Letusanswereachquestioninturn:

6Chapter1

1.AtransactionisanyoneexecutionofauserprograminaDBMS.ThisisthebasicunitofchangeinaDBMS.

2.ADBMSistypicallysharedamongmanyusers.Transactionsfromtheseuserscanbeinterleavedtoimprovetheexecutiontimeofusers’queries.Byinterleav-ingqueries,usersdonothavetowaitforotheruser’stransactionstocompletefullybeforetheirowntransactionbegins.Withoutinterleaving,ifuserAbeginsatransactionthatwilltake10secondstocomplete,anduserBwantstobe-ginatransaction,userBwouldhavetowaitanadditional10secondsforuserA’stransactiontocompletebeforethedatabasewouldbeginprocessinguserB’srequest.

3.Ausermustguaranteethathisorhertransactiondoesnotcorruptdataorinsertnonsenseinthedatabase.Forexample,inabankingdatabase,ausermustguar-anteethatacashwithdrawtransactionaccuratelymodelstheamountapersonremovesfromhisorheraccount.Adatabaseapplicationwouldbeworthlessifapersonremoved20dollarsfromanATMbutthetransactionsettheirbalancetozero!ADBMSmustguaranteethattransactionsareexecutedfullyandin-dependentlyofothertransactions.AnessentialpropertyofaDBMSisthatatransactionshouldexecuteatomically,orasifitistheonlytransactionrunning.Also,transactionswilleithercompletefully,orwillbeabortedandthedatabasereturnedtoit’sinitialstate.Thisensuresthatthedatabaseremainsconsistent.

4.Stricttwo-phaselockingusessharedandexclusivelockstoprotectdata.Atrans-actionmustholdalltherequiredlocksbeforeexecuting,anddoesnotreleaseanylockuntilthetransactionhascompletely nished.

5.TheWALpropertya ectstheloggingstrategyinaDBMS.TheWAL,Write-AheadLog,propertystatesthateachwriteactionmustberecordedinthelog(ondisk)beforethecorrespondingchangeisre ectedinthedatabaseitself.Thisprotectsthedatabasefromsystemcrashesthathappenduringatransaction’sexecution.Byrecordingthechangeinalogbeforethechangeistrulymade,thedatabaseknowstoundothechangestorecoverfromasystemcrash.Otherwise,ifthesystemcrashesjustaftermakingthechangeinthedatabasebutbeforethedatabaselogsthechange,thenthedatabasewouldnotbeabletodetecthischangeduringcrashrecovery.

2

INTRODUCTIONTODATABASE

DESIGN

Exercise2.1Explainthefollowingtermsbrie y:attribute,domain,entity,relation-ship,entityset,relationshipset,one-to-manyrelationship,many-to-manyrelationship,participationconstraint,overlapconstraint,coveringconstraint,weakentityset,aggre-gation,androleindicator.

Answer2.1Termexplanations:

Attribute-apropertyordescriptionofanentity.Atoydepartmentemployeeentitycouldhaveattributesdescribingtheemployee’sname,salary,andyearsofservice.

Domain-asetofpossiblevaluesforanattribute.

Entity-anobjectintherealworldthatisdistinguishablefromotherobjectssuchasthegreendragontoy.

Relationship-anassociationamongtwoormoreentities.

Entityset-acollectionofsimilarentitiessuchasallofthetoysinthetoydepart-ment.

Relationshipset-acollectionofsimilarrelationships

One-to-manyrelationship-akeyconstraintthatindicatesthatoneentitycanbeassociatedwithmanyofanotherentity.Anexampleofaone-to-manyrelationshipiswhenanemployeecanworkforonlyonedepartment,andadepartmentcanhavemanyemployees.

Many-to-manyrelationship-akeyconstraintthatindicatesthatmanyofoneentitycanbeassociatedwithmanyofanotherentity.Anexampleofamany-to-manyrelationshipisemployeesandtheirhobbies:apersoncanhavemanydi erenthobbies,andmanypeoplecanhavethesamehobby.

7

8Chapter2

Participationconstraint-aparticipationconstraintdetermineswhetherrelation-shipsmustinvolvecertainentities.Anexampleisifeverydepartmententityhasamanagerentity.Participationconstraintscaneitherbetotalorpartial.Atotalparticipationconstraintsaysthateverydepartmenthasamanager.Apartialparticipationconstraintsaysthateveryemployeedoesnothavetobeamanager.Overlapconstraint-withinanISAhierarchy,anoverlapconstraintdetermineswhetherornottwosubclassescancontainthesameentity.

Coveringconstraint-withinanISAhierarchy,acoveringconstraintdetermineswheretheentitiesinthesubclassescollectivelyincludeallentitiesinthesuperclass.Forexample,withanEmployeesentitysetwithsubclassesHourlyEmployeeandSalaryEmployee,doeseveryEmployeeentitynecessarilyhavetobewithineitherHourlyEmployeeorSalaryEmployee?

Weakentityset-anentitythatcannotbeidenti eduniquelywithoutconsideringsomeprimarykeyattributesofanotheridentifyingownerentity.AnexampleisincludingDependentinformationforemployeesforinsurancepurposes.

Aggregation-afeatureoftheentityrelationshipmodelthatallowsarelationshipsettoparticipateinanotherrelationshipset.ThisisindicatedonanERdiagrambydrawingadashedboxaroundtheaggregation.

Roleindicator-Ifanentitysetplaysmorethanonerole,roleindicatorsdescribethedi erentpurposeintherelationship.AnexampleisasingleEmployeeentitysetwitharelationReports-Tothatrelatessupervisorsandsubordinates.

Exercise2.2Auniversitydatabasecontainsinformationaboutprofessors(identi edbysocialsecuritynumber,orSSN)andcourses(identi edbycourseid).Professorsteachcourses;eachofthefollowingsituationsconcernstheTeachesrelationshipset.Foreachsituation,drawanERdiagramthatdescribesit(assumingnofurtherconstraintshold).

1.Professorscanteachthesamecourseinseveralsemesters,andeacho eringmustberecorded.

2.Professorscanteachthesamecourseinseveralsemesters,andonlythemostrecentsucho eringneedstoberecorded.(Assumethisconditionappliesinallsubsequentquestions.)

3.Everyprofessormustteachsomecourse.

4.Everyprofessorteachesexactlyonecourse(nomore,noless).

5.Everyprofessorteachesexactlyonecourse(nomore,noless),andeverycoursemustbetaughtbysomeprofessor.

IntroductiontoDatabaseDesign9

Semester

ssnsemesteridcourseIdProfessorTeachesCourse

Figure2.1ERDiagramforExercise2.2,Part1

ssnsemestercourseIdProfessorTeachesCourse

Figure2.2ERDiagramforExercise2.2,Part2

6.Nowsupposethatcertaincoursescanbetaughtbyateamofprofessorsjointly,butitispossiblethatnooneprofessorinateamcanteachthecourse.Modelthissituation,introducingadditionalentitysetsandrelationshipsetsifnecessary.Answer2.21.TheERdiagramisshowninFigure2.1.

2.TheERdiagramisshowninFigure2.2.

3.TheERdiagramisshowninFigure2.3.

4.TheERdiagramisshowninFigure2.4.

5.TheERdiagramisshowninFigure2.5.

6.TheE.R.diagramisshowninFigure2.6.AnadditionalentitysetcalledGroupisintroducedtoidentifytheprofessorswhoteamtoteachacourse.Weassumethatonlythelatesto eringofacourseneedstoberecorded.

Exercise2.3Considerthefollowinginformationaboutauniversitydatabase:

10Chapter2

ssnsemestercourseIdProfessorTeachesCourse

Figure2.3ERDiagramforExercise2.2,Part3

ssnsemestercourseIdProfessorTeachesCourse

Figure2.4ERDiagramforExercise2.2,Part4

ssnsemestercourseIdProfessorTeachesCourse

Figure2.5ERDiagramforExercise2.2,Part5

IntroductiontoDatabaseDesignssn11gid

Professormember_ofGroup

semesterTeaches

courseIdCourse

Figure2.6ERDiagramforExercise2.2,Part6

ProfessorshaveanSSN,aname,anage,arank,andaresearchspecialty.

Projectshaveaprojectnumber,asponsorname(e.g.,NSF),astartingdate,anendingdate,andabudget.

GraduatestudentshaveanSSN,aname,anage,andadegreeprogram(e.g.,M.S.orPh.D.).

Eachprojectismanagedbyoneprofessor(knownastheproject’sprincipalinves-tigator).

Eachprojectisworkedonbyoneormoreprofessors(knownastheproject’sco-investigators).

Professorscanmanageand/orworkonmultipleprojects.

Eachprojectisworkedonbyoneormoregraduatestudents(knownastheproject’sresearchassistants).

Whengraduatestudentsworkonaproject,aprofessormustsupervisetheirworkontheproject.Graduatestudentscanworkonmultipleprojects,inwhichcasetheywillhavea(potentiallydi erent)supervisorforeachone.

Departmentshaveadepartmentnumber,adepartmentname,andamaino ce.Departmentshaveaprofessor(knownasthechairman)whorunsthedepartment.

12Chapter2

Professorsworkinoneormoredepartments,andforeachdepartmentthattheyworkin,atimepercentageisassociatedwiththeirjob.

Graduatestudentshaveonemajordepartmentinwhichtheyareworkingontheirdegree.

Eachgraduatestudenthasanother,moreseniorgraduatestudent(knownasastudentadvisor)whoadviseshimorheronwhatcoursestotake.

http://www.77cn.com.cneonlythebasicERmodelhere;thatis,entities,relationships,andattributes.Besuretoindicateanykeyandparticipationconstraints.

Answer2.3TheERdiagramisshowninFigure2.7.

Exercise2.4Acompanydatabaseneedstostoreinformationaboutemployees(iden-ti edbyssn,withsalaryandphoneasattributes),departments(identi edbydno,withdnameandbudgetasattributes),andchildrenofemployees(withnameandageasattributes).Employeesworkindepartments;eachdepartmentismanagedbyanemployee;achildmustbeidenti eduniquelybynamewhentheparent(whoisanemployee;assumethatonlyoneparentworksforthecompany)isknown.Wearenotinterestedininformationaboutachildoncetheparentleavesthecompany.DrawanERdiagramthatcapturesthisinformation.

Answer2.4TheERdiagramisshowninFigure2.8.

Exercise2.5NotownRecordshasdecidedtostoreinformationaboutmusicianswhoperformonitsalbums(aswellasothercompanydata)inadatabase.Thecompanyhaswiselychosentohireyouasadatabasedesigner(atyourusualconsultingfeeof$2500/day).

EachmusicianthatrecordsatNotownhasanSSN,aname,anaddress,andaphonenumber.Poorlypaidmusiciansoftensharethesameaddress,andnoaddresshasmorethanonephone.

EachinstrumentusedinsongsrecordedatNotownhasauniqueidenti cationnumber,aname(e.g.,guitar,synthesizer, ute)andamusicalkey(e.g.,C,B- at,E- at).

EachalbumrecordedontheNotownlabelhasauniqueidenti cationnumber,atitle,acopyrightdate,aformat(e.g.,CDorMC),andanalbumidenti er.EachsongrecordedatNotownhasatitleandanauthor.

Introduction to Database Design

age

speciality work_in

pid

start_date

ssn

rank

sponsor

end_date

budget Professor Manages project

Figure 2.7 ER Diagram for Exercise 2.3

Work_dept

Runs

SupervisesWork_proj

ssn

pc_time

Dept

Major Graduate

senior Advisor grad

dno

office ssn dname age deg_prog name

13

14

salaryssn

dname

phone

dno

budget

Figure 2.8 ER Diagram for Exercise 2.4

Employees

Manages

Departments

Dependent

Works_In

Child

Chapter 2

name

age

本文来源：https://www.bwwdw.com/article/oz8i.html