Database Systems: The Complete Book
Solutions for Chapter 4

Solutions for Section 4.2
Solutions for Section 4.3
Solutions for Section 4.4
Solutions for Section 4.5
Solutions for Section 4.6
Solutions for Section 4.7

Solutions for Section 4.2

Exercise 4.2.1

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

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

Exercise 4.2.4

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

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

Return to Top

Solutions for Section 4.3

Exercise 4.3.1

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 and extent declarations. 
class Customer
    (extent Customers key ssNo)
{
    attribute string name;
    attribute string addr;
    attribute string phone;
    attribute integer ssNo;
    relationship Set<Account> ownsAccts
        inverse Account::ownedBy;
}

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

Return to Top

Solutions for Section 4.4

Exercise 4.4.1(a)

Since the relationship between customers and accounts is many-many, we should create a separate relation from that relationship-pair. 
     Customers(ssNo, name, address, phone)
     Accounts(number, type, balance)
     CustAcct(ssNo, number)

Exercise 4.4.1(d)

Ther is only one attribute, but three pairs of relationships from Person to itself. Since motherOf and fatherOf are many-one, we can store their inverses in the relation for Person. That is, for each person, childrenOfMale and childrenOfFemale will indicate that persons's father and mother. The children relationship is many-many, and requires its own relation. This relation actually turns out to be redundant, in the sense that its tuples can be deduced from the relationships stored with Person. The schema: 
     Persons(name, childrenOfFemale, childrenOfMale)
     Parent-Child(parent, child)

Exercise 4.4.4

You get a schema like: 
     Studios(name, address, ownedMovie)

Since name -> address is the only FD, the key is {name, ownedMovie}, and the FD has a left side that is not a superkey.

Exercise 4.4.5(a,b,c)

     (a) Struct Card { string rank, string suit };
     (b) class Hand {
             attribute Set theHand;
         };

For part (c) we have: 

     Hands(handId, rank, suit)

Notice that the class Hand has no key, so we need to create one: handID. Each hand has, in the relation Hands, one tuple for each card in the hand.

Exercise 4.4.5(e)

     Struct PlayerHand { string Player, Hand theHand };
     class Deal {
         attribute Set theDeal;
     }

Alternatively, PlayerHand can be defined directly within the declaration of attribute theDeal.

Exercise 4.4.5(h)

Since keys for Hand and Deal are lacking, a mechanical way to design the database schema is to have one relation connecting deals and player-hand pairs, and another to specify the contents of hands. That is: 
     Deals(dealID, player, handID)
     Hands(handID, rank, suit)

However, if we think about it, we can get rid of handID and connect the deal and the player directly to the player's cards, as: 

     Deals(dealID, player, rank, suit)

Exercise 4.4.5(i)

First, card is really a pair consisting of a suit and a rank, so we need two attributes in a relation schema to represent cards. However, much more important is the fact that the proposed schema does not distinguish which card is in which hand. Thus, we need another attribute that indicates which hand within the deal a card belongs to, something like: 
     Deals(dealID, handID, rank, suit)

Exercise 4.4.6(c)

Attribute b is really a bag of (f,g) pairs. Thus, associated with each a-value will be zero or more (f,g) pairs, each of which can occur several times. We shall use an attribute count to indicate the number of occurrences, although if relations allow duplicate tuples we could simply allow duplicate (a,f,g) triples in the relation. The proposed schema is: 
     C(a, f, g, count)

Return to Top

Solutions for Section 4.5

Exercise 4.5.1(b)

     Studios(name, address, movies{(title, year, inColor, length,
         stars{(name, address, birthdate)})})

Since the information about a star is repeated once for each of their movies, there is redundancy. To eliminate it, we have to use a separate relation for stars and use pointers from studios. That is: 

     Stars(name, address, birthdate)
     Studios(name, address, movies{(title, year, inColor, length,
         stars{*Stars})})
Since each movie is owned by one studio, the information about a movie appears in only one tuple of Studios, and there is no redundancy.

Exercise 4.5.2

     Customers(name, address, phone, ssNo, accts{*Accounts})
     Accounts(number, type, balance, owners{*Customers})

Return to Top

Solutions for Section 4.6

Exercise 4.6.1(a)

We need to add new nodes labeled George Lucas and Gary Kurtz. Then, from the node sw (which represents the movie Star Wars), we add arcs to these two new nodes, labeled directedBy and producedBy, respectively.

Exercise 4.6.2

Create nodes for each account and each customer. From each customer node is an arc to a node representing the attributes of the customer, e.g., an arc labeled name to the customer's name. Likewise, there is an arc from each account node to each attribute of that account, e.g., an arc labeled balance to the value of the balance.

To represent ownership of accounts by customers, we place an arc labeled owns from each customer node to the node of each account that customer holds (possibly jointly). Also, we place an arc labeled ownedBy from each account node to the customer node for each owner of that account.

Exercise 4.6.5

In the semistructured model, nodes represent data elements, i.e., entities rather than entity sets. In the E/R model, nodes of all types represent schema elements, and the data is not represented at all.

Return to Top

Solutions for Section 4.7

Exercise 4.7.1(a)

<STARS-MOVIES>
     <STAR starId = "cf" starredIn = "sw, esb, rj">
         <NAME>Carrie Fisher</NAME>
         <ADDRESS><STREET>123 Maple St.</STREET>
    	 <CITY>Hollywood</CITY></ADDRESS>
         <ADDRESS><STREET>5 Locust Ln.</STREET>
    	 <CITY>Malibu</CITY></ADDRESS>
     </STAR>
     <STAR starId = "mh" starredIn = "sw, esb, rj">
         <NAME>Mark Hamill</NAME>
         <ADDRESS><STREET>456 Oak Rd.<STREET>
    	 <CITY>Brentwood</CITY></ADDRESS>
     </STAR>
     <STAR starId = "hf" starredIn = "sw, esb, rj, wit">
         <NAME>Harrison Ford</NAME>
         <ADDRESS><STREET>whatever</STREET>
              <CITY>whatever</CITY></ADDRESS>
     </STAR>
     <MOVIE movieId = "sw" starsOf = "cf, mh">
         <TITLE>Star Wars</TITLE>
         <YEAR>1977</YEAR>
     </MOVIE>
     <MOVIE movieId = "esb" starsOf = "cf, mh">
         <TITLE>Empire Strikes Back</TITLE>
         <YEAR>1980</YEAR>
     </MOVIE>
     <MOVIE movieId = "rj" starsOf = "cf, mh">
         <TITLE>Return of the Jedi</TITLE>
         <YEAR>1983</YEAR>
     </MOVIE>
     <MOVIE movieID = "wit" starsOf = "hf">
         <TITLE>Witness</TITLE>
         <YEAR>1985</YEAR>
     </MOVIE>
</STARS-MOVIES>

Exercise 4.7.2

     <!DOCTYPE Bank [
         <!ELEMENT BANK (CUSTOMER* ACCOUNT*)>
         <!ELEMENT CUSTOMER (NAME, ADDRESS, PHONE, SSNO)>
             <!ATTLIST CUSTOMER
                 custId ID
                 owns IDREFS>
         <!ELEMENT NAME (#PCDATA)>
         <!ELEMENT ADDRESS (#PCDATA)>
         <!ELEMENT PHONE (#PCDATA)>
         <!ELEMENT SSNO (#PCDATA)>
         <!ELEMENT ACCOUNT (NUMBER, TYPE, BALANCE)>
             <!ATTLIST ACCOUNT
                 acctId ID
                 ownedBy IDREFS>
         <!ELEMENT NUMBER (#PCDATA)>
         <!ELEMENT TYPE (#PCDATA)>
         <!ELEMENT BALANCE (#PCDATA)>
     ]>

Return to Top