數(shù)據(jù)庫系統(tǒng)概念(database system concep

1、C H A P T E R8Relational Database DesignExercises8.1Suppose that we decompose the schema R=(A,B,C,D,Einto(A,B,C(A,D,E.Show that this decomposition is a lossless-join decomposition if thefollowing set F of functional dependencies holds:ABCCDEBDEAAnswer:A decompositionR1,R2is a lossless-join decomposi

2、tion ifR1R2R1or R1R2R2.Let R1=(A,B,C,R2=(A,D,E,and R1R2=A.Since A is a candidate key(see PracticeExercise8.6,Therefore R1R2R1.8.2List all functional dependencies satised by the relation of Figure8.17.Answer:The nontrivial functional dependencies are:AB andCB,and a dependency they logically imply:ACB

3、.There are19trivial functional dependencies of the form,where.Cdoes not functionally determine A because therst and third tuples havethe same C but different A values.The same tuples also show B does notfunctionally determine A.Likewise,A does not functionally determineC because therst two tuples ha

4、ve 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

5、many-to-one relationship set exists between entity sets studentand instructor.Answer:Let Pk(rdenote the primary key attribute of relation r.The functional dependencies Pk(studentPk(instructorandPk(instructorPk(studentindicate a one-to-one relationshipbecause any two tuples with the same value for st

6、udent must havethe same value for instructor,and any two tuples agreeing oninstructor must have the same value for student.The functional dependency Pk(studentPk(instructorindicates amany-to-one relationship since any student value which is repeatedwill have the same instructor value,but many studen

7、t values mayhave the same instructor value.8.4Use Armstrongs 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:ifandthenFollowing the hint,we derive:give

8、naugmentation ruleunion of identical setsgivenaugmentation ruletransitivity rule and set union commutativity8.5Use Armstrongs axioms to prove the soundness of the pseudotransitiv-ity rule.Answer:Proof using Armstrongs axioms of the Pseudotransitivity Rule:ifand,then.givenaugmentation rule and set un

9、ion commutativitygiventransitivity 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 represen

10、tation 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,transitiveSince A C D and C D E ,A E (union,decom-position,transi-tiveSince A A ,we have (reexiveA ABC DE from the above

11、steps (unionSince E A ,E ABC DE (transitiveSince C D E ,C D ABC DE (transitiveSince B D and BC C D ,BC ABC DE (augmentative,transitiveAlso,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 appe

12、ar 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 Exercise 8.

13、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.

14、8Consider the algorithm in Figure8.18to compute+.Show that thisalgorithm is more efcient than the one presented in Figure8.8(Sec-Answer:The algorithm is correct because:If A is added to result then there is a proof thatA.To see this,observe thattrivially sois correctly part of result.IfAis added to

15、result there must be some FDsuch thatAandis 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 mathe

16、matical background.If A+,then A is eventually added to result.We prove this byinduction on the length of the proof ofA using Armstrongsaxioms.First observe that if procedure addin is called with someargument,all the attributes inwill be added to result.Also if aparticular FDs fdcount becomes0,all th

17、e attributes in its tail willdenitely be added to result.The base case of the proof,AA+,is obviously true because therst call to addinhas the argument.The inductive hypotheses is that ifA canbe proved in n steps or less then Aresult.If there is a proof inn+1steps thatA,then the last step was an appl

18、ication ofeither reexivity,augmentation or transitivity on a factproved in n or fewer steps.If reexivity or augmentation was usedin the(n+1st step,A must have been in result by the end of the n thstep itself.Otherwise,by the inductive hypothesisresult.Therefore the dependency used in proving,Awillha

19、ve f dcount set to0by the end of the n th step.Hence A will beadded to result.To see that this algorithm is more efcient 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 of the givenFDs.The recursive

20、 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 l

21、oop 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 bcholds on relation r.Also write an SQL assertion that enforces the func-tional dependency.Assume that no null values are present.(Although

22、part 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 ifbc does not hold on r.select bfrom rgroup by bhaving count(distinct c>1b.create assertion b to c check(not exists(s

23、elect bfrom rgroup by bhaving count(distinct c>18.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

24、 operator is dened 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 thenal result.Thus, the decomposition would be lossy(in a manner

25、 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 decomp

26、osition algorithm,suppose you use a functional de-pendencyto decompose a relation schema r(,into r1(, and r2(,.a.What primary and foreign-key constraint do you expect to holdon the decomposed relations?b.Give an example of an inconsistency that can arise due to anerroneous update,if the foreign-key

27、constraint were not enforcedon the decomposed relations above.c.When a relation is decomposed into3NF using the algorithm inyou expect will hold on the decomposed schema?14Chapter8Relational Database DesignAnswer:a.should be a primary key for r1,andshould be the foreign keyfrom r2,referencing r1.b.I

28、f 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,thiswould amount to simply setting the value ofto null in sometuples.c.For every schema r i(added to the schema be

29、cause of a rule,should be made the primary key.Also,a candidate keyfor 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

30、 occur inany other relation r j,then a foreign key constraint is created fromthose attributes in r j,referencing(the primary key ofr i.8.12Let R1,R2,.,R n be a decomposition of schema U.Let u(Ube a rela-(u.Show thattion,and let r i= RIur11r21···1r nAnswer:Consider some tuple t in u.(u

31、implies that tR ir i,1in.Thus,Note that r i= RitR11tR21.1tR nr11r21.1r nBy the denition of natural join,tR11tR21.1tR n= (tR1×tR2×.×tR nwhere the conditionis satised if values of attributes with the samename in a tuple are equal and where=U.The cartesian productof single tuples generat

32、es one tuple.The selection process is satisedbecause 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 the denition of decomposition,U=R1R2.R n,which meansthat all attributes of t

33、 are in tR11tR21.1tR n.That is,t is equalto the result of this join.Since t is any arbitrary tuple in u,ur11r21.1r n8.13Show that the decomposition in Practice Exercise8.1is not a dependency-preserving decomposition.Answer:The dependency BD is not preserved.F1,the restrictionof F to(A,B,Cis AABC,AAB

34、,AAC,ABC,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 1F 2+is easily seen not to c

35、ontain B D since the only FD in F 1F 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 si

36、de 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 is A ,=A .Observe that B A is not in F +1since B +=B D .8.14Show th

37、at 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

38、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 (1R n (r

39、.We want to prove that r =j .We claim that if t 1and t 2are two tuples in j such that t 1X =t2X ,then t 1=t 2.To prove this claim,we use the following inductive argument Let F =F 1F 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

40、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 1X =t 2X ,we know that t 1result =t 2result is true.Induction Step :Let t 1result =

41、t 2result be true at the end of thek th execution of the f or loop.Suppose the functional dependency considered in the k +1th execution of the f or loop is ,and that result .result implies that t 1=t 2is true.The facts that holds for some attribute set Ri in ,and that t 1R i and t 2R i are inR i (r

42、imply that t 1=t 2is also true.Since is now added to result by the algorithm,we know that t 1result =t 2result is true at theend of the k +1th execution of the f or loop.16Chapter8Relational Database DesignSinceis dependency-preserving and X is a key for R,all attributes in Rare in result when the a

43、lgorithm terminates.Thus,t1R=t2Ris true,that is,t1=t2as claimed earlier.Our claim implies that the size of X(jis 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 r.Using the result of PracticeExercise8.12,we know that rj.Henc

44、e we conclude that r=j.Note that since X is trivially in3NF,Xis a dependency-preservinglossless-join decomposition into3NF.8.15Give an example of a relation schema Rand set Fof functional depen-dencies such that there are at least three distinct lossless-join decompo-sitions of Rinto BCNF.Answer:Giv

45、en the relation R=(A,B,C,Dthe set of functionaldependencies F=AB,CD,BC allows three distinctBCNF decompositions.R1=(A,B,(C,D,(B,Cis in BCNF as isR2=(A,B,(C,D,(A,CR2=(A,B,(C,D,(A,CR3=(B,C,(A,D,(A,B8.16Let a prime attribute be one that appears in at least one candidate key.Letandbe sets of attributes

46、such thatholds,butdoes not hold.Let A be an attribute that is not in,is not in,and forwhichA holds.We say that A is transitively dependent on.Wecan restate our denition of3NF as follows:A relation schema R is in3NF with respect to a set F of functional dependencies if there are nononprime attributes

47、 A in R for which A is transitively dependent on akey for R.Show that this new denition is equivalent to the original one.Answer:Suppose R is in3NF according to the textbook denition.Weshow that it is in3NF according to the denition in the exercise.Let A bea nonprime attribute in R that is transitiv

48、ely dependent on a keyforR.Then there existsR such thatA,A,A,anddoes not hold.But thenA violates the textbookdenition of3NF sinceAimpliesA is nontrivialSincedoes 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 exerci

49、se denition,it is in3NF according to the textbook denition.Suppose R is not in3NF according the the textbook denition.Then there is an FDthat fails all three conditions.Thusis nontrivial.is not a superkey for R.Some A inis not in any candidate key.This implies that A is nonprime andA.Letbe a candida

50、te key for R.Then,does not hold(sinceis not a superkey, A,and A(since A is nonprime.Thus A is transitively dependent on,violating the exercise denition.8.17A functional dependencyis called a partial dependency if thereis a proper subsetofsuch that.We say thatis partially dependent on.A relation sche

51、ma R is in second normal form(2NFif 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 denitions 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 for R.We can also rewrite the denition of2NF given h