《2023年数据库系统基础教程第二版课后习题超详细解析答案.pdf》由会员分享，可在线阅读，更多相关《2023年数据库系统基础教程第二版课后习题超详细解析答案.pdf（48页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、学习必备 欢迎下载 Database Systems:The Complete Book Solutions for Chapter 2 Solutions for Section 2.1 Exercise 2.1.1 The E/R Diagram.Exercise 2.1.8(a)The E/R Diagram Kobvxybz Solutions for Section 2.2 Exercise 2.2.1 The Addresses entity set is nothing but a single address,so we would prefer to make address
2、 an attribute of Customers.Were the bank to record several addresses for a customer,then it might make sense to have an Addresses entity set and make Lives-at a many-many relationship.The Acct-Sets entity set is useless.Each customer has a unique account set containing his or her accounts.However,re
3、lating customers directly to their accounts in a many-many relationship conveys the same information and eliminates the account-set concept altogether.Solutions for Section 2.3 Exercise 2.3.1(a)Keys ssNo and number are appropriate for Customers and Accounts,respectively.Also,we think it does not mak
4、e sense for an account to be related to zero customers,so we should round the edge connecting Owns to Customers.It does not seem inappropriate to have a customer with 0 accounts;学习必备 欢迎下载 they might be a borrower,for example,so we put no constraint on the connection from Owns to Accounts.Here is the
5、 The E/R Diagram，showing underlined keys and the numerocity constraint.Exercise 2.3.2(b)If R is many-one from E1 to E2,then two tuples(e1,e2)and(f1,f2)of the relationship set for R must be the same if they agree on the key attributes for E1.To see why,surely e1 and f1 are the same.Because R is many-
6、one from E1 to E2,e2 and f2 must also be the same.Thus,the pairs are the same.Solutions for Section 2.4 Exercise 2.4.1 Here is the The E/R Diagram.We have omitted attributes other than our choice for the key attributes of Students and Courses.Also omitted are names for the relationships.Attribute gr
7、ade is not part of the key for Enrollments.The key for Enrollements is studID from Students and dept and number from Courses.Exercise 2.4.4b Here is the The E/R Diagram Again,we have omitted relationship names and attributes other than our choice for the key attributes.The key for Leagues is its own
8、 name;this entity set is not weak.The key for Teams is its own name plus the name of the league of which the team is a part,e.g.,(Rangers,MLB)or(Rangers,NHL).The key for Players consists of the players number and the key for the team on which he or she plays.Since the latter key is itself a pair con
9、sisting of team and league names,the key for players is the triple(number,teamName,leagueName).e.g.,Jeff 备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习学习必备 欢迎下载 Garcia is(5,49ers,NFL).Database Systems:The Complete Book Solutions for Chapter 3 Solutions
10、for Section 3.1 Exercise 3.1.2(a)We can order the three tuples in any of 3!=6 ways.Also,the columns can be ordered in any of 3!=6 ways.Thus,the number of presentations is 6*6=36.Solutions for Section 3.2 Exercise 3.2.1 Customers(ssNo,name,address,phone)Flights(number,day,aircraft)Bookings(ssNo,numbe
11、r,day,row,seat)Being a weak entity set,Bookings relation has the keys for Customers and Flights and Bookings own attributes.Notice that the relations obtained from the toCust and toFlt relationships are unnecessary.They are:toCust(ssNo,ssNo1,number,day)toFlt(ssNo,number,day,number1,day1)That is,for
12、toCust,the key of Customers is paired with the key for Bookings.Since both include ssNo,this attribute is repeated with two different names,ssNo and ssNo1.A similar situation exists for toFlt.Exercise 3.2.3 Ships(name,yearLaunched)SisterOf(name,sisterName)Solutions for Section 3.3 Exercise 3.3.1 Sin
13、ce Courses is weak,its key is number and the name of its department.We do not have a 备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习学习必备 欢迎下载 relation for GivenBy.In part(a),there is a relation for Courses and a relation for LabCourses that has only the
14、key and the computer-allocation attribute.It looks like:Depts(name,chair)Courses(number,deptName,room)LabCourses(number,deptName,allocation)For part(b),LabCourses gets all the attributes of Courses,as:Depts(name,chair)Courses(number,deptName,room)LabCourses(number,deptName,room,allocation)And for(c)
15、,Courses and LabCourses are combined,as:Depts(name,chair)Courses(number,deptName,room,allocation)Exercise 3.3.4(a)There is one relation for each entity set,so the number of relations is e.The relation for the root entity set has a attributes,while the other relations,which must include the key attri
16、butes,have a+k attributes.Solutions for Section 3.4 Exercise 3.4.2 Surely ID is a key by itself.However,we think that the attributes x,y,and z together form another key.The reason is that at no time can two molecules occupy the same point.Exercise 3.4.4 The key attributes are indicated by capitaliza
17、tion in the schema below:Customers(SSNO,name,address,phone)Flights(NUMBER,DAY,aircraft)Bookings(SSNO,NUMBER,DAY,row,seat)Exercise 3.4.6(a)The superkeys are any subset that contains A1.Thus,there are 2n-1 such subsets,since each of the n-1 attributes A2 through An may independently be chosen in or ou
18、t.Solutions for Section 3.5 Exercise 3.5.1(a)We could try inference rules to deduce new dependencies until we are satisfied we have them all.A more systematic way is to consider the closures of all 15 nonempty sets of attributes.For the single attributes we have A+=A,B+=B,C+=ACD,and D+=AD.Thus,the o
19、nly new dependency we get with a single attribute on the left is C-A.Now consider pairs of attributes:AB+=ABCD,so we get new dependency AB-D.AC+=ACD,and AC-D is nontrivial.AD+=AD,so nothing new.BC+=ABCD,so we get BC-A,and BC-D.BD+=ABCD,giving us 备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习载学习必备欢迎下载学习必备欢迎下载学习必备欢迎
20、下载学习必备欢迎备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习学习必备 欢迎下载 BD-A and BD-C.CD+=ACD,giving CD-A.For the triples of attributes,ACD+=ACD,but the closures of the other sets are each ABCD.Thus,we get new dependencies ABC-D,ABD-C,and BCD-A.Since ABCD+=ABCD,we get no new dependencies.The collection of 11 new dependenc
21、ies mentioned above is:C-A,AB-D,AC-D,BC-A,BC-D,BD-A,BD-C,CD-A,ABC-D,ABD-C,and BCD-A.Exercise 3.5.1(b)From the analysis of closures above,we find that AB,BC,and BD are keys.All other sets either do not have ABCD as the closure or contain one of these three sets.Exercise 3.5.1(c)The superkeys are all
22、those that contain one of those three keys.That is,a superkey that is not a key must contain B and more than one of A,C,and D.Thus,the(proper)superkeys are ABC,ABD,BCD,and ABCD.Exercise 3.5.3(a)We must compute the closure of A1A2.AnC.Since A1A2.An-B is a dependency,surely B is in this set,proving A1
23、A2.AnC-B.Exercise 3.5.4(a)Consider the relation A B 0 2 1 2 This relation satisfies A-B but does not satisfy B-A.Exercise 3.5.8(a)If all sets of attributes are closed,then there cannot be any nontrivial functional dependencies.For suppose A1A2.An-B is a nontrivial dependency.Then A1A2.An+contains B
24、and thus A1A2.An is not closed.Exercise 3.5.10(a)We need to compute the closures of all subsets of ABC,although there is no need to think about the empty set or the set of all three attributes.Here are the calculations for the remaining six sets:A+=A B+=B C+=ACE AB+=ABCDE AC+=ACE BC+=ABCDE We ignore
25、 D and E,so a basis for the resulting functional dependencies for ABC are:C-A and AB-C.Note that BC-A is true,but follows logically from C-A,and therefore may be omitted from our list.备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习学习必备 欢迎下载 Solutions for
26、 Section 3.6 Exercise 3.6.1(a)In the solution to Exercise 3.5.1 we found that there are 14 nontrivial dependencies,including the three given ones and 11 derived dependencies.These are:C-A,C-D,D-A,AB-D,AB-C,AC-D,BC-A,BC-D,BD-A,BD-C,CD-A,ABC-D,ABD-C,and BCD-A.We also learned that the three keys were A
27、B,BC,and BD.Thus,any dependency above that does not have one of these pairs on the left is a BCNF violation.These are:C-A,C-D,D-A,AC-D,and CD-A.One choice is to decompose using C-D.That gives us ABC and CD as decomposed relations.CD is surely in BCNF,since any two-attribute relation is.ABC is not in
28、 BCNF,since AB and BC are its only keys,but C-A is a dependency that holds in ABCD and therefore holds in ABC.We must further decompose ABC into AC and BC.Thus,the three relations of the decomposition are AC,BC,and CD.Since all attributes are in at least one key of ABCD,that relation is already in 3
29、NF,and no decomposition is necessary.Exercise 3.6.1(b)(Revised 1/19/02)The only key is AB.Thus,B-C and B-D are both BCNF violations.The derived FDs BD-C and BC-D are also BCNF violations.However,any other nontrivial,derived FD will have A and B on the left,and therefore will contain a key.One possib
30、le BCNF decomposition is AB and BCD.It is obtained starting with any of the four violations mentioned above.AB is the only key for AB,and B is the only key for BCD.Since there is only one key for ABCD,the 3NF violations are the same,and so is the decomposition.Solutions for Section 3.7 Exercise 3.7.
31、1 Since A-B,and all the tuples have the same value for attribute A,we can pair the B-value from any tuple with the value of the remaining attribute C from any other tuple.Thus,we know that R must have at least the nine tuples of the form(a,b,c),where b is any of b1,b2,or b3,and c is any of c1,c2,or
32、c3.That is,we can derive,using the definition of a multivalued dependency,that each of the tuples(a,b1,c2),(a,b1,c3),(a,b2,c1),(a,b2,c3),(a,b3,c1),and(a,b3,c2)are also in R.Exercise 3.7.2(a)First,people have unique Social Security numbers and unique birthdates.Thus,we expect the functional dependenc
33、ies ssNo-name and ssNo-birthdate hold.The same applies to children,so we expect childSSNo-childname and childSSNo-childBirthdate.Finally,an automobile has a unique brand,so we expect autoSerialNo-autoMake.There are two multivalued dependencies that do not follow from these functional dependencies.Fi
34、rst,the information about one child of a person is independent of other information about that person.That is,if a person with social security number s has a tuple with cn,cs,cb,then if there is 备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习学习必备 欢迎下载 an
35、y other tuple t for the same person,there will also be another tuple that agrees with t except that it has cn,cs,cb in its components for the child name,Social Security number,and birthdate.That is the multivalued dependency ssNo-childSSNo childName childBirthdate Similarly,an automobile serial numb
36、er and make are independent of any of the other attributes,so we expect the multivalued dependency ssNo-autoSerialNo autoMake The dependencies are summarized below:ssNo-name birthdate childSSNo-childName childBirthdate autoSerialNo-autoMake ssNo-childSSNo childName childBirthdate ssNo-autoSerialNo a
37、utoMake Exercise 3.7.2(b)We suggest the relation schemas ssNo,name,birthdate ssNo,childSSNo childSSNo,childName childBirthdate ssNo,autoSerialNo autoSerialNo,autoMake An initial decomposition based on the two multivalued dependencies would give us ssNo,name,birthDate ssNo,childSSNo,childName,childBi
38、rthdate ssNo,autoSerialNo,autoMake Functional dependencies force us to decompose the second and third of these.Exercise 3.7.3(a)Since there are no functional dependencies,the only key is all four attributes,ABCD.Thus,each of the nontrvial multivalued dependencies A-B and A-C violate 4NF.We must sepa
39、rate out the attributes of these dependencies,first decomposing into AB and ACD,and then decomposing the latter into AC and AD because A-C is still a 4NF violation for ACD.The final set of relations are AB,AC,and AD.Exercise 3.7.7(a)Let W be the set of attributes not in X,Y,or Z.Consider two tuples
40、xyzw and xyzw in the relation R in question.Because X-Y,we can swap the ys,so xyzw and xyzw are in R.Because X-Z,we can take the pair of tuples xyzw and xyzw and swap Zs to get xyzw and xyzw.Similarly,we can take the pair xyzw and xyzw and swap Zs to get xyzw and xyzw.In conclusion,we started with t
41、uples xyzw and xyzw and showed that xyzw and xyzw must also be in the relation.That is exactly the statement of the MVD X-Y-union-Z.Note that the above statements all make sense even if there are attributes in common among X,Y,and Z.Exercise 3.7.8(a)备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习载学习必备欢迎下载学习必备欢迎下载学习
42、必备欢迎下载学习必备欢迎备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习学习必备 欢迎下载 Consider a relation R with schema ABCD and the instance with four tuples abcd,abcd,abcd,and abcd.This instance satisfies the MVD A-BC.However,it does not satisfy A-B.For example,if it did satisfy A-B,then because the instance contains the tuples a
43、bcd and abcd,we would expect it to contain abcd and abcd,neither of which is in the instance.Database Systems:The Complete Book Solutions for Chapter 4 Solutions for Section 4.2 Exercise 4.2.1 class Customer attribute string name;attribute string addr;attribute string phone;attribute integer ssNo;re
44、lationship Set ownsAccts inverse Account:ownedBy;class Account attribute integer number;attribute string type;attribute real balance;relationship Set ownedBy inverse Customer:ownsAccts Exercise 4.2.4 class Person attribute string name;relationship Person motherOf inverse Person:childrenOfFemale rela
45、tionship Person fatherOf inverse Person:childrenOfMale relationship Set children inverse Person:parentsOf relationship Set childrenOfFemale inverse Person:motherOf relationship Set childrenOfMale inverse Person:fatherOf relationship Set parentsOf inverse Person:children 备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载
46、学习载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习学习必备 欢迎下载 Notice that there are six different relationships here.For example,the inverse of the relationship that connects a person to their(unique)mother is a relationship that connects a mother(i.e.,a female person)to the set of her c
47、hildren.That relationship,which we call childrenOfFemale,is different from the children relationship,which connects anyone-male or female-to their children.Exercise 4.2.7 A relationship R is its own inverse if and only if for every pair(a,b)in R,the pair(b,a)is also in R.In the terminology of set th
48、eory,the relation R is symmetric.Solutions for Section 4.3 Exercise 4.3.1 We think that Social Security number should me the key for Customer,and account number should be the key for Account.Here is the ODL solution with key and extent declarations.class Customer (extent Customers key ssNo)attribute
49、 string name;attribute string addr;attribute string phone;attribute integer ssNo;relationship Set ownsAccts inverse Account:ownedBy;class Account (extent Accounts key number)attribute integer number;attribute string type;attribute real balance;relationship Set ownedBy inverse Customer:ownsAccts Solu
50、tions for Section 4.4 Exercise 4.4.1(a)Since the relationship between customers and accounts is many-many,we should create a separate relation from that relationship-pair.Customers(ssNo,name,address,phone)Accounts(number,type,balance)CustAcct(ssNo,number)备欢迎下载学习必备欢迎下载学习必备欢迎下载学习必备欢迎下载学习载学习必备欢迎下载学习必备欢