数据库复习Word下载.docx
《数据库复习Word下载.docx》由会员分享,可在线阅读,更多相关《数据库复习Word下载.docx(20页珍藏版)》请在冰豆网上搜索。
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.
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
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>
='
c.start_date='
andreturned_date>
'
16-10-99'
d.start_date='
orreturned_date>
e.aandc
12.WhensearchingaB+-treeforarangeofkeyvalues(d)
a)thesearchalwaysstartsattherootnode
b)thesearchalwaysendsataleafnode
c)multipleleafnodesmaybeaccessed
e)aandbonly
13.TheinsertionofarecordinaB+-treewillalwayscausetheheightofthetreetoincreasebyonewhen(c)
a)thetreeconsistsofonlyarootnode
b)therecordistobeinsertedintoafullleafnode
c)allthenodesinthepathfromtheroottothedesiredleafnodearefullbeforeinsertion
d)allthenodesintheB+-treearehalffull
二、简答题
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&
apos;
an"
]
return$b/ename
(3)for$cindistinct-values(//skills/stype)
return<
stype>
{$c}<
/stype>
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:
CAR>
OwnerName>
Ghost<
/OwnerName>
Year>
1998Jun<
/Year>
Make>
Toyota<
/Make>
OwnerAddress>
StreetNo>
2113<
StreetName>
Hoover<
/StreetName>
City>
LosAngeles<
/City>
State>
CA<
/State>
ZipCode>
90089<
/ZipCode>
/CAR>
解:
well-formedXML的定义:
所有元素都要正确关闭(空元素:
元素/>
)
标记之间不能交叉
所有属性指都要加引号,属性要以名值对方式出现
其它规定;
Startthedocumentwithadeclaration,surroundedby<
?
…?
,?
XMLVERSION=“1.0”STANDALONE=“yes”?
。
ValidXML的定义是:
如果一个文档类型声明(DTD)与一个XML文档相关联,如果该文档符合该DTD,那么该文档被认为是有效的。
a)不是well-formed。
没有以<
...?
开头
元素<
没有闭合。
修改后的XML文档为:
xmlversion=”1.0”standalone=”no”?
/OwnerAddress>
b)次xml文档不是合法的。
XML中有以下几个地方与dtd中的定义不符:
元素Car的属性Category是必需的,但XML文档中未出现该属性;
dtd中未出现标记<
而在XML文档中却出现该标记。
改正如下:
CARCategory=”SUV”>
Zip>
/Zip>
6.ConsiderthefollowingXMLfiles.
Parts.xml:
xmlversion="
1.0"
Parts>
Part>
Name>
Barbie<
/Name>
SerialNumber>
1234<
/SerialNumber>
SellingPriceUnit="
Dollar"
250<
/SellingPrice>
SupplierName>
ToyCompany<
/SupplierName>
ToysRUS<
/Part>
…
/Parts>
Suppliers.xml:
Suppliers>
Supplier>
Score>
100<
/Score>
FreeShipping>
Yes<
/FreeShipping>
/Supplier>
/Suppliers>
a)DisplaytheSuppliernamesforPartswithSellingPricehigherthan200Dollars.
NoticethatifaSuppliers,suppliesmorethanonepartwithpricehigherthan200,