Understanding the Make-a-Mammal database

To follow this tutorial, you should understand the main tables in the MAM (Make-a-Mammal) database. 

How the tables link to each other

Firstly, there is a geographical dimension:

The Make-a-Mammal or MAM chain of stores categorises its 13 products into 8 animals and 4 species.

Purchases are also categorised by where they took place:

Geographically, the MAM chain is split by quadrant (not shown here), region, town, shopping centre and then store.

Here's a diagram of these tables joined with the transactions and point-of-sale tables:

How the main transaction tables fit into the database diagram.

What this means in practise - a walk-through

Consider the following two rows in the transactions table tblTransaction:

These two transactions are for the same point-of-sale record (number 36779).

You can see from this that someone bought (as part of the same point-of-sale visit 36779):

• Two of product number 4, costing £11.04 each; and
• Two of product 9, costing £11.16 each.

Looking at the products table (tblProduct) shows which products were bought:

Apparently this person bought 2 Crocky products and 2 Pingu ones.

Chasing through the tables gives us the animals:

Product 9 (Pingu) is - unsurprisingly - a penguin, and product 4 (Crocky) is (equally unsurprisingly) a crocodile.

You could now follow the Pos table row to find out the store number:

These items were bought in store number 2, and the person was served by staff number 3369.

You could then find from this in which shopping centre the transaction took place:

Store 2 turns out to be in store A of the Times Square shopping centre.

You could then follow the trail to find out that this is in town number 170 (Sutton) in region 3 (London).