完整数据库复习

数据库理论与技术2005-06课程复习题

一、选择填空

1.

2.Records ina filemay bevariable lengthbecause d

4.The differencebetween filesstoring spannedversus unspanned records is that e--aa a file with spanned recordswill useless diskspace forstoring recordsthan withunspannedrecords,if anintegral number of recordsdo notfit ina blockbafilewithspannedrecords canhave recordsthat arestored onmore thanone diskblock c afile withspannedrecordsmust beused whenthe size of arecord islarger thanthe blocksized all of the abovee NOA

5.An indexis usedin relationaldatabase systemsto baimprove the efficiency ofnormalizing relationsbimprove theefficiency ofretrieving recordsfrom arelationc improvetheefficiencyof theCreate Tablestatementd band conlye NOA-一

6.Indexing techniquesshould beevaluated onthe basisof:e-abca Accesstimeb Insertionand deletiontimec Spaceoverheadd a and b onlye Allof theabove

7.Which of the followingwould notbe considereda benefitof indexingc-ba To improve performance during datasorting.b Toinsure uniquenessof keyvalues.c Toavoid readingthe recordswhen processingqueries thatretrieve onlyindexed columnsdToimproveperformanceduringlarge sequentialtable scans.e Tohelp queryoptimizer incost estimation

8.The differencebetween adense indexand a sparse indexisthatcea adense index contains keys and pointers for asubset of the recordswhereas asparse indexcontains keysand pointersforevery record.b.dens.inde.ca.onl.b..primar.inde.wherea..spars.inde.ca.onl.b..secondar.index.cadense indexcontainskeysandpointersfor eachrecord whereasasparseindexcontainskeysand pointersfor asubset of the records.d the sizeofdense indexis alwayssmaller thanthesizeof sparseindex.e NOA

9.For a B+-tree ofn=10,consisting of3levels,the maximumnumberofleaf nodeswould be…一c ba121b100c1000d36e10011f Conside.thi.relation.CarVIN,Year.Model.Price.Th.ca.relatio.contain..tota.o.lO,OO.records.Th.dat.include.th.vehicl.identificatio.numbers.year.model.an.bas.pric.fo.

5.differen.model.ove..

4.yea.perio.fro.1960-

199.inclusive.Eac.bloc.o.th.fil.contain.

2.records.Th.record.i.th.fil.ar.ordere.sequentiall.accordin.t.model.I.woul.b.possibl.le.t.creat..clustere.inde.o.attribute...cg VinhYeari ModeljPricek Alloftheabove

10.A secondaryindex:ba Mustuse atree structuresuch asa B+-tree orB-tree.b Mustbe adense index.c Cannotbe createdontheprimary keyof arelation.d Alloftheabove.e Noneoftheabove.

12.When searchinga B+-tree fora rangeof keyvalues dathe searchalways startsat the root nodebthe searchalways endsat aleaf nodecmultiple leafnodes maybe accesseddalloftheaboveeaandbonly

13.The insertionof arecord inaB+-tree willalways causethe heightofthe tree toincrease byonewhen cathetreeconsists ofonly aroot nodebthe recordis tobe insertedinto afull leafnodec allthe nodesin thepath fromtherootto thedesired leafnode arefull beforeinsertiond allthe nodesin theB+-tree arehalf fulleNOA

二、简答题设有下列嵌套关系模式Emp=ename,ChildernSet setofChildern,SkillsSet setofSkillsChildern=name,birthdayBirthday=day,month,yearSkills=stype,ExamsSet setoffExamsExams=year,city假定数据库中包含表试用写出下列查询1empEmp SQL:1999列出所有有一个孩子的生日在三月的员工的姓名；2列出所有在城市”参加过技能种类为“的考试的员工的姓名；3Xian”typing”列出关系中的所有技能种类；4emp解:a---------select ename from Emp as e,e.ChildrenSet ascwhere Marchinselect birthday.month fromcb---------select e.name fromEmp ase,e.SkillsSet ass,s.ExamsSet asxwhere s.stype=,typing,and x.city=Xianc---------select distincts.stype fromEmpase,e.SkillsSet ass试给出第题中的嵌套关系模式的表示，并用重写第题中的所有查询2XML DTDXquery2解:!DOCTYPE Emp[!ELEMENT Empename,Childern*,Skills*!ELEMENT Childernname,birthday!ELEMENT birthdayday,month,year!ELEMENT Skillsstype,Exams+!ELEMENT Examsyear,city!ELEMENT ename#PCDATA!ELEMENT name#PCDATA!ELEMENT day#PCDATA!ELEMENT month#PCDATA!ELEMENT year#PCDATA〉!ELEMENT stype#PCDATA!ELEMENT city#PCDATA]lfor$a in/db/emp[children/birthday/month=3]return$a/ename2for$b in/db/emp[skills/stype=ntypingn andskills/exams/city=nXiapos;an]return$b/ename3for$c indistinct-values//skills/stypereturn stype{$c}/stype给定如图所示的银行数据库中部分基表的数据定义，试写一个触发器执行下列动3SQL SQL作在对帐户进行操作时，对帐户的每一个拥有者检查其是否还拥有account deletecustomer,其他帐户，如果没有，则将该拥有者从中删除customercreate tablecustomercustomer-name char2O,ctis tomer-s treetcRar3O,customer-city cHar3Ozprimary keycnstomer-namecreate tablebranch{branch-name branch-city char15,assets char3O,integer.primary keybra nch-na me,check assets=Ocreate tableaccount々cco unt-nurn bercharlO,branch-name balancectiar15,primary keyinteger.^account-number,check{balance=Ocreate tabledepositorcustomer-name char20,accou nt-n ubriber cHarlO,primary Iceycustomer-namer account-number^图银行数据库中部分基表的数据定义3SQL解Create triggercheck-delete-trigger afterdelete onaccount Referencingold rowas orowForeach rowDeletefrom customerWherecustomer.customer-name notinselect customer-namefromdepositorwhere account-numberorow.account-numberend设有如下所示的

1.Bank DTD:!DOCTYPE bank[!ELEMENT bankaccount|customer|depositor+!ELEMENT accountacct-no,branch-name,balance!ELEMENT customercust-no,cust-name,cust-street,cust-city。