First Course in Database Systems: Solutions for Chapter 2

A First Course in Database Systems

Solutions for Chapter 2

Solutions for Section 2.1

Solutions for Section 2.2

Solutions for Section 2.3

Solutions for Section 2.4

Solutions for Section 2.5

Solutions for Section 2.6

Solutions for Section 2.7

Solutions for Section 2.1

Exercise 2.1.1

interface Customer {
    attribute string name;
    attribute string addr;
    attribute string phone;
    attribute integer ssNo;
    relationship Set<Account> ownsAccts
        inverse Account::ownedBy;
}

interface Account {
    attribute integer number;
    attribute string type;
    attribute real balance;
    relationship Set<Customer> ownedBy
        inverse Customer::ownsAccts
}

Exercise 2.1.5

interface Person {
    attribute string name;
    relationship Person motherOf
        inverse Person::childrenOfFemale
    relationship Person fatherOf
        inverse Person::childrenOfMale
    relationship Set<Person> children
        inverse Person::parentsOf
    relationship Set<Person> childrenOfFemale
        inverse Person::motherOf
    relationship Set<Person> childrenOfMale
        inverse Person::fatherOf
    relationship Set<Person> parentsOf
        inverse Person::children
}

Notice that there are six different relationships here. For example, the inverse of the relationship that connects a person to their (unique) mother is a relationship that connects a mother (i.e., a female person) to the set of her children. That relationship, which we call childrenOfFemale, is different from the children relationship, which connects anyone -- male or female -- to their children.

Exercise 2.1.9

A relationship R is its own inverse if and only if for every pair (a,b) in R, the pair (b,a) is also in R. In the terminology of set theory, the relation R is ``symmetric.''

Exercise 2.1.10

A type can never be suitable for both an attribute and a relationship. Every relationship type is built from a single interface (class) name. But an attribute type may not involve an interface name.

Return to Top

Solutions for Section 2.2

Exercise 2.2.1

Diagram in postscript

Exercise 2.2.7(a)

Diagram in postscript

Return to Top

Solutions for Section 2.3

Exercise 2.3.1

We do not like the use of an Address class here. If addresses had a many-many relationship with customers, then it would make sense to create an address class. However, customers have unique addresses, so we suggest making address an attribute of customer, instead. As the design stands, not only is the address class unnecessary, but it can actually be harmful. Since several customers may refer to the same address object, should one of them move, we might accidentally change the address of all.
The class AcctSet seems pointless to us. 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.

Return to Top

Solutions for Section 2.4

Exercise 2.4.1(a)

interface Ship {
    attribute string name;
    attribute integer displacement;
    attribute string type;
}

interface Gunship: Ship {
    attribute integer numberOfGuns;
    attribute integer bore;
}

interface Carrier: Ship {
    attribute integer deckLength;
    relationship Set<AirGroup> airGroups
        inverse AirGroup:: *some relationship*;
}

interface Submarine: Ship {
     attribute integer maxSafeDepth;
}

interface BattleCarrier: Gunship, Carrier {}

Exercise 2.4.1(b)

The Ise would be an object in class BattleCarrier. Its Ship attributes would be name = ``Ise'', displacement = 36000, and type = ``battlecarrier''. From Gunship it inherits attributes numberOfGuns = 8 and bore = 14. From Carrier it inherits attribute deckLength = 200 and relationship airGroups connecting it to airgroups 1 and 2.

Exercise 2.4.3(a)

Diagram in postscript

Exercise 2.4.3(b)

The Ise would be represented by a ship entity in Ships with attributes name = ``Ise'', displacement = 36000, and type = ``battlecarrier''. It would also have an entity in Gunships with #guns = 8 and bore = 14. It would have an entity in Carriers with lgthDeck = 200. This entity is related to the entities for air groups 1 and 2 through relationship AirGroups. Note there is no ``battlecarrier'' entity set.

Return to Top

Solutions for Section 2.5

Exercise 2.5.1(a)

We think that Social Security number should me the key for Customer, and account number should be the key for Account. Here is the ODL solution with key declarations.

interface Customer
    (key ssNo)
{
    attribute string name;
    attribute string addr;
    attribute string phone;
    attribute integer ssNo;
    relationship Set<Account> ownsAccts
        inverse Account::ownedBy;
}

interface Account
    (key number)
{
    attribute integer number;
    attribute string type;
    attribute real balance;
    relationship Set<Customer> ownedBy
        inverse Customer::ownsAccts
}

Exercise 2.5.2(a)

We'll use keys ssNo and number as in Exercise 2.5.1(a). Also, we think it does not make sense for an account to be related to zero customers, so we'll put ``>0'' on 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 E/R diagram in postscripti, showing underlined keys and the numerocity constraint.

Exercise 2.5.3(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.

Return to Top

Solutions for Section 2.6

Exercise 2.6.1

Here is the E/R diagram in postscript. We have omitted attributes other than our choice for the key attributes of Students and Courses. Also omitted is 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.6.4b

Here is the E/R diagram in postscript (revised 4/18/97). 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., (Oilers, NFL) or (Oilers, 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., Steve Young is (8, 49ers, NFL).

Return to Top

Solutions for Section 2.7

Exercise 2.7.1(a)

This problem is a straightforward many-many relationship, for which we have to introduce a ``connecting'' logical record type, which we call Ownership. There are links from this logical record type to Customers and Accounts. Here is the Network diagram in postscript.

Exercise 2.7.3

A many-one binary relationship requires one link, but a many-many relationship requires two (plus a new logical record type). Thus, the minimum number of links is when all relationships are many-one; there are then m links. The maximum occurs when all relationships are many-many; then there are 2m links.

Return to Top