数据库系统基础教程第三章答案.doc

Exercise 3、1、1
Answers for this exercise may vary because of different interpretations、
Some possible FDs:
Social Security number à name
Area code à state
Street address, city, state à zipcode
Possible keys:
{Social Security number, street address, city, state, area code, phone number}
Need street address, city, state to uniquely determine location、 A person could have multiple addresses、 The same is true for phones、 These days, a person could have a landline and a cellular phone
Exercise 3、1、2
Answers for this exercise may vary because of different interpretations
Some possible FDs:
ID à x-position, y-position, z-position
ID à x-velocity, y-velocity, z-velocity
x-position, y-position, z-position à ID
Possible keys:
{ID}
{x-position, y-position, z-position}
The reason why the positions would be a key is no two molecules can occupy the same point、
Exercise 3、1、3a
The superkeys are any subset that contains A1、 Thus, there are 2(n-1) such subsets, since each of the n-1 attributes A2 through An may independently be chosen in or out、
Exercise 3、1、3b
The superkeys are any subset that contains A1 or A2、 There are 2(n-1) such subsets when considering A1 and the n-1 attributes A2 through An、 There are 2(n-2) such subsets when considering A2 and the n-2 attributes A3 through An、 We do not count A1 in these subsets because they are already counted in the first group of subsets、 The total number of subsets is 2(n-1) + 2(n-2)、
Exercise 3、1、3c
The superkeys are any subset that contains {A1,A2} or {A3,A4}、 There are 2(n-2) such subsets when considering {A1,A2} and the n-2 attributes A3 through An、 There are 2(n-2) – 2(n-4) such subsets when considering {A3,A4} and attributes A5 through An along with the individual attributes A1 and A2、 We get the 2(n-4) term because we have to discard the subsets that contain the key {A1,A2} to avoid double counting、 The total number of subsets is 2(n-2) + 2(n-2) – 2(n-4)、
Exercise 3、1、3d
The superkeys are any subset that contains {A1,A2} or {A1,A3}、 There are 2(n-2) such subsets when considering {A1,A2} and the n-2 attributes A3 through An、 There are 2(n-3) such subsets when considering {A1,A3} and the n-3 attributes A4 through An We do not count A2 in these subsets because they are already counted in the first group of subsets、 The total number of subsets is 2(n-2) + 2(n-3)、
Exercise 3、2、1a
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 only 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 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 dependencies mentioned above are:
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、2、1b
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、2、1c
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, C, and D、 Thus, the (proper) superkeys are ABC, ABD, BCD, and ABCD、
Exercise 3、2、2a
i) For the single attributes we have {A}+ = ABCD, {B}+ = BCD, {C}+ = C, and {D}+ = D、 Thus, the new dependencies are AàC and AàD、
Now consider pairs of attributes:
{AB}+ = ABCD, {AC}+ = ABCD, {AD}+ = ABCD, {BC}+ = BCD, {BD}+ = BCD, {CD}+ = CD、 Thus the new dependencies are ABàC, ABàD, ACàB, ACàD, ADàB, ADàC, BCàD and BDàC、
For the triples of attributes, {BCD}+ = BCD, but the closures of the other sets are each ABCD、 Thus, we get new dependencies ABCàD, ABDàC, and ACDàB、
Since {ABCD}+ = ABCD, we get no new dependencies、
The collection of 13 new dependencies mentioned above are:
AàC, AàD, ABàC, ABàD, ACàB, ACàD, ADàB, ADàC, BCàD, BDàC, ABCàD, ABDàC and ACDàB、
ii) For the single attributes we have {A}+ = A, {B}+ = B, {C}+ = C and {D}+ = D、 Thus, there are no new dependencies、
Now consider pairs of attributes:
{AB}+ = ABCD, {AC}+ = AC, {AD}+ = ABCD, {BC}+ = ABCD, {BD}+ = BD, {CD}+ = ABCD、 Thus the new dependencies are ABàD, ADàC, BCàA and CDàB、
For the triples of attributes, all the closures of the sets are each ABCD、 Thus, we get new dependencies ABCàD, ABDàC, ACDàB and BCDàA、
Since {ABCD}+ = ABCD, we get no new dependencies、
The collection of 8 new dependencies mentioned above are:
ABàD, ADàC, BCàA, CDàB, ABCàD, ABDàC, ACDàB and BCDàA、
iii) For the single attributes we have {A}+ = ABCD, {B}+ = ABCD, {C} = ABCD, and {D}+ = ABCD、 Thus, the new dependencies are AàC, AàD, BàD, BàA, CàA, CàB, DàB and DàC、
Since all the single attributes’ closures are ABCD, any superset of the single attributes will also lead to a closure of ABCD、 Knowing this, we can enumerate the rest of the new dependencies、
The collection of 24 new dependencies mentioned above are:
AàC, AàD, BàD, BàA, CàA, CàB, DàB, DàC, ABàC, ABàD, ACàB, ACàD, ADàB, ADàC, BCàA, BCàD, BDàA, BDàC, CDàA, CDàB, ABCàD, ABDàC, ACDàB and BCDàA、
Exercise 3、2、2b
i) From the analysis of closures in 3、2、2a(i), we find that the only key is A、 All other sets either do not have ABCD as the closure or contain A、
ii) From the analysis of closures 3、2、2a(ii), we find that AB, AD, BC, and CD are keys、 All other sets either do not have ABCD as the closure or contain one of these four sets、
iii) From the analysis of closures 3、2、2a(iii), we find that A, B, C and D are keys、 All other sets either do not have ABCD as the closure or contain one of these four sets、
Exercise 3、2、2c
i) The superkeys are all those sets that contain one of the keys in 3、2、2b(i)、 The superkeys are AB, AC, AD, ABC, ABD, ACD, BCD and ABCD、
ii) The superkeys are all those sets that contain one of the keys in 3、2、2b(ii)、 The superkeys are ABC, ABD, ACD, BCD and ABCD、
iii) The superkeys are all those sets that contain one of the keys in 3、2、2b(iii)、 The superkeys are AB, AC, AD, BC, BD, CD, ABC, ABD, ACD, BCD and ABCD、
Exercise 3、2、3a
Since A1A2…AnC contains A1A2…An, then the closure of A1A2…AnC contains B、 Thus it follows that A1A2…AnCàB、
Exercise 3、2、3b
From 3、2、3a, we know that A1A2…AnCàB、 Using the concept of trivial dependencies, we can show that A1A2…AnCàC、 Thus A1A2…AnCàBC、
Exercise 3、2、3c
From A1A2…AnE1E2…Ej, we know that the closure contains B1B2…Bm because of the FD A1A2…Anà B1B2…Bm、 The B1B2…Bm and the E1E2…Ej bine to form the C1C2…Ck、 Thus the closure of A1A2…AnE1E2…Ej contains D as well、 Thus, A1A2…AnE1E2…EjàD、
Exercise 3、2、3d
From A1A2…AnC1C2…Ck, we know that the closure contains B1B2…Bm because of the FD A1A2…Anà B1B2…Bm、 The C1C2…Ck also tell us that the closure of A1A2…AnC1C2…Ck contains D1D2…Dj、 Thus, A1A2…AnC1C2…CkàB1B2…BkD1D2…Dj、
Exercise 3、2、4a
If attribute A represented Social Security Number and B represented a person’s name, then we would assume AàB but BàA would not be valid because there may be many people with the same name and different Social Security Numbers、
Exercise 3、2、4b
Let attribute A represent Social Security Number, B represent gender and C represent name、 Surely Social Security Number and gender can uniquely identify a person’s name (i、e、 ABàC)、 A Social Security Number can also uniquely identify a person’s name (i、e、 AàC)、 However, gender does not uniquely determine a name (i、e、 BàC is not valid)、
Exercise 3、2、4c
Let attribute A represent latitude and B represent longitude、 Together, both attributes can uniquely determine C, a point on the world map (i、e、 ABàC)、 However, neither A nor B can uniquely identify a point (i、e、 AàC and BàC are not valid)、
Exercise 3、2、5
Given a relation with attributes A1A2…An, we are told that there are no functional dependencies of the form B1B2…Bn-1àC where B1B2…Bn-1 is n-1 of the attributes from A1A2…An and C is the remaining attribute from A1A2…An、 In this case, the set B1B2…Bn-1 and any subset do not functionally determine C、 Thus the only functional dependencies that we can make are ones where C is on both the left and right hand sides、 All of these functional dependencies would be trivial and thus the relation has no nontrivial FD’s、
Exercise 3、2、6
Let’s prove this by using the contrapositive、 We wish to show that if X+ is not a subset of Y+, then it must be that X is not a subset of Y、
If X+ is not a subset of Y+, there must be attributes A1A2…An in X+ that are not in Y+、 If any of these attributes were originally in X, then we are done because Y does not contain any of the A1A2…An、 However, if the A1A2…An were added by the closure, then we must examine the case further、 Assume that there was some FD C1C2…CmàA1A2…Aj where A1A2…Aj is some subset of A1A2…An、 It must be then that C1C2…Cm or some subset of C1C2…Cm is in X、 However, the attributes C1C2…Cm cannot be in Y because we assumed that attributes A1A2…An are only in X+ and are not in Y+、 Thus, X is not a subset of Y、
By proving the contrapositive, we have also proved if X ⊆ Y, then X+ ⊆ Y+、
Exercise 3、2、7
The algorithm to find X+ is outlined on pg、 76、 Using that algorithm, we can prove that
(X+)+ = X+、 We will do this by using a proof by contradiction、
Suppose that (X+)+ ≠ X+、 Then for (X+)+, it must be that some FD allowed additional attributes to be added to the original set X+、 For example, X+ à A where A is some attribute not in X+、 However, if this were the case, then X+ would not be the closure of X、 The closure of X would have to include A as well、 This contradicts the fact that we were given the closure of X, X+、 Therefore, it must be that (X+)+ = X+ or else X+ is not the closure of X、
Exercise 3、2、8a
If all sets of attributes are closed, then there cannot be any nontrivial functional dependencies、 Suppose A1A2、、、AnàB is a nontrivial dependency、 Then {A1A2、、、An}+ contains B and thus A1A2、、、An is not closed、
Exercise 3、2、8b
If the only closed sets are ø and {A,B,C,D}, then the following FDs hold:
AàB AàC AàD
BàA BàC BàD
CàA CàB CàD
DàA DàB DàC
ABàC ABàD
ACàB ACàD
ADàB ADàC
BCàA BCàD
BDàA BDàC
CDàA CDàB
ABCàD
ABDàC
ACDàB
BCDàA
Exercise 3、2、8c
If the only closed sets are ø, {A,B} and {A,B,C,D}, then the following FDs hold:
AàB
BàA
CàA CàB CàD
DàA DàB DàC
ACàB ACàD
ADàB ADàC
BCàA BCàD
BDàA BDàC
CDàA CDàB
ABCàD
ABDàC
ACDàB
BCDàA
Exercise 3、2、9
We can think of this problem as a situation where the attributes A,B,C represent cities and the functional dependencies represent one way paths between the cities、 The minimal bases are the minimal number of pathways that are needed to connect the cities、 We do not want to create another roadway if the two cities are already connected、
The systematic way to do this would be to check all possible sets of the pathways、 However, we can simplify the situation by noting that it takes more than two pathways to visit the two other cities and e back、 Also, if we find a set of pathways that is minimal, adding additional pathways will not create another minimal set、
The two sets of minimal bases that were given in example 3、11 are:
{AàB, BàC, CàA}
{AàB, BàA, BàC, CàB}
The additional sets of minimal bases are:
{CàB, BàA, AàC}
{AàB, AàC, BàA, CàA}
{AàC, BàC, CàA, CàB}
Exercise 3、2、10a
We need to pute 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 D and E, so a basis for the resulting functional dependencies for ABC is: 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、
Exercise 3、2、10b
We need to pute 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}+=AD
{B}+=B
{C}+=C
{AB}+=ABDE
{AC}+=ABCDE
{BC}+=BC
We ignore D and E, so a basis for the resulting functional dependencies for ABC is: ACàB、
Exercise 3、2、10c
We need to pute 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}+=C
{AB}+=ABD
{AC}+=ABCDE
{BC}+=ABCDE
We ignore D and E, so a basis for the resulting functional dependencies for ABC is: ACàB and BCàA、
Exercise 3、2、10d
We need to pute 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}+=ABCDE
{B}+=ABCDE
{C}+=ABCDE
{AB}+=ABCDE
{AC}+=ABCDE
{BC}+=ABCDE
We ignore D and E, so a basis for the resulting functional dependencies for ABC is: AàB, BàC and CàA、
Exercise 3、2、11
For step one of Algorithm 3、7, suppose we have the FD ABCàDE、 We want to use Armstrong’s Axioms to show that ABCàD and ABCàE follow、 Surely the functional dependencies DEàD and DEàE hold because they are trivial and follow the reflexivity property、 Using the transitivity rule, we can derive the FD ABCàD from the FDs ABCàDE and DEàD、 Likewise, we can do the same for ABCàDE and DEàE and derive the FD ABCàE、
For steps two through four of Algorithm 3、7, suppose we have the initial set of attributes of the closure as ABC、 Suppose also that we have FDs CàD and DàE、 According to Algorithm 3、7, the closure should bee ABCDE、 Taking the FD CàD and augmenting both sides with attributes AB we get the FD ABCàABD、 We can use the splitting method in step one to get the FD ABCàD、 Since D is not in the closure, we can add attribute D、 Taking the FD DàE and augmenting both sides with attributes ABC we get the FD ABCDàABCDE、 Using again the splitting method in step one we get the FD ABCDàE、 Since E is not in the closure, we can add attribute E、
Given a set of FDs, we can prove that a FD F follows by taking the closure of the left side of FD F、 The steps to pute the closure in Algorithm 3、7 can be mimicked by Armstrong’s axioms and thus we can prove F from the given set of FDs using Armstrong’s axioms、
Exercise 3、3、1a
In the solution to Exercise 3、2、1 we found that there are 14 nontrivial dependencies, including the three given ones and eleven derived dependencies、 They 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