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

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

Solutions for Section 2.2

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; 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.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 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.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 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.,

Solutions 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, number, day, row, seat)

Being a weak entity set, Bookings' relation has the keys for Customers and Flights and Bookings'

own attributes.

Ships(name, yearLaunched)

Solutions for Section 3.3

Exercise 3.3.1

Since 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. It looks like:

Depts(name, chair)

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), Courses and LabCourses are combined, as:

Depts(name, chair)

Courses(number, deptName, room, allocation)

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.

The key attributes are indicated by capitalization in the schema below:

Customers(SSNO, name, address, phone)

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

>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

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.

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)

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 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

AB->C. Note that BC->A is true, but follows logically from C->A, and therefore may be omitted

from our list.

Solutions for 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 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.