数据库系统期末试题及答案.docx
- 文档编号:26099550
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:10
- 大小:37.24KB
数据库系统期末试题及答案.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.Ifboth
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 期末 试题 答案