数据库试题参考.docx
- 文档编号:5645235
- 上传时间:2022-12-30
- 格式:DOCX
- 页数:11
- 大小:66.37KB
数据库试题参考.docx
《数据库试题参考.docx》由会员分享,可在线阅读,更多相关《数据库试题参考.docx(11页珍藏版)》请在冰豆网上搜索。
数据库试题参考
(学生考试用)
课程名称:
数据库系统(英)学分:
3大纲编号06022505
试卷编号:
A考试方式:
闭卷满分分值:
70考试时间:
120分钟
组卷日期:
2010年1月20日组卷教师(签字):
审定人(签字):
学生班级:
学生学号:
学生姓名:
说明:
1.所有答案做在答卷纸上,并请标明题号
2.考试完毕后连考卷一起交上
Question1(20points,1perpart)
1.Ifaproductcanbemanufacturedinmanyplants,andaplantcanmanufacturemanyproducts,thisisanexampleofwhichtypeofrelationshipfromproductstoplants?
A)Many-manyB)Many-oneC)One-oneD)One-many
2.Ifweusethe“E/R”approachofconvertingthefollowingE/Rdiagramtorelations,whichofthefollowingwouldbeoneoftherelationschemasweconstruct?
A)B(d,e,f)B)C(g,e)C)A(d,f,g)D)B(d,f)
3.Whichofthefollowingfourexpressionsofrelationalalgebraisnotequivalenttotheotherthree?
TheyareallbasedontherelationsR(A,B)andS(B,C).
A)
B)
C)
D)
4.AssumeRandSareeachrelationswithattributesaandbonly.
Q1:
Q2:
Whichofthefollowingiscorrect?
第1页
A)Q1andQ2producethesameanswer.
B)TheanswertoQ1isalwayscontainedintheanswertoQ2.
C)TheanswertoQ2isalwayscontainedintheanswertoQ1.
D)Q1andQ2producecompletelydifferentanswers.
5.SupposetherelationR(A,B,C)hasthefollowingfourtuples:
A
B
C
3
1
2
4
1
2
3
1
4
4
1
4
WhichofthefollowingfunctionaldependenciescanyouinferdoesnotholdinR?
A)A->BB)BC->AC)C->BD)AB->A
ThefollowingtwoquestionsrefertoarelationR(A,B,C,D,E)withfunctionaldependenciesABC->DE,andD->AB.
6.WhichofthefollowingisthehighestnormalformofR?
A)1NFB)2NFC)3NFD)BCNF
7.ThenumberofsuperkeysofRis:
A)2B)7C)10D)12
8.Inthefollowing,assumeaisanattributeofsomecharacter-stringtype,e.g.CHAR(10),andthatitmaybeNULL.
Q1:
SELECT*FROMRWHEREaISNULL;
Q2:
SELECT*FROMRWHEREaNOTLIKE'%';
A) Q1andQ2producethesameanswer.
B)TheanswertoQ1isalwayscontainedintheanswertoQ2.
C)TheanswertoQ2isalwayscontainedintheanswertoQ1.
D)Q1andQ2producedifferentanswers.
9.Inthisquestion,R(x)istheschemaofrelationR.
Q1:
SELECTxFROMRrr
WHERENOTEXISTS(SELECT*FROMRWHEREx>rr.x);
Q2:
SELECTMAX(x)FROMR;
A) Q1andQ2producethesameanswer.
B)TheanswertoQ1isalwayscontainedintheanswertoQ2.
C)TheanswertoQ2isalwayscontainedintheanswertoQ1.
D)Q1andQ2producedifferentanswers.
共5页
10.InSQL,anALTERstatement:
A)Maybeusedtoaddaview.
B)Maybeusedtoaddaconstraint.
C)Maybeusedtodropatable.
D)Maybeusedtodropaview.
11.InSQL,anUPDATEstatementwithoutaWHEREclause:
A)ResultsinaCartesianproduct.
B)Updatesnorowsinatable.
C)Updateseverycolumninatable.
D)Updateseveryrowinatable.
12.Considerrelation'G'andthequerygivenbelow:
student
grade
1
A
2
NULL
3
B
4
A
SELECTgrade,COUNT(*)FROMG
GROUPBYgrade;
Howmanytuplesarereturned?
A)1B)2C)3D)4
13.Inthe3-valuedlogic,thevalueofexpression
R.a>=R.bORR.a<=0ORR.b>=0
canbe:
A)OnlyTRUEorFALSE
B)OnlyFALSEorUNKNOWN
C)OnlyTRUEorUNKNOWN
D)AnyofTRUE,FALSE,orUNKNOWN
14.Uniqueconstraints:
A)RequirecolumnsthathaveNOTNULLconstraints
B)Mayonlybedefinedoncepertable
C)Maybeviolatedbyainsertion
D)Areidenticaltoprimarykeyconstraints
第2页
15.Supposewehavethefollowingtabledeclarations:
CREATETABLEA(wINTPRIMARYKEY);
CREATETABLEB(xINTPRIMARYKEY
REFERENCESA(w)ONDELETESETNULL);
CREATETABLEC(yINTREFERENCESA(w));
CREATETABLED(z1INTREFERENCESB(x)ONDELETESETNULL,
z2INTREFERENCESA(w)ONUPDATECASCADE);
Considerthefollowingoperations:
I.DELETEFROMC;DELETEFROMB;DELETEFROMA;DELETEFROMD;
II.DELETEFROMC;DELETEFROMD;DELETEFROMA;DELETEFROMB;
III.DELETEFROMB;DELETEFROMC;DELETEFROMD;DELETEFROMA;
Whichoftheaboveoperationswillemptyallfourtableswithouterror?
A)IIIonlyB)Ionly
C)IIandIIIonlyD)IandIIIonly
Thefollowingtwoquestionsconcerntherelations:
Emps(id,name,dept,salary)
Managers(dept,mgr)
ThefirstgivestheemployeeID,theirname,department,andsalary;thesecondgivesforeachdepartment,themanagerofthatdepartment,whichistheemployeeIDofthepersonmanagingthedepartment.
16.WewishtoconstraintherelationssothatinthemgrattributeofaManagerstupletheremustappeartheIDofanemployeeinEmps.Whichofthefollowingchanges,byitself,enforcesthatconstraint?
A)InthedeclarationofManagers,addforattributemgrtheattribute-basedcheckCHECK(EXISTS(SELECT*FROMEmpsWHEREid=mgr)).
B)InthedeclarationofEmps,addtheconstraintFOREIGNKEYidREFERENCESManagers(mgr).
C)InthedeclarationofManagers,addtheconstraintFOREIGNKEYmgrREFERENCESemps(id).
D)Morethanoneoftheabove.
17.Supposewewishtoconstrainthedatasothatinnodepartmentcantheemployeeshaveatotalsalarygreaterthan$1,000,000.Thefollowingisaframeworkforanassertionthatwillenforcethisconstraint:
CREATEASSERTIONcheapCHECK(NOTEXISTS(Q));
WhichqueryQbestenforcesthisconstraint?
共5页
A)SELECT*FROMEmpsWHERESUM(salary)>1000000
B)SELECTdept,SUM(salary)FROMEmpsGROUPBYdept
C)SELECTSUM(salary)FROMEmps,ManagersWHEREid=mgr
GROUPBYEmps.deptHAVINGSUM(salary)>1000000
D)SELECTdeptFROMEmps
GROUPBYdeptHAVINGSUM(salary)>1000000
18.Aisaunary(one-column)relationdeclaredby
CREATETABLEA(iINT);
Belowisantrigger:
CREATETRIGGERMystery
AFTERINSERTORUPDATEONA
REFERENCINGOLDASOldRow,NEWASNewRow
FOREACHROW
WHEN(10>(SELECTMAX(i)FROMA))
UPDATEASETi=i+1;
WithAempty,weexecutethestatement:
INSERTINTOAVALUES
(1);
Theresultofthisstatementis:
A)Aisleftwithasingletuplewithvalue10
B)Aisleftwithasingletuplewithvalue2
C)Aisleftwithtuples2,3,…,10
D)Thetriggerneverstopsmodifyingtuples
19.RelationR(a,b,c)currentlyhasthefollowinginstance:
{(1,2,3),(3,4,2),(2,6,1)}
Wemakethefollowingviewdefinitions:
CREATEVIEWVASSELECTa*bASd,cFROMR;
CREATEVIEWWASSELECTd,SUM(c)ASeFROMVGROUPBYd;
Whatisthesumofallthecomponentsofallthetuplesofthefollowingquery?
SELECTAVG(d),eFROMWGROUPBYe;
A)10B)17C)23D)28
20.Initially,userAistheownerofrelationR,andnootheruserholdsprivilegesonR.
Thefollowingareexecuted:
byA:
GRANTUPDATEONRTOB
byA:
GRANTUPDATE(a)ONRTOCWITHGRANTOPTION
byC:
GRANTUPDATE(a)ONRTOBWITHGRANTOPTION
byA:
REVOKEUPDATE(a)ONRFROMCCASCADE
第3页
WhichofthefollowingbestdescribesthestatusofB'sprivilegesonR?
(A)BcanupdateanyattributeofRexcepta,butcannotgrantthatprivilege.
(B)BhasnoprivilegesonRandcannotgrantany.
(C)BcanupdateanyattributeofRexcepta,butcangrantotherstheprivilegetoupdateR.a.
(D)BcanperformanyupdateonRbutcannotgrantthatprivilege.
Question2(22points)
1.(5points)Considerthefollowingtwotables:
T1:
A
D
E
T2:
A
B
C
10
a
5
10
b
6
15
b
8
25
c
3
25
a
6
10
b
15
ShowtheresultsofthefollowingalgebraicorSQLexpressions:
a)
(1points)
b)SELECT*FROMT1NATRUALLEFTOUTERJOINT2ONE c)SELECT*FROMT1NATRUALFULLOUTERJOINT2WHEREE 2.(7points)SupposewearebuildingaWebcrawler(网络搜索器)toindexpages.Weneedtokeeptrackofthefollowingattributes: R(docID,docURL,docTitle,docDate,linkedDocID,linkText,wordID,wordText) whereeachdocumenthasan(integer)ID,URL,title,anddate;documentslinktootherdocumentswithaparticulartextlabelontheirlink;anddocumentscontainwords(eachofwhichhasawordID)andtext(whichispotentiallydifferentforeachwordID). YouaregiventhefollowingFDs: docID->docURL,docTitle,docDate docID,linkedDocID->linkText wordID->wordText a)SpecifyallkeysofR.(1points) b)IfweprojecttheseFD’sontorelationS(docID,linkedDocID,linkText,wordID,wordText),giveaminimalbasisfortheFD’sthatholdinS.(2points) c)GivethehighestnormalformofRandS,respectively.(2points) d)Usethe3NFsynthesisalgorithmtofindalossless-join,dependency-preservingdecompositionofRinto3NFrelations.(2points) 共5页 3.(5points)Thefollowingsetofrequirementsareforauniversitydatabasethatisusedtokeeptrackofstudent'stranscripts. I)Theuniversitykeepstrackofeachstudent'sname,student'snumber,socialsecuritynumber,currentaddress,phonenumber,permanentaddress,birthdate,sex,class,degree.Bothsocialsecuritynumberandstudentnumberhaveuniquevaluesforeachstudent. II)Eachdepartmentisdescribedbyaname,departmentcode,officenumber,officephoneandcollege.Bothnameandcodehaveuniquevaluesforeachdepartment. III)Eachcoursehasacoursename,description,coursenumber,numberofsemesterhoursandofferingdepartment.Thevalueofcoursenumberisunique. IV)Eachsectionhasaninstructor,semester,year,courseandsectionnumber.Thesectionnumberdistinguishessectionsofthesamecoursethataretaughtduringthesamesemester/year;itsvaluesare1,2,3...uptothenumberofsectionstaughtduringeachsemester. V)Agradereporthasastudent,asection,andagrade. DesignanE/Rdiagramforaboverequirements,andspecifythekeys. 4.(5points)ConvertthefollowingE/Rdiagramtoarelationaldatabaseschema,indicatingtheprimarykeysfortherelations. 第4页 Question3(28points) Thefollowingfivetablesareforacompanymanagementsystem: EMPLOYEE(SSN,Fname,Lname,Bdate,Address,Sex,Salary,Dnumber) DEPARTMENT(Dnumber,Dname,MgrSSN)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 试题 参考
![提示](https://static.bdocx.com/images/bang_tan.gif)