Final Brain Buster Exercise

The Problem

Add a field or fields that will allow the creation of a mailing list report. This report should be able to create the listing with a single database query. (By single, I mean "in one trip" rather than one query that can do it all). The database model should be designed so that each of these scenarios can be retrieved using a single query (although each query will be different, only one of them is needed each time):

A dog's parents, or
All of a dog's children, or
All of a dog's children by one specific mate.

The query can also have the following filter options:

Include only dogs who are currently enrolled at TPI (Teacher's Pet, Inc.). We'll use this to mail out our newsletter.
Include only dogs who are NOT currently enrolled at TPI but have attended in the past. We'll use this to mail alumni meeting notices.
Include only dogs who are currently or previously enrolled at TPI. We'll use this to mail out "preferred customer" discount coupons.
Include only dogs who are not currently enrolled, and have never been enrolled. We'll use this to send out promotional literature.

See Solution Top of page

The Solution

While it may not have been obvious, relationships don't always have to be between tables. The most elegant solution is to relate a table back to itself (this type of relationship is called a "pig's ear."). Therefore both DOG.MOTHER and DOG.FATHER contain links to DOG.DOG. You'll also see that we used the "mutually exclusive" notation. That is, a dog can FATHER zero or more children, or a dog can MOTHER zero or more children, but a dog cannot both FATHER and MOTHER children! And don't quibble about cloning and such, because for our scenario we're talking the natural world, not the unnatural world.

In addition to the two parent fields, we also added an ENROLLMENT field to track a dog's enrollment status. Our ERD doesn't show how this works, but our data dictionary would probably show that this field contains three values: 0 = not enrolled, 1 = currently enrolled, and 2 = alumnus.

Now, let's take one of the query scenarios and show how this would look in SQL. If we wanted to list all of Fido's children (let's say Fido is dog #33) by mate Fifi (dog #99), but only those offspring who have been enrolled or are currently enrolled:

      SELECT dog.firstname,
             dog.lastname
      FROM   dog
      WHERE  dog.father = 33
        AND  dog.mother = 99
        AND  dog.enrollment > 0;

Top of page