Exercise: Santa’s list

Tom Jetwett gave this problem as an exam question in December a few years ago. It admittedly has a cultural bias, but no one from any cultural background who has ever visited a shopping mall in the United States during the holiday season could be unaware of the story of Santa Claus.

You are designing a database to help Santa Claus and his elves to keep track of the toys he gives to children. He obviously needs to know the name and address of each child on his list, and when they were born. Every year, each child will give Santa a list of the toys that he/she wants. Santa will record whether that child has been naughty or nice that year, then pick which toys to actually deliver. A child won’t get more than one of each toy, probably won’t get everything that he/she asked for, and might get something that wasn’t asked for (like a lump of coal if he’s been naughty). Of course, Santa doesn’t want to give a child any of the same toys this year as he gave them last year.

Hint: the solution is much easier than you might think the first time you read through this exercise.

Model the information provided by completing the following steps.

  1. Start by identifying and describing each class in English. Note, this is following a top-down design where you first model all the objects in the enterprise and then you determine how they interact.
  2. Continue by drawing the UML class diagram by drawing the classes, but avoid filling in the attributes for now, just name the classes. Include association classes if required.
  3. Next, determine what relationships exist between the classes you identified. Draw each association in the diagram including the multiplicity constraints and describe in English (both directions) to make sure you've correctly modeled the information given.
  4. Now complete each class by filling in the attributes and their corresponding data type.
  5. Map the UML class diagram to the relational model by drawing the relation scheme diagram. Be sure to model all constraints such as any non-null attributes, primary key, and foreign key.

The solution to this exercise may be discussed in class by your instructor or posted online at a later date.