数据库英语综合测试题16.docx
《数据库英语综合测试题16.docx》由会员分享，可在线阅读，更多相关《数据库英语综合测试题16.docx（19页珍藏版）》请在冰豆网上搜索。
数据库英语综合测试题16
Part3:
QuestionsandAnswers
1.Considerthefollowingrelationalschema:
student（studentno,_rstname,lastname）
book（isbn,title,authors,publisher,year）
loan（studentno,isbn,checkoutdate,duration）
UseSQLtowritethefollowingqueries:
A.Createthetableforthebooktableappropriatedomainandrequiredcontraints.
B.Changethedatatypeofthecheckoutdateattributeoftheloantabletodate.
C.Addaconstraintintotheloantabletomakesuretheloandurationisnomorethan180days.
D.GrantArvilandAmyselectandupdateauthrorizationonthebooktable.
2.Whatare6basicoperatorsofrelationalalgebra?
3.Explainhownatural-joinoperationcanbeaccomplishedbybasicrelationalalgebraoperations?
4.Explainhowthedivisionoperationcanbeaccomplishedbybasicrelationalalgebraoperations?
5.Thedatabaseofaresearchcentercontainsthefollowingthreetablesaboutemployees,projects,andthetimespentbytheemployeesontheprojects.
Employee（ssn:
int,name:
string,jobTitle:
string）
Project（pid:
int,name:
string,sponsor:
string,startYear:
int,endYear:
int）
WorkedOn（ssn:
int,pid:
int,year:
int,month:
int,noHours:
int）.
ThetableEmployeelistsalltheemployeesofthecentre.ThetableProjectlistsalltheprojectsofthecentrewiththeirsponsorandthestartandendyearoftheproject.ThetableWorkedOnrecordshowmanyhourstheemployeeshavespentonwhichprojectinwhichmonth.Foreachtable,theattributesthatmakeuptheprimarykeyareunderlined.
Expresseachofthefollowingqueriesinrelationalalgebra.
A.Returnthenamesoftheprojectsthatwereactivein2008.
B.Returnthenamesofthoseprogrammerswhoinsomemonthspentmorethan60hoursonaprojectsponsoredbytheEU.
C.ReturnthenamesofthoseprogrammerswhoneverworkedonaprojectsponsoredbytheEU.
6.ConsidertherelationalschemaofQuestion5.WriteSQLqueriesoverthisschemathatanswerthefollowingquestions.
A.Howmanyprojectsthatwereactivein2008weresponsoredbytheEU?
B.Foreachproject,year,andmonth,howmanyhoursofworkhavebeenspent?
（Returnonlydataforaproject,yearandmonthifsometimehasbeenspent.）
C.HowmanyprogrammersaretherewhohaveexperienceinworkingonaprojectsponsoredbytheEU?
（Notethataprogrammerwhoworkedontwoormoreprojectsshouldbecountedonlyonce.）
D.ReturnthenamesoftheprogrammerswhoworkedonnomorethantwoprojectssponsoredbytheEU.（NotethatthisincludestheprogrammerswhoneverworkedonanyprojectsponsoredbytheEU.）
E.Whichprogrammer（s）spentthemaximaltotalnumberofhoursonEUprojectsamongallprogrammersworkingonEUprojects?
7.Considerthefollowingrelationthatkeepstrackofthebookingsinahotel:
Booking（guestID,guestName,creditCard,roomNo,roomCat,from,to）.
Supposethefollowingfunctionaldependenciesholdontherelation:
guestID®guestName,creditCard
roomNo®roomCat
roomNo,from®guestID,to
roomNo,to®guestID,from.
A.Decomposetherelationinsmallerrelationssuchthat
–eachofthesmallerrelationsisinBNCFwithrespecttotheprojectionoftheoriginaldependencies;
–thedecompositionisalosslessjoindecomposition.
B.Isyourdecompositiondependencypreserving?
Ifyouransweris“yes”,arguewhy.Ifyouransweris“no”,showwhichdependencieshavebeenlost.
8.DrawanERdiagramthatcapturesallthefollowinginformation:
_PatientsareidentifiedbyanSSN,andtheirnames,addressesandagesmustberecorded.
_DoctorsareidentifiedbyanSSN.Foreachdoctor,thename,specialtyandyearsofexperiencemustberecorded.
_Eachpharmacyhasaname,addressandphonenumber.Apharmacymusthaveamanager.
_ApharmacistisidentifiedbyanSSN,he/shecanonlyworkforonepharmacy.Foreach
pharmacist,thename,qualificationmustberecorded.
_Foreachdrug,thetradenameandformulamustberecorded.
_Everypatienthasaprimaryphysician.Everydoctorhasatleastonepatient.
_Eachpharmacysellsseveraldrugs,andhasapriceforeach.Adrugcouldbesoldatseveralpharmacies,andthepricecouldvaryfromonepharmacytoanother.
_Doctorsprescribedrugsforpatients.Adoctorcouldprescribeoneormoredrugsforseveralpatients,andapatientcouldobtainprescriptionsfromseveraldoctors.Eachprescriptionhasadateandquantityassociatedwithit.
9.ConvertthefollowingE/Rdesign（forasimplebankingapplication）intoarelationaldesign.Givetherelationaldesignasarelationaldiagramwitharrowstoindicatetheforeignkeyrelationships.Underlineallattributesthatcorrespondtoprimarykeys.
10.Considerthefollowingemployeedatabase,wheretheprimarykeysareunderlined.
Employee（ename:
string,street:
string,city:
string）;
Works（employee:
string,company:
string,salary:
real）;
Company（cname:
string,city:
string）;
Manages（employee:
string,manager-name:
string）
GiveasingleSQLstatementforeachofthefollowingqueries:
A.Findthenames,streetaddresses,andcitiesofresidenceofallemployeeswhoworkfor“FirstBankCorporation"andearnmorethan$40,000.
B.Findthenamesofallemployeesinthedatabasewholiveinthesamecitiesasthecompaniesforwhichtheywork.
C.Giveallmanagersof\FirstBankCorporation"a10percentsalaryraise.
D.Findthenamesofallemployeesinthedatabasewhoearnmorethananyemployeeof“SmallBankCorporation".
E.Assumethatthecompaniesmaybelocatedinseveralcities.Findthenamesofallcompanieslocatedineverycityinwhich\SmallBankCorporation"islocated.
F.Findthenameofthecompanythathasthemostemployees.
G.Findthosecompanieswhoseemployeesearnahighersalary,onaverage,thantheaveragesalaryat“FirstBankCorporation",displaythosecompanies'namesinascendingorder.
11.Considerthefollowing（simplified）relationalschemaforuniversitystudy:
Student（id:
integer,family:
string,given:
string,degree:
string,enrolled:
date）
Course（id:
integer,code:
string,session:
string,title:
string,syllabus:
string）
Enrolment（student:
integer,course:
string,mark:
real,grade:
string）
A.ForeachofthefollowingSQLqueries,writeanefficientrelationalalgebraexpressionthatmightbeusedtoimplementthequery.Tomaketheexpressionsclearer,youmayuseasmanynamedintermediatetemporaryrelationsasyouwish.Correct,butgrosslyinefficient,relationalalgebraexpressionswillbeawardedonlyhalfmarks.
A.selectgiven,familyfromStudent
B.select*fromEnrolmentwherestudent=2233456
C.selectgiven,family,course
fromEnrolment,Student
whereEnrolment.student=Student.id
D.selecte.code,e.session,c.title,e.mark,e.grade
fromEnrolmente,Coursec,Students
wheree.course=c.idande.student=s.idands.id=2234567
12.ConsiderthefollowingE/Rdiagram,modelingdataaboutpatientsinahospital:
A.PerformaconversionoftheE/Rdiagramintorelationschemas.Youshouldeliminaterelationsthatarenotnecessary（e.g.,bycombiningrelations）.
13.Giventheinterleavedschedules:
Schedule1
T1
RA.
RC.
WC.
Commit
T2
RC.
WC.
RB.
WB.
Commit
T3
RC.
RA.
WA.
Commit
Schedule2
T1
RA.
RC.
WA.
Commit
T2
RC.
RB.
WB.
Commit
T3
RB.
WB.
Commit
Schedule3
T1
RC.
WA.
WA.
Commit
T2
WA.
RB.
WB.
Commit
A.Whichofthefollowingschedulesareserializable?
Giveaserialscheduleoridentifypossibleanomalies.
B.Drawtheprecedencegraphforallthreeschedulesandcheckwhethertheyareconflict-serializableornot.
C.Applystrict2PLtothenon-conflict-serializableschedules
D.Inoneoftheschedulesadeadlockemerges–drawthewaits-for-graphforthisscheduleafteralltransactionsarecapturedinthedeadlocksituation.
（UseX（.）todenoteexclusivelocksandS（.）todenotesharedlocks!
）
14.ConsiderarelationalschemaABCDEFGHIJ,whichcontainsthefollowingFDs:
AB®C,D®E,AE®G,GD®H,IF®J.
A.Checkwhetherornotthefunctionaldependenciesentail
ABD®GH
ABD®HJ
ABC®G
GD®HE
B.LetAdenoteakeyfortheaforementionedrelation.Derivealosslessjoin,dependencypreservingdecompositionin3NF!
15.WhatdotheACIDpropertiesstandfor?
Giveabriefdescriptionofthefourcharacteristics.
16.Whataretheserialschedule,equivalentschedulesandserializableschedule?
17.LetRandSaretworelationsshownasbelow:
R
A
B
C
1
2
3
4
5
6
7
8
9
S
B
C
D
2
3
10
2
3
11
6
7
12
Writetheresultsofthefollowingqueries:
A.ÕA,B+C®X（R）
B.ÕB,C（R）-ÕB,C（S）
C.B
B,sumD.（S）
D.B
B,maxD.（RS）
18.ConsiderthefollowingrelationalschemaformovieDVDrentalstore:
customer（customerid,firstname,lastname）
DVD（dvdid,title,genre,director,releasedyear）
borrow（customerid,dvdid,checkoutdate,duration）
UseSQLtowritethefollowingqueries:
A.Create3tablesfortheaboveschemawithappropriatedomainandrequiredcontraints.
B.Changethedatatypeofthecheckoutdateattributeoftheborrowtabletodate.
19.Considerthefollowinggradebookrelationalschemadescribingthedataforagradebookofaparticularinstructor
catalog（cno,ctitle）
students（sid,fname,lname,minit）
courses（term,secno,cno,score）
enrolls（sid,term,secno）
UserelationalgebraandSQLtowritethefollowingqueries:
A.Retrievethenamesofstudentsenrolledinthe'Database'classinthetermofFall2009.
B.RetrievethenamesofstudentswhohaveenrolledinCS226orCS227.
C.Retrievethenamesofstudentswhohavenoten
升级会员 