数据库复习.docx
- 文档编号:5506443
- 上传时间:2022-12-17
- 格式:DOCX
- 页数:20
- 大小:162.50KB
数据库复习.docx
《数据库复习.docx》由会员分享,可在线阅读,更多相关《数据库复习.docx(20页珍藏版)》请在冰豆网上搜索。
数据库复习
数据库理论与技术
(2005-06)
课程复习题
一、选择填空:
1.Recordsinafilemaybevariablelengthbecause(d)
a.thesizeofadatafieldwithintherecordmayvary
b.afieldwithintherecordmayberepeatedavariablenumberoftimes
c.afieldmayappearinonlysomeoftherecords
d.alloftheabove
e.NOA
2.Variablelengthfieldsmeanrecordsarevariablelength.Thishastheconsequencethat(c)-----a
a)thebufferpoolmanagermustsupportvariablelengthframes.
b)slot#’scannotbedeterminedasfixedaddressesonthepage,soaslotdirectoryoneachpageisnecessary.
c)differentrecordsfromthesametablecanhavedifferentnumbersoffields.
d)thedifferentfieldsofthesamerecordmustbekeptondifferentpages.
e)B+-treeindexesarenotpossiblefortheserecordsbecausetheorderoftheB+-treecannotbedetermined.
3.Thedifferencebetweenfilesstoringspannedversusunspannedrecordsisthat(e)---a
a)afilewithspannedrecordswilluselessdiskspaceforstoringrecordsthanwithunspannedrecords,ifanintegralnumberofrecordsdonotfitinablock
b)afilewithspannedrecordscanhaverecordsthatarestoredonmorethanonediskblock
c)afilewithspannedrecordsmustbeusedwhenthesizeofarecordislargerthantheblocksize
d)alloftheabove
e)NOA
4.Anindexisusedinrelationaldatabasesystemsto(b)
a)improvetheefficiencyofnormalizingrelations
b)improvetheefficiencyofretrievingrecordsfromarelation
c)improvetheefficiencyoftheCreateTablestatement
d)bandconly
e)NOA
5.Indexingtechniquesshouldbeevaluatedonthebasisof:
(e)----abc
a)Accesstime
b)Insertionanddeletiontime
c)Spaceoverhead
d)aandbonly
e)Alloftheabove
6.Whichofthefollowingwouldnotbeconsideredabenefitofindexing?
(c)---b
a)Toimproveperformanceduringdatasorting.
b)Toinsureuniquenessofkeyvalues.
c)Toavoidreadingtherecordswhenprocessingqueriesthatretrieveonlyindexedcolumns
d)Toimproveperformanceduringlargesequentialtablescans.
e)Tohelpqueryoptimizerincostestimation
7.Thedifferencebetweenadenseindexandasparseindexisthat(c)---e
a)adenseindexcontainskeysandpointersforasubsetoftherecordswhereasasparseindexcontainskeysandpointersforeveryrecord.
b)adenseindexcanonlybeaprimaryindexwhereasasparseindexcanonlybeasecondaryindex.
c)adenseindexcontainskeysandpointersforeachrecordwhereasasparseindexcontainskeysandpointersforasubsetoftherecords.
d)thesizeofdenseindexisalwayssmallerthanthesizeofsparseindex.
e)NOA
8.ForaB+-treeofn=10,consistingof3levels,themaximumnumberofleafnodeswouldbe(c)-----b
a)121
b)100
c)1000
d)36
e)10011
9.Considerthisrelation:
Car(VIN,Year,Model,Price)Thecarrelationcontainsatotalof10,000records.Thedataincludesthevehicleidentificationnumbers,year,model,andbasepricefor50differentmodelsovera40yearperiodfrom1960-1999(inclusive).Eachblockofthefilecontains20records.Therecordsinthefileareorderedsequentiallyaccordingtomodel.Itwouldbepossible(withoutreorganizingthefile)tocreateaclusteredindexonattribute:
(c)
a)Vin
b)Year
c)Model
d)Price
e)Alloftheabove
10.Asecondaryindex:
(b)
a)MustuseatreestructuresuchasaB+-treeorB-tree.
b)Mustbeadenseindex.
c)Cannotbecreatedontheprimarykeyofarelation.
d)Alloftheabove.
e)Noneoftheabove.
11.Considerthetable:
rented(cust_no,vid_no,start_date,return_date).
Supposeyouhavecreatedanindexwiththecommand:
createindexindx1onrented(start_date,returned_date)
Strat_dateisthemainorderingcriterionfortheindexentries;returned_datebecomesonlyimportantiftwoentrieshavethesamestart_date.Whichofthefollowingconditionscanbeevaluatedusingtheindex?
(c)----e
a.start_datebetween'15-10-99'and'20-10-99'
b.returned_date>='20-10-99'
c.start_date='15-10-99'andreturned_date>'16-10-99'
d.start_date='15-10-99'orreturned_date>'16-10-99'
e.aandc
12.WhensearchingaB+-treeforarangeofkeyvalues(d)
a)thesearchalwaysstartsattherootnode
b)thesearchalwaysendsataleafnode
c)multipleleafnodesmaybeaccessed
d)alloftheabove
e)aandbonly
13.TheinsertionofarecordinaB+-treewillalwayscausetheheightofthetreetoincreasebyonewhen(c)
a)thetreeconsistsofonlyarootnode
b)therecordistobeinsertedintoafullleafnode
c)allthenodesinthepathfromtheroottothedesiredleafnodearefullbeforeinsertion
d)allthenodesintheB+-treearehalffull
e)NOA
二、简答题
1.设有下列嵌套关系模式:
Emp=(ename,ChildernSetsetof(Childern),SkillsSetsetof(Skills))
Childern=(name,birthday)
Birthday=(day,month,year)
Skills=(stype,ExamsSetsetoff(Exams))
Exams=(year,city)
假定数据库中包含表emp(Emp)。
试用SQL:
1999写出下列查询:
1)列出所有有一个孩子的生日在三月的员工的姓名;
2)列出所有在城市”Xi’an”参加过技能种类为”typing”的考试的员工的姓名;
3)列出关系emp中的所有技能种类;
解:
a――-selectenamefromEmpase,e.ChildrenSetasc
where‘March’in(selectbirthday.monthfromc)
b―――selecte.namefromEmpase,e.SkillsSetass,s.ExamsSetasx
wheres.stype=’typing’andx.city=’Xi’an’
c―――selectdistincts.stypefromEmpase,e.SkillsSetass
2.试给出第2题中的嵌套关系模式的XMLDTD表示,并用Xquery重写第2题中的所有查询。
解:
DOCTYPEEmp[
ELEMENTEmp(ename,Childern*,Skills*)>
ELEMENTChildern(name,birthday)>
ELEMENTbirthday(day,month,year)>
ELEMENTSkills(stype,Exams+)>
ELEMENTExams(year,city)>
ELEMENTename(#PCDATA)>
ELEMENTname(#PCDATA)>
ELEMENTday(#PCDATA)>
ELEMENTmonth(#PCDATA)>
ELEMENTyear(#PCDATA)>
ELEMENTstype(#PCDATA)>
ELEMENTcity(#PCDATA>
]>
(1)for$ain/db/emp[children/birthday/month=3]
return$a/ename
(2)for$bin/db/emp[skills/stype="typing"andskills/exams/city="Xi'an"]
return$b/ename
(3)for$cindistinct-values(//skills/stype)
return
3.给定如图3所示的银行数据库中部分基表的SQL数据定义,试写一个SQL触发器执行下列动作:
在对帐户(account)进行delete操作时,对帐户的每一个拥有者(customer),检查其是否还拥有其他帐户,如果没有,则将该拥有者从customer中删除。
图3银行数据库中部分基表的SQL数据定义
解:
Createtriggercheck-delete-triggerafterdeleteonaccount
Referencingoldrowasorow
Foreachrow
Deletefromcustomer
Wherecustomer.customer-namenotin
(selectcustomer-namefromdepositor
whereaccount-number<>orow.account-number)
end
4.设有如下所示的BankDTD:
DOCTYPEbank[
ELEMENTbank((account|customer|depositor)+)>
ELEMENTaccount(acct-no,branch-name,balance)>
ELEMENTcustomer(cust-no,cust-name,cust-street,cust-city)>
ELEMENTdepositor(acct-no,cust-no)>
ELEMENTaccount-number(#PCDATA)>
…similar#PCDATAdeclarationforbranch-name,balance,
cust-name,cust-street,cust-city
]>
试将上述BankDTD改写为具有ID和IDREF属性类型的DTD。
解:
DOCTYPEbank[
ELEMENTbank((account|customer|depositor)+)>
ELEMENTaccount(branch-name,balance)>
ATTLISTaccount
acct-noID#REQUIRD>
ELEMENTcustomer(cust-name,cust-street,cust-city)>
ATTLISTcustomer
cust-noID#REQUIRED>
ELEMENTdepositor>
ATTLISTdepositor
acct-noIDREF#REQUIRED
cust-noIDREF#REQUIRED>
ELEMENTbranch-name(#PCDATA)>
……similar#PCDATAdeclarationforbalance,cust-name,cust-street,cust-city
]>
5.a)IsthefollowingXMLfilewell-formed?
Ifyes,why,andifno,showthewrongpartsandmodifytheXMLfilesothatitbecomeswell-formed.
b)IstheXMLfilevalid?
Ifyes,why,andifno,showthewrongpartsandmodifytheXMLfilesothatitisvalid.
car.dtd:
ELEMENTCAR(OwnerName+,Year,Make,OwnerAddress+)>
ATTLISTCARCategory(Sport|SUV|Sedan)#REQUIERD>
ELEMENTOwnerName(#PCDATA)>
ELEMENTYear(#PCDATA)>
ELEMENTMake(#PCDATA)>
ELEMENTOwnerAddress(StreetNo,StreetName,City,State,Zip)+>
ELEMENTStreetNo(#PCDATA)>
ELEMENTStreetName(#PCDATA)>
ELEMENTCity(#PCDATA)>
ELEMENTState(#PCDATA)>
ELEMENTZip(#PCDATA)>
car.xml:
解:
well-formedXML的定义:
所有元素都要正确关闭(空元素:
<元素/>)
标记之间不能交叉
所有属性指都要加引号,属性要以名值对方式出现
其它规定;Startthedocumentwithadeclaration,surroundedby
…?
>,?
XMLVERSION=“1.0”STANDALONE=“yes”?
>。
ValidXML的定义是:
如果一个文档类型声明(DTD)与一个XML文档相关联,如果该文档符合该DTD,那么该文档被认为是有效的。
a)不是well-formed。
没有以
...?
>开头
元素
修改后的XML文档为:
xmlversion=”1.0”standalone=”no”?
>
b)次xml文档不是合法的。
XML中有以下几个地方与dtd中的定义不符:
元素Car的属性Category是必需的,但XML文档中未出现该属性;
dtd中未出现标记
改正如下:
xmlversion=”1.0”standalone=”no”?
>
6.ConsiderthefollowingXMLfiles.
Parts.xml:
xmlversion="1.0"?
>
…
Suppliers.xml:
xmlversion="1.0"?
>
…
a)DisplaytheSuppliernamesforPartswithSellingPricehigherthan200Dollars.
NoticethatifaSuppliers,suppliesmorethanonepartwithpricehigherthan200,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 复习