Solutions Manual
Chapter 10
Section 10.1
Exercise 10.1.1
(a) SELECT on MovieStar, SELECT on MovieExec.
(b) SELECT on MovieExec, SELECT on Movies, SELECT on StarsIn.
(e) UPDATE on MovieExec (or UPDATE(name) on MovieExec).
2
Exercise 10.1.2
After step (4), the grant diagram is as follows:
A
✲
B
D
✲
✛
E
After step (5), the grant diagram is as follows:
A
p
✲
B
p
After step (6), the grant diagram is as follows:
3
Exercise 10.1.3
After step (5), the grant diagram is as follows:
A
p
∗∗
✲
B
p
∗
✲C
p
∗
✲D
p
∗
After step (6), the grant diagram is as follows:
Exercise 10.1.4
The grant diagram after the final step is as follows:
4
Section 10.2
Exercise 10.2.1
(a) The rules for trips that have reasonable connections are:
(b) Using the book’s syntax, the SQL is:
WITH RECURSIVE Trips(frm, to, dep, arr) AS
Exercise 10.2.2
Because FROM is one of the SQL reserved words, using it as an identifier is not
Exercise 10.2.3
(a)
FollowOn(x,y)←SequelOf(x,y)
FollowOn(x,y)←FollowOn(x,z)AND
SequelOf(z,y)
(b) Using the book’s syntax, the SQL is:
(c) Using the book’s syntax, the SQL is:
WITH RECURSIVE FollowOn(movie, followOn) AS
(SELECT movie, sequel
6
(d) One of the ways is to first get all of the recursive tuples as for the original
FollowOn in (a), and then subtract the those tuples that represent sequel or
sequel of a sequel. Using the book’s syntax, the SQL would be:
WITH RECURSIVE FollowOn(movie, followOn) AS
(SELECT movie, sequel
FROM SequelOf )
UNION
(SELECT F.movie, S.sequel
FROM FollowOn F, Sequel S
Another way would be to start FollowOn tuples only from the tuples of
movies that have more than two sequels (using a join similar to the one
above but with three Sequel tables).
(e) We simply need to count the number of followon values per movie. Using
the book’s syntax, the SQL would be:
WITH RECURSIVE FollowOn(movie, followOn) AS
7
(f) This is, in a sense, a reverse of (e) above, because to have at most one fol-
lowon means that the total count of the tuples grouped by the given movie
x must be no greater than 2 (one for the movie and its sequel, and the other
for the sequel and its sequel). Using the book’s syntax, the SQL would be:
Exercise 10.2.4
(a) WITH RECURSIVE Path(class, rclass) AS
(SELECT class, rclass
FROM Rel )
UNION
(c) WITH RECURSIVE Path(class, rclass) AS
(SELECT class, rclass
FROM Rel
WHERE mult = ’multi’)
(d) This could be viewed as relation from (a) EXCEPT relation from (b).
WITH RECURSIVE PathAll(class, rclass) AS
(SELECT class, rclass
9
WHERE mult = ’single’)
UNION
(SELECT PathSingle.class, Rel.rclass
(e) We include the edge label as part of the recursive relation and then, basi-
cally, we build the path as in (a) except we only add edges that have an
opposite label.
WITH RECURSIVE Path(class, rclass, mult) AS
(f) WITH RECURSIVE Path(class, rclass) AS
(SELECT class, rclass
FROM Rel
10
SELECT *
Section 10.3
Exercise 10.3.1
(a) Stars(name, address, birthdate)
Movies(title, year, length, stars({*Stars}))
Exercise 10.3.2
Customers(name, address, phone, ssNo, accts({*Accounts}))
Accounts(number, type, balance, owners({*Customers}))
Exercise 10.3.3
Exercise 10.3.4
Players(name)