When I rest, I rust [Rast ich, so rost ich].

—Anonymous German proverb

This appendix contains solutions to the exercises in Chapter 5 through Chapter 7.

Following are the solutions to the exercises in Section 5.5.

Figure A-1 shows the solution to Exercise 1.

Figure A-1. Solution to Exercise 1

The subtlest aspect of this exercise is that you need to notice
that you do not need queries (other than the total table rowcounts) to
find the filter ratios for the `R`

and `D`

nodes. From the exact matches
on uniquely indexed names for each of these, a single match for
`R`

and an `IN`

list for `D`

, you can deduce the ratios. You just need
to calculate 1/*R* and 2/*D*, where *D* and *R* are the respective rowcounts of those
tables, to find their filter ratios. Did you remember to add the
`*`

to the filter ratio on `R`

to indicate that it turns out to be a
unique condition? (This turns out to be important for optimizing some
queries!) You would add an asterisk for the condition on `D`

, as well, if the match were with a single
name instead of a list of names.

The other trick to notice is that, by the assumption of
never-null foreign keys with perfect referential integrity, the
rowcounts of the joins would simply equal the rowcounts of the detail
tables. Therefore, the detail join ratios are simply *d*/*m*,
where *d* is the rowcount of the upper detail table and ...

Start Free Trial

No credit card required