数据库系统概念(database system concepts)英文第六版课后练习题答案第23章

合集下载

数据库系统概念(database system concepts)英文第六版课后练习题答案第22章

C H A P T E R22Object-Based DatabasesPractice Exercises22.1A car-rental company maintains a database for all vehicles in its cur-rentﬂeet.For all vehicles,it includes the vehicle identiﬁcation number,license number,manufacturer,model,date of purchase,and color.Spe-cial data are included for certain types of vehicles:•Trucks:cargo capacity.•Sports cars:horsepower,renter age requirement.•Vans:number of passengers.•Off-road vehicles:ground clearance,drivetrain(four-or two-wheel drive).Construct an SQL schema deﬁnition for this e inheritancewhere appropriate.Answer:For this problem,we use table inheritance.We assume thatMyDate,Color and DriveTrainType are pre-deﬁned types.create type Vehicle(vehicle id integer,license number char(15),manufacturer char(30),model char(30),purchase date MyDate,color Color)create table vehicle of type Vehiclecreate table truck(cargo capacity integer)under vehiclecreate table sportsCar12Chapter22Object-Based Databases(horsepower integerrenter age requirement integer)under vehiclecreate table van(num passengers integer)under vehiclecreate table offRoadVehicle(ground clearance realdriveTrain DriveTrainType)under vehicle22.2Consider a database schema with a relation Emp whose attributes areas shown below,with types speciﬁed for multivalued attributes.Emp=(ename,ChildrenSet multiset(Children),SkillSet multiset(Skills))Children=(name,birthday)Skills=(type,ExamSet setof(Exams))Exams=(year,city)a.Deﬁne the above schema in SQL,with appropriate types for eachattribute.ing the above schema,write the following queries in SQL.i.Find the names of all employees who have a child born on orafter January1,2000.ii.Find those employees who took an examination for the skilltype“typing”in the city“Dayton”.iii.List all skill types in the relation Emp.Answer:a.No Answer.b.Queries in SQL.i.Program:select enamefrom emp as e,e.ChildrenSet as cwhere’March’in(select birthday.monthfrom c)ii.Program:Practice Exercises3select e.enamefrom emp as e,e.SkillSet as s,s.ExamSet as xwhere s.type=’typing’and x.city=’Dayton’iii.Program:select distinct s.typefrom emp as e,e.SkillSet as s22.3Consider the E-R diagram in Figure22.5,which contains composite,multivalued,and derived attributes.a.Give an SQL schema deﬁnition corresponding to the E-R diagram.b.Give constructors for each of the structured types deﬁned above.Answer:a.The corresponding SQL:1999schema deﬁnition is given below.Note that the derived attribute age has been translated into amethod.create type Name(ﬁrst name varchar(15),middle initial char,last name varchar(15))create type Street(street name varchar(15),street number varchar(4),apartment number varchar(7))create type Address(street Street,city varchar(15),state varchar(15),zip code char(6))create table customer(name Name,customer id varchar(10),address Adress,phones char(7)array[10],dob date)method integer age()b.create function Name(f varchar(15),m char,l varchar(15))returns Namebeginsetﬁrst name=f;set middle initial=m;set last name=l;endcreate function Street(sname varchar(15),sno varchar(4),ano varchar(7))4Chapter22Object-Based Databasesreturns Streetbeginset street name=sname;set street number=sno;set apartment number=ano;endcreate function Address(s Street,c varchar(15),sta varchar(15),zip varchar(6))returns Addressbeginset street=s;set city=c;set state=sta;set zip code=zip;end22.4Consider the relational schema shown in Figure22.6.a.Give a schema deﬁnition in SQL corresponding to the relationalschema,but using references to express foreign-key relationships.b.Write each of the queries given in Exercise6.13on the aboveschema,using SQL.Answer:a.The schema deﬁnition is given below.Note that backward ref-erences can be addedbut they are not so important as in OODBSbecause queries can be written in SQL and joins can take care ofintegrity constraints.create type Employee(person name varchar(30),street varchar(15),city varchar(15))create type Company(company name varchar(15),(city varchar(15))create table employee of Employeecreate table company of Companycreate type Works(person ref(Employee)scope employee,comp ref(Company)scope company,salary int)create table works of Workscreate type Manages(person ref(Employee)scope employee,(manager ref(Employee)scope employee)create table manages of Managesb.i.select comp−>namePractice Exercises5from worksgroup by comphaving count(person)≥all(select count(person)from worksgroup by comp)ii.select comp−>namefrom worksgroup by comphaving sum(salary)≤all(select sum(salary)from worksgroup by comp)iii.select comp−>namefrom worksgroup by comphaving avg(salary)>(select avg(salary)from workswhere comp−>company name="First Bank Corporation")22.5Suppose that you have been hired as a consultant to choose a databasesystem for your client’s application.For each of the following appli-cations,state what type of database system(relational,persistent pro-gramming language–based OODB,object relational;do not specify acommercial product)you would recommend.Justify your recommen-dation.a.A computer-aided design system for a manufacturer of airplanes.b.A system to track contributions made to candidates for publicofﬁce.c.An information system to support the making of movies.Answer:a.A computer-aided design system for a manufacturer of airplanes:An OODB system would be suitable for this.That is because CADrequires complex data types,and being computation oriented,CAD tools are typically used in a programming language envi-ronment needing to access the database.b.A system to track contributions made to candidates for publicofﬁce:A relational system would be apt for this,as data types are ex-pected to be simple,and a powerful querying mechanism is es-sential.c.An information system to support the making of movies:Here there will be extensive use of multimedia and other complexdata types.But queries are probably simple,and thus an objectrelational system is suitable.6Chapter22Object-Based Databases22.6How does the concept of an object in the object-oriented model differfrom the concept of an entity in the entity-relationship model?Answer:An entity is simply a collection of variables or data items.An object is an encapsulation of data as well as the methods(code)tooperate on the data.The data members of an object are directly visibleonly to its methods.The outside world can gain access to the object’sdata only by passing pre-deﬁned messages to it,and these messagesare implemented by the methods.。

数据库系统概念英文精编版第六版教学设计

数据库系统概念英文精编版第六版教学设计介绍在本教学设计中，我们将介绍《Database System Concepts》（数据库系统概念）英文精编版第六版，旨在帮助学生掌握数据库系统的关键概念、基本结构以及数据库的设计和实现。

此教学设计将涵盖以下主题：•数据库基础知识•数据模型和ER图•SQL语言•数据库设计•事务管理和并发控制•数据库安全本教学设计旨在为初学者提供一个实践性的课程，旨在帮助学生掌握现代数据库系统的基础知识，并增强他们在实际生活和职业中利用数据库系统进行数据管理的能力。

教学内容数据库基础知识本节将首先介绍数据库系统的一些基础概念，如数据、数据库、数据库管理系统和关系数据库等。

学习者将学会如何使用SQL语句来完成基本的数据检索和修改。

数据模型和ER图本章将介绍抽象概念对于数据库设计的重要性。

学生们将学会如何使用实体关系（ER）图来表达数据库中各个实体之间的关系，从而方便设计和管理数据库。

SQL语言SQL是处理和查询关系数据的标准语言。

在本节中，我们将介绍SQL语言的不同方面，如数据定义、数据操作和嵌套查询等。

数据库设计数据库设计是一项关键性的工作，我们需要通过设计来确保数据库可以支持我们所需要的数据存储和管理。

本节将介绍关于设计和开发数据库的一些基本技术，如关系模式、规范化和冗余等。

事务管理和并发控制并发控制是数据库系统设计中最具挑战性的问题之一。

在本节中，我们将介绍什么是事务，事务管理和并发控制如何确保数据库在多个用户同时访问时仍然保持正确性。

数据库安全数据安全是建立在数据库的完整性和可用性之上的。

在本节中，我们将介绍一些关于数据库安全的基本概念，如授权和认证、数据加密等。

教学方法本教学设计采用电子教学的方式，其中，我们将提供录制的视频课程、示例问题、以及针对每个主题的在线测验。

本课程将使用不同的教学方法：•通过开放式问题和讨论区域，用以解释不同的概念。

•提供示例代码来增强实践性学习。

数据库系统概念(database system concepts)英文第六版 PPT 第四章

Database System Concepts - 6th Edition
4.12
©Silberschatz, Korth and Sudarshan
View Definition
A view is defined using the create view statement which has
logical model (that is, all the actual relations stored in the database.)
Consider a person who needs to know an instructors name
and department, but not the salary. This person should see a relation described, in SQL, by select ID, name, dept_name from instructor
©Silberschatz, Korth and Sudarshan
Joined Relations
Join operations take two relations and return as a result
another relation.
A join operation is a Cartesian product which requires that
Join type – defines how tuples in each relation that do not
match any tuple in the other relation (based on the join condition) are treated.

数据库系统概念(database system concepts)英文第六版课后练习题答案第7章

7.2 Answer: Note: the name of the relationship "course offering" needs to be changed to "section".
a. The E-R diagram is shown in Figure 7.2. Note that an alterantive is to model examinations as weak entities related to a section, rather than as a strong entity. The marks relationship would then be a binary relationship between student and exam, without directly involving section.
7.7 Answer: The primary key of a weak entity set can be inferred from its relationship with the strong entity set. If we add primary key attributes to the weak entity set, they will be present in both the entity set and the relationship set and they have to be the same. Hence there will be redundancy.
b. As indicated in the answer to the previous part, a path in the graph between a pair of entity sets indicates a (possibly indirect) relationship between the two entity sets. If there is a cycle in the graph then every pair of entity sets on the cycle are related to each other in at least two distinct ways. If the E-R diagram is acyclic then there is a unique path between every pair of entity sets and, thus, a unique relationship between every pair of entity sets.

数据库系统概念(database system concepts)英文第六版 PPT 第11章

11.8
©Silberschatz, Korth and Sudarshan
Sparse Index Files (Cont.)
Compared to dense indices:

Less space and less maintenance overhead for insertions and deletions. Generally slower than dense index for locating records.
Database System Concepts - 6th Edition
11.3
©Silberschatz, Korth and Sudarshan
Index Evaluation Metrics
Access types supported efficiently. E.g.,

records with a specified value in the attribute or records with an attribute value falling in a specified range of values.
actual records with that particular search-key value.
Secondary indices have to be dense
Database System Concepts - 6th Edition 11.10 ©Silberschatz, Korth and Sudarshan
value in the file.
E.g. index on ID attribute of instructor relation

数据库系统概念(database system concepts)英文第六版课后练习题答案第11章

2Chapter11Indexing and Hashingb.c.11.4Answer:•With structure11.3.a:Insert9:10:InsertExercises3Delete23:Delete19:•With structure11.3.b:Insert9:Insert4Chapter 11Indexing and HashingDelete23:Delete 19:•With structure 11.3.c:Insert9:Insert10:Insert8:Delete 23:Delete 19:Exercises511.5Answer:If there are K search-key values and m −1siblings are involvedin the redistribution,the expected height of the tree is:log ⌊(m −1)n /m ⌋(K )11.6Answer:Extendable hash structure000 001010 011 100101 110 11111.7Answer:a.Delete 11:From the answer to Exercise 11.6,change the third bucketto:At this stage,it is possible to coalesce the second and third buckets.Then it is enough if the bucket address table has just four entriesinstead of eight.For the purpose of this answer,we do not do the coalescing.b.Delete 31:From the answer to 11.6,change the last bucket to:6Chapter11Indexing and Hashingc.Insert1:From the answer to11.6,change theﬁrst bucket to:d.Insert15:From the answer to11.6,change the last bucket to:11.8Answer:The pseudocode is shown in Figure11.1.11.9Answer:Let i denote the number of bits of the hash value used in thehash table.Let bsize denote the maximum capacity of each bucket.Thepseudocode is shown in Figure11.2.Note that we can only merge two buckets at a time.The common hashpreﬁx of the resultant bucket will have length one less than the two bucketsmerged.Hence we look at the buddy bucket of bucket j differing from itonly at the last bit.If the common hash preﬁx of this bucket is not i j,thenthis implies that the buddy bucket has been further split and merge is notpossible.When merge is successful,further merging may be possible,which is handled by a recursive call to coalesce at the end of the function.11.10Answer:If the hash table is currently using i bits of the hash value,thenmaintain a count of buckets for which the length of common hash preﬁxis exactly i.Consider a bucket j with length of common hash preﬁx i j.If the bucketis being split,and i j is equal to i,then reset the count to1.If the bucketis being split and i j is one less that i,then increase the count by1.It thebucket if being coalesced,and i j is equal to i then decrease the count by1.If the count becomes0,then the bucket address table can be reduced insize at that point.However,note that if the bucket address table is not reduced at that point,then the count has no signiﬁcance afterwards.If we want to postpone thereduction,we have to keep an array of counts,i.e.a count for each value ofExercises7 functionﬁndIterator(value V){/*Returns an iterator for the search on the value V*/Iterator iter();Set iter.v alue=V;Set C=root nodewhile(C is not a leaf node)beginLet i=samllest number such that V<=C.K iif there is no such number i then beginLet P m=last non-null pointer in the nodeSet C=C.P m;endelse Set C=C.P i;end/*C is a leaf node*/Let i be the least value such that K i=Vif there is such a value i then beginSet iter.index=i;Set iter.page=C;Set iter.acti v e=T RUE;endelse if(V is the greater than the largest value in the leaf)then beginif(C.P n.K1=V)then beginSet iter.page=C.P n;Set iter.index=1;Set iter.acti v e=T RUE;endelse Set iter.acti v e=F AL SE;endelse Set iter.acti v e=F AL SE;return(iter)}Class Iterator{variables:value V/*The value on which the index is searched*/boolean active/*Stores the current state of the iterator(TRUE or FALSE)*/int index/*Index of the next matching entry(if active is TRUE)*/PageID page/*Page Number of the next matching entry(if active is TRUE)*/ function next(){if(active)then beginSet ret Page=page;Set retI ndex=index;if(index+1=page.size)then beginpage=page.P nindex=0endelse index=index+1;if(page.K index=V)then acti v e=F AL SE;return(ret Page,retI ndex)endelse return null;}}Figure11.1Pseudocode forﬁndIterator and the Iterator class8Chapter11Indexing and Hashingdelete(value K l)beginj=ﬁrst i high-order bits of h(K l);delete value K l from bucket j;coalesce(bucket j);endcoalesce(bucket j)begini j=bits used in bucket j;k=any bucket withﬁrst(i j−1)bits same as thatof bucket j while the bit i j is reversed;i k=bits used in bucket k;if(i j=i k)return;/*buckets cannot be merged*/if(entries in j+entries in k>bsize)return;/*buckets cannot be merged*/move entries of bucket k into bucket j;decrease the value of i j by1;make all the bucket-address-table entries,which pointed to bucket k,point to j;coalesce(bucket j);endFigure11.2Pseudocode for deletioncommon hash preﬁx.The array has to be updated in a similar fashion.Thebucket address table can be reduced if the i th entry of the array is0,wherei is the number of bits the table is using.Since bucket table reduction isan expensive operation,it is not always advisable to reduce the table.Itshould be reduced only when sufﬁcient number of entries at the end ofcount array become0.11.11Answer:We reproduce the instructor relation below.Exercises9 ID dept salary10101Comp.Sci.Wu9000015151MusicEinstein9500032343HistoryGold8700045565Comp.Sci.Caliﬁeri6200076543FinanceCrick7200083821Comp.Sci.Kim80000a.Bitmap for salary,with S1,S2,S3and S4representing the given inter-vals in the same orderS1000000000000S3010*********b.The question is a bit trivial if there is no bitmap on the deptname attribute is:Comp.Sci010********* Music000101000000 History000000000100 Elec.Eng.010********* Finance010*********10Chapter11Indexing and HashingScan on these records with salary80000or more gives Wu and Singhas the instructors who satisfy the given query.11.12Answer:If the index entries are inserted in ascending order,the newentries get directed to the last leaf node.When this leaf node getsﬁlled,it is split into two.Of the two nodes generated by the split,the left nodeis left untouched and the insertions takes place on the right node.Thismakes the occupancy of the leaf nodes to about50percent,except the lastleaf.If keys that are inserted are sorted in descending order,the above situationwould still occur,but symmetrically,with the right node of a split nevergetting touched again,and occupancy would again be50percent for allnodes other than theﬁrst leaf.11.13Answer:a.The cost to locate the page number of the required leaf page foran insertion is negligible since the non-leaf nodes are in memory.On the leaf level it takes one random disk access to read and onerandom disk access to update it along with the cost to write onepage.Insertions which lead to splitting of leaf nodes require anadditional page write.Hence to build a B+-tree with n r entries ittakes a maximum of2∗n r random disk accesses and n r+2∗(n r/f)page writes.The second part of the cost comes from the fact that inthe worst case each leaf is halfﬁlled,so the number of splits thatoccur is twice n r/f.The above formula ignores the cost of writing non-leaf nodes,sincewe assume they are in memory,but in reality they would also bewritten eventually.This cost is closely approximated by2∗(n r/f)/f,which is the number of internal nodes just above the leaf;we canadd further terms to account for higher levels of nodes,but these aremuch smaller than the number of leaves and can be ignored.b.Substituting the values in the above formula and neglecting the costfor page writes,it takes about10,000,000∗20milliseconds,or56hours,since each insertion costs20milliseconds.Exercises11c.function insert leaf(value K,pointer P)if(tree is empty)create an empty leaf node L,which is also the rootelse Find the last leaf node in the leaf nodes chain Lif(L has less than n−1key values)then insert(K,P)at theﬁrst available location in Lelse beginCreate leaf node L1Set L.P n=L1;Set K1=last value from page Linsert parent(1,L,K1,L1)insert(K,P)at theﬁrst location in L1endfunction insert parent(level l,pointer P,value K,pointer P1)if(level l is empty)then beginCreate an empty non-leaf node N,which is also the rootinsert(P,K,P1)at the starting of the node Nreturnelse beginFind the right most node N at level lif(N has less than n pointers)then insert(K,P1)at theﬁrst available location in Nelse beginCreate a new non-leaf page N1insert(P1)at the starting of the node Ninsert parent(l+1,pointer N,value K,pointer N1)endendThe insert leaf function is called for each of the value,pointerpairs in ascending order.Similar function can also be build for de-scending order.The search for the last leaf or non-leaf node at anylevel can be avoided by storing the current last page details in anarray.The last node in each level might be less than halfﬁlled.To makethis index structure meet the requirements of a B+-tree,we can re-distribute the keys of the last two pages at each level.Since the lastbut one node is always full,redistribution makes sure that both ofthen are at least halfﬁlled.11.14Answer:In a B+-tree index orﬁle organization,leaf nodes that areadjacent to each other in the tree may be located at different places ondisk.When aﬁle organization is newly created on a set of records,it ispossible to allocate blocks that are mostly contiguous on disk to leafsnodes that are contiguous in the tree.As insertions and deletions occur12Chapter11Indexing and Hashingon the tree,sequentiality is increasingly lost,and sequential access has towait for disk seeks increasingly often.a.One way to solve this problem is to rebuild the index to restoresequentiality.b.i.In the worst case each n-block unit and each node of the B+-treeis halfﬁlled.This gives the worst case occupancy as25percent.ii.No.While splitting the n-block unit theﬁrst n/2leaf pages areplaced in one n-block unit,and the remaining in the second n-block unit.That is,every n-block split maintains the order.Hence,the nodes in the n-block units are consecutive.iii.In the regular B+-tree construction,the leaf pages might not besequential and hence in the worst case,it takes one seek per leafing the block at a time method,for each n-node block,we will have at least n/2leaf nodes in it.Each n-node block canbe read using one seek.Hence the worst case seeks comes downby a factor of n/2.iv.Allowing redistribution among the nodes of the same block,doesnot require additional seeks,where as,in regular B+-tree werequire as many seeks as the number of leaf pages involvedin the redistribution.This makes redistribution for leaf blocksefﬁcient with this scheme.Also the worst case occupancy comesback to nearly50percent.(Splitting of leaf nodes is preferredwhen the participating leaf nodes are nearly full.Hence nearly50percent instead of exact50percent)。

数据库系统概念(英文精编版.第六版)

Atomicity of updates
Failures
may lead to inconsistencies (1) account_A = account_A – 100 (2) account_B = account_B + 100
Example:

Concurrent access by multiple users
Exercises
Computer users interacts with data in the _______ level A. physical B. logical C. view D. all of the above Application users interact with data in the _______ level. A. physical B. logical C. view D. all of the above How the data are actually stored is called _______ A. Physical level B. Logical level C. View level D. Conceptual level
property is called ( )
A. Data inconsistency C. Data isolation B. Data redundancy D. Data integrity
1.3 View of Data
Hierarchy of Abstraction Levels
Three Abstraction Levels of Data
机械工业出版社
本课程学习内容
关系数据模型关系数据库语言

数据库系统概念(database system concepts)英文第六版课后练习题答案第12章

C H A P T E R12Query ProcessingPractice Exercises12.1Assume(for simplicity in this exercise)that only one tupleﬁts in a blockand memory holds at most3blocks.Show the runs created on each passof the sort-merge algorithm,when applied to sort the following tuples ontheﬁrst attribute:(kangaroo,17),(wallaby,21),(emu,1),(wombat,13),(platypus,3),(lion,8),(warthog,4),(zebra,11),(meerkat,6),(hyena,9),(hornbill,2),(baboon,12).Answer:We will refer to the tuples(kangaroo,17)through(baboon,12)using tuple numbers t1through t12.We refer to the j th run used by the i thpass,as r i j.The initial sorted runs have three blocks each.They are:r11={t3,t1,t2}r12={t6,t5,t4}r13={t9,t7,t8}r14={t12,t11,t10}Each pass merges three runs.Therefore the runs after the end of theﬁrstpass are:r21={t3,t1,t6,t9,t5,t2,t7,t4,t8}r22={t12,t11,t10}At the end of the second pass,the tuples are completely sorted into onerun:r31={t12,t3,t11,t10,t1,t6,t9,t5,t2,t7,t4,t8}12.2Consider the bank database of Figure12.13,where the primary keys areunderlined,and the following SQL query:12Chapter12Query Processingselect T.branch namefrom branch T,branch Swhere T.assets>S.assets and S.branch city=“Brooklyn”Write an efﬁcient relational-algebra expression that is equivalent to thisquery.Justify your choice.Answer:Query:T.branch name(( branch name,assets(␳T(branch)))1T.assets>S.assets( assets(␴(branch city=’Brooklyn’)(␳S(branch)))))This expression performs the theta join on the smallest amount of datapossible.It does this by restricting the right hand side operand of the jointo only those branches in Brooklyn,and also eliminating the unneededattributes from both the operands.12.3Let relations r1(A,B,C)and r2(C,D,E)have the following properties:r1has20,000tuples,r2has45,000tuples,25tuples of r1ﬁt on one block,and30tuples of r2ﬁt on one block.Estimate the number of block transfers andseeks required,using each of the following join strategies for r11r2:a.Nested-loop join.b.Block nested-loop join.c.Merge join.d.Hash join.Answer:r1needs800blocks,and r2needs1500blocks.Let us assume M pagesof memory.If M>800,the join can easily be done in1500+800diskaccesses,using even plain nested-loop join.So we consider only the casewhere M≤800pages.a.Nested-loop join:Using r1as the outer relation we need20000∗1500+800=30,000,800disk accesses,if r2is the outer relation we need45000∗800+1500=36,001,500disk accesses.b.Block nested-loop join:If r1is the outer relation,we need⌈800M−1⌉∗1500+800disk accesses,if r2is the outer relation we need⌈1500M−1⌉∗800+1500disk accesses.c.Merge-join:Assuming that r1and r2are not initially sorted on the join key,the to-tal sorting cost inclusive of the output is B s=1500(2⌈log M−1(1500/M)⌉+Exercises32)+800(2⌈log M−1(800/M)⌉+2)disk accesses.Assuming all tupleswith the same value for the join attributesﬁt in memory,the totalcost is B s+1500+800disk accesses.d.Hash-join:We assume no overﬂow occurs.Since r1is smaller,we use it as thebuild relation and r2as the probe relation.If M>800/M,i.e.no needfor recursive partitioning,then the cost is3(1500+800)=6900diskaccesses,else the cost is2(1500+800)⌈log M−1(800)−1⌉+1500+800disk accesses.12.4The indexed nested-loop join algorithm described in Section12.5.3can beinefﬁcient if the index is a secondary index,and there are multiple tuples with the same value for the join attributes.Why is it inefﬁcient?Describea way,using sorting,to reduce the cost of retrieving tuples of the innerrelation.Under what conditions would this algorithm be more efﬁcient than hybrid merge join?Answer:If there are multiple tuples in the inner relation with the same value for the join attributes,we may have to access that many blocks of the inner relation for each tuple of the outer relation.That is why it is inefﬁcient.To reduce this cost we can perform a join of the outer relation tuples with just the secondary index leaf entries,postponing the inner relation tuple retrieval.The resultﬁle obtained is then sorted on the inner relation addresses,allowing an efﬁcient physical order scan to complete the join.Hybrid merge–join requires the outer relation to be sorted.The above algorithm does not have this requirement,but for each tuple in the outer relation it needs to perform an index lookup on the inner relation.If the outer relation is much larger than the inner relation,this index lookup cost will be less than the sorting cost,thus this algorithm will be more efﬁcient.12.5Let r and s be relations with no indices,and assume that the relationsare not sorted.Assuming inﬁnite memory,what is the lowest-cost way (in terms of I/O operations)to compute r1s?What is the amount of memory required for this algorithm?Answer:We can store the entire smaller relation in memory,read the larger relation block by block and perform nested loop join using the larger one as the outer relation.The number of I/O operations is equal to b r+b s,and memory requirement is min(b r,b s)+2pages.12.6Consider the bank database of Figure12.13,where the primary keys areunderlined.Suppose that a B+-tree index on branch city is available on relation branch,and that no other index is available.List different ways to handle the following selections that involve negation:a.␴¬(branch city<“Brooklyn”)(branch)4Chapter12Query Processingb.␴¬(branch city=“Brooklyn”)(branch)c.␴¬(branch city<“Brooklyn”∨assets<5000)(branch)Answer:e the index to locate theﬁrst tuple whose branch cityﬁeld hasvalue“Brooklyn”.From this tuple,follow the pointer chains till theend,retrieving all the tuples.b.For this query,the index serves no purpose.We can scan theﬁlesequentially and select all tuples whose branch cityﬁeld is anythingother than“Brooklyn”.c.This query is equivalent to the query␴(branch city≥′Brooklyn′∧assets<5000)(branch)Using the branch-city index,we can retrieve all tuples with branch-cityvalue greater than or equal to“Brooklyn”by following the pointerchains from theﬁrst“Brooklyn”tuple.We also apply the additionalcriteria of assets<5000on every tuple.12.7Write pseudocode for an iterator that implements indexed nested-loopjoin,where the outer relation is pipelined.Your pseudocode must deﬁnethe standard iterator functions open(),next(),and close().Show what stateinformation the iterator must maintain between calls.Answer:Let outer be the iterator which returns successive tuples fromthe pipelined outer relation.Let inner be the iterator which returns suc-cessive tuples of the inner relation having a given value at the join at-tributes.The inner iterator returns these tuples by performing an indexlookup.The functions IndexedNLJoin::open,IndexedNLJoin::close andIndexedNLJoin::next to implement the indexed nested-loop join iteratorare given below.The two iterators outer and inner,the value of the lastread outer relation tuple t r and aﬂag done r indicating whether the end ofthe outer relation scan has been reached are the state information whichneed to be remembered by IndexedNLJoin between calls.IndexedNLJoin::open()beginouter.open();inner.open();done r:=false;if(outer.next()=false)move tuple from outer’s output buffer to t r;elsedone r:=true;endExercises5IndexedNLJoin::close()beginouter.close();inner.close();endboolean IndexedNLJoin::next()beginwhile(¬done r)beginif(inner.next(t r[JoinAttrs])=false)beginmove tuple from inner’s output buffer to t s;compute t r1t s and place it in output buffer;return true;endelseif(outer.next()=false)beginmove tuple from outer’s output buffer to t r;rewind inner toﬁrst tuple of s;endelsedone r:=true;endreturn false;end12.8Design sort-based and hash-based algorithms for computing the relationaldivision operation(see Practise Exercises of Chapter6for a deﬁnition of the division operation).Answer:Suppose r(T∪S)and s(S)be two relations and r÷s has to be computed.For sorting based algorithm,sort relation s on S.Sort relation r on (T,S).Now,start scanning r and look at the T attribute values of theﬁrst tuple.Scan r till tuples have same value of T.Also scan s simultaneously and check whether every tuple of s also occurs as the S attribute of r,ina fashion similar to merge join.If this is the case,output that value of Tand proceed with the next value of T.Relation s may have to be scanned multiple times but r will only be scanned once.Total disk accesses,after6Chapter12Query Processingsorting both the relations,will be|r|+N∗|s|,where N is the number ofdistinct values of T in r.We assume that for any value of T,all tuples in r with that T valueﬁt inmemory,and consider the general case at the end.Partition the relation ron attributes in T such that each partitionﬁts in memory(always possiblebecause of our assumption).Consider partitions one at a time.Build ahash table on the tuples,at the same time collecting all distinct T valuesin a separate hash table.For each value of T,Now,for each value V T ofT,each value s of S,probe the hash table on(V T,s).If any of the values isabsent,discard the value V T,else output the value V T.In the case that not all r tuples with one value for Tﬁt in memory,partitionr and s on the S attributes such that the condition is satisﬁed,run thealgorithm on each corresponding pair of partitions r i and s i.Output theintersection of the T values generated in each partition.12.9What is the effect on the cost of merging runs if the number of bufferblocks per run is increased,while keeping overall memory available forbuffering runsﬁxed?Answer:Seek overhead is reduced,but the the number of runs that canbe merged in a pass decreases potentially leading to more passes.A valueof b b that minimizes overall cost should be chosen.。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

C H A P T E R23XMLPractice Exercises23.1Give an alternative representation of university information contain-ing the same data as in Figure23.1,but using attributes instead ofsubelements.Also give the DTD or XML S chema for this representation.Answer:a.The XML representation of data using attributes is shown in Figure23.100.b.The DTD for the bank is shown in Figure23.101.12Chapter23XML<university><department dept name=“Comp.Sci.”building=“Taylor”budget=“100000”></department><department dept name=“Biology”building=“Watson”budget=“90000”></department><course course id=“CS-101”title=“Intro.to Computer Science”dept name=“Comp.Sci.”credits=“4”></course><course course id=“BIO-301”title=“Genetics”dept name=“Biology.”credits=“4”></course><instructor IID=“10101”name=“Srinivasan”dept name=“Comp.Sci.”salary=“65000”></instructor><instructor IID=“83821”name=“Brandt”dept name=“Comp.Sci”salary=“92000”></instructor><instructor IID=“76766”name=“Crick”dept name=“Biology”salary=“72000”></instructor><teaches IID=“10101”course id=“CS-101”></teaches><teaches IID=“83821”course id=“CS-101”></teaches><teaches IID=“76766”course id=“BIO-301”></teaches></university>Figure23.100XML representation.23.2Give the DTD or XML S chema for an XML representation of the followingnested-relational schema:Emp=(ename,ChildrenSet setof(Children),SkillsSet setof(Skills))Children=(name,Birthday)Birthday=(day,month,year)Skills=(type,ExamsSet setof(Exams))Exams=(year,city)Practice Exercises3 Answer:Query:<!DOCTYPE db[<!ELEMENT emp(ename,children*,skills*)><!ELEMENT children(name,birthday)><!ELEMENT birthday(day,month,year)><!ELEMENT skills(type,exams+)><!ELEMENT exams(year,city)><!ELEMENT ename(#PCDATA)><!ELEMENT name(#PCDATA)><!ELEMENT day(#PCDATA)><!ELEMENT month(#PCDATA)><!ELEMENT year(#PCDATA)><!ELEMENT type(#PCDATA)><!ELEMENT city(#PCDATA)>]><!DOCTYPE university[<!ELEMENT department><!ATTLIST departmentdept name ID#REQUIREDbuilding CDATA#REQUIREDbudget CDATA#REQUIRED><!ELEMENT instructor><!ATTLIST instructorIID ID#REQUIREDname CDATA#REQUIREDdept name IDREF#REQUIRED>salary CDATA#REQUIRED><!ELEMENT course><!ATTLIST coursecourse id ID#REQUIREDtitle CDATA#REQUIREDdept name IDREF#REQUIRED>credits CDATA#REQUIRED><!ELEMENT teaches><!ATTLIST teachesIID IDREF#REQUIRED>course id IDREF#REQUIRED]>Figure23.101The DTD for the university.4Chapter23XML23.3Write a query in XP ath on the schema of Practice Exercise23.2to list allskill types in Emp.Answer:Code:/db/emp/skills/type23.4Write a query in XQ uery on the XML representation in Figure23.11toﬁnd the total salary of all instructors in each department.Answer:Query:for$b in distinct(/university/department/dept name)return<dept-total><dept name>$b/text()</dept name>let$s:=sum(/university/instructor[dept name=$b]/salary)return<total-salary>$s</total-salary></dept-total>23.5Write a query in XQ uery on the XML representation in Figure23.1tocompute the left outer join of department elements with course ele-ments.(Hint:Use universal quantiﬁcation.)Answer:Query:<lojoin>for$d in/university/department,$c in/university/coursewhere$c/dept name=$d/dept namereturn<dept-course>$d$c</dept-course>|for$d in/university/department,where every$c in/university/course satisﬁes(not($c/dept name=$d/dept name))return<dept-course>$c</dept-course></lojoin>23.6Write queries in XQ uery to output course elements with associatedinstructor elements nested within the course elements,given the uni-versity information representation using ID and IDREFS in Figure23.11.Practice Exercises5 Answer:The answer in XQuery is<university-2>for$c in/university/coursereturn<course><course id>$c/*</course id>for$a in$c/id(@instructors)return$a</course></university-2>23.7Give a relational schema to represent bibliographical information spec-iﬁed according to the DTD fragment in Figure23.16.The relational schema must keep track of the order of author elements.You can as-sume that only books and articles appear as top-level elements in XML documents.Answer:Relation schema:book(bid,title,year,publisher,place)article(artid,title,journal,year,number,volume,pages)book author(bid,ﬁrst name,last name,order)article author(artid,ﬁrst name,last name,order)23.8Show the tree representation of the XML data in Figure23.1,and therepresentation of the tree using nodes and child relations described in Section23.6.2.Answer:The answer is shown in Figure23.102.nodes(1,element,university,–)nodes(2,element,department,–)nodes(3,element,department,–)nodes(4,element,course,–)nodes(5,element,course,–)nodes(6,element,instructor,–)nodes(7,element,instructor,–)nodes(8,element,instructor,–)nodes(9,element,teaches,–)nodes(10,element,teaches,–)nodes(11,element,teaches,–)child(2,1)child(3,1)child(4,1)child(5,1)child(6,1)child(7,1)child(8,1)child(9,1)Continued in Figure23.103Figure23.102Relational Representation of XML Data as Trees.6Chapter23XML23.9Consider the following recursive DTD:<!DOCTYPE parts[<!ELEMENT part(name,subpartinfo*)><!ELEMENT subpartinfo(part,quantity)><!ELEMENT name(#PCDATA)><!ELEMENT quantity(#PCDATA)>]>a.Give a small example of data corresponding to this DTD.b.Show how to map this DTD to a relational schema.You can as-sume that part names are unique;that is,wherever a part ap-pears,its subpart structure will be the same.c.Create a schema in XML S chema corresponding to this DTD.Answer:a.The answer is shown in Figure23.104.b.Show how to map this DTD to a relational schema.part(partid,name)subpartinfo(partid,subpartid,qty)Attributes partid and subpartid of subpartinfo are foreign keys topart.c.The XML S chema for the DTD is as follows:<xs:schema xmlns:xs=“/2001/XMLSchema”><xs:element name=“parts”type=“partsType”/><xs:complexType name=“partType”><xs:sequence><xs:element name=“name”type=“xs:string”/><xs:element name=“subpartinfo”type=“subpartinfoType”minOccurs=“0”maxOccurs=“unbounded”/></xs:sequence><xs:complexType name=“subpartinfoType”/><xs:sequence><xs:element name=“part”type=“partType”/><xs:element name=“quantity”type=“xs:string”/></xs:sequence></xs:schema>Practice Exercises7 child(10,1)child(11,1)nodes(12,element,dept name,Comp.Sci.)nodes(13,element,building,Taylor)nodes(14,element,budget,100000)child(12,2)child(13,2)child(14,2)nodes(15,element,dept name,Biology)nodes(16,element,building,Watson)nodes(17,element,budget,90000)child(15,3)child(16,3)child(17,3)nodes(18,element,course id,CS-101)nodes(19,element,title,Intro.to Computer Science)nodes(20,element,dept name,Comp.Sci.)nodes(21,element,credits,4)child(18,4)child(19,4)child(20,4)child(21,4)nodes(22,element,course id,BIO-301)nodes(23,element,title,Genetics)nodes(24,element,dept name,Biology)nodes(25,element,credits,4)child(22,5)child(23,5)child(24,5)child(25,5)nodes(26,element,IID,10101)nodes(27,element,name,Srinivasan)nodes(28,element,dept name,Comp.Sci.)nodes(29,element,salary,65000)child(26,6)child(27,6)child(28,6)child(29,6)nodes(30,element,IID,83821)nodes(31,element,name,Brandt)nodes(32,element,dept name,Comp.Sci.)nodes(33,element,salary,92000)child(30,7child(31,7)child(32,7)child(33,7)nodes(34,element,IID,76766)nodes(35,element,dept name,Biology)nodes(36,element,salary,72000)child(34,8)child(35,8)child(36,8)nodes(37,element,IID,10101)nodes(38,element,course id,CS-101)child(37,9)child(38,9)nodes(39,element,IID,83821)nodes(40,element,course id,CS-101)child(39,10)child(40,10)nodes(41,element,IID,76766)nodes(42,element,course id,BIO-301)child(41,11)child(42,11)Figure23.103Continuation of Figure23.102.8Chapter23XML<parts><part><name>bicycle</name><subpartinfo><part><name>wheel</name><subpartinfo><part><name>rim</name></part><qty>1</qty></subpartinfo><subpartinfo><part><name>spokes</name></part><qty>40</qty></subpartinfo><subpartinfo><part><name>tire</name></part><qty>1</qty></subpartinfo></part><qty>2</qty></subpartinfo><subpartinfo><part><name>brake</name></part><qty>2</qty></subpartinfo><subpartinfo><part><name>gear</name></part><qty>3</qty></subpartinfo><subpartinfo><part><name>frame</name></part><qty>1</qty></subpartinfo></part></parts>Figure23.104Example Parts Data in XML.。

数据库系统概念(database system concepts)英文第六版 课后练习题 答案 第23章

数据库系统概念(database system concepts)英文第六版 课后练习题 答案 第22章