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;

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 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-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 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., (Rangers, MLB) or (Rangers, NHL). The key for Players consists of the player's 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).

Database Systems: The Complete Book

Solutions for Chapter 3

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.

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

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)

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), 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 attributes, 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 capitalization 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 2^{n-1} such subsets, since each of the n-1 attributes A2 through An may independently be chosen in or out.

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.

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