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)
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})
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.
Solutions for Section 4.7
Exercise 4.7.1(a)
Exercise 4.7.2
owns IDREFS>
ownedBy IDREFS> ]>
Database Systems: The Complete
Book
Solutions for Chapter 5
Solutions for Section 5.2
Exercise 5.2.1(a)
PI_model( SIGMA_{speed >= 1000} ) (PC)
Exercise 5.2.1(f)
The trick is to theta-join PC with itself on the condition that the hard disk sizes are equal. That gives us tuples that have two PC model numbers with the same value of hd. However, these two PC's could in fact be the same, so we must also require in the theta-join that the model numbers be unequal. Finally, we want the hard disk sizes, so we project onto hd.
The expression is easiest to see if we write it using some temporary values. We start by renaming PC twice so we can talk about two occurrences of the same attributes. R1 = RHO_{PC1} (PC) R2 = RHO_{PC2} (PC)
R3 = R1 JOIN_{PC1.hd = PC2.hd AND PC1.model <> PC2.model} R2 R4 = PI_{PC1.hd} (R3)
Exercise 5.2.1(h)
First, we find R1, the model-speed pairs from both PC and Laptop. Then, we find from R1 those computers that are ``fast,'' at least 133Mh. At the same time, we join R1 with Product to connect model numbers to their manufacturers and we project out the speed to get R2. Then we join R2 with itself (after renaming) to find pairs of different models by the same maker. Finally, we get our answer, R5, by projecting onto one of the maker attributes. A sequence of steps giving the desired expression is: R1 = PI_{model,speed} (PC) UNION PI_{model,speed} (Laptop) R2 = PI_{maker,model} (SIGMA_{speed>=700} (R1) JOIN Product) R3 = RHO_{T(maker2, model2)} (R2)
R4 = R2 JOIN_{maker = maker2 AND model <> model2} (R3) R5 = PI_{maker} (R4)
Exercise 5.2.2
Here are figures for the expression trees of Exercise 5.2.1 Part (a) Part (f) Part (h). Note that the third figure is not really a tree, since it uses a common subexpression. We could duplicate the nodes to make it a tree, but using common subexpressions is a valuable form of query optimization. One of the benefits one gets from constructing ``trees'' for queries is the ability to combine nodes that represent common subexpressions.
Exercise 5.2.7
The relation that results from the natural join has only one attribute from each pair of equated attributes. The theta-join has attributes for both, and their columns are identical.
Exercise 5.2.9(a)
If all the tuples of R and S are different, then the union has n+m tuples, and this number is the maximum possible.
The minimum number of tuples that can appear in the result occurs if every tuple of one relation also appears in the other. Surely the union has at least as many tuples as the larger of R and that is, max(n,m) tuples. However, it is possible for every tuple of the smaller to appear in the other, so it is possible that there are as few as max(n,m) tuples in the union.
Exercise 5.2.10
In the following we use the name of a relation both as its instance (set of tuples) and as its schema (set of attributes). The context determines uniquely which is meant. PI_R(R JOIN S) Note, however, that this expression works only for sets; it does not preserve the multipicity of tuples in R. The next two expressions work for bags.
R JOIN DELTA(PI_{R INTERSECT S}(S)) In this expression, each projection of a tuple from S onto the attributes that are also in R appears exactly once in the second argument of the join, so it preserves multiplicity of tuples in R, except for those that