A.1 Chapter 5 Exercise Solutions

Following are the solutions to the exercises in Section 5.5.

A.1.1 Exercise 1

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 m is the rowcount of the lower master table. The master join ratios under the same assumptions are exactly 1.0, and you simply leave them off.

A.1.2 Exercise 2

Figure A-2 shows the solution to Exercise 2.

Figure A-2. Solution to Exercise 2

In this problem, you need the same shortcuts as for Exercise 1, for join ratios and for the filter ratio to B. Did you remember to add the * for the unique filter on B? Did you remember to indicate the direction of the outer joins to Z and R with the midpoint arrows on those join links?

A.1.3 Exercise 3

Figure A-3 shows the solution to Exercise 3.

Figure A-3. Solution to Exercise 3

In this problem, you need the same tricks as for Exercise 1, for join ratios and for the filter ratio to B. Did you remember to add the * for the unique filter on B? Did you remember to indicate the direction of the outer joins to C and PL with the midpoint arrows on those join links?

The joins to ITxR and IV from ITx and P, respectively, are special one-to-one joins that you indicate with arrows on both ends of the join links. The filter ratios on both ends of these one-to-one joins are exactly 1.0. These are a special class of detail table that frequently comes up in real-world applications: time-dependent details that have one row per master row corresponding to any given effective date. For example, even though you might have multiple inventory tax rates for a given taxing entity, only one of those rates will be in effect at any given moment, so the date ranges defined by Effective_Start_Date and Effective_End_Date will be nonoverlapping. Even though the combination of ID and date-range condition do not constitute equality conditions on a full unique key, the underlying valid data will guarantee that the join is unique when it includes the date-range conditions.

Since you count the date range defined by Effective_Start_Date and Effective_End_Date as part of the join, do not count it as a filter, and consider only the subtable that meets the date-range condition as effective for the query diagram. Thus, you find P and IV to have identical effective rowcounts of 8,500, and you find identical rowcounts of 4 for ITx and ITxR. This confirms the one-to-one nature of these joins and the join ratios of 1.0 on each end of the links.

As for the example in Figure 5-4, you should use only subtable rowcounts for the joins to SPCT, TRCT, and CTCT, because Code_Translations is one of those apples-and-oranges tables that join only a specific subtable at a time.

I have relaxed my own rule about showing just a single significant figure in the join and filter ratios. This is largely just to help you see that you really have made the right calculations; you haven't just hit the right number for the wrong reason.

A.1.4 Exercise 4

Figure A-4 shows the solution to Exercise 4, the fully simplified solution to Exercise 1.

Figure A-4. Solution to Exercise 4

Since this problem involves only large detail join ratios and master join ratios equal to 1.0, you just add a capital F to the most highly filtered node and add a lowercase f to the other filtered nodes, with an asterisk for the unique filter on node R. (Did you remember the asterisk?)

A.1.5 Exercise 5

Figure A-5 shows the solution to Exercise 5, the fully simplified solution to Exercise 2.

Figure A-5. Solution to Exercise 5

Since this problem involves only large detail join ratios, and master join ratios equal to 1.0, you just add a capital F to the most highly filtered node and add a lowercase f to the other filtered node, with an asterisk for the unique filter on node B. (Did you remember the asterisk?)

A.1.6 Exercise 6

Figure A-6 shows the solution to Exercise 6, the fully simplified solution to Exercise 3.

Figure A-6. Solution to Exercise 6

Since this problem involves only large detail join ratios, you can leave those out. However, note that it does include one master join ratio well under 1.0, in the join down into ITx, so you leave that one in. Otherwise, you just add a capital F to the most highly filtered node and add a lowercase f to the other filtered nodes, with an asterisk for the unique filter on node B. (Did you remember the asterisk?)