数据库系统概念答案(第五版)

Exercises2.4Describethedifferencesinmeaningbetweenthetermsrelationandrelationschema.Answer:Arelationschemaisatypedefinition,andarelationisaninstanceofthatschema.Forexample,student(ss#,name)isarelationschemaandss#name123-45-6789TomJones456-78-9123JoeBrownisarelationbasedonthatschema.2.5ConsidertherelationaldatabaseofFigure2.35,wheretheprimarykeysareun-derlined.Giveanexpressionintherelationalalgebratoexpresseachofthefol-lowingqueries:a.FindthenamesofallemployeeswhoworkforFirstBankCorporation.b.FindthenamesandcitiesofresidenceofallemployeeswhoworkforFirstBankCorporation.c.Findthenames,streetaddress,andcitiesofresidenceofallemployeeswhoworkforFirstBankCorporationandearnmorethan$10,000perannum.d.Findthenamesofallemployeesinthisdatabasewholiveinthesamecityasthecompanyforwhichtheywork.e.Assumethecompaniesmaybelocatedinseveralcities.FindallcompanieslocatedineverycityinwhichSmallBankCorporationislocated.Answer:a.Πperson-name(σcompany-name=“FirstBankCorporation”(works))7CHAPTER28Chapter2RelationalModelemployee(person-name,street,city)works(person-name,company-name,salary)company(company-name,city)manages(person-name,manager-name)Figure2.35.RelationaldatabaseforExercises2.1,2.3and2.9.b.Πperson-name,city(employee(σcompany-name=“FirstBankCorporation”(works)))c.Πperson-name,street,city(σ(company-name=“FirstBankCorporation”∧salary10000)worksemployee)d.Πperson-name(employeeworkscompany)e.Note:SmallBankCorporationwillbeincludedineachanswer.Πcompany-name(company÷(Πcity(σcompany-name=“SmallBankCorporation”(company))))2.6ConsidertherelationofFigure2.20,whichshowstheresultofthequery“Findthenamesofallcustomerswhohavealoanatthebank.”Rewritethequerytoincludenotonlythename,butalsothecityofresidenceforeachcustomer.ObservethatnowcustomerJacksonnolongerappearsintheresult,eventhoughJacksondoesinfacthavealoanfromthebank.a.ExplainwhyJacksondoesnotappearintheresult.b.SupposethatyouwantJacksontoappearintheresult.Howwouldyoumodifythedatabasetoachievethiseffect?c.Again,supposethatyouwantJacksontoappearintheresult.Writeaqueryusinganouterjointhataccomplishesthisdesirewithoutyourhavingtomodifythedatabase.Answer:TherewrittenqueryisΠcustomer-name,customer-city,amount(borrowerloancustomer)a.AlthoughJacksondoeshavealoan,noaddressisgivenforJacksoninthecustomerrelation.SincenotupleincustomerjoinswiththeJacksontupleofborrower,Jacksondoesnotappearintheresult.b.ThebestsolutionistoinsertJackson’saddressintothecustomerrelation.Iftheaddressisunknown,nullvaluesmaybeused.Ifthedatabasesystemdoesnotsupportnulls,aspecialvaluemaybeused(suchasunknown)forJackson’sstreetandcity.Thespecialvaluechosenmustnotbeaplausiblenameforanactualcityorstreet.c.Πcustomer-name,customer-city,amount((borrowerloan)customer)2.7ConsidertherelationaldatabaseofFigure2.35.Giveanexpressionintherela-tionalalgebraforeachrequest:a.GiveallemployeesofFirstBankCorporationa10percentsalaryraise.Exercises9b.Giveallmanagersinthisdatabasea10percentsalaryraise,unlessthesalarywouldbegreaterthan$100,000.Insuchcases,giveonlya3percentraise.c.DeletealltuplesintheworksrelationforemployeesofSmallBankCorpora-tion.Answer:a.works←Πperson-name,company-name,1.1∗salary(σ(company-name=“FirstBankCorporation”)(works))∪(works−σcompany-name=“FirstBankCorporation”(works))b.Thesamesituationariseshere.Asbefore,t1,holdsthetuplestobeupdatedandt2holdsthesetuplesintheirupdatedform.t1←Πworks.person-name,company-name,salary(σworks.person-name=manager-name(works×manages))t2←Πworks.person-name,company-name,salary∗1.03(σt1.salary∗1.1100000(t1))t2←t2∪(Πworks.person-name,company-name,salary∗1.1(σt1.salary∗1.1≤100000(t1)))works←(works−t1)∪t2c.works←works−σcompany−name=“SmallBankCorporation”(works)2.8Usingthebankexample,writerelational-algebraqueriestofindtheaccountsheldbymorethantwocustomersinthefollowingways:a.Usinganaggregatefunction.b.Withoutusinganyaggregatefunctions.Answer:a.t1←account-numberGcountcustomer-name(depositor)Πaccount-numberσnum-holders2ρaccount-holders(account-number,num-holders)(t1)b.t1←(ρd1(depositor)×ρd2(depositor)×ρd3(depositor))t2←σ(d1.account-number=d2.account-number=d3.account-number)(t1)Πd1.account-number(σ(d1.customer-name=d2.customer-name∧d2.customer-name=d3.customer-name∧d3.customer-name=d1.customer-name)(t2))2.9ConsidertherelationaldatabaseofFigure2.35.Givearelational-algebraexpres-sionforeachofthefollowingqueries:a.Findthecompanywiththemostemployees.b.Findthecompanywiththesmallestpayroll.c.Findthosecompanieswhoseemployeesearnahighersalary,onaverage,thantheaveragesalaryatFirstBankCorporation.Answer:10Chapter2RelationalModela.t1←company-nameGcount-distinctperson-name(works)t2←maxnum-employees(ρcompany-strength(company-name,num-employees)(t1))Πcompany-name(ρt3(company-name,num-employees)(t1)ρt4(num-employees)(t2))b.t1←company-nameGsumsalary(works)t2←minpayroll(ρcompany-payroll(company-name,payroll)(t1))Πcompany-name(ρt3(company-name,payroll)(t1)ρt4(payroll)(t2))c.t1←company-nameGavgsalary(works)t2←σcompany-name=“FirstBankCorporation”(t1)Πt3.company-name((ρt3(company-name,avg-salary)(t1))