数据库系统期末试题及答案.docx
《数据库系统期末试题及答案.docx》由会员分享，可在线阅读，更多相关《数据库系统期末试题及答案.docx（10页珍藏版）》请在冰豆网上搜索。
数据库系统期末试题及答案
华南农业高校期末考试试卷〔A卷〕
2021-2021学年第一学期 考试科目：
数据库系统
考试类型：
闭卷 考试时间：
120分钟
学号姓名年级专业
题号
一
二
三
四
五
六
总分
得分
评阅人
Question1:
true-falsequestion（15points）Foreachofthefollowingstatements,indicatewhetheritisTRUEorFALSE（Using√forTRUEand×forFALSE）.Youwillget1pointforeachcorrectanswer,-0.5pointforeachincorrectanswer,and0pointforeachanswerleftblank.Besuretowriteyouranswerintheanswersheet.
1.Aprimarykeyisafield（orgroupoffields）thatuniquelydescribeseachrecordinthedatabase.
2.Dataredundancyimprovestheintegrityofadatabase.
3.SQListhelanguageusedbyrelationaldatabasestocreateobjectsandtomanipulateandretrievedata.
4.Arelationaldatabasemanagementsystemdoesnotincludetoolsforbackingup&restoringdatabases.
5.Anattributeisalsoknownasarowinmostdatabases.
6.Anassociationbetweenentitiesisknownasarelationship.
7.Integrityconstraintslimitthenumberofentitiesthatcanbeplacedinatableordatabase.
8.TheEntity-Relationshipdatamodelisoftenusedinthephysicaldesignphase.
9.Theconcept“relation〞inrelationmodelisexactlythesameastheconcept“relationship〞inERmodel.
10.Mostrelationshipsetsinadatabasesysteminvolvestwoentitysets.
11.Theclosureofanattributesetcontainsthatattributeset.
12.Losslessdecompositionisnecessaryinadecompositionalgorithm.
13.IfA→BandC→Dhold,thenAC→BDalsoholds.
14.Itisnotnecessarythatalegalschedulepreservestheorderinwhichtheinstructionsappearineachindividualtransaction.
15.Updateoperationsindatabasemustbewrittenintologbeforeupdatingdatabase.
Question2single-choicequestion（2pointsforeachproblem,30pointsintotal）
1.istheonlyoneincorrectdescriptionfromthefollowings:
A.R=（R-S）∪（R∩S）B.R-S=R-（R∩S）
C.R∩S=S-（R-S）D.R∩S=S-（S-R）
2.Choosetheonlyonecorrectexpressionfromthefollowings:
_______.
A.（some）inB.（=all）notinC.existsrrØD.X-YØXY
3.ofthefollowingfourexpressionsofrelationalalgebraisnotequivalenttotheotherthreeTheyareallbasedontherelationsR（A,B）andS（B,C）.
A.
B.
C.
D.
4.Inthefollowing,assumeaisanattributeofsomecharacter-stringtype,e.g.CHAR（10）,andthatitmaybeNULL.
Q1:
SELECT*FROMRWHEREaISNULL;
Q2:
SELECT*FROMRWHEREaNOTLIKE'%';
A.Q1andQ2producethesameanswer.
B.TheanswertoQ1isalwayscontainedintheanswertoQ2.
C.TheanswertoQ2isalwayscontainedintheanswertoQ1.
D.Q1andQ2producedifferentanswers.
5.TheEntity-Relationshipdatamodelis
A.DBMSdependentB.DBMSindependentC.bothAandBD.neitherAnorB
6.InSQL,anUPDATEstatementwithoutaWHEREclause:
A.Updateseveryrowinatable.B.Updatesnorowsinatable.
C.Updateseverycolumninatable.D.ResultsinaCartesianproduct.
7.Ifacoursecanbetaughtbymanyteachers,andateachercanteachonlyonecourse,thenthemappingcardinalityfromcoursetoteacheris
A.one-to-oneB.one-to-manyC.many-to-oneD.many-to-many
8.Ifthereisamany-to-onerelationshipbetweenentityAandB,then
A.thereexistsafunctionaldependencyfromtheprimarykeyinBtotheprimarykeyinA,i.e.,PK（B）→PK（A）.
B.thereexistsafunctionaldependencyfromtheprimarykeyinAtotheprimarykeyinB,i.e.,PK（A）→PK（B）.
C.bothAandB.D.neitherAnorB
9.IfafunctionaldependencyAB→RholdsonrelationR（A,B,C）,then（A,B）isdefinitelya______ofR.
A.superkeyB.primarykeyC.candidatekeyD.foreignkey
10.ArelationalschemaRisin_____ifthedomainsofallattributesofRareatomic
A.1NFB.3NFC.BCNFD.4NF
11.Whichoneofthefollowingstatementistrue
A.3NFismorestrictthanBCNFB.4NFismorestrictthanBCNF
C.1NFismorestrictthanBCNFD.BCNFisthemoststrictnormalform
12.IfatransactionTihasobtainanexclusivelockondataitemQ,thentransitionTjcan______.
A.obtainanexclusivelockondataitemQB.obtainasharedlockondataitemQ
C.waitforlockgrantingondataitemQD.readorwriteQwithoutalock
13.Ifbothandappearinthelogafterthenearestcheckpointtosystemcrash,thentransactionTimustbe______
A.undoneB.redoneC.deletedD.NeitherAorB
14.______isthefinalstateinalifecycleofatransaction.
A.committedB.abortedC.failedD.AorB
15.in2PLprotocol,atstage,Atransactionmayobtainlocks,butmaynotreleaselocks.
A.ShrinkingphaseB.GrowingphaseC.CommittedD.Aborted
Question3（12points）Considerthefollowingdatabaserequirement:
AhospitalhaspropertieslikeID,name,location,rank,capacity.AdoctorcanbedescribedbyID,name,age,skill.ApatienthaspropertieslikeID,name,age,sex,address.Theabovethreeentitiesmustsatisfysomeconstraints:
Eachdoctorcanbeunemployedoremployedbyonehospital.Ifadoctorisemployed,hissalaryneedstoberecordedinthedatabase.Apatientcangotomanyhospitals.
1.DrawERdiagramtoillustratetheabovedatabaserequirement[8points].
2.TranslateyourERdiagramintorelationaldatabaseschemas,andpointouttheprimarykeysandforeignkeys.Youcanwriteyouranswersinthefollowingformat:
“R（a1,a2,a3,a4）,primarykey:
a1,foreignkey:
a4〞[4points].
Question4.（24points）Thefollowingfivetablesareforacompanymanagementsystem:
EMPLOYEE（ID,Name,Birthday,Address,Sex,Salary,Dnumber）
DEPARTMENT（Dnumber,Dname,MgrID）
PROJECT（Pnumber,Pname,Pcity）
WORKS_ON（Pnumber,E-ID,Wdate,Hours）
CHILD（E-ID,CHD-ID,CHD_name,Sex,Birthday）
1.Basedonthegivingrelations,Specifythefollowingqueriesusingrelationalalgebra（3pointsforeach）.
1）Listthenamesofallemployeeswithbirthdayearlierthan‘1970-1-1’andsalarylessthan$5000.
2）Listthenamesofallemployeeswhohaveachild.
3）Listthecitiesandthetotalnumberofprojectswhicharelocatedonsamecity.
2.SpecifythefollowingoperationsinSQL（3pointsforeach）.
1）DefinethetableWORKS_ON,declarePnumber,E-ID,Wdateastheprimarykey,Pnumberastheforeignkeyreferencingtheprimarykeyofproject,E-IDastheforeignkeyreferencingtheprimarykeyofemployee,andensurethatthevaluesofHoursarenon-negativewithdefaultvalue8.
2）Foreachemployeeworkingonthe‘Network’project（Pname）,increasehis/hersalaryby5%.
3）Listthenamesofalldepartmentmanagerswhohavenochild.
4）Findthenamesofallemployeesindepartment5（Dnumber）whohaveworkedonboth'X'projectand'Y'project（Pname）.
5）Foreachproject,findtheprojectnumber,projectnameandthetotalhours（byallemployees）spentonthatprojectinOct.2021.
Question5（10points）ConsiderarelationR（A,B,C,D,E,F）withthesetofFunctionalDependencies
F={A→BCD,BC→DE,B→D,D→A}
1.ComputetheClosuresofattributesetsA+,C+,E+[3points].
2.GiveonecandidatekeyofR[2points].
3.IsFequivalentto{A→BC,BC→E,B→D,D→A}[2points]
4.WhatisthehighestnormalformofRExplainyourreasons[3points].
Question6（9points）Thereare3transactions:
ConsiderthefollowingscheduleSontransitionsset{T1,T2,T3,T4},withRandWdenotesreadandwriteoperationrespectively.
S=R1（A）R2（B）R3（A）R2（C）R4（D）W2（B）R1（B）W1（D）R3（B）W3（B）W2（C）
1.ListallconflictoperationpairsinS[3points].
2.Sno-conflictoperationstoseewhetheritisaserializableschedule[3points].
3.WriteallitsequivalentserialschedulesifSisconflictserializableOrshowwhyifitisnotconflictserializable[3points].
华南农业高校期末考试试卷〔A卷-AnswerSheets〕
2021-2021学年第1学期考试科目：
Databasesystem
考试类型：
〔闭卷〕 考试时间：
120分钟
学号姓名年级专业
题号
一
二
三
四
五
六
总分
得分
评阅人
Instructionstocandidates:
1.Writeyourname,studentnumberandclassonboththequestionpapersandtheanswerpapers.
2.DONOTwriteyouranswersonthequestionpapers.WritethemALLONTHEANSWERPAPERS.
3.WriteyouranswersineitherChineseorEnglish.
4.Handinallpapers（boththequestionpapersandtheanswerpapers）.
Question1（15points）
题号
1
2
3
4
5
6
7
8
9
1
得分
√
×
√
×
×
√
×
×
×
√
√
√
√
×
√
Question2（30points）
题号
1
2
3
4
5
6
7
8
9
1
得分
C
C
C
D
B
A
B
B
A
A
B
C
B
D
B
得分
Question3（12points）
hospital（ID,name,location,rank,capacity）,primarykey:
ID,
doctor（ID,name,age,skill,hospitalID,salary）,primarykey:
ID,foreignkey:
hospitalIDrefertohospital（ID）
patient（ID,name,age,sex,address）,primarykey:
ID,
livein（patientID,hospitalID）primarykey:
（hospitalID,PatientID）,foreignkeyhospitalIDrefertohospital（ID），
foreignkeypatientIDrefertopatient（ID）
得分
Question4[24points]
1：
1）
2）
3）
2：
1）CREATETALBEWorks_On（
Pnumberint,
E-IDchar（15）,
Wdatedate,
Hoursintdefault8CHECK（Hours>=0）,
PRIMARYKEY（Pnumber,E-ID,Wdate）,
FOREIGNKEY（E-ID）REFERENCESEmployee（ID）,
FOREIGNKEY（Pnumber）REFERENCESProject（Pnumber）,
2）
UPDATEEmployeeSETSalary=Salary1.05
WHEREIDIN
（SELECTE-IDFROMProjectnaturaljoinWorks_on
WHEREPname=’Network’）;
3）
SELECTNameFROMEmployee,Department
WHEREID=MrgIDANDIDNOTIN
（SELECTE-IDFROMChild）;
4）
SELECTNameFROMEmployee
WHEREDnumber=5ANDIDIN
（SELECTE-IDFROMProjectnaturaljoinWorks_on
WHEREPname=’X’）
ANDIDIN
（SELECTE-IDFROMProjectnaturaljoinWorks_on
WHEREPname=’Y’）;
5）
SELECTPnumber,Pname,SUM（Hours）
FROMProjectNATURALJO
升级会员 