文档库 最新最全的文档下载
当前位置:文档库 › 数据库系统基础教程第三章答案

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

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

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 A n 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 A n. There are 2(n-2) such subsets when considering A2 and the n-2 attributes A3 through A n. 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 A n. There are 2(n-2) – 2(n-4) such subsets when considering {A3,A4} and attributes A5 through A n 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 A n. There are 2(n-3) such subsets when considering {A1,A3} and the n-3 attributes A4 through A n 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…A n C contains A1A2…A n, then the closure of A1A2…A n C contains B. Thus it follows that A1A2…A n C→B.

Exercise 3.2.3b

From 3.2.3a, we know that A1A2…A n C→B. Using the concept of trivial dependencies, we can show that A1A2…A n C→C. Thus A1A2…A n C→BC.

Exercise 3.2.3c

From A1A2…A n E1E2…E j, we know that the closure contains B1B2…B m because of the FD A1A2…A n→

B1B2…B m. The B1B2…B m and the E1E2…E j combine to form the C1C2…C k. Thus the closure of

A1A2…A n E1E2…E j contains D as well. Thus, A1A2…A n E1E2…E j→D.

Exercise 3.2.3d

From A1A2…A n C1C2…C k, we know that the closure contains B1B2…B m because of the FD A1A2…A n→

B1B2…B m. The C1C2…C k also tell us that the closure of A1A2…A n C1C2…C k contains D1D2…D j. Thus, A1A2…A n C1C2…C k→B1B2…B k D1D2…D j.

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…A n, we are told that there are no functional dependencies of the form B1B2…B n-1→C where B1B2…B n-1 is n-1 of the attributes from A1A2…A n and C is the remaining attribute from A1A2…A n. In this case, the set B1B2…B n-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…A n 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…A n. However, if the A1A2…A n were added by the closure, then we must examine the case further. Assume that there was some FD C1C2…C m→A1A2…A j where A1A2…A j is some subset of A1A2…A n. It must be then that C1C2…C m or some subset of C1C2…C m is in X. However, the attributes C1C2…C m cannot be in Y because we assumed that attributes A1A2…A n 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...A n→B is a nontrivial dependency. Then {A1A2...A n}+ contains B and thus A1A2...A n 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 come 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 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 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 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}+=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 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}+=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 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}+=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 become 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 compute 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 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: C→A, C→D,

D→A, AC→D, and CD→A.

One choice is to decompose using the violation C→D. Using the above FDs, we get ACD and BC as decomposed relations. BC is surely in BCNF, since any two-attribute relation is. Using Algorithm 3.12 to discover the projection of FDs on relation ACD, we discover that ACD is not in BCNF since C is its only key. However, D→A is a dependency that holds in ABCD and therefore holds in ACD. We must further decompose ACD into AD and CD. Thus, the three relations of the decomposition are BC, AD, and CD.

Exercise 3.3.1b

By computing the closures of all 15 nonempty subsets of ABCD, we can find all the

nontrivial FDs. They are B→C, B→D, AB→C, AB→D, BC→D, BD→C, ABC→D and ABD→C. From the closures we can also deduce that the only key is AB. Thus, any dependency above that does not contain AB on the left is a BCNF violation. These are: B→C, B→D, BC→D and

BD→C.

One choice is to decompose using the violation B→C. Using the above FDs, we get BCD and AB as decomposed relations. AB is surely in BCNF, since any two-attribute relation is. Using Algorithm 3.12 to discover the projection of FDs on relation BCD, we discover that BCD is in BCNF since B is its only key and the projected FDs all have B on the left side. Thus the two relations of the decomposition are AB and BCD.

Exercise 3.3.1c

In the solution to Exercise 3.2.2(ii), we found that there are 12 nontrivial dependencies, including the four given ones and the eight derived ones. They are AB→C, BC→D, CD→A, AD→B, AB→D, AD→C, BC→A, CD→B, ABC→D, ABD→C, ACD→B and BCD→A.

We also found out that the keys are AB, AD, BC, and CD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. However, all of the FDs contain a key on the left so there are no BCNF violations.

No decomposition is necessary since all the FDs do not violate BCNF.

Exercise 3.3.1d

In the solution to Exercise 3.2.2(iii), we found that there are 28 nontrivial dependencies, including the four given ones and the 24 derived ones. They are A→B, B→C, C→D, D→A, 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.

We also found out that the keys are A,B,C,D. Thus, any dependency above that does not

have one of these attributes on the left is a BCNF violation. However, all of the FDs contain a key on the left so there are no BCNF violations.

No decomposition is necessary since all the FDs do not violate BCNF.

Exercise 3.3.1e

By computing the closures of all 31 nonempty subsets of ABCDE, we can find all the nontrivial FDs. They are AB→C, DE→C, B→D, AB→D, BC→D, BE→C, BE→D, ABC→D, ABD→C, ABE→C, ABE→D, ADE→C, BCE→D, BDE→C, ABCE→D, and ABDE→C. From the closures we can

also deduce that the only key is ABE. Thus, any dependency above that does not contain ABE on the left is a BCNF violation. These are: AB→C, DE→C, B→D, AB→D, BC→D, BE→C, BE→D, ABC→D, ABD→C, ADE→C, BCE→D and BDE→C.

One choice is to decompose using the violation AB→C. Using the above FDs, we get ABCD

and ABE as decomposed relations. Using Algorithm 3.12 to discover the projection of FDs on relation ABCD, we discover that ABCD is not in BCNF since AB is its only key and the FD B→D follows for ABCD. Using violation B→D to further decompose, we get BD and ABC as decomposed relations. BD is in BCNF because it is a two-attribute relation. Using Algorithm 3.12 again, we discover that ABC is in BCNF since AB is the only key and AB→C

is the only nontrivial FD. Going back to relation ABE, following Algorithm 3.12 tells us that ABE is in BCNF because there are no keys and no nontrivial FDs. Thus the three relations of the decomposition are ABC, BD and ABE.

Exercise 3.3.1f

By computing the closures of all 31 nonempty subsets of ABCDE, we can find all the nontrivial FDs. They are: C→B, C→D, C→E, D→B, D→E, AB→C, AB→D, AB→E, AC→B, AC→D, AC→E, AD→B, AD→C, AD→E, BC→D, BC→E, BD→E, CD→B, CD→E, CE→B, CE→D, DE→B,

ABC→D, ABC→E, ABD→C, ABD→E, ABE→C, ABE→D, ACD→B, ACD→E, ACE→B, ACE→D, ADE→B, ADE→C, BCD→E, BCE→D, CDE→B, ABCD→E, ABCE→D, ABDE→C and ACDE→B. From the closures we can also deduce that the keys are AB, AC and AD. Thus, any dependency above that does not contain one of the above pairs on the left is a BCNF violation. These are: C→B, C→D,

C →E,

D →B, D →E, BC →D, BC →E, BD →E, CD →B, CD →E, C

E →B, CE →D, DE →B, BCD →E, BCE →D and CDE →B.

One choice is to decompose using the violation D →B. Using the above FDs, we get BDE and ABC as decomposed relations. Using Algorithm 3.12 to discover the projection of FDs on relation BDE, we discover that BDE is in BCNF since D, BD, DE are the only keys and all the projected FDs contain D, BD, or DE in the left side. Going back to relation ABC,

following Algorithm 3.12 tells us that ABC is not in BCNF because since AB and AC are its only keys and the FD C →B follows for ABC. Using violation C →B to further decompose, we get BC and AC as decomposed relations. Both BC and AC are in BCNF because they are two-attribute relations. Thus the three relations of the decomposition are BDE, BC and AC.

Exercise 3.3.2

Yes, we will get the same result. Both A →B and A →BC have A on the left side and part of

the process of decomposition involves finding {A}+

to form one decomposed relation and A

plus the rest of the attributes not in {A}+

as the second relation. Both cases yield the same decomposed relations.

Exercise 3.3.3

Yes, we will still get the same result. Both A →B and A →BC have A on the left side and

part of the process of decomposition involves finding {A}+

to form one decomposed

relation and A plus the rest of the attributes not in {A}+

as the second relation. Both cases yield the same decomposed relations.

Exercise 3.3.4

This is taken from Example 3.21 pg. 95.

Suppose that an instance of relation R only contains two tuples.

The projections of R onto the relations with schemas {A,B} and {B,C} are:

If we do a natural join on the two projections, we will get:

The result of the natural join is not equal to the original relation R.

Exercise 3.4.1a

This is the initial tableau:

→A.

Since there is not an unsubscripted row, the decomposition for R is not lossless for this set of FDs.

We can use the final tableau as an instance of R as an example for why the join is not lossless. The projected relations are:

The joined relation is:

The joined relation has three more tuples than the original tableau.

Exercise 3.4.1b

This is the initial tableau:

This is the final tableau after applying FDs AC→E and BC→D

Since there is an unsubscripted row, the decomposition for R is lossless for this set of FDs.

Exercise 3.4.1c

This is the initial tableau:

This is the final tableau after applying FDs A→D, D→E and B→D.

Since there is an unsubscripted row, the decomposition for R is lossless for this set of FDs.

Exercise 3.4.1d

This is the initial tableau:

This is the final tableau after applying FDs A→D, CD→E and E→D

Since there is an unsubscripted row, the decomposition for R is lossless for this set of FDs.

Exercise 3.4.2

When we decompose a relation into BCNF, we will project the FDs onto the decomposed relations to get new sets of FDs. These dependencies are preserved if the union of these new sets is equivalent to the original set of FDs.

For the FDs of 3.4.1a, the dependencies are not preserved. The union of the new sets of FDs is CE→A. However, the FD B→E is not in the union and cannot be derived. Thus the two sets of FDs are not equivalent.

For the FDs of 3.4.1b, the dependencies are preserved. The union of the new sets of FDs is AC→E and BC→D. This is precisely the same as the original set of FDs and thus the two sets of FDs are equivalent.

For the FDs of 3.4.1c, the dependencies are not preserved. The union of the new sets of FDs is B→D and A→E. The FDs A→D and D→E are not in the union and cannot be derived. Thus the two sets of FDs are not equivalent.

For the FDs of 3.4.1d, the dependencies are not preserved. The union of the new sets of FDs is AC→E. However, the FDs A→D, CD→E and E→D are not in the union and cannot be derived. Thus the two sets of FDs are not equivalent.

Exercise 3.5.1a

In the solution to Exercise 3.3.1a we found that there are 14 nontrivial 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 keys were AB, BC, and BD. Since all the attributes on the right sides of the FDs are prime, there are no 3NF violations.

Since there are no 3NF violations, it is not necessary to decompose the relation.

Exercise 3.5.1b

In the solution to Exercise 3.3.1b we found that there are 8 nontrivial dependencies. They are B→C, B→D, AB→C, AB→D, BC→D, BD→C, ABC→D and ABD→C.

We also found out that the only key is AB. FDs where the left side is not a superkey or the attributes on the right are not part of some key are 3NF violations. The 3NF violations are B→C, B→D, BC→D and BD→C.

Using algorithm 3.26, we can decompose into relations using the minimal basis B→C and

B→D. The resulting decomposed relations would be BC and BD. However, none of these two sets of attributes is a superkey. Thus we add relation AB to the result. The final set of decomposed relations is BC, BD and AB.

Exercise 3.5.1c

In the solution to Exercise 3.3.1c we found that there are 12 nontrivial dependencies. They are AB→C, BC→D, CD→A, AD→B, AB→D, AD→C, BC→A, CD→B, ABC→D, ABD→C, ACD→B and BCD→A.

We also found out that the keys are AB, AD, BC, and CD. Since all the attributes on the right sides of the FDs are prime, there are no 3NF violations.

Since there are no 3NF violations, it is not necessary to decompose the relation.

Exercise 3.5.1d

In the solution to Exercise 3.3.1d we found that there are 28 nontrivial dependencies. They are A→B, B→C, C→D, D→A, 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.

We also found out that the keys are A,B,C,D. Since all the attributes on the right sides

of the FDs are prime, there are no 3NF violations.

Since there are no 3NF violations, it is not necessary to decompose the relation.

Exercise 3.5.1e

In the solution to Exercise 3.3.1e we found that there are 16 nontrivial dependencies. They are AB→C, DE→C, B→D, AB→D, BC→D, BE→C, BE→D, ABC→D, ABD→C, ABE→C, ABE→D, ADE→C, BCE→D, BDE→C, ABCE→D, and ABDE→C.

We also found out that the only key is ABE. FDs where the left side is not a superkey or the attributes on the right are not part of some key are 3NF violations. The 3NF

violations are AB→C, DE→C, B→D, AB→D, BC→D, BE→C, BE→D, ABC→D, ABD→C, ADE→C, BCE→D and BDE→C.

Using algorithm 3.26, we can decompose into relations using the minimal basis AB→C,

DE→C and B→D. The resulting decomposed relations would be ABC, CDE and BD. However,

none of these three sets of attributes is a superkey. Thus we add relation ABE to the result. The final set of decomposed relations is ABC, CDE, BD and ABE.

Exercise 3.5.1f

In the solution to Exercise 3.3.1f we found that there are 41 nontrivial dependencies. They are: C→B, C→D, C→E, D→B, D→E, AB→C, AB→D, AB→E, AC→B, AC→D, AC→E, AD→B, AD→C, AD→E, BC→D, BC→E, BD→E, CD→B, CD→E, CE→B, CE→D, DE→B, ABC→D, ABC→E,

ABD→C, ABD→E, ABE→C, ABE→D, ACD→B, ACD→E, ACE→B, ACE→D, ADE→B, ADE→C, BCD→E, BCE→D, CDE→B, ABCD→E, ABCE→D, ABDE→C and ACDE→B.

We also found out that the keys are AB, AC and AD. FDs where the left side is not a superkey or the attributes on the right are not part of some key are 3NF violations. The

3NF violations are C→E, D→E, BC→E, BD→E, CD→E and BCD→E.

Using algorithm 3.26, we can decompose into relations using the minimal basis AB→C, C→D, D→B and D→E. The resulting decomposed relations would be ABC, CD, BD and DE. Since relation ABC contains a key, we can stop with the decomposition. The final set of decomposed relations is ABC, CD, BD and DE.

Exercise 3.5.2a

The usual procedure to find the keys would be to take the closure of all 63 nonempty subsets. However, if we notice that none of the right sides of the FDs contains

attributes H and S. Thus we know that attributes H and S must be part of any key. We eventually will find out that HS is the only key for the Courses relation.

Exercise 3.5.2b

The first step to verify that the given FDs are their own minimal basis is to check to see if any of the FDs can be removed. However, if we remove any one of the five FDs, the remaining four FDs do not imply the removed FD.

The second step to verify that the given FDs are their own minimal basis is to check to see if any of the left sides of an FD can have one or more attributes removed without losing the dependencies. However, this is not the case for the four FDs that contain two attributes on the left side.

Thus, the given set of FDs has been verified to be the minimal basis.

Exercise 3.5.2c

Since the only key is HS, the given set of FDs has some dependencies that violate 3NF. We also know that the given set of FDs is a minimal basis. Thus the decomposed relations are CT, HRC, HTR, HSR and CSG. Since the relation HSR contains a key, we do not need to add an additional relation. The final set of decomposed relations is CT, HRC, HTR, HSR and CSG.

None of the decomposed relations violate BCNF. This can be verified by projecting the given set of FDs onto each of the decomposed relations. All of the projections of FDs have superkeys on their left sides.

Exercise 3.5.3

The usual procedure to find the keys would be to take the closure of all 63 nonempty subsets. However, if we notice that none of the right sides of the FDs contains

attributes I and S. Thus we know that attributes I and S must be part of any key. We eventually will find out that IS is the only key for the Stocks relation.

The first step to verify that the given FDs are their own minimal basis is to check to see if any of the FDs can be removed. However, if we remove any one of the four FDs, the remaining three FDs do not imply the removed FD.

The second step to verify that the given FDs are their own minimal basis is to check to see if any of the left sides of an FD can have one or more attributes removed without losing the dependencies. However, this is not the case for the one FD that contains two attributes on the left side.

Thus, the given set of FDs has been verified to be the minimal basis.

Since the only key is IS, the given set of FDs has some dependencies that violate 3NF. We also know that the given set of FDs is a minimal basis. Thus the decomposed relations are SD, IB, ISQ and BO. Since the relation ISQ contains a key, we do not need to add an additional relation. The final set of decomposed relations is SD, IB, ISQ and BO.

Exercise 3.5.4

This is the initial tableau:

This is the final tableau after applying FDs AB→C, C→B and A→D.

Since there is an unsubscripted row, the decomposition for R is lossless for this set of FDs.

Exercise 3.5.5

Suppose that our relation relates to the work environment and has three attributes, Name, RoomNumber and ComputerID. Suppose also that only the following FDs hold:

Name→RoomNumber

ComputerID→RoomNumber

The first FD says that a person can only be in one office at a time. The second FD says that each computer can only be associated with one office at a time (i.e. the computer is fixed to each room). None of the left sides of the FDs are keys and the respective right side attributes do not belong to any key. Using algorithm 3.20 to decompose, we take the violating FD Name→RoomNumber and use it to decompose the initial relation into two smaller relations:

R1(Name,RoomNumber)

R2(Name,ComputerID)

We do not need to further decompose the two relations so we are done. However, we have lost the FD ComputerID→RoomNumber with this decomposition. Suppose that ‘John Doe’

works in room number ‘1’. Suppose also that computer ID ‘A’ is located in room ‘1’. The original relation would contain the tuple:

{‘John Doe’, 1, ‘A’}

If we follow the decomposition, we would expect the tuple above to be broken into two smaller tuples:

{‘John Doe’, 1}

{‘John Doe’, ‘A’}

Suppose that ‘John Doe’ moves to room 2 and ‘Jane Doe’ moves into room 1 and that

‘Jane Doe’ is using computer ID ‘A’. The following tuples would be in the respective relations:

{‘John Doe’, 2}

{‘Jane Doe’, 1}

{‘John Doe’, ‘A’}

{‘Jane Doe’, ‘A’}

If we were to join back the two relations, we would get:

{‘John Doe’, 2, ‘A’}

{‘Jane Doe’, 1, ‘A’}

which violates the FD ComputerID→RoomNumber

Exercise 3.6.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 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.6.2a

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→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. First, 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 any 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, child Social Security number, and child birthdate. That is the multivalued dependency

ssNochildSSNo→→childName childBirthdate

Similarly, an automobile serial number 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, autoMake

Exercise 3.6.2b

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, childBirthdate}

{ssNo, autoSerialNo, autoMake}

Functional dependencies force us to decompose the second and third of these.

Exercise 3.6.3a

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 separate 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 is AB, AC, and AD.

Exercise 3.6.3b

数据库系统原理及应用教程第四版课后答案

第一章 1、(1)数据:数据用于载荷信息的物理符号。 (2)数据的特征;○1数据有“型”与“值”之分;○2数据受数据类型与取值范围的约束;○3数据有定性表示与定量之分;○4数据应具有载体与多种表现形式。 3、(1)数据管理的功能: ○1组织与保存数据功能,即将收集到的数据合理地分类组织,将其存储在物理载体上,使数据能够长期的被保存; ○2数据维护功能,即根据需要随时进行插入新数据,修改原数据与删除失效数据的操作; ○3数据查询与数据统计功能,即快速的得到需要的正确数据,满足各种使用要求;○4数据的安全与完整性控制功能,即能保护数据的安全与完整性。 (2)数据管理的目标:收集完整的信息,将信息用数据表示,按数据结构合理科学的组织并保存数据;为各种使用快速地提供需要的数据,并保护数据的安全与完整性。 4、(1)数据库:就是数据管理的新方法与技术,她就是一个按数据结构来存储与管理数据的计算机软件系统。 (2)数据库中的数据具有的特点:○1数据库中的数据具有整体性,即数据库中的数据要保持自身完整的数据结构;○2数据库中的数据具有数据共享性,不同的用户可以按各自的用法使用数据库中的数据,多个用户可以同时共享数据库中的数据资源。 5、(1)数据库管理系统:它就是专门用于管理数据库的计算机管理软件。数据库管理系统能够为数据库提供数据的定义、建立、维护、查询与统计等操作功能,并完成对数据完整性、安全性进行操作的功能。 (2)数据库管理系统主要功能:就是数据存储、数据操作与数据控制功能。其数据存储与数据操作就是:数据库的定义功能,指未说明库中的数据情况而进行的建立数据库结构的操作;数据库建立功能,指大批数据录入到数据库的操作,它使得库中含有需要保护的数据记录;数据库维护功能,指对数据的插入、删除与修改操纵,其操作做能满足库中信息变化或更新的需求;数据库查询与统计功能,指通过对数据库的访问,为实际应用提供需要的数据。数据库管理系统的数据控制功能为:数据安全性控制功能,即为了保证数据库的数据安全可靠,防止不合法的使用造成数据库泄露与破坏,也就就是避免数据被人偷瞧、篡改或破坏;数据库完整性控制功能,指为了保证数据库中的数据的正确、有效与相容,防止不合语意的错误数据被输入或输出。 14、(1)数据库系统的软件由几部分组成?数据库系统的软件中包括操作系统(OS)、数据库管理系统(DBMS)、主语言系统、应用程序软件与用户数据库。 (2)作用:①操作系统或汉字操作系统:操作系统就是所有计算机软件的基础,在数据库系统中它起着支持DBMS及主语言系统工作的作用。如果管理的信息中有汉字,则需要中文操作系统的支持,以提供汉字的输入、输出方法与汉字信息的处理方法。②数据库管理系统与主语言系统:数据库管理系统就是为定义、建立、维护、使用及控制数据库而提供的有关数据管理的系统软件。主语言系统就是为应用程序提供的诸如程序控制、数据输入输出、功能函数、图形处理、计算方法等数据处理功能的系统软件。③应用开发工具软件:应用开发工具就是DBMS系统为应用开发人员与最终用户提供的高效率、多功能的应用生成器、第四代计算机语言等各种软件工具.如报表生成器、表单生成器、查询与视图设计器等,它们为数据库系统的开发与使用提供了良好的环境与帮助。④应用系统及数据库:数据库应用系统包括为特定的应用环境建立的数据库、开发的各类应用程序及编写的文档资料,它们就是一个有机整体。通过运行数据库应用系统,可以实现对数据库中数据的维护、查询、管理与处理操作。(3)关系:

数据库系统基础教程(第二版)课后习题答案

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 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, relating 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 make 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;

数据库应用基础教程答案

数据库应用基础教程答案 【篇一:access数据库应用基础教程(第三版)习题及答 案】 txt>程(第三版)习题集答案 第1章数据库系统概述 1. 什么是数据库?什么是数据库系统?答:数据库(database) 是存放数据的仓库,严格的讲,数据库是长期存储在计算机内,有组 织的,可共享的大量数据集合。 数据库系统(database systems),是由数据库及其管理软件组成的系统。它是为适应数据处理的需要而发展起来的一种较为理想的 数据处理的核心机构。它是一个实际可运行的存储、维护和应用系 统提供数据的软件系统,是存储介质、处理对象和管理系统的集合体。 2. 什么是数据库管理系统?它有哪些主要功能? 答:数据库管理系统(database management system)是一种操纵 和管理数据 库的大型软件,用于建立、使用和维护数据库,简称dbms。它对数据库进行统一的管理和控制,以保证数据库的安全性和完整性。 数据库管理系统的主要功能有:数据定义、数据操作、数据库的运 行管理、数据组织、数据库的保护、数据库的维护和通信。 3. 说出几种常用的数据模型。 答:层次模型、网状模型、关系模型。 4. 什么是关系模型? 答:关系模型是用二维表的形式表示实体和实体间联系的数据模型。 5. 简述数据库设计的步骤。 答:需求分析、概念结构设计、逻辑结构设计、物理结构设计、数 据库的建立和测试、数据库运行和维护。 第2章 sql 语言简介 1. 什么是sql语言?sql语言具有哪些特点和功能? 答:sql是一种数据库查询和程序设计语言,用于存取数据以及查询更新和管理关系 数据库系统。 sql的特点和功能有:查询,操作,定义和控制四个方面,sql语言 具有高度的非过程化,语言简洁,语义明显,语法结构简单,直观

数据库基础教程CH1 答案

Exercises 2.3.1 In this exercise we introduce one of our running examples of a relational database schema. The database schema consists of four relations, whose schemas are: Product (maker, model, type) PC (model, speed, ram. hd, price) Laptop (model, speed, ram, hd, screen, price) Printer (model, color, type, price) The Product relation gives the manufacturer, model number and type (PC, laptop, or printer) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types; that assumption is not realistic, and a real database would include a code for the manufacturer as part of the model number. The PC relation gives for each model number that is a PC the speed (of the processor, in gigahertz), the amount of RAM (in megabytes), the size of the hard disk (in gigabytes), and the price. The Laptop relation is similar, except that the screen size (in inches) is also included. The Printer relation records for each printer model whether the printer produces color output (true, if so), the process type (laser or ink-jet, typically), and the price. Write the following declarations: a) A suitable schema for relation Product. b) A suitable schema for relation PC. c) A suitable schema for relation Laptop. d) A suitable schema for relation Printer. e)An alteration to your Printer schema from (d) to delete the attribute color. f)An alteration to your Laptop schema from (c) to add the attribute od (optical-disk type, e.g., cd or dvd). Let the default value for this attribute be 'none' if the laptop does not have an optical disk. Exercise 2.3.1a CREATE TABLE Product ( maker CHAR(30), model CHAR(10) PRIMARY KEY, type CHAR(15) ); Exercise 2.3.1b CREATE TABLE PC ( model CHAR(30), speed DECIMAL(4,2), ram INTEGER, hd INTEGER, price DECIMAL(7,2) );

数据库原理及应用教程第4版习题参考答案

习题参考答案 第1章习题参考答案 一、选择题 1. C 2. B 3. D 4. C 5. D 6. B 7. A 8. B 9. D 10. B 11. C 12. D 13. D 14. D 15. B 16. C 17. D 18. A 19. D 20. A 21. D 22. D 23. C 24. A 25. C 二、填空题 1. 数据库系统阶段 2. 关系 3. 物理独立性 4. 操作系统 5. 数据库管理系统(DBMS) 6. 一对多 7. 独立性 8. 完整性控制 9. 逻辑独立性 10. 关系模型 11. 概念结构(逻辑) 12. 树有向图二维表嵌套和递归 13. 宿主语言(或主语言) 14. 数据字典 15. 单用户结构主从式结构分布式结构客户/服务器结构浏览器/服务器结构 16. 现实世界信息世界计算机世界 三、简答题 1、简述数据库管理技术发展的三个阶段。各阶段的特点是什么? 答:数据库管理技术经历了人工管理阶段、文件系统阶段和数据库系统阶段。 (1)、人工管理数据的特点: A、数据不保存。 B、系统没有专用的软件对数据进行管理。 C、数据不共

享。D、数据不具有独立性。 (2)、文件系统阶段的特点: A、数据以文件的形式长期保存。 B、由文件系统管理数据。 C、程序与数据之间有一定的独立性。 D、文件的形式已经多样化 E、数据具有一定的共享性 (3)、数据库系统管理阶段特点: A、数据结构化。 B、数据共享性高、冗余度底。 C、数据独立性高。 D、有统一的数据控制功能。 2、从程序和数据之间的关系来分析文件系统和数据库系统之间的区别和联系 答:数据管理的规模日趋增大,数据量急剧增加,文件管理系统已不能适应要求,数据库管理技术为用户提供了更广泛的数据共享和更高的数据独立性,进一步减少了数据的余度,并为用户提供了方便的操作使用接口。数据库系统对数据的管理方式与文件管理系统不同,它把所有应用程序中使用的数据汇集起来,以记录为单位存储,在数据库管理系统的监督和管理下使用,因此数据库中的数据是集成的,每个用户享用其中的一部分。 3、简述数据库、数据库管理系统、数据库系统三个概念的含义和联系。答:数据库是指存储在计算机内、有组织的、可共享的数据集合。 数据库管理系统是软件系统的一个重要组成部分,它通过借助操作系统完成对硬件的访问,并对数据库的数据进行存取、维护和管理。 数据库系统是指计算机系统中引入数据库后的系统构成。它主要由数据库、数据库用户、计算机硬件系统和计算机软件系统几部分组成。 三者的联系是:数据库系统包括数据库和数据库管理系统。数据库系统主要通过数据库管理系统对数据库进行管理的。 4、数据库系统包括哪几个主要组成部分?各部分的功能是什么?画出整个数据库系统的层次结构图。 答:数据库系统包括:数据库、数据库用户、软件系统和硬件系统。 数据库主要是来保存数据的。 数据库用户是对数据库进行使用的人,主要对数据库进行存储、维护和检索等操作。 软件系统主要完成对数据库的资源管理、完成各种操作请求。 硬件系统主要完成数据库的一些物理上的操作,如物理存储、输入输出等。

南京理工大学《数据库系统基础教程》试题和答案

一、选择题60(选择一个最合适的答案,在答题纸上涂黑) 1.一个事务中的一组更新操作是一个整体,要么全部执行,要么全部不执行。这是事务的:A.原子性B.一致性 C.隔离性 D.持久性 2.在数据库的三级模式结构中,描述一个数据库中全体数据的全局逻辑结构和特性的是:A.外模式 B.模式 C.存储模式D.模式 3.关于联系的多重性,下面哪种说法不正确? A.一个多对多的联系中允许多对一的情形。 B.一个多对多的联系中允许一对一的情形。 C.一个多对一的联系中允许一对一的情形。 D.一个多对一的联系中允许多对多的情形。 4.考虑学校里的"学生"和"课程"之间的联系,该联系的多重性应该是: A. 一对一 B. 多对一 C. 一对多 D. 多对多 5.下面哪种约束要求一组属性在同一实体集任意两个不同实体上的取值不同。 A. 键(key)约束。 B. 单值约束。 C. 参照完整性。 D. 域(domain)约束 6.关系模型要求各元组的每个分量的值必须是原子性的。对原子性,下面哪种解释不正确:A.每个属性都没有部结构。 B.每个属性都不可再分解。 C.各属性值应属于某种基本数据类型。 D.属性值不允许为NULL。 7.对于一个关系的属性(列)集合和元组(行)集合,下面哪种说法不正确: A.改变属性的排列次序不影响该关系。 B.改变元组的排列次序不影响该关系。 C.改变元组的排列次序会改变该关系。 D.关系的模式包括其名称及其属性集合。 8.若R是实体集R1与R2间的一个多对多联系,将其转换为关系R',哪种说法不正确:A.R'属性应包括R1与R2的所有属性。 B.R'属性应包括R1与R2的键属性。 C.R1与R2的键属性共同构成R'的键。 D.R'的属性应包括R自身定义的属性。 9.关于函数依赖的判断,下面哪种说法不正确? A.若任意两元组在属性A上一致,在B上也一致,则有A → B成立。 B.若任意两元组在属性A上一致,在B上不一致,则A → B不成立。 C.若任意两元组在属性A上不可能一致,则不管在B上是否一致,有A → B成立。

Access数据库应用基础教程(第三版)习题及答案

Access数据库应用基础教程(第三版)习题集答案 第1章数据库系统概述 1. 什么是数据库?什么是数据库系统?答:数据库(database)是存放数据的仓库,严格的讲,数据库是长期存储在计算机内,有组织的,可共享的大量数据集合。 数据库系统(database systems),是由数据库及其管理软件组成的系统。它是为适应数据处理的需要而发展起来的一种较为理想的数据处理的核心机构。它是一个实际可运行的存储、维护和应用系统提供数据的软件系统,是存储介质、处理对象和管理系统的集合体。 2. 什么是数据库管理系统?它有哪些主要功能? 答:数据库管理系统(database management system)是一种操纵和管理数据

库的大型软件,用于建立、使用和维护数据库,简称dbms。它对数据库进行统一的管理和控制,以保证数据库的安全性和完整性。数据库管理系统的主要功能有:数据定义、数据操作、数据库的运行管理、数据组织、数据库的保护、数据库的维护和通信。 3. 说出几种常用的数据模型。 答:层次模型、网状模型、关系模型。4. 什么是关系模型? 答:关系模型是用二维表的形式表示实体和实体间联系的数据模型。 5. 简述数据库设计的步骤。 答:需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库的建立和测试、数据库运行和维护。 第2章 SQL 语言简介 1. 什么是SQL语言?SQL语言具有哪些特点和功能? 答:SQL是一种数据库查询和程序设计语言,用于存取数据以及查询更新和管理关系

数据库系统。 SQL的特点和功能有:查询,操作,定义和控制四个方面,SQL语言具有高度的非过程化,语言简洁,语义明显,语法结构简单,直观易懂的特点。SQL语言即可以作为独立语言使用,用户可以在终端键盘上直接键入SQL命令对数据库进行操作,也可以作为嵌入式语言,嵌入到其他高级语言中。 2. SQL语言包含哪几个部分? 答:SQL语言包含4个部分:数据定义语言(DDL-Data Definition Language)、数据查询语言(DQL-Data Query Language)、数据操纵语言(DML-Data Manipulation Language)、数据控制语言(DCL-Data Control Language) 3. 在联接查询中,包含哪几类联接?答:联接可分为3类: (1)内部联接(典型的联接运算,使用类似于 = 或 <> 的比较运算符)。内部联接使用比较运算符根据每个表的通用列中的值匹配两个表中的行。内部联接包括同等

(完整word版)数据库系统原理及应用教程第四版课后答案

第一章 1、(1)数据:数据用于载荷信息的物理符号。 (2)数据的特征;○1数据有“型”和“值”之分;○2数据受数据类型和取值范围的约束;○3数据有定性表示和定量之分;○4数据应具有载体和多种表现形式。 3、(1)数据管理的功能:○1组织和保存数据功能,即将收集到的数据合理地分类组织,将其存储在物理载体上,使数据能够长期的被保存;○2数据维护功能,即根据需要随时进行插入新数据,修改原数据和删除失效数据的操作;○3数据查询和数据统计功能,即快速的得到需要的正确数据,满足各种使用要求;○4数据的安全和完整性控制功能,即能保护数据的安全和完整性。 (2)数据管理的目标:收集完整的信息,将信息用数据表示,按数据结构合理科学的组织并保存数据;为各种使用快速地提供需要的数据,并保护数据的安全和完整性。 4、(1)数据库:是数据管理的新方法和技术,他是一个按数据结构来存储和管理数据的计算机软件系统。 (2)数据库中的数据具有的特点:○1数据库中的数据具有整体性,即数据库中的数据要保持自身完整的数据结构;○2数据库中的数据具有数据共享性,不同的用户可以按各自的用法使用数据库中的数据,多个用户可以同时共享数据库中的数据资源。 5、(1)数据库管理系统:它是专门用于管理数据库的计算机管理软件。数据库管理系统能够为数据库提供数据的定义、建立、维护、查询和统计等操作功能,并完成对数据完整性、安全性进行操作的功能。 (2)数据库管理系统主要功能:是数据存储、数据操作和数据控制功能。其数据存储和数据操作是:数据库的定义功能,指未说明库中的数据情况而进行的建立数据库结构的操作;数据库建立功能,指大批数据录入到数据库的操作,它使得库中含有需要保护的数据记录;数据库维护功能,指对数据的插入、删除和修改操纵,其操作做能满足库中信息变化或更新的需求;数据库查询和统计功能,指通过对数据库的访问,为实际应用提供需要的数据。数据库管理系统的数据控制功能为:数据安全性控制功能,即为了保证数据库的数据安全可靠,防止不合法的使用造成数据库泄露和破坏,也就是避免数据被人偷看、篡改或破坏;数据库完整性控制功能,指为了保证数据库中的数据的正确、有效和相容,防止不合语意的错误数据被输入或输出。 14、(1)数据库系统的软件由几部分组成?数据库系统的软件中包括操作系统(OS)、数据库管理系统(DBMS)、主语言系统、应用程序软件和用户数据库。 (2)作用:①操作系统或汉字操作系统:操作系统是所有计算机软件的基础,在数据库系统中它起着支持DBMS及主语言系统工作的作用。如果管理的信息中有汉字,则需要中文操作系统的支持,以提供汉字的输入、输出方法和汉字信息的处理方法。②数据库管理系统和主语言系统:数据库管理系统是为定义、建立、维护、使用及控制数据库而提供的有关数据管理的系统软件。主语言系统是为应用程序提供的诸如程序控制、数据输入输出、功能函数、图形处理、计算方法等数据处理功能的系统软件。③应用开发工具软件:应用开发工具是DBMS系统为应用开发人员和最终用户提供的高效率、多功能的应用生成器、第四代计算机语言等各种软件工具.如报表生成器、表单生成器、查询和视图设计器等,它们为数据库系统的开发和使用提供了良好的环境和帮助。④应用系统及数据库:数据库应用系统包括为特定的应用环境建立的数据库、开发的各类应用程序及编写的文档资料,它们是一个有机整体。通过运行数据库应用系统,可以实现对数据库中数据的维护、查询、管理和处理操作。(3)关系:

数据库系统基础教程(第二版)课后习题答案2

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 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, relating 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 make 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;

2019年大学《数据库原理及应用教程》试题及答案

《数据库原理及应用教程》试题及答案 一、选择题 1、下面叙述正确的是(C) A. 算法的执行效率与数据的存储结构无关 B. 算法的空间复杂度是指算法程序中指令(或语句)的条数 C. 算法的有穷性是指算法必须能在执行有限个步骤之后终止 D. 以上三种描述都不对 (2) 以下数据结构中不属于线性数据结构的是(C) A. 队列 B. 线性表 C. 二叉树 D. 栈 (3) 在一棵二叉树上第5层的结点数最多是(B) 注:由公式2k-1得 A. 8 B. 16 C. 32 D. 15 (4) 下面描述中,符合结构化程序设计风格的是(A) A. 使用顺序、选择和重复(循环)三种基本控制结构表示程序的控制逻辑 B. 模块只有一个入口,可以有多个出口 C. 注重提高程序的执行效率 D. 不使用goto语句 (5) 下面概念中,不属于面向对象方法的是(D) 注:P55-58 A. 对象 B. 继承 C. 类 D. 过程调用 (6) 在结构化方法中,用数据流程图(DFD)作为描述工具的软件开发阶段是 (B) A. 可行性分析 B. 需求分析 C. 详细设计 D. 程序编码 (7) 在软件开发中,下面任务不属于设计阶段的是(D) A. 数据结构设计 B. 给出系统模块结构

C. 定义模块算法 D. 定义需求并建立系统模型 (8) 数据库系统的核心是(B) A. 数据模型 B. 数据库管理系统 C. 软件工具 D. 数据库 (9) 下列叙述中正确的是(C) A.数据库是一个独立的系统,不需要操作系统的支持 B.数据库设计是指设计数据库管理系统 C.数据库技术的根本目标是要解决数据共享的问题 D.数据库系统中,数据的物理结构必须与逻辑结构一致 (10) 下列模式中,能够给出数据库物理存储结构与物理存取方法的是(A) 注:P108 A. 内模式 B. 外模式 C. 概念模式 D. 逻辑模式 (11) 算法的时间复杂度是指(C) A. 执行算法程序所需要的时间 B. 算法程序的长度 C. 算法执行过程中所需要的基本运算次数 D. 算法程序中的指令条数 (12) 算法的空间复杂度是指(D) A. 算法程序的长度 B. 算法程序中的指令条数 C. 算法程序所占的存储空间 D. 算法执行过程中所需要的存储空间 (13) 设一棵完全二叉树共有699个结点,则在该二叉树中的叶子结点数为(B) 注:利用公式n=n0+n1+n2、n0=n2+1和完全二叉数的特点可求出 A. 349 B. 350 C. 255 D. 351 (14) 结构化程序设计主要强调的是(B) A.程序的规模 B.程序的易读性

基础教程-SQL_Server2008_创建数据库以及数据库基础详解资料

第2章创建数据库 《转自网上-初学用到得资料,感觉很好,就分享给大家,仅仅是转载》 数据库是用来存储数据的空间,它作为存储结构的最高层次是其他一切数据库操作的基础。用户可以通过创建数据库来存储不同类别或者形式的数据。 因此,在本章用户将详细地学习针对数据库的基本操作和数据库的日常管理操作,即如何创建数据库、对数据/日志文件进行操作、生成数据库快照等日常操作。 本章学习目标: ?了解数据库对象及构成 ?掌握创建数据库的两种方法 ?掌握管理数据库的方法 ?了解数据库快照 2.1 SQL Server数据库概述 SQL Server中的数据库是由数据表的集合组成的,每个数据表中包含数据以及其他数据库对象,这些对象包括视图、索引、存储过程和触发器等。 数据库系统使用一组操作系统文件来映射数据库管理系统中保存的数据库,数据库中的所有数据和对象都存储在其映射的操作系统文件中。这些操作系统文件可以是数据文件或日志文件。 要熟练地理解和掌握数据库,必须对数据库的一些基本概念及构成有一个清楚的认识。 2.1.1 常见数据库对象 数据库中存储了表、视图、索引、存储过程、触发器等数据库对象,这些数据库对象存储在系统数据库或用户数据库中,用来保存SQL Server数据库的基本信息及用户自定义的数据操作等。 1.表与记录 表是数据库中实际存储数据的对象。由于数据库中的其他所有对象都依赖于表,因此可以将表理解为数据库的基本组件。一个数据库可以有多个行和列,并且每列包含特定类型的信息。列和行也可以称为字段与记录。字段是表中纵向元素,包含同一类型的信息,例如读者卡号(Rcert)、姓名(name)和性别(Sex)等;字段组成记录,记录是表中的横向元素,包含有

数据库系统基础教程课后答案第五章

Exercise 5.1.1 As a set: Average = 2.37 As a bag: Average = 2.48 Exercise 5.1.2 As a set:

Average = 218 As a bag: Average = 215 Exercise 5.1.3a As a set:

As a bag: Exercise 5.1.3b πbore(Ships Classes) Exercise 5.1.4a For bags: On the left-hand side: Given bags R and S where a tuple t appears n and m times respectively, the union of bags R and S will have tuple t appear n + m times. The further union of bag T with the tuple t appearing o times will have tuple t appear n + m + o times in the final result. On the right-hand side: Given bags S and T where a tuple t appears m and o times respectively, the union of bags R and S will have tuple t appear m + o times. The further union of bag R with the tuple t appearing n times will have tuple t appear m + o + n times in the final result. For sets: This is a similar case when dealing with bags except the tuple t can only appear at most once in each set. The tuple t only appears in the result if all the sets have the tuple t. Otherwise, the tuple t will not appear in the result. Since we cannot have duplicates, the result only has at most one copy of the tuple t. Exercise 5.1.4b For bags: On the left-hand side:

数据库基础教程课后习题答案顾韵华

习题1 1、简述数据库系统的特点。 答:数据库系统的特点有: 1)数据结构化 在数据库系统中,采用统一的数据模型,将整个组织的数据组织为一个整体;数据不再仅面向特定应用,而是面向全组织的;不仅数据内部是结构化的,而且整体是结构化的,能较好地反映现实世界中各实体间的联系。这种整体结构化有利于实现数据共享,保证数据和应用程序之间的独立性。 2)数据共享性高、冗余度低、易于扩充 数据库中的数据能够被多个用户、多个应用程序共享。数据库中相同的数据不会多次重复出现,数据冗余度降低,并可避免由于数据冗余度大而带来的数据冲突问题。同时,当应用需求发生改变或增加时,只需重新选择不同的子集,或增加数据即可满足。 3)数据独立性高 数据独立性是由DBMS 的二级映像功能来保证的。数据独立于应用程序,降低了应用程序的维护成本。 4)数据统一管理与控制 数据库中的数据由数据库管理系统(DBMS )统一管理与控制,应用程序对数据的访问均经由DBMS 。DBMS 提供四个方面的数据控制功能:并发访问控制、数据完整性、数据安全性保护、数据库恢复。 2、什么是数据库系统? 答:在计算机系统上引入数据库技术就构成一个数据库系统(DataBase System ,DBS )。数据库系统是指带有数据库并利用数据库技术进行数据管理的计算机系统。DBS 有两个基本要素:一是DBS 首先是一个计算机系统;二是该系统的目标是存储数据并支持用户查询和更新所需要的数据。 3、简述数据库系统的组成。 答:数据库系统一般由数据库、数据库管理系统(及其开发工具)、数据库管理员(DataBase Administrator ,DBA )和用户组成。 4、试述数据库系统的三级模式结构。这种结构的优点是什么? 答:数据库系统的三级模式结构是指数据库系统是由外模式、模式和内模式三级构成,同时包含了二级映像,即外模式/模式映像、模式/内模式映像,如下图所示。 数据库应用1…… 外模式A 外模式B 模式 应用2应用3应用4应用5…… 模式 外模式/模式映像 模式/内模式映像 数据库系统的这种结构具有以下优点: (1)保证数据独立性。将外模式与模式分开,保证了数据的逻辑独立性;将内模式与模式分开,保证了数据的物理独立性。 (2)有利于数据共享,减少了数据冗余。 (3)有利于数据的安全性。不同的用户在各自的外模式下根据要求操作数据,只能对

SQLServer2008数据库应用教程课后答案

第1章数据库基础 一、单项选择题 1.C 2.A 3.C 4.D 5.D 6.B 7.A 8.B 9.B 10.D 11.C 12.A 13.C 14.B 15.A 16.B 17.A 18.D 19.B 20.B 21.A; D 22.A 23.C 24.D 25.B 26.B 27.B 28.D 29.B 30.B 二、填空题 1.概念;数据 2.属性 3.码 4.一对一联系;一对多(或多对一)联系;多对多联系 5.候选码 6.候选码 7.关系名(属性1,属性2,…,属性n) 8.关系数据结构;关系操作集合;关系完整性约束 9.实体;参照;用户定义的;实体;参照 10.空植 11.需求分析阶段;概念结构设计阶段;逻辑结构设计阶段;物理结构设计阶段;数据库实施阶段;数据库运行和维护阶段 12.准确了解并分析用户对系统的要求,尤其是用户的信息要求、处理要求、安全性与完整性要求,确定所要开发的应用系统的目标,产生用户和设计者都能接受的需求说明书,做为下一步数据库概念结构设计的基础。 13.将需求分析得到的用户需求抽象为信息结构即概念模型。 14.将概念结构进一步转化为某一DBMS支持的数据模型,并对其进行优化。 15.为逻辑数据模型选取一个最适合应用环境的物理结构,包括数据库在物理设备上的存储结构和存取方法。 三、指出以下各缩写的英文意思和中文意思 1.DB:DataBase 2.DBMS:Database Management System 3.RDBMS: 4.DBS:DataBase System 5.DBA:Relational Database Management System 6.NF:Normal Form 7.DDL:Data Definition Language 四、按题目要求回答问题

Oracle11g数据库基础教程-参考答案

Oracle11g数据库基础教程 参考答案

第1章Oracle 11g数据库安装与配置1.简答题 (1) 企业版数据库服务器包含所有的数据库组件,主要针对高端的应用环境,适用于安全性和性能要求较高的联机事务处理(OLTP)、查询密集型的数据仓库和要求较高的Internet应用程序:标准版数据库服务器提供大部分核心的数据库功能和特性,适合于工作组或部门级的应用程序:个人版数据库服务器只提供基本数据库管理功能和特性,适合单用户的开发环境,为用户提供开发测试平台。 (2) 常用数据库类型包括事务处理类、数据仓库类以通用类型。其中事务处理类型主要针对具有大量并发用户连接,并且用户主要执行简单事务处理的应用环境。事务处理数据库的典型应用有银行系统数据库、Internet电子商务数据库、证券交易系统数据库等。对于需要较高的可用性和事务处理性能、存在大量用户并行访问相同数据以及需要较高恢复性能的数据库环境,事务处理类型的配置可以提供最佳性能;数据仓库类型的数据库主要针对有大量的对某个主题进行复杂查询的应用环境。数据仓库的典型应用有客户订单研究、支持呼叫、销售预测、采购模式以及其他战略性业务问题的历史数据研究。对于需要对大量数据进行快速访问,以及复杂查询的数据库环境,数据仓库类型配置是最佳选择;通用类型配置的数据库是事务处理数据库与数据仓库配置的折衷方案。既可以支持大量并发用户的事务处理,又可以快速对大量历史数据进行复杂的数据扫描和处理。 (3) 数据库名可以由字母、数字、下划线(_)、#和美元符号($)组成,且必须以字母开头,长度不超过30个字符。在单机环境中,可以不设置域名,域名长度不能超过128个字符。Oracle服务标识符(SID)是一个Oracle实例的唯一名称标识,长度不能超过12个字符。(4) ● OracleServiceORCL:数据库服务(数据库实例),是Oracle核心服务,是数据库启 动的基础,只有该服务启动,Oracle数据库才能正常启动。(必须启动) ● OracleOraDb11g_home1TNSListener:监听器服务,该服务只有在远程访问数据库时 才需要(无论远程计算机还是本地计算机,凡是通过Oracle Net网络协议连接数据库都属于远程访问)。(必须启动) ● OracleOraDb11g_home1ConfigurationManager:配置Oracle启动时的参数的服务。(非 必须启动) ● OracleOraDb11g_home1ClrAgent:提供对.NET支持的Oracle数据库扩展服务。(非 必须启动) ● OracleJobSchedulerORCL:数据库作业调度服务。(非必须启动) ● OracleDBConsoleorcl:Oracle控制台服务,即企业管理器服务。只有该服务启动了, 才可以使用Web方式的企业管理器管理数据库。(非必须启动) ● OracleVssWriterORCL:是Oracle对VSS提供支持的服务。(非必须启动) ● OracleMTSRecoveryService:是允许数据库充当一个微软事务服务器、COM/COM+ 对象和分布式环境下的事务资源管理器的服务。

VBNET数据库编程基础教程

VBNET数据库编程基础教程 众所周知,https://www.wendangku.net/doc/f71666870.html,自身并不具备对数据库进行操作的功能,它对数据库的处理是通过.NET FrameWork SDK中面向数据库编程的类库和微软的MDAC来实现的。其中,https://www.wendangku.net/doc/f71666870.html,又是.NET FrameWork SDK中重要的组成部分。要了解https://www.wendangku.net/doc/f71666870.html,的数据库编程,首先要明白https://www.wendangku.net/doc/f71666870.html,的工作原理以及相关的对象、方法、属性。本文将结合具体实例为你简单介绍https://www.wendangku.net/doc/f71666870.html,数据库访问对象以及https://www.wendangku.net/doc/f71666870.html,数据库编程基本方法。 一、https://www.wendangku.net/doc/f71666870.html,数据库访问对象 (一)https://www.wendangku.net/doc/f71666870.html,简介 https://www.wendangku.net/doc/f71666870.html,是由微软Microsoft ActiveX Data Object(ADO)升级发展而来的。是在.NET中创建分布式数据共享程序的开发接口。https://www.wendangku.net/doc/f71666870.html,的数据存取API提供两种数据访问方式,分别用来识别并处理两种类型的数据源,即SQL Server 7.0(及更高的版本)和可以通过OLE DB进行访问的其他数据源。为此ADO.NE T中包含了两个类库,System.Data.SQL库可以直接连接到SQL Server的数据,System.Data.ADO库可以用于其他通过OLE DB进行访问的数据源。如Acces s数据。 (二)https://www.wendangku.net/doc/f71666870.html,的名称空间 https://www.wendangku.net/doc/f71666870.html,是围绕System.Data基本名称空间设计,其他名称空间都是从Syste m.Data派生而来。它们使得https://www.wendangku.net/doc/f71666870.html,不仅访问DataBase中的数据,而且可以访问支持OLE DB的数据源。 当我们讨论https://www.wendangku.net/doc/f71666870.html,时,实际讨论的是System.Data和System.Data.OleDb 名称空间。这两个空间的所有类几乎都可以支持所有类型的数据源中的数据。这里我们讨论与后文实例有关的类。即OleDbconnection、OleDbDataAdapter、D ataSet和DataView。 上面列举的类中没有OleDb前缀的,派生自System.Data空间,有此前缀的派生自System.Data.OleDb空间。在使用中,如果要引用OleDb前缀的类,必须导入System.Data.OleDb名称空间。语法如下: Imports System.Data.OleDb 使用没有此前缀的类必须导入System.Data名称空间。语法如下: Imports System.Data

相关文档
相关文档 最新文档