数据库系统基础教程第二版课后习题答案Word格式文档下载.docx

1、 they might be a borrower, for example, so we put no constraint on the connection from Owns to Accounts. Here is the 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 fo

2、r 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-one from E1 to E2, e2 and f2 must also be the same. Thus, the pairs are the same. Solutions for Section 2.4Exercise 2.4.1Here is the The E/R Diagram.We have omitted attribute

3、s other than our choice for the key attributes of Students and Courses. Also omitted are names for the relationships. Attribute grade 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.4bHere is the The E/R Diagram

4、Again, we have omitted relationship names and attributes other than our choice for the key attributes. The key for Leagues is its own 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）.

5、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 consisting of team and league names, the key for players is the triple （number, teamName, leagueName）. e.g., Jeff Garcia is （5, 49ers, NFL）. Solutions for Chapt

6、er 3Solutions for Section 3.1Exercise 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.2Exercise 3.2.1 Customers（ssNo, name, address, phone） Flights（number, day,

7、 aircraft） Bookings（ssNo, number, 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

8、（ssNo, number, day, number1, day1）That is, for 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,

9、 sisterName）Solutions for Section 3.3Exercise 3.3.1Since 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 key and the computer-allocation attribute. I

10、t looks like: Depts（name, chair） Courses（number, deptName, room） LabCourses（number, deptName, allocation）For part （b）, LabCourses gets all the attributes of Courses, as: LabCourses（number, deptName, room, allocation）And for （c）, Courses and LabCourses are combined, as: Courses（number, deptName, room

11、, 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 attributes, have a+k attributes. Solutions for Section 3.4Exercise 3.4.2Surely ID is

12、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.4The key attributes are indicated by capitalization in the schema below: Customers（SSNO, name, address, phone） Flights（NUM

13、BER, 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 out. Solutions for Section 3.5Exercise 3.5.1（a）We could try in

14、ference 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 only new dependency we get with a single attri

15、bute 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 BD-A and BD-C. CD+ = ACD, giving CD-For the triples of attributes, ACD+ = ACD,

16、 but the closures of the other sets are each ABCD. Thus, we get new dependencies ABC-D, ABD-C, and BCD-Since ABCD+ = ABCD, we get no new dependencies. The collection of 11 new dependencies mentioned above is: C-A, AB-D, AC-D, BC-A, BC-D, BD-A, BD-C, CD-A, ABC-Exercise 3.5.1（b）From the analysis of cl

17、osures 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 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

18、, 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 A1A2.AnC-B. Exercise 3.5.4（a）Consider the relation AB21This relation satisfies A-B but does not satisfy B-

19、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 and thus A1A2.An is not closed. Exercise 3.5.10（a）We need to compute the closures of all subsets of ABC, alth

20、ough 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 D and E, so a basis for the resulting functional dependencies for ABC are:A and AB-C. Note th

21、at BC-A is true, but follows logically from C-A, and therefore may be omitted from our list. Solutions for Section 3.6Exercise 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:A, C-D, D

22、-D, AB- C, AC-We also learned that the three keys were AB, BC, and BD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. These are:A, AC-D, and CD-One choice is to decompose using C-D. That gives us ABC and CD as decomposed relations. CD is surely in B

23、CNF, since any two-attribute relation is. ABC is not in 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 attribute

24、s are in at least one key of ABCD, that relation is already in 3NF, 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

25、will have A and B on the left, and therefore will contain a key. One possible 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

26、are the same, and so is the decomposition. Solutions for Section 3.7Exercise 3.7.1Since 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 ni

27、ne tuples of the form （a,b,c）, where b is any of b1, b2, or b3, and c is any of c1, c2, or 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 dependencies ssNo-name and ssNo-birthdate hold. The same applies to children, so we expect childSSNo-childname and childSSNo-childBirt