HP 3000 Manuals

ALLBASE/SQL Database Administration Guide

Identifying the Data for Tables 

Before designing the tables, indexes, and views of the database, you must
determine what the data is.  Where will you get the data?  How will the
data be used?  Consider the following test case.

Integrated Peripherals Incorporated uses purchase order documents that
track the purchasing of parts from external vendors.  The goal of the
Data Management Group is to create an automatic process for tracking
purchases, paying accounts, mapping external parts to internal parts, and
keeping track of internal part stock levels.

Figure 2-1  shows the purchase order used by Integrated Peripherals.


          Figure 2-1.  Identifying Data Categories 

You can use similar documents as a starting point for designing the
tables in your own databases.  Data from the purchase order can be
arranged in categories.

Once the data has been identified, it must be broken up into tables.
This is done by identifying entities, attributes, and keys within the
data.  An entity is a category of information to be tracked by the
database; an attribute is a characteristic of an entity; and a key is one
or more attributes that uniquely identify a member of an entity.

For example, on the purchase order for Integrated Peripherals, the
database designer might identify the following general groups of
information as entities:

   *   Vendor information
   *   Order information
   *   Order item information

The Vendor Information entity has several attributes, including vendor
number, vendor name, vendor address, sales contact, and phone number.
The vendor number attribute might be identified as a key for the Vendor
Information entity, since it is a unique code that distinguishes vendors
from one another.

Make a list of the entities used by your organization in creating
applications.  For each of the entities, list the attributes, including
keys.  Refer to Figure 2-2  for the entities, attributes, and keys
taken from the purchase order used by Integrated Peripherals.


          Figure 2-2.  Entities, Attributes, and Keys 

The entities become tables, and the attributes and keys become the
columns in the tables.  Each entity must have a unique value or
combination of values for each row in the database table in which it will
appear.  This value or combination is known as the primary key.

Note that the key for order item information is composed of two items.
This is because neither the Item Number nor the Order Number is
significant by itself.  Remember that a primary key must be unique.  Item
number alone cannot be the primary key because every order can have an
item #1.  The order number alone cannot be the primary key either because
one order can have several items, forcing the order number to be repeated
(not unique).  Therefore, the two pieces of information are combined to
yield a unique key.

Once the data has been defined and put into groups, it can be normalized
to produce the tables that will become the database.

MPE/iX 5.5 Documentation