Exercises
2.4Describethedifferencesinmeaningbetweenthetermsrelationandrelationschema.
Answer:Arelationschemaisatypedefinition,andarelationisaninstanceofthatschema.Forexample,student(ss#,name)isarelationschemaand
ss#123-45-6789456-78-9123nameTomJonesJoeBrownisarelationbasedonthatschema.
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))
7
8Chapter2RelationalModel
employee(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(employee1
(σcompany-name=“FirstBankCorporation”(works)))c.Πperson-name,street,city
(σ(company-name=“FirstBankCorporation”∧salary>10000)
works1employee)
d.Πperson-name(employee1works1company)
e.Note:SmallBankCorporationwillbeincludedineachanswer.
Πcompany-name(company÷
(Πcity(σcompany-name=“SmallBankCorporation”(company))))2.6ConsidertherelationofFigure2.20,whichshowstheresultofthequery“Find
thenamesofallcustomerswhohavealoanatthebank.”Rewritethequerytoincludenotonlythename,butalsothecityofresidenceforeachcustomer.ObservethatnowcustomerJacksonnolongerappearsintheresult,eventhoughJacksondoesinfacthavealoanfromthebank.
a.ExplainwhyJacksondoesnotappearintheresult.
b.SupposethatyouwantJacksontoappearintheresult.Howwouldyoumodifythedatabasetoachievethiseffect?
c.Again,supposethatyouwantJacksontoappearintheresult.Writeaqueryusinganouterjointhataccomplishesthisdesirewithoutyourhavingtomodifythedatabase.Answer:Therewrittenqueryis
Πcustomer-name,customer-city,amount(borrower1loan1customer)
a.AlthoughJacksondoeshavealoan,noaddressisgivenforJacksoninthecustomerrelation.SincenotupleincustomerjoinswiththeJacksontupleofborrower,Jacksondoesnotappearintheresult.
b.ThebestsolutionistoinsertJackson’saddressintothecustomerrelation.Iftheaddressisunknown,nullvaluesmaybeused.Ifthedatabasesystemdoesnotsupportnulls,aspecialvaluemaybeused(suchasunknown)forJackson’sstreetandcity.Thespecialvaluechosenmustnotbeaplausiblenameforanactualcityorstreet.
c.Πcustomer-name,customer-city,amount((borrower1loan)1customer)2.7ConsidertherelationaldatabaseofFigure2.35.Giveanexpressionintherela-tionalalgebraforeachrequest:
a.GiveallemployeesofFirstBankCorporationa10percentsalaryraise.
Exercises9
b.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.1>100000(t1))t2←t2∪(Πworks.person-name,company-name,salary∗1.1
(σt1.salary∗1.1≤100000(t1)))
works←(works−t1)∪t2
c.works←works−σcompany−name=“SmallBankCorporation”(works)2.8Usingthebankexample,writerelational-algebraqueriestofindtheaccounts
heldbymorethantwocustomersinthefollowingways:
a.Usinganaggregatefunction.
b.Withoutusinganyaggregatefunctions.Answer:
a.t1←account-numberGcountcustomer-name(depositor)Πaccount-numberσnum-holders>2ρ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:
10Chapter2RelationalModel
a.t1←company-nameGcount-distinctperson-name(works)
t2←maxnum-employees(ρcompany-strength(company-name,num-employees)(t1))Πcompany-name(ρt3(company-name,num-employees)(t1)1ρt4(num-employees)(t2))b.t1←company-nameGsumsalary(works)
t2←minpayroll(ρcompany-payroll(company-name,payroll)(t1))Πcompany-name(ρt3(company-name,payroll)(t1)1ρt4(payroll)(t2))c.t1←company-nameGavgsalary(works)
t2←σcompany-name=“FirstBankCorporation”(t1)Πt3.company-name((ρt3(company-name,avg-salary)(t1))
1t3.avg-salary>first-bank.avg-salary(ρfirst-bank(company-name,avg-salary)(t2)))
2.10Listtworeasonswhynullvaluesmightbeintroducedintothedatabase.
Answer:Nullsmaybeintroducedintothedatabasebecausetheactualvalueiseitherunknownordoesnotexist.Forexample,anemployeewhoseaddresshaschangedandwhosenewaddressisnotyetknownshouldberetainedwithanulladdress.Ifemployeetupleshaveacompositeattributedependents,andaparticularemployeehasnodependents,thenthattuple’sdependentsattributeshouldbegivenanullvalue.2.11Considerthefollowingrelationalschema
employee(empno,name,office,age)books(isbn,title,authors,publisher)loan(empno,isbn,date)
Writethefollowingqueriesinrelationalalgebra.
a.FindthenamesofemployeeswhohaveborrowedabookpublishedbyMcGraw-Hill.
b.FindthenamesofemployeeswhohaveborrowedallbookspublishedbyMcGraw-Hill.
c.FindthenamesofemployeeswhohaveborrowedmorethanfivedifferentbookspublishedbyMcGraw-Hill.
d.Foreachpublisher,findthenamesofemployeeswhohaveborrowedmorethanfivebooksofthatpublisher.Answer:Noanswer
CHAPTER3
Exercises
3.8ConsidertheinsurancedatabaseofFigure3.11,wheretheprimarykeysareun-derlined.ConstructthefollowingSQLqueriesforthisrelationaldatabase.
a.Findthenumberofaccidentsinwhichthecarsbelongingto“JohnSmith”wereinvolved.
b.Updatethedamageamountforthecarwithlicensenumber“AABB2000”intheaccidentwithreportnumber“AR2197”to$3000.
Answer:Note:Theparticipatedrelationrelatesdrivers,cars,andaccidents.a.SQLquery:
selectfromwhere
count(distinct*)accidentexists(select*
fromparticipated,person
whereparticipated.driverid=person.driveridandperson.name=’JohnSmith’
andaccident.reportnumber=participated.reportnumber)updateparticipated
setdamageamount=3000
wherereportnumber=“AR2197”anddriveridin
(selectdriveridfromowns
wherelicense=“AABB2000”)
11
b.SQLquery:
12Chapter3SQL
person(driverid,name,address)car(license,model,year)
accident(reportnumber,date,location)owns(driverid,license)participated(driverid,car,reportnumber,damageamount)
Figure3.11.Insurancedatabase.employee(employeename,street,city)
works(employeename,companyname,salary)company(companyname,city)
manages(employeename,managername)
Figure3.12.Employeedatabase.
3.9ConsidertheemployeedatabaseofFigure3.12,wheretheprimarykeysareun-derlined.GiveanexpressioninSQLforeachofthefollowingqueries.
a.FindthenamesofallemployeeswhoworkforFirstBankCorporation.b.Findallemployeesinthedatabasewholiveinthesamecitiesasthecom-paniesforwhichtheywork.
c.Findallemployeesinthedatabasewholiveinthesamecitiesandonthesamestreetsasdotheirmanagers.
d.Findallemployeeswhoearnmorethantheaveragesalaryofallemployeesoftheircompany.
e.Findthecompanythathasthesmallestpayroll.
Answer:
a.FindthenamesofallemployeeswhoworkforFirstBankCorporation.selectemployeenamefromworks
wherecompanyname=’FirstBankCorporation’
b.Findallemployeesinthedatabasewholiveinthesamecitiesasthecom-paniesforwhichtheywork.selecte.employeenamefromemployeee,worksw,companyc
wheree.employeename=w.employeenameande.city=c.cityand
w.companyname=c.companyname
c.Findallemployeesinthedatabasewholiveinthesamecitiesandonthesamestreetsasdotheirmanagers.
selectP.employeenamefromemployeeP,employeeR,managesM
whereP.employeename=M.employeenameand
M.managername=R.employeenameandP.street=R.streetandP.city=R.city
Exercises13
d.Findallemployeeswhoearnmorethantheaveragesalaryofallemployeesoftheircompany.
Thefollowingsolutionassumesthatallpeopleworkforatmostonecom-pany.
selectemployeenamefromworksT
wheresalary>(selectavg(salary)
fromworksS
whereT.companyname=S.companyname)
e.Findthecompanythathasthesmallestpayroll.
selectcompanynamefromworks
groupbycompanynamehavingsum(salary)<=all(selectsum(salary)
fromworks
groupbycompanyname)
3.10ConsidertherelationaldatabaseofFigure3.12.GiveanexpressioninSQLfor
eachofthefollowingqueries.
a.GiveallemployeesofFirstBankCorporationa10percentraise.b.GiveallmanagersofFirstBankCorporationa10percentraise.
c.DeletealltuplesintheworksrelationforemployeesofSmallBankCorpora-tion.Answer:
a.GiveallemployeesofFirstBankCorporationa10-percentraise.(thesolu-tionassumesthateachpersonworksforatmostonecompany.)
updateworks
setsalary=salary*1.1
wherecompanyname=’FirstBankCorporation’
b.GiveallmanagersofFirstBankCorporationa10-percentraise.
updateworks
setsalary=salary*1.1
whereemployeenamein(selectmanagername
frommanages)
andcompanyname=’FirstBankCorporation’
c.DeletealltuplesintheworksrelationforemployeesofSmallBankCorpora-tion.
deleteworks
wherecompanyname=’SmallBankCorporation’3.11Letthefollowingrelationschemasbegiven:
14Chapter3SQL
R=(A,B,C)S=(D,E,F)
Letrelationsr(R)ands(S)begiven.GiveanexpressioninSQLthatisequivalenttoeachofthefollowingqueries.a.b.c.d.
ΠA(r)σB=17(r)r×s
ΠA,F(σC=D(r×s))
Answer:a.ΠA(r)
selectdistinctAfromr
b.σB=17(r)
select*fromr
whereB=17
c.r×s
selectdistinct*fromr,s
d.ΠA,F(σC=D(r×s))
selectdistinctA,Ffromr,s
whereC=D
3.12LetR=(A,B,C),andletr1andr2bothberelationsonschemaR.Givean
expressioninSQLthatisequivalenttoeachofthefollowingqueries.
a.b.c.d.
r1∪r2r1∩r2r1−r2
ΠAB(r1)1ΠBC(r2)
Answer:a.r1∪r2
(select*fromr1)union(select*fromr2)
b.r1∩r2
Wecanwritethisusingtheintersectoperation,whichisthepreferredapproach,butforvarietywepresentansolutionusinganestedsubquery.
Exercises15
select*fromr1
where(A,B,C)in(select*
fromr2)
c.r1−r2
select∗fromr1
where(A,B,C)notin(select∗
fromr2)
Thiscanalsobesolvedusingtheexceptclause.d.ΠAB(r1)1ΠBC(r2)
selectr1.A,r2.B,r3.Cfromr1,r2
wherer1.B=r2.B
3.13Showthat,inSQL,<>allisidenticaltonotin.
Answer:LetthesetSdenotetheresultofanSQLsubquery.Wecompare(x<>allS)with(xnotinS).Ifaparticularvaluex1satisfies(x1<>allS)thenforallelementsyofSx1=y.Thusx1isnotamemberofSandmustsatisfy(x1notinS).Similarly,supposethereisaparticularvaluex2whichsatisfies(x2notinS).ItcannotbeequaltoanyelementwbelongingtoS,andhence(x2<>allS)willbesatisfied.Thereforethetwoexpressionsareequivalent.3.14ConsidertherelationaldatabaseofFigure3.12.UsingSQL,defineaviewcon-sistingofmanagernameandtheaveragesalaryofallemployeeswhoworkforthatmanager.Explainwhythedatabasesystemshouldnotallowupdatestobeexpressedintermsofthisview.Answer:
createviewsalinfoas
selectmanagername,avg(salary)frommanagesm,worksw
wherem.employeename=w.employeenamegroupbymanagernameUpdatesshouldnotbeallowedinthisviewbecausethereisnowaytode-terminehowtochangetheunderlyingdata.Forexample,supposetherequestis“changetheaveragesalaryofemployeesworkingforSmithto$200”.ShouldeverybodywhoworksforSmithhavetheirsalarychangedto$200?Orshouldthefirst(ormore,ifnecessary)employeefoundwhoworksforSmithhavetheirsalaryadjustedsothattheaverageis$200?Neitherapproachreallymakessense.
3.15WriteanSQLquery,withoutusingawithclause,tofindallbrancheswhere
thetotalaccountdepositislessthantheaveragetotalaccountdepositatallbranches,
16Chapter3SQL
a.Usinganestedqueryinthefromclauser.b.Usinganestedqueryinahavingclause.
Answer:Weoutputthebranchnamesalongwiththetotalaccountdepositatthebranch.
a.Usinganestedqueryinthefromclauser.
selectbranchname,totbalancefrom(selectbranchname,sum(balance)
fromaccount
groupbybranchname)asbranchtotal(branchname,totbalance)wheretotbalance¡
(selectavg(totbalance)
from(selectbranchname,sum(balance)
fromaccount
groupbybranchname)asbranchtotal(branchname,totbalance))
b.Usinganestedqueryinahavingclause.
selectbranchname,sum(balance)fromaccount
groupbybranchnamehavingsum(balance)¡
(selectavg(totbalance)
from(selectbranchname,sum(balance)
fromaccount
groupbybranchname)asbranchtotal(branchname,totbalance))
3.16Listtworeasonswhynullvaluesmightbeintroducedintothedatabase.
Answer:NoAnswer3.17ShowhowtoexpressthecoalesceoperationfromExercise3.4usingthecase
operation.
Answer:NoAnswer.3.18GiveanSQLschemadefinitionfortheemployeedatabaseofFigure3.12.Choose
anappropriatedomainforeachattributeandanappropriateprimarykeyforeachrelationschema.Answer:
createdomaincreatedomaincreatedomaincreatetable
companynameschar(20)citynameschar(30)personnameschar(20)employee
Exercises17
(employeenamestreetcity
primarykeycreatetable(employeenamecompanynamesalary
primarykeycreatetable(companynamecity
primarykeycreatetable(employeenamemanagernameprimarykey
personnames,char(30),citynames,
(employeename))works
personnames,companynames,numeric(8,2),(employeename))company
companynames,citynames,
(companyname))manages
personnames,personnames,(employeename))
3.19Usingtherelationsofoursamplebankdatabase,writeSQLexpressionstodefine
thefollowingviews:
a.Aviewcontainingtheaccountnumbersandcustomernames(butnotthebalances)forallaccountsattheDeerParkbranch.
b.Aviewcontainingthenamesandaddressesofallcustomerswhohaveanaccountwiththebank,butdonothavealoan.
c.AviewcontainingthenameandaverageaccountbalanceofeverycustomeroftheRockRidgebranch.Answer:NoAnswer.
3.20ForeachoftheviewsthatyoudefinedinExercise3.19,explainhowupdates
wouldbeperformed(iftheyshouldbeallowedatall).Answer:NoAnswer.3.21Considerthefollowingrelationalschema
employee(empno,name,office,age)books(isbn,title,authors,publisher)loan(empno,isbn,date)
WritethefollowingqueriesinSQL.
a.PrintthenamesofemployeeswhohaveborrowedanybookpublishedbyMcGraw-Hill.
18Chapter3SQL
b.PrintthenamesofemployeeswhohaveborrowedallbookspublishedbyMcGraw-Hill.
c.Foreachpublisher,printthenamesofemployeeswhohaveborrowedmorethanfivebooksofthatpublisher.Answer:NoAnswer.
3.22Considertherelationalschema
student(studentid,studentname)registered(studentid,courseid)
WriteanSQLquerytolistthestudent-idandnameofeachstudentalongwiththetotalnumberofcoursesthatthestudentisregisteredfor.Studentswhoarenotregisteredforanycoursemustalsobelisted,withthenumberofregisteredcoursesshownas0.Answer:NoAnswer.
3.23Supposethatwehavearelationmarks(studentid,score).WriteanSQLqueryto
findthedenserankofeachstudent.Thatis,allstudentswiththetopmarkgetarankof1,thosewiththenexthighestmarkgetarankof2,andsoon.Hint:Splitthetaskintoparts,usingthewithclause.Answer:NoAnswer.
CHAPTER4
SQLExercises
4.7Referential-integrityconstraintsasdefinedinthischapterinvolveexactlytwo
relations.Consideradatabasethatincludesthefollowingrelations:
salaried-worker(name,office,phone,salary)hourly-worker(name,hourly-wage)address(name,street,city)
Supposethatwewishtorequirethateverynamethatappearsinaddressappearineithersalaried-workerorhourly-worker,butnotnecessarilyinboth.a.Proposeasyntaxforexpressingsuchconstraints.
b.Discusstheactionsthatthesystemmusttaketoenforceaconstraintofthisform.Answer:
a.Forsimplicity,wepresentavariantoftheSQLsyntax.Aspartofthecreatetableexpressionforaddressweinclude
foreignkey(name)referencessalaried-workerorhourly-worker
b.Toenforcethisconstraint,wheneveratupleisinsertedintotheaddressrela-tion,alookuponthenamevaluemustbemadeonthesalaried-workerrelationand(ifthatlookupfailed)onthehourly-workerrelation(orvice-versa).4.8WriteaJavafunctionusingJDBCmetadatafeaturesthattakesaResultSetas
aninputparameter,andprintsouttheresultintabularform,withappropriatenamesascolumnheadings.Answer:NoAnswer.
19
20Chapter4AdvancedSQL
4.9WriteaJavafunctionusingJDBCmetadatafeaturesthatprintsalistofallre-lationsinthedatabase,displayingforeachrelationthenamesandtypesofitsattributes.
Answer:NoAnswer.4.10Consideranemployeedatabasewithtworelations
employee(employee-name,street,city)
works(employee-name,company-name,salary)
wheretheprimarykeysareunderlined.Writeaquerytofindcompanieswhoseemployeesearnahighersalary,onaverage,thantheaveragesalaryatFirstBankCorporation.
a.UsingSQLfunctionsasappropriate.b.WithoutusingSQLfunctions.Answer:
a.createfunctionavg-salary(cnamevarchar(15))
returnsinteger
declareresultinteger;
selectavg(salary)intoresultfromworks
whereworks.company-name=cnamereturnresult;end
selectcompany-namefromworks
whereavg-salary(company-name)>avg-salary(”FirstBankCorporation”)b.selectcompany-namefromworks
groupbycompany-name
havingavg(salary)>(selectavg(salary)
fromworks
wherecompany-name=”FirstBankCorporation”)4.11RewritethequeryinSection4.6.1thatreturnsthename,streetandcityofall
customerswithmorethanoneaccount,usingthewithclauseinsteadofusingafunctioncall.
Answer:NoAnswer.4.12ComparetheuseofembeddedSQLwiththeuseinSQLoffunctionsdefinedin
ageneral-purposeprogramminglanguage.Underwhatcircumstanceswouldyouuseeachofthesefeatures?
Answer:SQLfunctionsareprimarilyamechanismforextendingthepowerofSQLtohandleattributesofcomplexdatatypes(likeimages),ortoperformcomplexandnon-standardoperations.EmbeddedSQLisusefulwhenimper-ativeactionslikedisplayingresultsandinteractingwiththeuserareneeded.
Exercises21
ThesecannotbedoneconvenientlyinanSQLonlyenvironment.EmbeddedSQLcanbeusedinsteadofSQLfunctionsbyretrievingdataandthenperform-ingthefunction’soperationsontheSQLresult.Howeveradrawbackisthatalotofquery-evaluationfunctionalitymayendupgettingrepeatedinthehostlanguagecode.
4.13ModifytherecursivequeryinFigure4.14todefinearelation
empldepth(employeename,managername,depth)
wheretheattributedepthindicateshowmanylevelsofintermediatemanagersaretherebetweentheemployeeandthemanager.Employeeswhoaredirectlyunderamanagerwouldhaveadepthof0.Answer:NoAnswer.
4.14Considertherelationalschema
part(partid,name,cost)
subpart(partid,subpartid,count)
Atuple(p1,p2,3)inthesubpartrelationdenotesthatthepartwithpart-idp2isadirectsubpartofthepartwithpart-idp1,andp1has3copiesofp2Notethatp2mayitselfhavefurthersubparts.WritearecursiveSQLquerythatoutputsthenamesofallsubpartsofthepartwithpart-id“P-100”.Answer:NoAnswer.
4.15ConsideragaintherelationalschemafromExercise4.14.WriteaJDBCfunction
usingnon-recursiveSQLtofindthetotalcostofpart“P-100”,includingthecostsofallitssubparts.Besuretotakeintoaccountthefactthatapartmayhavemultipleoccurrencesofasubpart.YoumayuserecursioninJavaifyouwish.Answer:NoAnswer.
7.22UsingthefunctionaldependenciesofPracticeExercise7.6,computeB+.
Answer:ComputingB+bythealgorithminFigure7.9westartwithresult={B}.ConsideringFDsoftheformβ→γinF,wefindthattheonlydepen-denciessatisfyingβ⊆resultareB→BandB→D.Thereforeresult={B,D}.NomoredependenciesinFapplynow.ThereforeB+={B,D}7.23ShowthatthefollowingdecompositionoftheschemaRofPracticeExercise7.1
isnotalossless-joindecomposition:
(A,B,C)(C,D,E).
Hint:GiveanexampleofarelationronschemaRsuchthat
ΠA,B,C(r)1ΠC,D,E(r)=r
Answer:Followingthehint,usethefollowingexampleofr:
Aa1a2
Bb1b2
Cc1c1
Dd1d2
Ee1e2
WithR1=(A,B,C),R2=(C,D,E):a.ΠR1(r)wouldbe:
Aa1a2Cc1c1Aa1a1a2a2
Bb1b2Dd1d2Bb1b1b2b2
Cc1c1Ee1e2Cc1c1c1c1
Dd1d2d1d2
Ee1e2e1e2
b.ΠR2(r)wouldbe:
c.ΠR1(r)1ΠR2(r)wouldbe:
Clearly,ΠR1(r)1ΠR2(r)=r.Therefore,thisisalossyjoin.
Exercises
6.14Explainthedistinctionsamongthetermsprimarykey,candidatekey,andsu-perkey.
Answer:Asuperkeyisasetofoneormoreattributesthat,takencollectively,al-lowsustoidentifyuniquelyanentityintheentityset.Asuperkeymaycontainextraneousattributes.IfKisasuperkey,thensoisanysupersetofK.Asuperkeyforwhichnopropersubsetisalsoasuperkeyiscalledacandidatekey.Itispos-siblethatseveraldistinctsetsofattributescouldserveascandidatekeys.Theprimarykeyisoneofthecandidatekeysthatischosenbythedatabasedesignerastheprincipalmeansofidentifyingentitieswithinanentityset.6.15ConstructanE-Rdiagramforahospitalwithasetofpatientsandasetofmedi-caldoctors.Associatewitheachpatientalogofthevarioustestsandexamina-tionsconducted.
Answer:SeeFigure6.16.16ConstructappropriatetablesforeachoftheE-RdiagramsinPracticeExercises6.1
and6.2.Answer:
a.Carinsurancetables:
person(driver-id,name,address)car(license,year,model)
accident(report-number,date,location)
participated(driver-id,license,report-number,damage-amount)b.Hospitaltables:
33
insurancedate-admittednamess#patientstest_logDr-Patientdate-checked-outtest_idtestperformed_bydoctorstest_namedatetimeresultdss#namespecializationFigure6.1E-Rdiagramforahospital.
patients(patient-id,name,insurance,date-admitted,date-checked-out)doctors(doctor-id,name,specialization)test(testid,testname,date,time,result)doctor-patient(patient-id,doctor-id)test-log(testid,patient-id)performed-by(testid,doctor-id)c.Universityregistrar’stables:
student(student-id,name,program)course(courseno,title,syllabus,credits)
course-offering(courseno,secno,year,semester,time,room)instructor(instructor-id,name,dept,title)enrols(student-id,courseno,secno,semester,year,grade)teaches(courseno,secno,semester,year,instructor-id)requires(maincourse,prerequisite)
因篇幅问题不能全部显示，请点此查看更多更全内容