数据库英语综合测试题16.docx
- 文档编号:26068782
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:19
- 大小:125.61KB
数据库英语综合测试题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,o,score)
enrolls(sid,term,secno)
UserelationalgebraandSQLtowritethefollowingqueries:
A.Retrievethenamesofstudentsenrolledinthe'Database'classinthetermofFall2009.
B.RetrievethenamesofstudentswhohaveenrolledinCS226orCS227.
C.Retrievethenamesofstudentswhohavenotenrolledinanyclass.
D.Retrievethetitlesofcourseswhoseaveragescoreofthewholeclassismorethan80.
20
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 英语 综合测试 16