数据库期末考试题库.docx
- 文档编号:6994567
- 上传时间:2023-01-15
- 格式:DOCX
- 页数:51
- 大小:415.14KB
数据库期末考试题库.docx
《数据库期末考试题库.docx》由会员分享,可在线阅读,更多相关《数据库期末考试题库.docx(51页珍藏版)》请在冰豆网上搜索。
数据库期末考试题库
1.DBMShasmanyadvantages.Thenwhataretwodisadvantagesofdatabasesystem?
Answer:
Twodisadvantagesassociatedwithdatabasesystemsarelistedbelow.
a.Setupofthedatabasesystemrequiresmoreknowledge,money,skills,andtime.
b.b.Thecomplexityofthedatabasemayresultinpoorperformance.
2Listfiveresponsibilitiesofadatabasemanagementsystem.
Ansers:
a.interactionwiththefilemanager.
b.integrityenforcement.
c.securityenforcement.
d.backupandrecovery.
e.concurrencycontrol.
3.Whatarefivemainfunctionsofadatabaseadministrator?
Answer:
Fivemainfunctionsofadatabaseadministratorare:
•Tocreatetheschemedefinition
•Todefinethestoragestructureandaccessmethods
•Tomodifytheschemeand/orphysicalorganizationwhennecessary
•Tograntauthorizationfordataaccess
•Tospecifyintegrityconstraints
4.Explainthedistinctionsamongthetermsprimarykey,candidatekey,andsuperkey.
Answer:
Asuperkeyisasetofoneormoreattributesthat,takencollectively,al-lowsustoidentifyuniquelyanentityintheentityset.Asuperkeymaycontainextraneousattributes.IfKisasuperkey,thensoisanysupersetofK.Asuperkeyforwhichnopropersubsetisalsoasuperkeyiscalledacandidatekey.Itispossiblethatseveraldistinctsetsofattributescouldserveascandidatekeys.Theprimarykeyisoneofthecandidatekeysthatischosenbythedatabasedesignerastheprincipalmeansofidentifyingentitieswithinanentityset
6.Whatislogicaldataindependenceandwhyisitimportant?
Answer:
Logicaldataindependencemeansthatusersareshieldedfromchangesinthelogicalstructureofthedata,i.e.,changesinthechoiceofrelationstobestored.Forexample,ifarelationStudents(sid,sname,gpa)isreplacedbyStudentnames(sid,sname)andStudentgpas(sid,gpa)forsomereason,applicationprogramsthatoperateontheStudentsrelationcanbeshieldedfromthischangebydefiningaviewStu-dents(sid,sname,gpa)(asthenaturaljoinofStudentnamesandStudentgpas).Thus,applicationprogramsthatrefertoStudentsneednotbechangedwhentherelationStudentsisreplacedbytheothertworelations.TheonlychangeisthatinsteadofstoringStudentstuples,thesetuplesarecomputedasneededbyusingtheviewdefinition;thisistransparenttotheapplicationprogram.
7.Whichofthefollowingplaysanimportantroleinrepresentinginformationabouttherealworldinadatabase?
Explainbriefly.
1.Thedatadefinitionlanguage.
2.Thedatamanipulationlanguage.
3.Thebuffermanager.
4.Thedatamodel.
8Considerthefollowinginformationaboutauniversitydatabase:
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’sprincipalinvestigator).
Eachprojectisworkedonbyoneormoreprofessors(knownastheproject’sco-investigators).
Professorscanmanageand/orworkonmultipleprojects.
Eachprojectisworkedonbyoneormoregraduatestudents(knownastheproject’sresearchassistants).
Whengraduatestudentsworkonaproject,aprofessormustsupervisetheirworkontheproject.Graduatestudentscanworkonmultipleprojects,inwhichcasetheywillhavea(potentiallydifferent)supervisorforeachone.
Departmentshaveadepartmentnumber,adepartmentname,andamainoffice.
Departmentshaveaprofessor(knownasthechairman)whorunsthedepartment.
Professorsworkinoneormoredepartments,andforeachdepartmentthattheyworkin,atimepercentageisassociatedwiththeirjob.
Graduatestudentshaveonemajordepartmentinwhichtheyareworkingontheirdegree.
Eachgraduatestudenthasanother,moreseniorgraduatestudent(knownasastudentadvisor)whoadviseshimorheronwhatcoursestotake.
DesignanddrawanERdiagramthatcapturestheinformationabouttheuniversity.
UseonlythebasicERmodelhere;thatis,entities,relationships,andattributes.Besuretoindicateanykeyandparticipationconstraints.
9.ConsidertheuniversitydatabasefromExercise8andtheERdiagramyoudesigned.WriteSQLstatementstocreatethecorrespondingrelationsandcaptureasmanyoftheconstraintsaspossible.Ifyoucannotcapturesomeconstraints,explainwhy.
AnswerThefollowingSQLstatementscreatethecorrespondingrelations.
1.CREATETABLEProfessors(profssnCHAR(10),
nameCHAR(64),
ageINTEGER,
rankINTEGER,
specialityCHAR(64),
PRIMARYKEY(profssn))
2.CREATETABLEDepts(dnoINTEGER,
dnameCHAR(64),
officeCHAR(10),
PRIMARYKEY(dno))
3.CREATETABLERuns(dnoINTEGER,
profssnCHAR(10),
PRIMARYKEY(dno,profssn),
FOREIGNKEY(profssn)REFERENCESProfessors,
FOREIGNKEY(dno)REFERENCESDepts)
4.CREATETABLEWorkDept(dnoINTEGER,
profssnCHAR(10),
pctimeINTEGER,
PRIMARYKEY(dno,profssn),
FOREIGNKEY(profssn)REFERENCESProfessors,
FOREIGNKEY(dno)REFERENCESDepts)
ObservethatwewouldneedcheckconstraintsorassertionsinSQLtoenforcetherulethatProfessorsworkinatleastonedepartment.
5.CREATETABLEProject(pidINTEGER,
sponsorCHAR(32),
startdateDATE,
enddateDATE,
budgetFLOAT,
PRIMARYKEY(pid))
6.CREATETABLEGraduates(gradssnCHAR(10),
ageINTEGER,
nameCHAR(64),
degprogCHAR(32),
majorINTEGER,
PRIMARYKEY(gradssn),
FOREIGNKEY(major)REFERENCESDepts)
NotethattheMajortableisnotnecessarysinceeachGraduatehasonlyonemajor
andsothiscanbeanattributeintheGraduatestable.
7.CREATETABLEAdvisor(seniorssnCHAR(10),
gradssnCHAR(10),
PRIMARYKEY(seniorssn,gradssn),
FOREIGNKEY(seniorssn)
REFERENCESGraduates(gradssn),
FOREIGNKEY(gradssn)REFERENCESGraduates)
8.CREATETABLEManages(pidINTEGER,
profssnCHAR(10),
PRIMARYKEY(pid,profssn),
FOREIGNKEY(profssn)REFERENCESProfessors,
FOREIGNKEY(pid)REFERENCESProjects)
9.CREATETABLEWorkIn(pidINTEGER,
profssnCHAR(10),
PRIMARYKEY(pid,profssn),
FOREIGNKEY(profssn)REFERENCESProfessors,
FOREIGNKEY(pid)REFERENCESProjects)
ObservethatwecannotenforcetheparticipationconstraintforProjectsinthe
WorkIntablewithoutcheckconstraintsorassertionsinSQL.
10.CREATETABLESupervises(profssnCHAR(10),
gradssnCHAR(10),
pidINTEGER,
PRIMARYKEY(profssn,gradssn,pid),
FOREIGNKEY(profssn)REFERENCESProfessors,
FOREIGNKEY(gradssn)REFERENCESGraduates,
FOREIGNKEY(pid)REFERENCESProjects)
NotethatwedonotneedanexplicittablefortheWorkProjrelationsinceeverytimeaGraduateworksonaProject,heorshemusthaveaSupervisor.
10.Considerthefollowingrelations:
Student(snum:
integer,sname:
string,major:
string,level:
string,age:
integer)
Class(name:
string,meetsat:
string,room:
string,fid:
integer)
Enrolled(snum:
integer,cname:
string)
Faculty(fid:
integer,fname:
string,deptid:
integer)
Themeaningoftheserelationsisstraightforward;forexample,Enrolledhasonerecordperstudent-classpairsuchthatthestudentisenrolledintheclass.
WritethefollowingqueriesinSQL.Noduplicatesshouldbeprintedinanyoftheanswers.
1.FindthenamesofallJuniors(level=JR)whoareenrolledinaclasstaughtbyI.Teach.
2.FindtheageoftheoldeststudentwhoiseitheraHistorymajororenrolledinacoursetaughtbyI.Teach.
3.FindthenamesofallclassesthateithermeetinroomR128orhavefiveormorestudentsenrolled.
4.Findthenamesofallstudentswhoareenrolledintwoclassesthatmeetatthesametime.
5.Findthenamesoffacultymemberswhoteachineveryroominwhichsomeclassistaught.
Theanswersaregivenbelow:
1.SELECTDISTINCTS.Sname
FROMStudentS,ClassC,EnrolledE,FacultyF
WHERES.snum=E.snumANDE.cname=C.nameANDC.fid=F.fidAND
F.fname=‘I.Teach’ANDS.level=‘JR’
2.SELECTMAX(S.age)
FROMStudentS
WHERE(S.major=‘History’)
ORS.snumIN(SELECTE.snum
FROMClassC,EnrolledE,FacultyF
WHEREE.cname=C.nameANDC.fid=F.fid
ANDF.fname=‘I.Teach’)
3.SELECTC.name
FROMClassC
WHEREC.room=‘R128’
ORC.nameIN(SELECTE.cname
FROMEnrolledE
GROUPBYE.cname
HAVINGCOUNT(*)>=5)
4.SELECTDISTINCTS.sname
FROMStudentS
WHERES.snumIN(SELECTE1.snum
FROMEnrolledE1,EnrolledE2,ClassC1,ClassC2
WHEREE1.snum=E2.snumANDEame<>Eame
ANDEame=C1.name
ANDEame=C2.nameANDC1.meetsat=C2.meetsat)
5.SELECTDISTINCTF.fname
FROMFacultyF
WHERENOTEXISTS((SELECT*
FROMClassC)
EXCEPT
(SELECTC1.room
FROMClassC1
WHEREC1.fid=F.fid))
1.Listfoursignificantdifferencesbetweenafile-processingsystemandaDBMS.
Answer:
Somemaindifferencesbetweenadatabasemanagementsystemand
afile-processingsystemare:
•Bothsystemscontainacollectionofdataandasetofprogramswhichaccessthatdata.Adatabasemanagementsystemcoordinatesboththephysicalandthelogicalaccesstothedata,whereasafile-processingsystemcoordinatesonlythephysicalaccess.
•Adatabasemanagementsystemreducestheamountofdataduplicationby
ensuringthataphysicalpieceofdataisavailabletoallprogramsauthorized
tohaveaccesstoit,whereasdatawrittenbyoneprograminafile-processing
systemmaynotbereadablebyanotherprogram.
•Adatabasemanagementsystemisdesignedtoallowflexibleaccesstodata
(i.e.,queries),whereasafile-processingsystemisdesignedtoallowpredeterminedaccesstodata(i.e.,compiledprograms).
•Adatabasemanagementsystemisdesignedtocoordinatemultipleusers
accessingthesamedataatthesametime.Afile-processingsy
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 期末考试 题库