数据库英文版第六版课后答案 (45)

合集下载

数据库系统概念(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.。

数据库英文版第六版课后答案 (28)

Exercises
7.14 Explain the distinctions among the terms primary key, candidate key, and superkey. Answer: A superkey is a set of one or more attributes that, taken collectively, allows us to identify uniquely an entity in the entity set. A superkey may contain extraneous attributes. If K is a superkey, then so is any superset of K. A superkey for which no proper subset is also a superkey is called a candidate key. It is possible that several distinct sets of attributes could
批注本地保存成功开通会员云端永久保存去开通
7 C H A P T E R
Database Design and the E-R
Model
This chapter introduces the entity-relationship model in detail. A signiﬁcant change in the 6th edition is the change in the E-R notation used in the book. There are several alternative E-R notations used in the industry. Increasingly, however, the UML class diagram notation is used instead of the traditional E-R notation used in earlier editions of the book. Among the reasons is the wide availability of UML tools, as well as the conciseness of the UML notation compared to the notation with separate ovals to represent attributes. In keeping with this trend, we have changed our E-R notation to be more compatible with UML.

数据库英文版第六版课后答案 (29)

8.23
Why are certain functional dependencies called trivial functional dependencies? Answer: Certain functional dependencies are called trivial functional dependencies because they are satisﬁed by all relations. Use the deﬁnition of functional dependency to argue that each of Armstrong’s axioms (reﬂexivity, augmentation, and transitivity) is sound. Answer: The deﬁnition of functional dependency is: ␣ → ␤ holds on R if in any legal relation r ( R), for all pairs of tuples t1 and t2 in r such that t1 [␣] = t2 [␣], it is also the case that t1 [␤] = t2 [␤].
8.22
Explain what is meant by repetition of information and inability to represent information. Explain why each of these properties may indicate a bad relational-database design. Answer: • Repetition of information is a condition in a relational database where the values of one attribute are determined by the values of another attribute in the same relation, and both values are repeated throughout the relation. This is a bad relational database design because it increases the storage required for the relation and it makes updating the relation more difﬁcult, and can lead to inconsistent data if updates are done to one copy of the value, but not to another. • Inability to represent information is a condition where a relationship exists among only a proper subset of the attributes in a relation. This is bad relational database design because all the unrelated attributes must be ﬁlled with null values otherwise a tuple without the unrelated information cannot be inserted into the relation. • Inability to represent information can also occur because of loss of information which results from the decomposition of one relation into two relations, which cannot be combined to recreate the original relation. Such a lossy decomposition may happen implicitly, even without explicitly carrying out decomposition, if the initial relational schema itself corresponds to the decomposition.

数据库系统概念（databasesystemconcepts）英文第六版课后练习题答案第8章

数据库系统概念（databasesystemconcepts）英文第六版课后练习题答案第8章C H A P T E R8Relational Database DesignExercises8.1Suppose that we decompose the schema R=(A,B,C,D,E)into(A,B,C)(A,D,E).Show that this decomposition is a lossless-join decomposition if thefollowing set F of functional dependencies holds:A→BCCD→EB→DE→AAnswer:A decomposition{R1,R2}is a lossless-join decomposition ifR1∩R2→R1or R1∩R2→R2.Let R1=(A,B,C),R2=(A,D,E),and R1∩R2=A.Since A is a candidate key(see Practice Exercise8.6),Therefore R1∩R2→R1.8.2List all functional dependencies satis?ed by the relation of Figure8.17.Answer:The nontrivial functional dependencies are:A→B and C→B,and a dependency they logically imply:AC→B.There are 19trivial functional dependencies of the form?→?,where.C does not functionally determine A because the?rst and third tuples havethe same C but different A values.The same tuples also showB does notfunctionally determine A.Likewise,A does not functionally determineC because the?rst two tuples have the same A value and different Cvalues.The same tuples also show B does not functionally determine C.8.3Explain how functional dependencies can be used to indicate the fol-lowing:910Chapter8Relational Database DesignA one-to-one relationship set exists between entity sets student andinstructor.A many-to-one relationship set exists between entity sets studentand instructor.Answer:Let Pk(r)denote the primary key attribute of relation r.The functi onal dependencies Pk(student)→Pk(instructor)and Pk(instructor)→Pk(student)indicate a one-to-one relationshipbecause any two tuples with the same value for student must havethe same value for instructor,and any two tuples agreeing on instructor must have the same value for student.The functional dependency Pk(student)→Pk(instructor)indicates amany-to-one relationship since any student value which isrepeatedwill have the same instructor value,but many student values mayhave the same instructor value.8.4Use Armstrong’s axioms to prove the soundness of the union rule.(Hint:Use the augmentation rule to show that,if?→?,then?→??.Apply theaugmentation rule again,using?→?,and then apply the transitivityrule.)Answer:To prove that:if?→?and?→?then?→??Following the hint,we derive:→?given→??augmentation rule→??union of identical sets→?given→??augmentation rule→??transitivity rule and set union commutativity8.5Use Armstrong’s axioms to prove the soundness of the pseudotransitiv-ity rule.Answer:Pr oof using Armstrong’s axioms of the Pseudotransitivity Rule:if?→?and??→?,then??→?.→?given→??augmentation rule and set union commutativity→?given→?transitivity rule8.6Compute the closure of the following set F of functional dependenciesfor relation schema R=(A,B,C,D,E).Exercises 11A →BCCD →EB →DE →AList the candidate keys for R .Answer:Note:It is not reasonable to expect students to enumerate all of F +.Some shorthand representation of the result should be acceptable as long as the nontrivial members of F +are found.Starting with A →BC ,we can conclude:A →B and A →C .Since A →B and B →D ,A →D (decomposition,transitive)Since A →C D and C D →E ,A →E (union,decom-position,transi-tive)Since A →A ,we have (re?exive)A →ABC DE from the above steps (union)Since E →A ,E →ABC DE (transitive)Since C D →E ,C D →ABC DE (transitive)Since B →D and BC →C D ,BC →ABC DE (augmentative,transitive)Also,C →C ,D →D ,B D →D ,etc.Therefore,any functional dependency with A ,E ,BC ,or C D on the left hand side of the arrow is in F +,no matter which other attributes appear in the FD.Allow *to represent any set of attributes in R ,then F +is B D →B ,B D →D ,C →C ,D →D ,B D →B D ,B →D ,B →B ,B →B D ,and all FDs of the form A ?→?,BC ?→?,C D ?→?,E ?→?where ?is any subset of {A ,B ,C ,D ,E }.The candidate keys are A ,BC ,C D ,and E .8.7Using the functional dependencies of Practice Exercise8.6,compute thecanonical cover F c .Answer:The given set of FDs F is:-A →BCCD →EB →DE →AThe left side of each FD in F is unique.Also none of the attributes in the left side or right side of any of the FDs is extraneous.Therefore the canonical cover F c is equal to F .12Chapter8Relational Database Design8.8Consider the algorithm in Figure8.18to compute?+.Show that thisalgorithm is more ef?cient than the one presented in Figure8.8(Sec-tion8.4.2)and that it computes?+correctly.Answer:The algorithm is correct because:If A is added to result then there is a proof that?→A.T o see this,observe that?→?trivially so?is correctly part of result.IfA∈?is added to result there must be some FD?→?such that A∈?and?is already a subset of result.(Otherwise f dcountwould be nonzero and the if condition would be false.)A full proofcan be given by induction on the depth of recursion for an executionof addin,but such a proof can be expected only from students witha good mathematical background.If A∈?+,then A is eventually added to result.We prove this byinduction on the length of the proof of?→A using Armstrong’saxioms.First observe that if procedure addin is called with someargument?,all the attributes in?will be added to result.Also if aparticular FD’s fdcount becomes0,all the attributes in its tail willde?nitely be added to result.The base case of the proof,A∈??A∈?+,is obviously true b ecause the?rst call to addinhas the argument?.The inductive hypotheses is that if?→A canbe proved in n steps or less then A∈result.If there is a proof inn+1steps that?→A,then the last step was an application ofeither re?exivity,augmentation or transiti vity on a fact?→?proved in n or fewer steps.If re?exivity or augmentation was usedin the(n+1)st step,A must have been in result by the end of the n thstep itself.Otherwise,by the inductive hypothesis??result.Therefore the dependency used in proving?→?,A∈?willhave f dcount set to0by the end of the n th step.Hence A will beadded to result.To see that this algorithm is more ef?cient than the one presented inthe chapter note that we scan each FD once in the main program.Theresulting array a ppears has size proportional to the size ofthe givenFDs.The recursive calls to addin result in processing linear in the sizeof a ppears.Hence the algorithm has time complexity which is linear inthe size of the given FDs.On the other hand,the algorithm given in thetext has quadratic time complexity,as it may perform the loop as manytimes as the number of FDs,in each loop scanning all of them once.8.9Given the database schema R(a,b,c),and a relation r on the schema R,write an SQL query to test whether the functional dependency b→cholds on relation r.Also write an SQL assertion that enforces the func-tional dependency.Assume that no null values are present.(Althoughpart of the SQL standard,such assertions are not supported by anydatabase implementation currently.)Answer:Exercises13a.The query is given below.Its result is non-empty if and only ifb→c does not hold on r.select bfrom rgroup by bhaving count(distinct c)>1b.create assertion b to c check(not exists(select bfrom rgroup by bhaving count(distinct c)>1))8.10Our discussion of lossless-join decomposition implicitly assumed thatattributes on the left-hand side of a functional dependency cannot take on null values.What could go wrong on decomposition,if this property is violated?Answer:The natural join operator is de?ned in terms of the cartesian product and the selection operator.The selection operator,gives unknown for any query on a null value.Thus,the natural join excludes all tuples with null values on the common attributes from the?nal result.Thus, the decomposition would be lossy(in a manner different from the usual case of lossy decomposition),if null values occur in the left-hand side of the functional dependency used to decompose the relation.(Null values in attributes that occur only in the right-hand side of the functional dependency do not cause any problems.)8.11In the BCNF decomposition algorithm,suppose you usea functional de-pendency?→?to decompose a relation schema r(?,?,?)into r1(?,?) and r2(?,?).a.What primary and foreign-key constraint do you expect toholdon the decomposed relations?b.Give an example of an inconsistency that can arise due to anerroneous update,if the foreign-key constraint were not enforcedon the decomposed relations above.c.When a relation is decomposed into3NF using the algorithm inSection8.5.2,what primary and foreign key dependencies wouldyou expect will hold on the decomposed schema?14Chapter8Relational Database DesignAnswer:a.?should be a primary key for r1,and?should be the foreign keyfrom r2,referencing r1.b.If the foreign key constraint is not enforced,then a deletion of atuple from r1would not have a corresponding deletion from thereferencing tuples in r2.Instead of deleting a tuple from r,this would amount to simply setting the value of?to null in some tuples.c.For every schema r i(??)added to the schema because of a rule→?,?should be made the primary key.Also,a candidate key?for the original relation is located in some newly created relationr k,and is a primary key for that relation.Foreign key constraints are created as follows:for each relationr i created above,if the primary key attributes of r i also occur inany other relation r j,then a foreign key constraint is created fromthose attributes in r j,referencing(the primary key of)r i.8.12Let R1,R2,...,R n be a decomposition of schema U.Let u(U)be a rela-(u).Show thattion,and let r i= RIu?r11r21···1r nAnswer:Consider some tuple t in u.(u)implies that t[R i]∈r i,1≤i≤n.Thus,Note that r i= Rit[R1]1t[R2]1...1t[R n]∈r11r21...1r nBy the de?nition of natural join,t[R1]1t[R2]1...1t[R n]= ?(??(t[R1]×t[R2]×...×t[R n]))where the condition?is satis?ed if values of attributes with the samename in a tuple are equal and where?=U.The cartesian productof single tuples generates one tuple.The selection process is satis?edbecause all attributes with the same name must have the same valuesince they are projections from the same tuple.Finally,the projectionclause removes duplicate attribute names.By th e de?nition of decomposition,U=R1∪R2∪...∪R n,which meansthat all attributes of t are in t[R1]1t[R2]1...1t[R n].That is,t is equalto the result of this join.Since t is any arbitrary tuple in u,u?r11r21...1r n8.13Show that the decomposition in Practice Exercise8.1is not a dependency-preserving decomposition.Answer:The dependency B→D is not preserved.F1,the restrictionof F to(A,B,C)is A→ABC,A→AB,A→AC,A→BC,Exercises 15A →B ,A →C ,A →A ,B →B ,C →C ,AB →AC ,AB →ABC ,AB →BC ,AB →AB ,AB →A ,AB →B ,AB →C,AC (same as AB ),BC (same as AB ),ABC (same as AB ).F 2,the restriction of F to (C ,D ,E )is A →ADE ,A →AD ,A →AE ,A →DE ,A →A ,A →D ,A →E ,D →D ,E (same as A ),AD ,AE ,DE ,ADE (same as A ).(F 1∪F 2)+is easily seen not to contain B →D since the only F D in F 1∪F 2with B as the left side is B →B ,a trivial FD .We shall see in Practice Exercise 8.15that B →D is indeed in F +.Thus B →D is not preserved.Note that C D →ABC DE is also not preserved.A simpler argument is as follows:F 1contains no dependencies with D on the right side of the arrow.F 2contains no dependencies withB on the left side of the arrow.Therefore for B →D to be preserved theremustbe an FD B →?in F +1and ?→D in F +2(so B →D would follow by transitivity).Since the intersection of the two schemes isA ,?=A .Observe thatB →A is not in F +1since B +=B D .8.14Show that it is possible to ensure that a dependency-preserving decom-position into 3NF is a lossless-join decomposition by guaranteeing that at least one schema contains a candidate key for the schema being decom-posed.(Hint :Show that the join of all the projections onto the schemas of the decomposition cannot have more tuples than the original relation.)Answer:Let F be a set of functional dependencies that hold on a schema R .Let ?={R 1,R 2,...,R n }be a dependency-preserving 3NF decompo-sition of R .Let X be a candidate key for R .Consider a legal instance r of R .Let j = X (r )1 R 1(r )1 R 2(r ) (1)R n (r ).We want to prove that r =j .We claim that if t 1and t 2are two tuples in j such that t 1[X ]=t2[X ],then t 1=t 2.To prove this claim,we use the following inductive argument –Let F ′=F 1∪F 2∪...∪F n ,where each F i is the restriction of F to the schema R i in ?.Consider the use of the algorithm given in Figure 8.8to compute the closure of X under F ′.We use induction on the number of times that the f or loop in this algorithm is executed.Basis :In the ?rst step of the algorithm,result is assigned to X ,and hence given that t 1[X ]=t 2[X ],we know that t 1[result ]=t 2[result ]is true.?Induction Step :Let t 1[result ]=t 2[result ]be true at the end of thek th execution of the f or loop.Suppose the functionaldependency considered in the k +1th execution of the f or loop is ?→?,and that ??result .??result implies that t 1[?]=t 2[?]is true.The facts that ?→?holds for some attribute set Ri in ?,and that t 1[R i ]and t 2[R i ]are inR i (r )imply that t 1[?]=t 2[?]is also true.Since ?is now added to result by the algorithm,we know that t 1[result ]=t 2[result ]is true at theend of the k +1th execution of the f or loop.16Chapter8Relational Database DesignSince?is dependency-preserving and X is a key for R,all attributes in Rare in result when the algorithm terminates.Thus,t1[R]=t2[R]is true,that is,t1=t2–as claimed earlier.Our claim implies that the size of X(j)is equal to the size of j.Notealso that X(j)= X(r)=r(since X is a key for R).Thus we haveproved that the size of j equals that of /doc/826273026.htmling the result of PracticeExercise8.12,we know that r?j.Hence we conclude that r=j.Note that since X is trivially in3NF,?∪{X}is a dependency-preservinglossless-join decomposition into3NF.8.15Give an example of a relation schema R′and set F′of functional depen-dencies such that there are at least three distinct lossless-join decompo-sitions of R′into BCNF.Answer:Given the relation R′=(A,B,C,D)the set of functionaldependencies F′=A→B,C→D,B→C allows three distinctBCNF decompositions.R1={(A,B),(C,D),(B,C)}is in BCNF as isR2={(A,B),(C,D),(A,C)}R2={(A,B),(C,D),(A,C)}R3={(B,C),(A,D),(A,B)}8.16Let a prime attribute be one that appears in at least one candidate key.Let?and?be sets of attributes such that?→?holds,but?→?does not hold.Let A be an attribute that is not in?,is not in?,and forwhich?→A holds.We say that A is transitively dependent on?.Wecan restate our de?nition of3NF as follows:A relation schema R is in3NF with respect to a set F of functional dependencies if there are nononprime attributes A in R for which A is transitively dependent on akey for R.Show that this new de?nition is equivalent to the original one.Answer:Suppose R is in3NF according to the textbook de?nition.Weshow that it is in3NF according to the de?nition in the exercise.Let A bea nonprime attribute in R that is transitively dependent on a key?forR.Then there exists??R such that?→A,?→?,A∈?,A∈,and?→?does not hold.But then?→A violates the textbookde?nition of3NF sinceA∈?implies?→A is nontrivialSince?→?does not hold,?is not a superkeyA is not any candidate key,since A is nonprimeExercises17 Now we show that if R is in3NF according to the exercise de?nition,it is in3NF according to the textbook de?nition.Suppose R is not in3NF according the the textbook de?nition.Then there is an FD?→?that fails all three conditions.Thus→?is nontrivial.is not a superkey for R.Some A inis not in any candidate key.This implies that A is nonprime and?→A.Let?be a candidate key for R.Then?→?,?→?does not hold(since?is not a superkey), A∈?,and A∈?(since A is nonprime).Thus A is transitively dependent on?,violating the exercise de?nition.8.17A functional dependency?→?is called a partial dependency if thereis a proper subset?of?such that?→?.We say that?is partially dependent on?.A relation schema R is in second normal form(2NF)if each attribute A in R meets one of the following criteria:It appears in a candidate key.It is not partially dependent on a candidate key.Show that every3NF schema is in2NF.(Hint:Show that every partial dependency is a transitive dependency.)Answer:Referring to the de?nitions in Practice Exercise8.16,a relation schema R is said to be in3NF if there is no non-prime attribute A in R for which A is transitively dependent on a key forR.We can also rewrite the de?nition of2NF given here as:“A relation schema R is in2NF if no non-prime attribute A is partially dependent on any candidate key for R.”To prove that every3NF schema is in2NF,it suf?ces to show that if a non-prime attribute A is partially dependent on a candidate key?,thenA is also transitively dependent on the key?.Let A be a non-prime attribute in R.Let?be a candidate key for R.Suppose A is partially dependent on?.From the de?nition of a partial dependency,we know that for someproper subset?of?,?→A.Since,?→?.Also,?→?does not hold,sin ce?is acandidate key.Finally,since A is non-prime,it cannot be in either?or?.Thus we conclude that?→A is a transitive dependency.Hence we have proved that every3NF schema is also in2NF.8.18Give an example of a relation schema R and a set of dependencies suchthat R is in BCNF but is not in4NF.18Chapter8Relational Database DesignAnswer:R(A,B,C)A→→BExercises19result:=?;/*fdcount is an array whose i th element contains the number of attributes on the left side of the i th FD that arenot yet known to be in?+*/for i:=1to|F|dobeginlet?→?denote the i th FD;fdcount[i]:=|?|;end/*appears is an array with one entry for each attribute.The entry for attribute A is a list of integers.Each integeri on the list indicates that A appears on the left sideof the i th FD*/for each attribute A dobeginappears[A]:=NI L;for i:=1to|F|dobeginlet?→?denote the i th FD;if A∈?then add i to appears[A];endendaddin(?);return(result);procedure addin(?);for each attribute A in?dobeginif A∈result thenbeginresult:=result∪{A};for each element i of appears[A]dobeginfdcount[i]:=fdcount[i]?1;if fdcount[i]:=0thenbeginlet?→?denote the i th FD;addin(?);endendendendFigure8.18.An algorithm to compute?+.。

数据库系统概念(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)英文第六版课后练习题答案第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)英文第六版课后练习题答案第19章

C H A P T E R19Distributed DatabasesPractice Exercises19.1How might a distributed database designed for a local-area network differfrom one designed for a wide-area network?Answer:Data transfer on a local-area network(LAN)is much faster thanon a wide-area network(WAN).Thus replication and fragmentation willnot increase throughput and speed-up on a LAN,as much as in a WAN.But even in a LAN,replication has its uses in increasing reliability andavailability.19.2To build a highly available distributed system,you must know what kindsof failures can occur.a.List possible types of failure in a distributed system.b.Which items in your list from part a are also applicable to a central-ized system?Answer:a.The types of failure that can occur in a distributed system includei.Site failure.ii.Disk failure.munication failure,leading to disconnection of one or moresites from the network.b.Theﬁrst two failure types can also occur on centralized systems.19.3Consider a failure that occurs during2PC for a transaction.For each pos-sible failure that you listed in Practice Exercise19.2a,explain how2PCensures transaction atomicity despite the failure.Answer:A proof that2PC guarantees atomic commits/aborts inspite ofsite and link failures,follows.The main idea is that after all sites replywith a<ready T>message,only the co-ordinator of a transaction canmake a commit or abort decision.Any subsequent commit or abort by a12Chapter19Distributed Databasessite can happen only after it ascertains the co-ordinator’s decision,eitherdirectly from the co-ordinator,or indirectly from some other site.Let usenumerate the cases for a site aborting,and then for a site committing.a.A site can abort a transaction T(by writing an<abort T>log record)only under the following circumstances:i.It has not yet written a<ready T>log-record.In this case,theco-ordinator could not have got,and will not get a<ready T>or<commit T>message from this site.Therefore only an abortdecision can be made by the co-ordinator.ii.It has written the<ready T>log record,but on inquiry it foundout that some other site has an<abort T>log record.In thiscase it is correct for it to abort,because that other site wouldhave ascertained the co-ordinator’s decision(either directly orindirectly)before actually aborting.iii.It is itself the co-ordinator.In this case also no site could havecommitted,or will commit in the future,because commit deci-sions can be made only by the co-ordinator.b.A site can commit a transaction T(by writing an<commit T>logrecord)only under the following circumstances:i.It has written the<ready T>log record,and on inquiry it foundout that some other site has a<commit T>log record.In thiscase it is correct for it to commit,because that other site wouldhave ascertained the co-ordinator’s decision(either directly orindirectly)before actually committing.ii.It is itself the co-ordinator.In this case no other participatingsite can abort/would have aborted,because abort decisions aremade only by the co-ordinator.19.4Consider a distributed system with two sites,A and B.Can site A distin-guish among the following?•B goes down.•The link between A and B goes down.•B is extremely overloaded and response time is100times longer than normal.What implications does your answer have for recovery in distributedsystems?Answer:Site A cannot distinguish between the three cases until communicationhas resumed with site B.The action which it performs while B is inacces-sible must be correct irrespective of which of these situations has actuallyPractice Exercises3 occurred,and must be such that B can re-integrate consistently into the distributed system once communication is restored.19.5The persistent messaging scheme described in this chapter depends ontimestamps combined with discarding of received messages if they are too old.Suggest an alternative scheme based on sequence numbers instead of timestamps.Answer:We can have a scheme based on sequence numbers similar to the scheme based on timestamps.We tag each message with a sequence number that is unique for the(sending site,receiving site)pair.The num-ber is increased by1for each new message sent from the sending site to the receiving site.The receiving site stores and acknowledges a received message only if it has received all lower numbered messages also;the message is stored in the received-messages relation.The sending site retransmits a message until it has received an ack from the receiving site containing the sequence number of the transmitted message, or a higher sequence number.Once the acknowledgment is received,it can delete the message from its send queue.The receiving site discards all messages it receives that have a lower sequence number than the latest stored message from the sending site.The receiving site discards from received-messages all but the(number of the) most recent message from each sending site(message can be discarded only after being processed locally).Note that this scheme requires aﬁxed(and small)overhead at the receiving site for each sending site,regardless of the number of messages received.In contrast the timestamp scheme requires extra space for every message.The timestamp scheme would have lower storage overhead if the number of messages received within the timeout interval is small compared to the number of sites,whereas the sequence number scheme would have lower overhead otherwise.19.6Give an example where the read one,write all available approach leadsto an erroneous state.Answer:Consider the balance in an account,replicated at N sites.Let the current balance be$100–consistent across all sites.Consider two trans-actions T1and T2each depositing$10in the account.Thus the balance would be$120after both these transactions are executed.Let the transac-tions execute in sequence:T1ﬁrst and then T2.Let one of the sites,say s, be down when T1is executed and transaction t2reads the balance from site s.One can see that the balance at the primary site would be$110at the end.19.7Explain the difference between data replication in a distributed systemand the maintenance of a remote backup site.Answer:In remote backup systems all transactions are performed at the primary site and the data is replicated at the remote backup site.The4Chapter19Distributed Databasesremote backup site is kept synchronized with the updates at the primarysite by sending all log records.Whenever the primary site fails,the remotebackup site takes over processing.The distributed systems offer greater availability by having multiplecopies of the data at different sites whereas the remote backup systemsoffer lesser availability at lower cost and execution overhead.In a distributed system,transaction code runs at all the sites whereas ina remote backup system it runs only at the primary site.The distributedsystem transactions follow two-phase commit to have the data in con-sistent state whereas a remote backup system does not follow two-phasecommit and avoids related overhead.19.8Give an example where lazy replication can lead to an inconsistent databasestate even when updates get an exclusive lock on the primary(master)copy.Answer:Consider the balance in an account,replicated at N sites.Let thecurrent balance be$100–consistent across all sites.Consider two trans-actions T1and T2each depositing$10in the account.Thus the balancewould be$120after both these transactions are executed.Let the trans-actions execute in sequence:T1ﬁrst and then T2.Suppose the copy of thebalance at one of the sites,say s,is not consistent–due to lazy replicationstrategy–with the primary copy after transaction T1is executed and lettransaction T2read this copy of the balance.One can see that the balanceat the primary site would be$110at the end.19.9Consider the following deadlock-detection algorithm.When transactionT i,at site S1,requests a resource from T j,at site S3,a request message withtimestamp n is sent.The edge(T i,T j,n)is inserted in the local wait-forgraph of S1.The edge(T i,T j,n)is inserted in the local wait-for graph ofS3only if T j has received the request message and cannot immediatelygrant the requested resource.A request from T i to T j in the same site ishandled in the usual manner;no timestamps are associated with the edge(T i,T j).A central coordinator invokes the detection algorithm by sendingan initiating message to each site in the system.On receiving this message,a site sends its local wait-for graph to the coordinator.Note that such a graph contains all the local information thatthe site has about the state of the real graph.The wait-for graph reﬂectsan instantaneous state of the site,but it is not synchronized with respectto any other site.When the controller has received a reply from each site,it constructs a graph as follows:•The graph contains a vertex for every transaction in the system.•The graph has an edge(T i,T j)if and only if:◦There is an edge(T i,T j)in one of the wait-for graphs.Practice Exercises5◦An edge(T i,T j,n)(for some n)appears in more than one wait-forgraph.Show that,if there is a cycle in the constructed graph,then the system isin a deadlock state,and that,if there is no cycle in the constructed graph,then the system was not in a deadlock state when the execution of thealgorithm began.Answer:Let us say a cycle T i→T j→···→T m→T i exists in the graphbuilt by the controller.The edges in the graph will either be local edgesof the from(T k,T l)or distributed edges of the form(T k,T l,n).Each localedge(T k,T l)deﬁnitely implies that T k is waiting for T l.Since a distributededge(T k,T l,n)is inserted into the graph only if T k’s request has reachedT l and T l cannot immediately release the lock,T k is indeed waiting for T l.Therefore every edge in the cycle indeed represents a transaction waitingfor another.For a detailed proof that this imlies a deadlock refer to Stuartet al.[1984].We now prove the converse implication.As soon as it is discovered thatT k is waiting for T l:a.a local edge(T k,T l)is added if both are on the same site.b.The edge(T k,T l,n)is added in both the sites,if T k and T l are ondifferent sites.Therefore,if the algorithm were able to collect all the local wait-for graphsat the same instant,it would deﬁnitely discover a cycle in the constructedgraph,in case there is a circular wait at that instant.If there is a circu-lar wait at the instant when the algorithm began execution,none of theedges participating in that cycle can disappear until the algorithmﬁn-ishes.Therefore,even though the algorithm cannot collect all the localgraphs at the same instant,any cycle which existed just before it startedwill anyway be detected.19.10Consider a relation that is fragmented horizontally by plant number:employee(name,address,salary,plant number)Assume that each fragment has two replicas:one stored at the New Yorksite and one stored locally at the plant site.Describe a good processingstrategy for the following queries entered at the San Jose site.a.Find all employees at the Boca plant.b.Find the average salary of all employees.c.Find the highest-paid employee at each of the following sites:Toronto,Edmonton,Vancouver,Montreal.d.Find the lowest-paid employee in the company.6Chapter19Distributed DatabasesAnswer:a.i.Send the query name(employee)to the Boca plant.ii.Have the Boca location send back the answer.pute average at New York.ii.Send answer to San Jose.c.i.Send the query toﬁnd the highest salaried employee to Toronto,Edmonton,Vancouver,and Montreal.pute the queries at those sites.iii.Return answers to San Jose.d.i.Send the query toﬁnd the lowest salaried employee to New York.pute the query at New York.iii.Send answer to San Jose.19.11Compute r⋉s for the relations of Figure19.9.Answer:The result is as follows.r⋉s=A B C12353219.12Give an example of an application ideally suited for the cloud and anotherthat would be hard to implement successfully in the cloud.Explain youranswer.Answer:Any application that is easy to partition,and does not needstrong guarantees of consistency across partitions,is ideally suited to thecloud.For example,Web-based document storage systems(like Googledocs),and Web based email systems(like Hotmail,Yahoo!mail or GMail),are ideally suited to the cloud.The cloud is also ideally suited to certainkinds of data analysis tasks where the data is already on the cloud;forexample,the Google Map-Reduce framework,and Yahoo!Hadoop arewidely used for data analysis of Web logs such as logs of URLs clicked byusers.Any database application that needs transactional consistency wouldbe hard to implement successfully in the cloud;examples include bankrecords,academic records of students,and many other types of organiza-tional records.19.13Given that the LDAP functionality can be implemented on top of a databasesystem,what is the need for the LDAP standard?Answer:The reasons are:a.Directory access protocols are simpliﬁed protocols that cater to alimited type of access to data.Practice Exercises7b.Directory systems provide a simple mechanism to name objects ina hierarchical fashion which can be used in a distributed directorysystem to specify what information is stored in each of the directoryservers.The directory system can be set up to automatically forwardqueries made at one site to the other site,without user intervention.19.14Consider a multidatabase system in which it is guaranteed that at mostone global transaction is active at any time,and every local site ensures local serializability.a.Suggest ways in which the multidatabase system can ensure thatthere is at most one active global transaction at any time.b.Show by example that it is possible for a nonserializable globalschedule to result despite the assumptions.Answer:a.We can have a special data item at some site on which a lock willhave to be obtained before starting a global transaction.The lockshould be released after the transaction completes.This ensures thesingle active global transaction requirement.To reduce dependencyon that particular site being up,we can generalize the solution byhaving an election scheme to choose one of the currently up sites tobe the co-ordinator,and requiring that the lock be requested on thedata item which resides on the currently elected co-ordinator.b.The following schedule involves two sites and four transactions.T1and T2are local transactions,running at site1and site2respectively.T G1and T G2are global transactions running at both sites.X1,Y1aredata items at site1,and X2,Y2are at site2.T1T2T G1T G2write(Y1)read(Y1)write(X2)read(X2)write(Y2)read(Y2)write(X1)read(X1)In this schedule,T G2starts only after T G1ﬁnishes.Within each site,there is local serializability.In site1,T G2→T1→T G1is a serializ-ability order.In site2,T G1→T2→T G2is a serializability order.Yetthe global schedule schedule is non-serializable.19.15Consider a multidatabase system in which every local site ensures localserializability,and all global transactions are read only.8Chapter19Distributed Databasesa.Show by example that nonserializable executions may result in sucha system.b.Show how you could use a ticket scheme to ensure global serializ-ability.Answer:a.The same system as in the answer to Exercise19.14is assumed,except that now both the global transactions are read-only.Considerthe schedule given below.Though there is local serializability in both sites,the global scheduleis not serializable.b.Since local serializability is guaranteed,any cycle in the system wideprecedence graph must involve at least two different sites,and twodifferent global transactions.The ticket scheme ensures that when-ever two global transactions access data at a site,they conﬂict ona data item(the ticket)at that site.The global transaction managercontrols ticket access in such a manner that the global transactionsexecute with the same serializability order in all the sites.Thus thechance of their participating in a cycle in the system wide precedencegraph is eliminated.。

数据库课后题答案

数据库课后题答案1）查询全体图书的图书号,书名,作者,出版社,单价。

SELECT * FROM 图书2）查询全体图书的信息,其中单价打8折，并且将该列设置别名为’打折价’ 。

SELECT 图书号,书名,作者,出版社,单价*0.8 ‘打折价’ FROM 图书3）显示所有借阅者的读者号，并去掉重复行。

SELECT DISTINCT 读者号FROM 借阅4）查询所有单价在20到30之间的图书信息。

SELECT * FROM 图书WHERE 单价BETWEEN 20 AND 305）查询所有单价不在20到30之间的图书信息。

SELECT * FROM 图书WHERE 单价NOT BETWEEN 20.00 AND 30.006）查询机械工业出版社、科学出版社、人民邮电出版社的图书信息SELECT * FROM 图书WHERE 出版社IN (‘机械工业出版社’, ‘科学出版社’, ‘人民邮电出版社’)7）查询既不是机械工业出版社、也不是科学出版社出版的图书信息SELECT * FROM 图书WHERE 出版社NOT IN (‘机械工业出版社’, ‘科学出版社’)8）查找姓名的第二个字符是’建’并且只有两三个字符的读者的读者号、姓名。

SELECT 读者号,姓名FROM 读者WHERE 姓名LIKE ‘_建_’9）查找姓名以’王’开头的所有读者的读者号、姓名。

SELECT 读者号,姓名FROM 读者WHERE 姓名LIKE ‘王%’10）查找姓名以’王’、’张’或’李’开头的所有读者的读者号、姓名。

SELECT 读者号,姓名FROM 读者WHERE 姓名LIKE ‘[王张李]%’11）查找姓名不是以’王’、’张’或’李’开头的所有读者的读者号、姓名。

SELECT 读者号,姓名FROM 读者WHERE 姓名NOT LIKE ‘[王张李]%’12）查询无归还日期的借阅信息。

SELECT * FROM 借阅WHERE 归还日期IS NULL13）查询有归还日期的借阅信息。

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

C H A P T E R25Advanced Data Types and New ApplicationsPractice Exercises25.1What are the two types of time,and how are they different?Why doesit make sense to have both types of time associated with a tuple?Answer:A temporal database models the changing states of someaspects of the real world.The time intervals related to the data storedin a temporal database may be of two types-valid time and transactiontime.The valid time for a fact is the set of intervals during which the factis true in the real world.The transaction time for a data object is the set oftime intervals during which this object is part of the physical database.Only the transaction time is system dependent and is generated by thedatabase system.Suppose we consider our sample bank database to be bitemporal.Only the concept of valid time allows the system to answer queries suchas-“What was Smith’s balance two days ago?”.On the other hand,queries such as-“What did we record as Smith’s balance two daysago?”can be answered based on the transaction time.The differencebetween the two times is important.For example,suppose,three daysago the teller made a mistake in entering Smith’s balance and correctedthe error only yesterday.This error means that there is a differencebetween the results of the two queries(if both of them are executedtoday).25.2Suppose you have a relation containing the x,y coordinates and namesof restaurants.Suppose also that the only queries that will be askedare of the following form:The query speciﬁes a point,and asks if thereis a restaurant exactly at that point.Which type of index would bepreferable,R-tree or B-tree?Why?Answer:The given query is not a range query,since it requires onlysearching for a point.This query can be efﬁciently answered by a B-treeindex on the pair of attributes(x,y).12Chapter25Advanced Data Types and New Applications25.3Suppose you have a spatial database that supports region queries(withcircular regions)but not nearest-neighbor queries.Describe an algo-rithm toﬁnd the nearest neighbor by making use of multiple regionqueries.Answer:Suppose that we want to search for the nearest neighbor of apoint P in a database of points in the plane.The idea is to issue multipleregion queries centered at P.Each region query covers a larger area ofpoints than the previous query.The procedure stops when the result ofa region query is non-empty.The distance from P to each point withinthis region is calculated and the set of points at the smallest distance isreported.25.4Suppose you want to store line segments in an R-tree.If a line segment isnot parallel to the axes,the bounding box for it can be large,containinga large empty area.•Describe the effect on performance of having large bounding boxes on queries that ask for line segments intersecting a given region.•Brieﬂy describe a technique to improve performance for such queries and give an example of its beneﬁt.Hint:You can divide segmentsinto smaller pieces.Answer:Large bounding boxes tend to overlap even where the regionof overlap does not contain any information.The followingﬁgure:Rshows a region R within which we have to locate a segment.Notethat even though none of the four segments lies in R,due to the largebounding boxes,we have to check each of the four bounding boxes toconﬁrm this.A signiﬁcant improvement is observed in the follwoingﬁgure:Practice Exercises3Rwhere each segment is split into multiple pieces,each with its own bounding box.In the second case,the box R is not part of the boxes indexed by the R-tree.In general,dividing a segment into smaller pieces causes the bounding boxes to be smaller and less wasteful of area. 25.5Give a recursive procedure to efﬁciently compute the spatial join of tworelations with R-tree indices.(Hint:Use bounding boxes to check if leaf entries under a pair of internal nodes may intersect.)Answer:Following is a recursive procedure for computing spatial join of two R-trees.SpJoin(node n1,node n2)beginif(the bounding boxes of n1and n2do not intersect)return;if(both n1and n2are leaves)output all pairs of entries(e1,e2)such thate1∈n1and e2∈n2,and e1and e2overlap;if(n1is not a leaf)NS1=set of children of n1;elseNS1={n1};if(n1is not a leaf)NS1=set of children of n1;elseNS1={n1};for each ns1in NS1and ns2in NS2;SpJoin(ns1,ns2);end25.6Describe how the ideas behind the RAID organization(Section10.3)canbe used in a broadcast-data environment,where there may occasionally be noise that prevents reception of part of the data being transmitted.4Chapter25Advanced Data Types and New ApplicationsAnswer:The concepts of RAID can be used to improve reliability ofthe broadcast of data over wireless systems.Each block of data that isto be broadcast is split into units of equal size.A checksum value iscalculated for each unit and appended to the unit.Now,parity data forthese units is calculated.A checksum for the parity data is appendedto it to form a parity unit.Both the data units and the parity unit arethen broadcast one after the other as a single transmission.On reception of the broadcast,the receiver uses the checksums to verify whether each unit is received without error.If one unit is foundto be in error,it can be reconstructed from the other units.The size of a unit must be chosen carefully.Small units not only requiremore checksums to be computed,but the chance that a burst of noisecorrupts more than one unit is also higher.The problem with usinglarge units is that the probability of noise affecting a unit increases;thus there is a tradeoff to be made.25.7Deﬁne a model of repeatedly broadcast data in which the broadcastmedium is modeled as a virtual disk.Describe how access time anddata-transfer rate for this virtual disk differ from the correspondingvalues for a typical hard disk.Answer:We can distinguish two models of broadcast data.In the caseof a pure broadcast medium,where the receiver cannot communicatewith the broadcaster,the broadcaster transmits data with periodic cy-cles of retransmission of the entire data,so that new receivers can catchup with all the broadcast information.Thus,the data is broadcast in acontinuous cycle.This period of the cycle can be considered akin to theworst case rotational latency in a disk drive.There is no concept of seektime here.The value for the cycle latency depends on the application,but is likely to be at least of the order of seconds,which is much higherthan the latency in a disk drive.In an alternative model,the receiver can send requests back to the broadcaster.In this model,we can also add an equivalent of disk accesslatency,between the receiver sending a request,and the broadcasterreceiving the request and responding to it.The latency is a function ofthe volume of requests and the bandwidth of the broadcast medium.Further,queries may get satisﬁed without even sending a request,sincethe broadcaster happened to send the data either in a cycle or basedon some other receivers request.Regardless,latency is likely to be atleast of the order of seconds,again much higher than the correspondingvalues for a hard disk.A typical hard disk can transfer data at the rate of1to5megabytes persecond.In contrast,the bandwidth of a broadcast channel is typicallyonly a few kilobytes per second.Total latency is likely to be of the orderof seconds to hundreds or even thousands of seconds,compared to afew milliseconds for a hard disk.25.8Consider a database of documents in which all documents are keptin a central database.Copies of some documents are kept on mobilePractice Exercises5 computers.Suppose that mobile computer A updates a copy of docu-ment1while it is disconnected,and,at the same time,mobile computer B updates a copy of document2while it is disconnected.Show how the version-vector scheme can ensure proper updating of the central database and mobile computers when a mobile computer reconnects. Answer:Let C be the computer onto which the central database is loaded.Each mobile computer(host)i stores,with its copy of each document d,a version-vector–that is a set of version numbers V d,i,j, with one entry for each other host j that stores a copy of the document d,which it could possibly update.Host A updates document1while it is disconnected from C.Thus, according to the version vector scheme,the version number V1,A,A is incremented by one.Now,suppose host A re-connects to C.This pair exchanges version-vectors andﬁnds that the version number V1,A,A is greater than V1,C,A by1,(assuming that the copy of document1stored host A was updated most recently only by host A).Following the version-vector scheme, the version of document1at C is updated and the change is reﬂected by an increment in the version number V1,C,A.Note that these are the only changes made by either host.Similarly,when host B connects to host C,they exchange version-vectors,and host Bﬁnds that V1,B,A is one less than V1,C,A.Thus,the version number V1,B,A is incremented by one,and the copy of docu-ment1at host B is updated.Thus,we see that the version-vector scheme ensures proper updating of the central database for the case just considered.This argument can be very easily generalized for the case where multiple off-line updates are made to copies of document1at host A as well as host B and host C.The argument for off-line updates to document2is similar.。

数据库第六版第四章答案

Intermediate SQLPractice Exercises4.1Write the following queries in SQL:a.Display a list of all instructors,showing their ID,name,and the num-ber of sections that they have taught.Make sure to show the numberof sections as0for instructors who have not taught any section.Yourquery should use an outerjoin,and should not use scalar subqueries.b.Write the same query as above,but using a scalar subquery,withoutouterjoin.c.Display the list of all course sections offered in Spring2010,alongwith the names of the instructors teaching the section.If a section hasmore than one instructor,it should appear as many times in the resultas it has instructors.If it does not have any instructor,it should stillappear in the result with the instructor name set to“—”.d.Display the list of all departments,with the total number of instructorsin each department,without using scalar subqueries.Make sure tocorrectly handle departments with no instructors.Answer:a.Display a list of all instructors,showing their ID,name,and the num-ber of sections that they have taught.Make sure to show the numberof sections as0for instructors who have not taught any section.Yourquery should use an outerjoin,and should not use scalar subqueries.select ID,name,count(course id,section id,year,semester)as’Number of sections’from instructor natural left outer join teachesgroup by ID,nameThe above query should not be written using count(*)since count*counts null values also.It could be written using count(section id),or1920Chapter4Intermediate SQLany other attribute from teaches which does not occur in instructor,which would be correct although it may be confusing to the reader.(Attributes that occur in instructor would not be null even if the in-structor has not taught any section.)b.Write the same query as above,but using a scalar subquery,withoutouterjoin.select ID,name,(select count(*)as’Number of sections’from teaches T where T.id=I.id)from instructor Ic.Display the list of all course sections offered in Spring2010,alongwith the names of the instructors teaching the section.If a section hasmore than one instructor,it should appear as many times in the resultas it has instructors.If it does not have any instructor,it should stillappear in the result with the instructor name set to“−”.select course id,section id,ID,decode(name,NULL,’−’,name)from(section natural left outer join teaches)natural left outer join instructorwhere semester=’Spring’and year=2010The query may also be written using the coalesce operator,by re-placing decode(..)by coalesce(name,’−’).A more complex versionof the query can be written using union of join result with anotherquery that uses a subquery toﬁnd courses that do not match;refer toexercise4.2.d.Display the list of all departments,with the total number of instructorsin each department,without using scalar subqueries.Make sure tocorrectly handle departments with no instructors.select dept name,count(ID)from department natural left outer join instructorgroup by dept name4.2Outer join expressions can be computed in SQL without using the SQLouter join operation.To illustrate this fact,show how to rewrite each of thefollowing SQL queries without using the outer join expression.a.select*from student natural left outer join takesb.select*from student natural full outer join takesAnswer:a.select*from student natural left outer join takescan be rewritten as:Exercises21 select*from student natural join takesunionselect ID,name,dept name,tot cred,NULL,NULL,NULL,NULL,NULLfrom student S1where not exists(select ID from takes T1where T1.id=S1.id)b.select*from student natural full outer join takescan be rewritten as:(select*from student natural join takes)union(select ID,name,dept name,tot cred,NULL,NULL,NULL,NULL,NULLfrom student S1where not exists(select ID from takes T1where T1.id=S1.id))union(select ID,NULL,NULL,NULL,course id,section id,semester,year,gradefrom takes T1where not exists(select ID from student S1where T1.id=S1.id))4.3Suppose we have three relations r(A,B),s(B,C),and t(B,D),with allattributes declared as not null.Consider the expressions•r natural left outer join(s natural left outer join t),and•(r natural left outer join s)natural left outer join ta.Give instances of relations r,s and t such that in the result of thesecond expression,attribute C has a null value but attribute D has anon-null value.b.Is the above pattern,with C null and D not null possible in the resultof theﬁrst expression?Explain why or why not.Answer:a.Consider r=(a,b),s=(b1,c1),t=(b,d).The second expression wouldgive(a,b,NULL,d).b.It is not possible for D to be not null while C is null in the result of theﬁrst expression,since in the subexpression s natural left outer join t,it is not possible for C to be null while D is not null.In the overallexpression C can be null if and only if some r tuple does not have amatching B value in s.However in this case D will also be null.4.4Testing SQL queries:To test if a query speciﬁed in English has been cor-rectly written in SQL,the SQL query is typically executed on multiple test22Chapter4Intermediate SQLdatabases,and a human checks if the SQL query result on each test databasematches the intention of the speciﬁcation in English.a.In Section Section3.3.3The Natural Joinsubsection.3.3.3we saw an ex-ample of an erroneous SQL query which was intended toﬁnd whichcourses had been taught by each instructor;the query computed thenatural join of instructor,teaches,and course,and as a result uninten-tionally equated the dept name attribute of instructor and course.Givean example of a dataset that would help catch this particular error.b.When creating test databases,it is important to create tuples in refer-enced relations that do not have any matching tuple in the referencingrelation,for each foreign key.Explain why,using an example queryon the university database.c.When creating test databases,it is important to create tuples with nullvalues for foreign key attributes,provided the attribute is nullable(SQL allows foreign key attributes to take on null values,as long asthey are not part of the primary key,and have not been declared asnot null).Explain why,using an example query on the universitydatabase.Hint:use the queries from Exercise Exercise4.1Item.138.Answer:a.Consider the case where a professor in Physics department teaches anElec.Eng.course.Even though there is a valid corresponding entryin teaches,it is lost in the natural join of instructor,teaches and course,since the instructors department name does not match the departmentname of the course.A dataset corresponding to the same is:instructor={(12345,’Guass’,’Physics’,10000)}teaches={(12345,’EE321’,1,’Spring’,2009)}course={(’EE321’,’Magnetism’,’Elec.Eng.’,6)}b.The query in question0.a is a good example for this.Instructors whohave not taught a single course,should have number of sections as0in the query result.(Many other similar examples are possible.)c.Consider the queryselect*from teaches natural join instructor;In the above query,we would lose some sections if teaches.ID is al-lowed to be NULL and such tuples exist.If,just because teaches.ID isa foreign key to instructor,we did not create such a tuple,the error inthe above query would not be detected.4.5Show how to deﬁne the view student grades(ID,GP A)giving the grade-point average of each student,based on the query in Exercise??;recallthat we used a relation grade points(grade,points)to get the numeric pointsExercises23 associated with a letter grade.Make sure your view deﬁnition correctly handles the case of null values for the grade attribute of the takes relation.Answer:We should not add credits for courses with a null grade;further to to correctly handle the case where a student has not completed any course, we should make sure we don’t divide by zero,and should instead return a null value.We break the query into a subquery thatﬁnds sum of credits and sum of credit-grade-points,taking null grades into account The outer query divides the above to get the average,taking care of divide by0.create view student grades(ID,GP A)asselect ID,credit points/decode(credit sum,0,NULL,credit sum)from((select ID,sum(decode(grade,NULL,0,credits))as credit sum,sum(decode(grade,NULL,0,credits*points))as credit pointsfrom(takes natural join course)natural left outer join grade pointsgroup by ID)unionselect ID,NULLfrom studentwhere ID not in(select ID from takes))The view deﬁned above takes care of NULL grades by considering the creditpoints to be0,and not adding the corresponding credits in credit sum.The query above ensures that if the student has not taken any course with non-NULL credits,and has credit sum=0gets a gpa of NULL.This avoid the division by0,which would otherwise have resulted.An alternative way of writing the above query would be to use student natural left outer join gpa,in order to consider students who have not taken any course.4.6Complete the SQL DDL deﬁnition of the university database of Figure Fig-ure4.8Referential Integrityﬁgcnt.50to include the relations student,takes, advisor,and prereq.Answer:create table student(ID varchar(5),name varchar(20)not null,dept name varchar(20),tot cred numeric(3,0)check(tot cred>=0),primary key(ID),foreign key(dept name)references departmenton delete set null);24Chapter4Intermediate SQLcreate table takes(ID varchar(5),course id varchar(8),section id varchar(8),semester varchar(6),year numeric(4,0),grade varchar(2),primary key(ID,course id,section id,semester,year),foreign key(course id,section id,semester,year)references sectionon delete cascade,foreign key(ID)references studenton delete cascade);create table advisor(i id varchar(5),s id varchar(5),primary key(s ID),foreign key(i ID)references instructor(ID)on delete set null,foreign key(s ID)references student(ID)on delete cascade);create table prereq(course id varchar(8),prereq id varchar(8),primary key(course id,prereq id),foreign key(course id)references courseon delete cascade,foreign key(prereq id)references course);4.7Consider the relational database of Figure Figure4.11ﬁgcnt.53.Give an SQLDDL deﬁnition of this database.Identify referential-integrity constraintsthat should hold,and include them in the DDL deﬁnition.Answer:create table employee(person name char(20),street char(30),city char(30),primary key(person name))Exercises25create table works(person name char(20),company name char(15),salary integer,primary key(person name),foreign key(person name)references employee,foreign key(company name)references company)create table company(company name char(15),city char(30),primary key(company name))pp create table manages(person name char(20),manager name char(20),primary key(person name),foreign key(person name)references employee,foreign key(manager name)references employee)Note that alternative datatypes are possible.Other choices for not nullattributes may be acceptable.4.8As discussed in Section Section4.4.7Complex Check Conditions and Assertionssubsection.4.4we expect the constraint“an instructor cannot teach sections in two differ-ent classrooms in a semester in the same time slot”to hold.a.Write an SQL query that returns all(instructor,section)combinationsthat violate this constraint.b.Write an SQL assertion to enforce this constraint(as discussed in Sec-tion Section4.4.7Complex Check Conditions and Assertionssubsection.4.4.7,current generation database systems do not support such assertions,although they are part of the SQL standard).Answer:a.select ID,name,section id,semester,year,time slot id,count(distinct building,room number)from instructor natural join teaches natural join sectiongroup by(ID,name,section id,semester,year,time slot id)having count(building,room number)>1Note that the distinct keyword is required above.This is to allow twodifferent sections to run concurrently in the same time slot and are26Chapter4Intermediate SQLtaught by the same instructor,without being reported as a constraintviolation.b.create assertion check not exists(select ID,name,section id,semester,year,time slot id,count(distinct building,room number)from instructor natural join teaches natural join sectiongroup by(ID,name,section id,semester,year,time slot id)having count(building,room number)>1)4.9SQL allows a foreign-key dependency to refer to the same relation,as in thefollowing example:create table manager(employee name char(20),manager name char(20),primary key employee name,foreign key(manager name)references manageron delete cascade)Here,employee name is a key to the table manager,meaning that each em-ployee has at most one manager.The foreign-key clause requires that everymanager also be an employee.Explain exactly what happens when a tuplein the relation manager is deleted.Answer:The tuples of all employees of the manager,at all levels,getdeleted as well!This happens in a series of steps.The initial deletion willtrigger deletion of all the tuples corresponding to direct employees ofthe manager.These deletions will in turn cause deletions of second levelemployee tuples,and so on,till all direct and indirect employee tuples aredeleted.4.10SQL-92provides an n-ary operation called coalesce,which is deﬁned asfollows:coalesce(A1,A2,...,A n)returns theﬁrst nonnull A i in the listA1,A2,...,A n,and returns null if all of A1,A2,...,A n are null.Let a and b be relations with the schemas A(name,address,title)and B(name,address,salary),respectively.Show how to express a natural full outer joinb using the full outer-join operation with an on condition and the coalesceoperation.Make sure that the result relation does not contain two copiesof the attributes name and address,and that the solution is correct even ifsome tuples in a and b have null values for attributes name or address.Answer:Exercises27 select coalesce(,)as name,coalesce(a.address,b.address)as address,a.title,b.salaryfrom a full outer join b on = anda.address=b.address4.11Some researchers have proposed the concept of marked nulls.A markednull⊥i is equal to itself,but if i=j,then⊥i=⊥j.One application of marked nulls is to allow certain updates through views.Consider the view instructor info(Section Section4.2Viewssection.4.2).Show how you can use marked nulls to allow the insertion of the tuple(99999,“Johnson”,“Music”) through instructor info.Answer:To insert the tuple(99999,“(”Johnson),“Music”)into the view instructor info,we can do the following:instructor←(99999,“Johnson”,⊥k,⊥)∪instructordepartment←(⊥k,“Music′′,⊥)∪departmentsuch that⊥k is a new marked null not already existing in the database.Note:“Music”here is the name of a building and may or may not be related to Music department.。

VisualFoxPro-6.0参考答案

第一章Visual FoxPro 6.0基础知识习题精练一参考答案：一、单项选择题1.A2.C3.C4.A5.D6.B7.B8.B9.A 10.D 11.B 12.B 13.C 14.D 15.A 16.A 17.B 18.D 19.C 20.A 21.B 22.C 23.B 24.C 25.A 26.C 27.A 28.D 29.B 30.B 31.B 32.D 33.A 34.B 35.C 36.B 37.C 38.A 39.C 40.B 41.A 42.D 43.C 44.B 45.A 46.B 47.C 48.A 49.B 50.B 51.D 52.B 53.D 54.A 55.C 56.A 57.D 58.A 59.D 60.C 61.A 62.D 63.C 64.C 65.B 66.C 67.C 68.B 69.A 70.C 71.A 72.B 73.A74.D 75.B 76.B 77.A 78.D 79.D 80.A 81.A二、填空题1.通用型 G 42.备注型 43..FPT4.变量数组对象5.数值型逻辑型货币型6.严格的 YMD7.$ ￥ 48.字段变量和内存变量9.利用赋值号“=”赋值利用STORE命令赋值 10.?<表达式> ??< 表达式> 11.私有数组全局数组局部数组 12.1 65000 13.不小写大写 14.函数名函数返回值函数名参数函数返回值 15..PRG .DBC .DBF习题精练二参考答案一、选择题1.B2.C3.A4.C5.C6.D7.C8.D9.D 10.A 11.C 12.D 13.D 14.C 15.A16.D 17.D 18.C 19.D 20.B 21.C 22.A 23.C 24.B 25.A 26.D 27.C 28.B 29.D 30.B 31.B 32.C 33.D 34.C 35.C 36.D 37.B 38.B 39.B 40.B 41.C 42.A 43.D 44.B 45.D 46.C 47.C 48.C 49.C 50.B 51.B 52.A 53.C 54.D 55.A 56.B 57.B 58.B 59.A 60.D 61.D 62.D 63.A 64.C 65.B 66.C 67.A 68.D 69.A 70.A 71.C 72.B 73.D 74.C 75.C 76.B 77.B 78.D 79.B 80.B 81.A 82.A 83.B 84.B 85.D 86.A 87.B 88.B 89.D 90.D 91.B 92.D 93.D 94.B 95.A96.A 97.C 98.A 99.C二、填空题1.人工管理、文件管理、数据库系统管理2.一对一、一对多、多对多3.元组,属性4.候选关键字5.外部关键字6.数据结构、数据操作、数据的完整性约束条件。

数据库英文版第六版课后答案（32）

数据库英⽂版第六版课后答案（32）C H A P T E R11Indexing and HashingThis chapter covers indexing techniques ranging from the most basic one tohighly specialized ones.Due to the extensive use of indices in database systems,this chapter constitutes an important part of a database course.A class that has already had a course on data-structures would likely be famil-iar with hashing and perhaps even B+-trees.However,this chapter is necessaryreading even for those students since data structures courses typically cover in-dexing in main memory.Although the concepts carry over to database accessmethods,the details(e.g.,block-sized nodes),will be new to such students.The sections on B-trees(Sections11.4.5)and bitmap indexing(Section11.9) may be omitted if desired. Exercises11.15When is it preferable to use a dense index rather than a sparse index?Explain your answer.Answer:It is preferable to use a dense index instead of a sparse indexwhen the?le is not sorted on the indexed?eld(such as when the indexis a secondary index)or when the index?le is small compared to the sizeof memory.11.16What is the difference between a clustering index and a secondary index?Answer:The clustering index is on the?eld which speci?es the sequentialorder of the?le.There can be only one clustering index while there canbe many secondary indices.11.17For each B+-tree of Practice Exercise11.3,show the steps involved in thefollowing queries:a.Find records with a search-key value of11.b.Find records with a search-key value between7and17,inclusive.Answer:With the structure provided by the solution to Practice Exer-cise11.3a:9798Chapter11Indexing and Hashinga.Find records with a value of11i.Search the?rst level index;follow the?rst pointer.ii.Search next level;follow the third pointer.iii.Search leaf node;follow?rst pointer to records with key value11.b.Find records with value between7and17(inclusive)i.Search top index;follow?rst pointer.ii.Search next level;follow second pointer.iii.Search third level;follow second pointer to records with keyvalue7,and after accessing them,return to leaf node.iv.Follow fourth pointer to next leaf block in the chain.v.Follow?rst pointer to records with key value11,then return.vi.Follow second pointer to records with with key value17.With the structure provided by the solution to Practice Exercise12.3b:a.Find records with a value of11i.Search top level;follow second pointer.ii.Search next level;follow second pointer to records with key value11.b.Find records with value between7and17(inclusive)i.Search top level;follow second pointer.ii.Search next level;follow?rst pointer to records with key value7,then return.iii.Follow second pointer to records with key value11,then return.iv.Follow third pointer to records with key value17.With the structure provided by the solution to Practice Exercise12.3c:a.Find records with a value of11i.Search top level;follow second pointer.ii.Search next level;follow?rst pointer to records with key value11.b.Find records with value between7and17(inclusive)i.Search top level;follow?rst pointer.ii.Search next level;follow fourth pointer to records with key value7,then return.iii.Follow eighth pointer to next leaf block in chain.iv.Follow?rst pointer to records with key value11,then return.v.Follow second pointer to records with key value17.Exercises99 11.18The solution presented in Section11.3.4to deal with nonunique searchkeys added an extra attribute to the search key.What effect could this change have on the height of the B+-tree?Answer:The resultant B-tree’s extended search key is unique.This results in more number of nodes.A single node(which points to mutiple records with the same key)in the original tree may correspond to multiple nodes in the result tree.Dependingon how they are organized the height of the tree may increase;it might be more than that of the original tree.11.19Explain the distinction between closed and open hashing.Discuss therelative merits of each technique in database applications.Answer:Open hashing may place keys with the same hash function value in different buckets.Closed hashing always places such keys together in the same bucket.Thus in this case,different buckets can be of different sizes,though the implementation may be by linking together?xed size buckets using over?ow chains.Deletion is dif?cult with open hashing as all the buckets may have to inspected before we can ascertain that a key value has been deleted,whereas in closed hashing only that bucket whose address is obtained by hashing the key value need be inspected.Deletions are more common in databases and hence closed hashing is more appropriate for them.For a small,static set of data lookups may be more ef?cient using open hashing.The symbol table of a compiler would be a good example.11.20What are the causes of bucket over?ow in a hash?le organization?Whatcan be done to reduce the occurrence of bucket over?ows?Answer:The causes of bucket over?ow are:-a.Our estimate of the number of records that the relation will have wastoo low,and hence the number of buckets allotted was not suf?cient.b.Skew in the distribution of records to buckets.This may happeneither because there are many records with the same search keyvalue,or because the the hash function chosen did not have thedesirable properties of uniformity and randomness.To reduce the occurrence of over?ows,we can:-a.Choose the hash function more carefully,and make better estimatesof the relation size.b.If the estimated size of the relation is n r and number of records perblock is f r,allocate(n r/f r)?(1+d)buckets instead of(n r/f r)buckets.Here d is a fudge factor,typically around0.2.Some space is wasted:About20percent of the space in the buckets will be empty.But thebene?t is that some of the skew is handled and the probability ofover?ow is reduced.11.21Why is a hash structure not the best choice for a search key on whichrange queries are likely?100Chapter11Indexing and HashingAnswer:A range query cannot be answered ef?ciently using a hashindex,we will have to read all the buckets.This is because key valuesin the range do not occupy consecutive locations in the buckets,they aredistributed uniformly and randomly throughout all the buckets.11.22Suppose there is a relation r(A,B,C),with a B+-tree index with searchkey(A,B).a.What is the worst-case cost of?nding records satisfying10using this index,in terms of the number of records retrieved n1and the height h of the tree?b.What is the worst-case cost of?nding records satisfying1050∧5n2that satisfy this selection,as well as n1and h de?ned above?c.Under what conditions on n1and n2would the index be an ef?cient way of?nding records satisfying10Answer:a.What is the worst case cost of?nding records satisfying10using this index,in terms of the number of records retrieved n1and the height h of the tree?This query does not correspond to a range query on the search key as the condition on the?rst attribute if the search key is a comparison condition.It looks up records which have the value of A between10and50.However,each record is likely to be in a different block, because of the ordering of records in the?le,leading to many I/O operation.In the worst case,for each record,it needs to traverse the whole tree(cost is h),so the total cost is n1?h.b.What is the worst case cost of?nding records satisfying1050∧5n2that satisfy this selection,as well as n1and h de?ned above.This query can be answered by using an ordered index on the search key(A,B).For each value of A this is between10and50,the system located records with B value between5and10.However,each record could is likely to be in a different disk block.This amounts to exe-cuting the query based on the condition on A,this costs n1?h.Then these records are checked to see if the condition on B is satis?ed.So, the total cost in the worst case is n1?h.c.Under what conditions on n1and n2would the index be an ef?cient way of?nding records satisfying10n1records satisfy the?rst condition and n2records satisfy the second condition.When both the conditions are queried,n1records are output in the?rst stage.So,in the case where n1=n2,no extra records are output in the furst stage.Otherwise,the records whichExercises101 dont satisfy the second condition are also output with an additionalcost of h each(worst case).11.23Suppose you have to create a B+-tree index on a large number of names,where the maximum size of a name may be quite large(say40characters) and the average name is itselflarge(say10characters).Explain how pre?x compression can be used to maximize the average fanout of nonleaf nodes. Answer:There arises2problems in the given scenario.The?rst prob-lem is names can be of variable length.The second problem is names can be long(maximum is40characters),leading to a low fanout and a correspondingly increased tree height.With variable-length search keys, different nodes can have different fanouts even if they are full.The fanout of nodes can be increased bu using a technique called pre?x compres-sion.With pre?x compression,the entire search key value is not stored at internal nodes.Only a pre?x of each search key which is suf?ceint to distinguish between the key values in the subtrees that it seperates.The full name can be stored in the leaf nodes,this way we dont lose any information and also maximize the average fanout of internal nodes. 11.24Suppose a relation is stored in a B+-tree?le organization.Suppose sec-ondary indices stored record identi?ers that are pointers to records on disk.a.What would be the effect on the secondary indices if a node splithappens in the?le organization?b.What would be the cost of updating all affected records in a sec-ondary index?c.How does using the search key of the?le organization as a logicalrecord identi?er solve this problem?d.What is the extra cost due to the use of such logical record identi?ers?Answer:a.When a leaf page is split in a B+-tree?le organization,a numberof records are moved to a new page.In such cases,all secondaryindices that store pointers to the relocated records would have tobe updated,even though the values in the records may not havechanged.b.Each leaf page may contain a fairly large number of records,andeach of them may be in different locations on each secondary index.Thus,a leaf-page split may require tens or even hundreds of I/Ooperations to update all affected secondary indices,making it a veryexpensive operation.c.One solution is to store the values of the primary-index search keyattributes in secondary indices,in place of pointers to the indexed102Chapter11Indexing and Hashingrecords.Relocation of records because of leaf-page splits then doesnot require any update on any secondary index.d.Locating a record using the secondary index now requires two steps:First we use the secondary index to?nd the primary index search-key values,and then we use the primary index to?nd the corre-sponding records.This approach reduces the cost of index updatedue to?le reorganization,although it increases the cost of accesingdata using a secondary index.11.25Show how to compute existence bitmaps from other bitmaps.Make sure that your technique works even in the presence of null values,by using a bitmap for the value null.Answer:The existence bitmap for a relation can be calculated by takingthe union(logical-or)of all the bitmaps on that attribute,including thebitmap for value null.11.26How does data encryption affect index schemes?In particular,how might it affect schemes that attempt to store data in sorted order?Answer:Note that indices must operate on the encrypted data orsomeone could gain access to the index to interpret the data.Otherwise,the index would have to be restricted so that only certain users could access it.To keep the data in sorted order,the index scheme would haveto decrypt the data at each level in a tree.Note that hash systems wouldnot be affected.11.27Our description of static hashing assumes that a large contiguous stretch of disk blocks can be allocated to a static hash table.Suppose you can allocate only C contiguous blocks.Suggest how to implement the hash table,if it can be much larger than C blocks.Access to a block should stillbe ef?cient.Answer:A separate list/table as shown below can be created.Starting address of?rst set of C blocksCStarting address of next set of C blocks2Cand so onDesired block address=Starting adress(from the table depending onthe block number)+blocksize*(blocknumber%C)For each set of C blocks,a single entry is added to the table.In thiscase,locating a block requires2steps:First we use the block number tond the actual block address,and then we can access the desired block.。

数据库王英英版答案

数据库王英英版答案根据关系数据库基于的数据模型——关系模型的特征判断下列正确的一项（）。

[单选题] *只存在一对多的实体关系，以图形方式来表示以二维表格结构来保存数据，在关系表中不允许有重复行存在(正确答案)能体现一对多、多对多的关系，但不能体现一对一的关系只存在一对一的实体关系，以图形方式来表示对于参照完整性规则，以下说法正确的是（）。

[单选题] *键和相应的主键必须同名当R1和R2是同一个关系模式时，表示同一个关系中不同元组之间的联系(正确答案)外键值不允许空，必须有值若外键是模式主键中的成分，则外键值允许空，否则不允许空如果要修改表的结构，应该使用SQL语言的命令（）。

[单选题] *UPDATE TABLEALTER TABLE(正确答案)MODIFY TABLEFIX TABLE在Oracle中，修改一个用户可以用（）语句。

[单选题] *CREATE USERALTER USER(正确答案)DELETE USERDROP USER收回权限语句（）。

[单选题] *使用GRANT语句使用REVOKE语句(正确答案)使用ALTER语句使用CREATE语句Oracle使用不同的审计方法来监控使用何种权限，以及访问哪些对象。

审计不会防止使用这些权限，但可以提供有用的信息，用于揭示权限的滥用和误用。

以下不属于Oracle审计方法的是（） [单选题] *模式对象审计语句审计细粒度审计时间审计(正确答案)Oracle数据库实例中以下哪个参数是开关审计功能的参数（） [单选题] *audit_enableenable_auditaudit_trail(正确答案)audit_mothedOracle数据库审计日志如果选择存放在数据库中，对应的后台日志表是（） [单选题] *dba_audit_trailsys.aud$(正确答案)sys.audit$sys.audit_trail下列哪个部分不是oracle实例的组成部分？（） [单选题] *系统全局区域服务器进程数据库作者(正确答案)系统监控器基于"学生-选课-课程"数据库中的三个关系：S（S#，SNAME，SEX，AGE），SC （S#，C#，GRADE），C（C#，CNAME，TEACHER）,若要求查找选修“数据库技术”这门课程的学生姓名，将使用关系（）。

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

Chapter 26: Advanced Transam Concepts, 6th Ed.
©Silberschatz, Korth and Sudarshan See for conditions on re-use
With the growth of networks, and the existence of multiple autonomous
database systems, workflows provide a convenient way of carrying out tasks that involve multiple systems.
Provide infrastructure for building and administering complex transaction
processing systems with a large number of clients and multiple servers.
Provide services such as:
Some commercial TP monitors: CICS from IBM, Pathway from Tandem,
Top End from NCR, and Encina from Transarc
Database System Concepts - 6th Edition
26.3
©Silberschatz, Korth and Sudarshan
TP Monitor Architectures
Database System Concepts - 6th Edition
26.4
©Silberschatz, Korth and Sudarshan

数据库系统概念第六版课后习题部分答案2s

数据库系统概念第六版课后习题部分答案2sC H A P T E R2Introduction to the Relational ModelPractice Exercises2.1Consider the relational database of Figure??.What are the appropriateprimary keys?Answer:The answer is shown in Figure2.1,with primary keys under-lined.2.2Consider the foreign key constraint from the dept name attribute of in-structor to the department relation.Give examples of inserts and deletes tothese relations,which can cause a violation of the foreign key constraint.Answer:Inserting a tuple:(10111,Ostrom,Economics,110,000)into the instructor table,where the department table does not have thedepartment Economics,would violate the foreign key constraint.Deleting the tuple:(Biology,Watson,90000)from the department table,where at least one student or instructortuple has dept name as Biology,would violate the foreign key con-straint.employee(person name,street,city)works(person name company name,salary)company(company name,city)Figure2.1Relational database for Practice Exercise2.1.12Chapter2Introduction to the Relational Model2.3Consider the time slot relation.Given that a particular time slot can meetmore than once in a week,explain why day and start time are part of theprimary key of this relation,while end time is not.Answer:The attributes day and start time are part of the primary keysince a particular class will most likely meet on several different days,and may even meet more than once in a day.However,end time is notpart of the primary key since a particular class that starts at a particulartime on a particular day cannot end at more than one time.2.4In the instance of instructor shown in Figure??,no two instructors havethe same name.From this,can we conclude that name can be used as asuperkey(or primary key)of instructor?Answer:No.For this possible instance of the instructor table the namesare unique,but in general this may not be always the case(unless theuniversity has a rule that two instructors cannot have the same name,which is a rather unlikey scenario).2.5What is the result of?rst performing the cross product of student andadvisor,and then performing a selection operation on the result with thepredicate s id=ID?(Using the symbolic notation of relational algebra,this query can be written as?s id=I D(student×advisor).)Answer:The result attributes include all attribute values of studentfollowed by all attributes of advisor.The tuples in the result are asfollows.For each student who has an advisor,the result has a rowcontaining that students attributes,followed by an s id attribute identicalto the students ID attribute,followed by the i id attribute containing theID of the students advisor.Students who do not have an advisor will not appear in the result.Astudent who has more than one advisor will appear a correspondingnumber of times in the result.2.6Consider the following expressions,which use the result ofa relationalalgebra operation as the input to another operation.For each expression,explain in words what the expression does.a.?year≥2009(takes)1studentb.?year≥2009(takes1student)c. ID,name,course id(student1takes)Answer:a.For each student who takes at least one course in2009,displaythe students information along with the information about whatcourses the student took.The attributes in the result are:ID,name,dept name,tot cred,course id,section id,semester,year,gradeb.Same as(a);selection can be done before the join operation.c.Provide a list of consisting ofExercises3ID,name,course idof all students who took any course in the university.2.7Consider the relational database of Figure??.Give an expression in therelational algebra to express each of the following queries:a.Find the names of all employees who live in city“Miami”.b.Find the names of all employees whose salary is greater than$100,000.c.Find the names of all employees who live in“Miami”and whosesalary is greater than$100,000.Answer:a. name(?city=“Miami”(employee))b. name(?salary>100000(employee))c. name(?city=“Miami”∧salary>100000(employee))2.8Consider the bank database of Figure??.Give an expression in therelational algebra for each of the following queries.a.Find the names of all branches located in“Chicago”.b.Find the names of all borrowers who have a loan in branch“Down-town”.Answer:a. branch name(?branch city=“Chicago”(branch))b. customer name(?branch name=“Downtown”(borro w er1loan))。

数据库四五六章作业答案

GRANT SELECT ON TABLE 职工，部门 TO 王明; （2）授予用户李勇对上述两个表的 INSERT 和 DELETE 权限 GRANT INSERT, DELETE ON TABLE 职工，部门 TO 李勇；（3）授予用户刘星对职工表的 SELECT 权限和对工资字Байду номын сангаас的 UPDATE 权限 GRANT SELETE,UPDATE(工资) ON TABLE 职工 TO 刘星；（4）授予用户张欣创建表的权限 GRANT CREAT TABLE TO 张欣；（5）授予用户对上述两个表的所有权限，并具有转授给他人的权力。 GRANT ALL PRIVILEGES ON 职工，部门 TO 用户 WITH GRANT OPTION; （6）用户杨兰只能查看每个部门职工的最高工资、最低工资、平均工资，但不能查看每个职工的工资。
F
设 K 为 R<U,F>中的属性火属性组合，若 K→U，则称 K 为 R 的候选码。最简单的情况，单个属性是码；最极端的情况，整个属性组是码，称为全码。关系模式 R 中属性或属性组 X 并非 R 的码，但 X 是另一个关系模式的码，则称 X 是 R 的外部码，也称外码。关系数据库中的关系是要满足一定要求的，满足不同程度要求的为不同范式。满足最低要求的叫第一范式，简称 1NF。若 R 属于 1NF，且每一个非主属性完全函数依赖于任何一个候选码，则 R 属于 2NF。若 R 属于 2NF 并且不存在非主属性对码的传递函数依赖，则称 R 属于 3NF。若 R 属于 3NF，并且不存在主属性和码之间的部分和传递函数依赖，则称 R 属于 BCNF。设 R(U)是属性及 U 上的一个关系模式。X，Y，Z，是 U 的子集，并且 Z= UX-Y。关系模式 R(U)中多值依赖 X→→Y 成立，当且仅当对 R(U)的任一关系 r，给定的一对（x，z）值，有一组 Y 的值，者组织仅仅决定于 x 的值而与 z 值无关。

数据库英文版第六版课后答案 (41)

20.9 Suppose half of all the transactions in a clothes shop purchase jeans, and one third of all transactions in the shop purchase T-shirts. Suppose also that half of the transactions that purchase jeans also purchase T-shirts. Write down all the (nontrivial) association rules you can deduce from the above information, giving support and conﬁdence of each rule.
building Packard Painter Taylor Watson Watson
room number 101 514 3128 100 120
157
158 Chapter 20 Data Warehousing and Mining
capacity
500 10 70 30 50
20.7 Explain why the nested-loops join algorithm (see Section 12.5.1) would work poorly on database stored in a column-oriented manner. Describe an alternative algorithm that would work better and explain why your solution is better. Answer: If the nested-loops join algorithm is used as is, it would require tuples for each of the relations to be assembled before they are joined. Assembling tuples can be expensive in a column store, since each attribute may come from a separate area of the disk; the overhead of assembly would be particularly wasteful if many tuples do not satisfy the join condition and would be discarded. In such a situation it would be better to ﬁrst ﬁnd which tuples match by accessing only the join columns of the relations. Sort-merge join, hash join, or indexed nested loops join can be used for this task. After the join is performed, only tuples that get output by the join need to be assembled; assembly can be done by sorting the join result on the record identiﬁer of one of the relations and accessing the corresponding attributes, then resorting on record identiﬁers of the other relation to access its attributes.

数据库英文版第六版课后答案

数据库英文版第六版课后答案Chapter 1: IntroductionQuestions1.What is a database?A database is a collection of organized and structured data stored electronically in a computer system. It allows users to efficiently store, retrieve, and manipulate large amounts of data.2.What are the advantages of using a database system?–Data sharing and integration: A database system allows multiple users to access and share data simultaneously.–Data consistency and integrity: A database system enforces rules and constraints to maintain the accuracy and integrity of the data.–Data security: A database system provides access control mechanisms to ensure that data is accessed by authorized users only.–Data independence: A database system separates the data from the application programs that use it, allowing for easier applicationdevelopment and maintenance.Exercises1.Discuss the advantages and disadvantages of using a database system.Advantages:–Data sharing and integration–Data consistency and integrity–Data security–Data independenceDisadvantages:–Cost: Database systems can be expensive to set up and maintain.–Complexity: Database systems require a certain level of expertise to design, implement, and manage.–Performance overhead: Database systems may introduce some overhead in terms of storage and processing.Overall, the advantages of using a database system outweigh the disadvantages in most cases, especially for large-scale applications with multiple users and complex data requirements.Chapter 2: Relational Model and Relational Algebra Questions1.What is a relation? How is it represented in the relational model?A relation is a table-like structure that represents a set of related data. It is represented as a two-dimensional table with rows and columns, where each row corresponds to a record and each column corresponds to a attribute or field.2.What is the primary key of a relation?The primary key of a relation is a unique identifier for each record in the relation. It is used to ensure the uniqueness and integrity of the data.Exercises1.Consider the following relation:Employees (EmpID, Name, Age, Salary)–EmpID is the primary key of the Employees relation.–Name, Age, and Salary are attributes of the Employees relation.2.Write a relational algebra expression to retrieve the names of all employees whose age is greater than 30.π Name (σ Age > 30 (Employees))Chapter 3: SQLQuestions1.What is SQL?SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It provides a set of commands and statements that allow users to create, modify, and query databases.2.What are the main components of an SQL statement?An SQL statement consists of the following main components:–Keywords: SQL commands and instructions.–Clauses: Criteria and conditions that specify what data to retrieve or modify.–Expressions: Values, variables, or calculations used in SQL statements.–Operators: Symbols used to perform operations on data. Exercises1.Write an SQL statement to create a table called。

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

C H A P T E R17Database System ArchitecturesPractice Exercises17.1Instead of storing shared structures in shared memory,an alternativearchitecture would be to store them in the local memory of a specialprocess,and access the shared data by interprocess communicationwith the process.What would be the drawback of such an architecture?Answer:The drawbacks would be that two interprocess messageswould be required to acquire locks,one for the request and one toconﬁrm grant.Interprocess communication is much more expensivethan memory access,so the cost of locking would increase.The processstoring the shared structures could also become a bottleneck.The beneﬁt of this alternative is that the lock table is protected betterfrom erroneous updates since only one process can access it.17.2In typical client–server systems the server machine is much more pow-erful than the clients;that is,its processor is faster,it may have multipleprocessors,and it has more memory and disk capacity.Consider in-stead a scenario where client and server machines have exactly thesame power.Would it make sense to build a client–server system insuch a scenario?Why?Which scenario would be better suited to adata-server architecture?Answer:With powerful clients,it still makes sense to have a client-server system,rather than a fully centralized system.If the data-serverarchitecture is used,the powerful clients can off-load all the long andcompute intensive transaction processing work from the server,freeingit to perform only the work of satisfying read-write requests.even ifthe transaction-server model is used,the clients still take care of theuser-interface work,which is typically very compute-intensive.A fully distributed system might seem attractive in the presence ofpowerful clients,but client-server systems still have the advantage ofsimpler concurrency control and recovery schemes to be implemented1718Chapter17Database System Architectureson the server alone,instead of having these actions distributed in allthe machines.17.3Consider a database system based on a client–server architecture,withthe server acting as a data server.a.What is the effect of the speed of the interconnection betweenthe client and the server on the choice between tuple and pageshipping?b.If page shipping is used,the cache of data at the client can beorganized either as a tuple cache or a page cache.The page cachestores data in units of a page,while the tuple cache stores data inunits of tuples.Assume tuples are smaller than pages.Describeone beneﬁt of a tuple cache over a page cache.Answer:a.We assume that tuples are smaller than a page andﬁt in a page.If the interconnection link is slow it is better to choose tuple ship-ping,as in page shipping a lot of time will be wasted in shippingtuples that might never be needed.With a fast interconnectionthough,the communication overheads and latencies,not the ac-tual volume of data to be shipped,becomes the bottle neck.Inthis scenario page shipping would be preferable.b.Two beneﬁts of an having a tuple-cache rather than a page-cache,even if page shipping is used,are:i.When a client runs out of cache space,it can replace objectswithout replacing entire pages.The reduced caching granu-larity might result in better cache-hit ratios.ii.It is possible for the server to ask clients to return some ofthe locks which they hold,but don’t need(lock de-escalation).Thus there is scope for greater concurrency.If page caching isused,this is not possible.17.4Suppose a transaction is written in C with embedded SQL,and about80percent of the time is spent in the SQL code,with the remaining20percent spent in C code.How much speedup can one hope to attain ifparallelism is used only for the SQL code?Explain.Answer:Since the part which cannot be parallelized takes20%of thetotal running time,the best speedup we can hope for has to be less than5.17.5Some database operations such as joins can see a signiﬁcant differencein speed when data(for example,one of the relations involved in ajoin)ﬁts in memory as compared to the situation where the data doesnotﬁt in memory.Show how this fact can explain the phenomenonof superlinear speedup,where an application sees a speedup greaterthan the amount of resources allocated to it.Answer:FILLPractice Exercises19 17.6Parallel systems often have a network structure where sets of n pro-cessors connect to a single Ethernet switch,and the Ethernet switches themselves connect to another Ethernet switch.Does this architecture correspond to a bus,mesh or hypercube architecture?If not,how would you describe this interconnection architecture?Answer:FILL。

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

C H A P T E R24Advanced Application DevelopmentExercises24.6Find out all performance information your favorite database system pro-vides.Look for at least the following:what queries are currently executingor executed recently,what resources each of them consumed(CPU andI/O),what fraction of page requests resulted in buffer misses(for eachquery,if available),and what locks have a high degree of contention.Youmay also be able to get information about CPU and I/O utilization fromthe operating system.Answer:•Postgresql:The E XP L AI N command lets us see what query plan the system creates for any query.The numbers that are currently quotedby E XP L AI N are:a.Estimated start-up costb.Estimated total costc.Estimated number of rows output by this plan noded.Estimated average width of rows•SQL:There is a Microsoft tool called SQL Pro f iler.The data is logged, and then the performance can be monitored.The following perfor-mance counters can be logged.a.Memoryb.Physical Diskc.Processd.Processore.SQLServer:Access Methodsf.SQLServer:Buffer Managerg.SQLServer:Cache Manager191192Chapter24Advanced Application Developmenth.SQLServer:Databasesi.SQLServer:General Statisticsj.SQLServer:Latchesk.SQLServer:Locksl.SQLServer:Memory Managerm.SQLServer:SQL Statisticsn.SQLServer:SQL Settable24.7 a.What are the three broad levels at which a database system can betuned to improve performance?b.Give two examples of how tuning can be done for each of the levels.Answer:a.We refer to performance tuning of a database system as the modiﬁ-cation of some system components in order to improve transactionresponse times,or overall transaction throughput.Database systemscan be tuned at various levels to enhance performance.viz.i.Schema and transaction designii.Buffer manager and transaction manageriii.Access and storage structuresiv.Hardware-disks,CPU,busses etc.b.We describe some examples for performance tuning of some of themajor components of the database system.i.T uning the schemaIn this chapter we have seen two examples of schema tuning,viz.vertical partition of a relation(or conversely-join of tworelations),and denormalization(or conversely-normalization).These examples reﬂect the general scenario,and ideas thereincan be applied to tune other schemas.ii.T uning the transactionsOne approach used to speed-up query execution is to improvethe its plan.Suppose that we need the natural join of two relations-say account and depositor from our sample bank database.A sort–merge-join(Section12.5.4)on the attribute account-number may bequicker than a simple nested-loop join on the relations.Other ways of tuning transactions are-breaking up longupdate transactions and combining related sets of queries into asingle query.Generic examples for these approaches are given inthis chapter.For client-server systems,wherein the query has to be trans-mitted from client to server,the query transmission time itselfmay form a large fraction of the total query ing storedprocedures can signiﬁcantly reduce the queries response time.Exercises193 iii.T uning the buffer managerThe buffer manager can be made to increase or decrease the number of pages in the buffer according to changing page-fault rates.However,it must be noted that a larger number of pages may mean higher costs for latch management and maintenance of other data-structures like free-lists and page map tables.iv.T uning the transaction managerThe transaction schedule affects system performance.A query that computes statistics for customers at each branch of the bank will need to scan the relations account and depositor.During these scans,no updates to any customer’s balance will be allowed.Thus,the response time for the update transactions is rge queries are best executed when there are few updates,such as at night.Checkpointing also incurs some cost.If recovery time is not critical,it is preferable to examine a long log(during recovery) rather than spend a lot of(checkpointing)time during normal operation.Hence it may be worthwhile to tune the checkpoint-ing interval according to the expected rate of crashes and the required recovery time.v.T uning the access and storage structuresA query’s response time can be improved by creating an ap-propriate index on the relation.For example,consider a query in which a depositor enquires about her balance in a particu-lar account.This query would result in the scan of the relation account if it has is no index on account-number.Similar index-ing considerations also apply to computing joins.i.e an index on account-number in the account relation saves scanning account when a natural join of account is taken with depositor.In contrast,performance of update transactions may suffer due to indexing.Let us assume that frequent updates to the balance are required.Also suppose that there is an index on balance(presumably for range queries)in account.Now,for each update to the value of the balance,the index too will have to be updated.In addition,concurrent updates to the index structure will require additional locking overheads.Note that the response time for each update would not be more if there were no index on balance.The type of index chosen also affects performance.For a range query,an order preserving index(like B-trees)is better than a hashed index.Clustering of data affects the response time for some queries.For example,assume that the tuples of the account relation are clustered on branch-name.Then the average execution time for a query thatﬁnds the total balance amount deposited at a partic-194Chapter24Advanced Application Developmentular branch can be improved.Even more beneﬁt accrues fromhaving a clustered index on branch-name.If the database system has more than one disk,declustering ofdata will enable parallel access.Suppose that we haveﬁve disksand that in a hypothetical situation where each customer hasﬁve accounts and each account has a lot of historical informationthat needs to be accessed.Storing one account per customer perdisk will enable parallel access to all accounts of a particularcustomer.Thus,the speed of a scan on depositor will increaseaboutﬁve-fold.vi.T uning the hardwareThe hardware for the database system typically consists of disks,the processor,and the interconnecting architecture(busses etc.).Each of these components may be a bottleneck and by increasingthe number of disks or their block-sizes,or using a faster pro-cessor,or by improving the bus architecture,one may obtain animprovement in system performance.24.8When carrying out performance tuning,should you try to tune yourhardware(by adding disks or memory)ﬁrst,or should you try to tuneyour transactions(by adding indices or materialized views)ﬁrst.Explainyour answer.Answer:The3levels of tuning-hardware,database system parameters,schema and transactions-interact with one another;so we must considerthem together when tuning a system.For example,tuning at the transac-tion level may result in the hardware bottleneck changing from the disksystem to the CPU,or vice versa.24.9Suppose that your application has transactions that each access and up-date a single tuple in a very large relation stored in a B+-treeﬁle organiza-tion.Assume that all internal nodes of the B+-tree are in memory,but onlya very small fraction of the leaf pages canﬁt in memory.Explain how tocalculate the minimum number of disks required to support a workloadof1000transactions per second.Also calculate the required number ofdisks,using values for disk parameters given in Section10.2.Answer:Given that all internal nodes of the B+-tree are in memory,and only a very small fraction of the leaf pages canﬁt in memory.We candeduce that each I/O transaction that access and update a single tuplerequires just1I/O operation.The disk with the parameters given in thechapter would support a little under100random-access I/O operationsof4kilbytes each per second.So,number of disks needed to support aworkload of1000transactions is1000/100=10disks.The disk parameters given in Section10.2are almost the same as thevalues in the current chapter.So,the number of disks required will bearound10in this case also.Exercises195 24.10What is the motivation for splitting a long transaction into a series of smallones?What problems could arise as a result,and how can these problems be averted?Answer:Long update transactions cause a lot of log information to be written,and hence extend the checkpointing interval and also the recovery time after a crash.A transaction that performs many updates may even cause the system log to overﬂow before the transaction commits.To avoid these problems with a long update transaction it may be advis-able to break it up into smaller transactions.This can be seen as a group transaction being split into many small mini-batch transactions.The same effect is obtained by executing both the group transaction and the mini-batch transactions,which are scheduled in the order that their operations appear in the group transaction.However,executing the mini-batch transactions in place of the group transaction has some costs,such as extra effort when recovering from system failures.Also,even if the group transaction satisﬁes the isolation requirement,the mini-batch may not.Thus the transaction manager can release the locks held by the mini-batch only when the last transaction in the mini-batch completes execution.24.11Suppose the price of memory falls by half,and the speed of disk access(number of accesses per second)doubles,while all other factors remain the same.What would be the effect of this change on the5minute and1 minute rule?Answer:There will be no effect of these changes on the5minute or the1 minute rule.The value of n,i.e.the frequency of page access at the break-even point,is proportional to the product of memory price and speed of disk access,other factors remaining constant.So when memory price falls by half and access speed doubles,n remains the same.24.12List at least4features of the TPC benchmarks that help make them realisticand dependable measures.Answer:Some features that make the TPC benchmarks realistic and dependable are-a.Ensuring full support for ACID properties of transactions,b.Calculating the throughput by observing the end-to-end performance,c.Making sizes of relations proportional to the expected rate of trans-action arrival,andd.Measuring the dollar cost per unit of throughput.24.13Why was the TPC-D benchmark replaced by the TPC-H and TPC-R bench-marks?Answer:Various TPC-D queries can be signiﬁcantly speeded up by using materialized views and other redundant information,but the overheads of using them should be properly accounted.Hence TPC-R and TPC-H196Chapter24Advanced Application Developmentwere introduced as reﬁnements of TPC-D,both of which use same schemaand workload.TPC-R models periodic reporting queries,and the databaserunning it is permited to use materialized views.TPC-H,on the otherhand,models ad hoc querying,and prohibits materialized views andother redundant information.24.14Explain what application characteristics would help you decide which ofTPCC,TPC-H,or TPC-R best models the application.Answer:Depending on the application characterictics,different bench-marks are used to model it.The TPCC benchmark is widely used for transaction processing.It is ap-propriate for applications which concentrate on the main activities in anorder-entry environment,such as entering and delivering orders,record-ing payments,checking status of orders,and monitoring levels of stock.The TPCH(H represents adhoc)benchmark models the applicationswhich prohibit materialized views and other redundant information,andpermits indices only on primary and foriegn keys.This benchmark mod-els ad-hoc querying where the queries are not known beforehand.The TPCR(R represents for reporting)models the applications which hasqueries,inserts,updates,and deletes.The application is permitted to usematerialized views and other redundant information.。