数据库复习Word下载.docx

1、a）Access timeb）Insertion and deletion timec）Space overheadd）a and b onlye）All of the above6.Which of the following would not be considered a benefit of indexing? （c）-ba）To improve performance during data sorting.b）To insure uniqueness of key values.c）To avoid reading the records when processing quer

2、ies that retrieve only indexed columnsd）To improve performance during large sequential table scans.e）To help query optimizer in cost estimation7.The difference between a dense index and a sparse index is that （c）-ea）a dense index contains keys and pointers for a subset of the records whereas a spars

3、e index contains keys and pointers for every record.b）a dense index can only be a primary index whereas a sparse index can only be a secondary index. c）a dense index contains keys and pointers for each record whereas a sparse index contains keys and pointers for a subset of the records.d）the size of

4、 dense index is always smaller than the size of sparse index.8.For a B+-tree of n = 10, consisting of 3 levels, the maximum number of leaf nodes would be （c）-ba）121b）100c）1000d）36e）100119.Consider this relation: Car（VIN,Year, Model, Price） The car relation contains a total of 10,000 records. The dat

5、a includes the vehicle identification numbers, year, model, and base price for 50 different models over a 40 year period from 1960-1999 （inclusive）. Each block of the file contains 20 records. The records in the file are ordered sequentially according to model. It would be possible （without reorgani

6、zing the file） to create a clustered index on attribute: （c）a）Vin b）Year c）Model d）Price 10.A secondary index: （b）a）Must use a tree structure such as a B+-tree or B-tree.b）Must be a dense index.c）Cannot be created on the primary key of a relation.d）All of the above.e）None of the above.11.Consider th

7、e table: rented（cust_no, vid_no, start_date, return_date）.Suppose you have created an index with the command:create index indx1 on rented （start_date,returned_date）Strat_date is the main ordering criterion for the index entries; returned_date becomes only important if two entries have the same start

8、_date. Which of the following conditions can be evaluated using the index? （c）-ea.start_date between 15-10-99 and 20-10-99b.returned_date = c.start_date= and returned_date16-10-99d.start_date= or returned_datee.a and c12.When searching a B+-tree for a range of key values （d）a）the search always start

9、s at the root nodeb）the search always ends at a leaf nodec）multiple leaf nodes may be accessede）a and b only13.The insertion of a record in a B+-tree will always cause the height of the tree to increase by one when （c）a）the tree consists of only a root nodeb）the record is to be inserted into a full

10、leaf nodec）all the nodes in the path from the root to the desired leaf node are full before insertiond）all the nodes in the B+-tree are half full二、简答题1.设有下列嵌套关系模式： Emp = （ename, ChildernSet setof（Childern）, SkillsSet setof（Skills） Childern = （name, birthday） Birthday = （day, month, year） Skills = （s

11、type, ExamsSet setoff（Exams） Exams = （year,city）假定数据库中包含表emp（Emp）。试用SQL:1999写出下列查询：1）列出所有有一个孩子的生日在三月的员工的姓名；2）列出所有在城市”Xian”参加过技能种类为”typing”的考试的员工的姓名；3）列出关系emp中的所有技能种类；解：a select ename from Emp as e, e.ChildrenSet as c where March in （ select birthday.month from c）b select e.name from Emp as e, e.Skil

12、lsSet as s, s.ExamsSet as x where s.stype=typing and x.city=Xianc select distinct s.stype from Emp as e , e.SkillsSet as s2.试给出第2题中的嵌套关系模式的XML DTD表示，并用Xquery重写第2题中的所有查询。!DOCTYPE Emp ELEMENT Childern （name,birthday）ELEMENT birthday （day,month,year）ELEMENT Skills （stype,Exams+） ELEMENT Exams （year,cit

13、y）ELEMENT ename （#PCDATA） ELEMENT name （#PCDATA）ELEMENT day （#PCDATA）ELEMENT month （#PCDATA）ELEMENT year （#PCDATA）ELEMENT stype （#PCDATA）ELEMENT city （#PCDATA（1）for $a in /db/empchildren/birthday/month=3 return $a/ename（2） for $b in /db/empskills/stype=typing and skills/exams/city=Xi'an return

14、$b/ename（3） for $c in distinct-values（/skills/stype） return $c 3.给定如图3所示的银行数据库中部分基表的SQL数据定义，试写一个SQL触发器执行下列动作：在对帐户（account）进行delete操作时，对帐户的每一个拥有者（customer），检查其是否还拥有其他帐户，如果没有，则将该拥有者从customer中删除。图3 银行数据库中部分基表的SQL数据定义 Create trigger check-delete-trigger after delete on account Referencing old row as oro

15、w For each row Delete from customer Where customer.customer-name not in （select customer-name from depositor where account-number orow.account-number） end4.设有如下所示的Bank DTD: ELEMENT account （acct-no, branch-name, balance）ELEMENT customer（cust-no,cust-name,cust-street, cust-city） ELEMENT depositor （ac

16、ct-no, cust-no） ELEMENT account-number （#PCDATA） similar #PCDATA declaration for branch-name, balance, cust-name, cust-street ,cust-city 试将上述Bank DTD改写为具有ID和IDREF属性类型的DTD。DOCTYPE bankELEMENT bank （account | customer|depositor）+）ELEMENT account（branch-name, balance）ATTLIST account acct-no ID #REQUIRD

17、 ELEMENT customer（cust-name , cust-street, cust-city ）ATTLIST customer cust-no ID #REQUIRED ELEMENT depositor ATTLIST depositoracct-no IDREF #REQUIREDcust-no IDREF #REQUIREDELEMENT branch-name（#PCDATA） similar #PCDATA declaration for balance , cust-name , cust-street , cust-city5.a） Is the following

18、 XML file well-formed? If yes, why, and if no, show the wrong parts and modify the XML file so that it becomes well-formed.b） Is the XML file valid? If yes, why, and if no, show the wrong parts and modify the XML file so that it is valid.car.dtd:ELEMENT CAR （OwnerName+, Year, Make, OwnerAddress+）ATT

19、LIST CAR Category （Sport | SUV | Sedan） #REQUIERDELEMENT OwnerName（#PCDATA）ELEMENT Year （#PCDATA）ELEMENT Make （#PCDATA）ELEMENT OwnerAddress （StreetNo, StreetName, City, State,Zip）+ELEMENT StreetNo （#PCDATA）ELEMENT StreetName （#PCDATA）ELEMENT City （#PCDATA）ELEMENT State （#PCDATA）ELEMENT Zip （#PCDATA）

20、car.xml:CAROwnerNameGhost Year1998JunMakeToyotaOwnerAddressStreetNo2113HooverCityLos AngelesStateCAZipCode90089/CAR 解：well-formed XML的定义：所有元素都要正确关闭（空元素:元素/）标记之间不能交叉所有属性指都要加引号，属性要以名值对方式出现其它规定；Start the document with a declaration, surrounded by ? ? ，? XML VERSION = “1.0” STANDALONE = “yes” ?。 Valid X

21、ML的定义是：如果一个文档类型声明（DTD）与一个XML文档相关联，如果该文档符合该DTD，那么该文档被认为是有效的。 a）不是wellformed。没有以.?开头元素 b）次xml文档不是合法的。XML中有以下几个地方与dtd中的定义不符：元素Car的属性Category是必需的，但XML文档中未出现该属性； dtd中未出现标记Zip/Zip6.Consider the following XML files.Parts.xml:xml version=1.0PartsPartNameBarbieSerialNumber1234SellingPrice Unit=Dollar250SupplierNameToy CompanyToysRUS/PartsSuppliers.xml:SuppliersSupplierScore100FreeShippingYes/Supplier/Suppliersa） Display the Supplier names for Parts with SellingPrice higher than 200 Dollars.Notice that if a Supplier s, supplies more than one part with price higher than 200,