Datasheet

Chapter 1
24
Designing the Ice Cream Shop Database
As its name suggests, the Ice Cream Shop database is an application that has been designed to
track stock and sales for an ice cream wholesaler called Dave and Rob's Ice Cream Shop. The
requirements analysis we conducted indicated to us that the primary purpose of the database is
to store information about the following things:
❑ Stock carried by the Ice Cream Shop
❑ Orders placed by companies
❑ Customer and supplier details
Our analysis has also indicated that ease of data entry and maintenance is a key requirement
for the application, and that the forms used by the staff at the shop must be intuitive and
simple to use. The fact that there will a fair amount of data to handle is enough for us to decide
to use Access to build the application with (which is just as well or we wouldn't have an
example to write about).
We have also conducted the appropriate technical analysis, which indicates that Access 2002 is
an appropriate application development tool for the delivery of this database solution
(otherwise this would be a very short book!).
Our data analysis has indicated that there are five primary objects or entities. We decided on
these particular entities by dividing up the data requirements into self-contained "lumps". For a
complex application there may be several different possible entity models (or ways of arranging
your data) and at the end of the day the choice of which model to use will come down to your
judgement, your experience and the type of requirements to be met. In this case the basic model
is fairly obvious:
❑ Suppliers
❑ Customer Companies
❑ Ice Creams
❑ Ingredients
❑ Sales
The four key processes that indicate the relationships between the entities are as follows:
❑ Ice Creams are sold to Customer Companies
❑ Each Ice Cream is composed of one or more Ingredients
❑ Each Ingredient can be used in one or more Ice Creams
❑ Ingredients are purchased from Suppliers