When I rest, I rust [Rast ich, so rost ich].
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
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 ...