Category : Paradox DBMS
Archive   : TECH91.ZIP
Filename : TI127.ASC

PRODUCT : Paradox NUMBER : 127
VERSION : All
OS : DOS
DATE : November 4, 1991 PAGE : 1/3

TITLE : Normalizing a non-normalized Paradox table




This information sheet demonstrates the best method for
converting a non-normalized table into a table in the first
normal form. In brief, a non-normalized table is one in which
repeating groups of information are spread out over many fields
rather than placing them into a few fields with many records.
For example, let's say you have a table which tracks orders
placed by a number of customers over a single year. We want this
table to display the Customer Number and the number of products
purchased for each month. For simplicity's sake we will assume
the year only has three months (January, February, and March).
Here is the non-normalized version of this table:

OrdersÍËÍCustomer #ËÍJanuary ËÍFebruaryÍËÍMarchÍ»
º 0001 º 3 º 7 º 0 º
º 0002 º 0 º 1 º 0 º
º 0003 º 0 º 0 º 14 º


This is the normalized version:

OrdersÍËÍCustomer #ËÍÍMonthÍÍÍËÍQuantityÍ»
º 0001 º January º 3 º
º 0001 º February º 7 º
º 0001 º March º 0 º
º 0002 º January º 0 º
º 0002 º February º 1 º
º 0002 º March º 0 º
º 0003 º January º 0 º
º 0003 º February º 0 º
º 0003 º March º 14 º

The first table is keyed on Customer # only. All information
related to a single customer is stored in a single record with
many fields. The second table is keyed on both Customer # and
Month. Each customer has three records (twelve if we were
dealing with a full year), one for each month, and that month's
corresponding quantity. It is necessary to do number of queries
to convert the data from the format of the first table to the
format of the second table. More specifically two queries must
be performed for each group to be incorporated into the
consolidated fields (i.e. for each month of the year in this
example).














PRODUCT : Paradox NUMBER : 127
VERSION : All
OS : DOS
DATE : November 4, 1991 PAGE : 2/3

TITLE : Normalizing a non-normalized Paradox table




To begin the process you must create a new table with the proper
structure (one which looks like the second table). Once this is
done the following two queries must be performed for each field
to be converted:

QUERY #1 (this is a multi-table query):

OrdersAËÍCustomer #ËÍJanuaryÍËÍFebruaryÍËÍMarchÍ»
º _Cust º _Quant º º º


OrdersBËÍCustomer #ËÍÍMonthÍÍÍËÍQuantityÍ»
Insert º _Cust º º _Quant º

This insert query links the OrdersA Customer # field to to the
OrdersB Customer # field and the OrdersA January quantity field
to the OrdersB Quantity field. The key word "Insert" tells
Paradox to copy the data from the OrdersA fields which contain
example elements Cust and Quant to their corresponding fields in
OrdersB. The underscore (_) stands for the example element key
(F5).

Once Query #1 is performed the OrdersB table will look like this:

OrdersBËÍCustomer #ËÍÍMonthÍÍÍËÍQuantityÍ»
º 0001 º º 3 º
º 0002 º º 0 º
º 0003 º º 0 º

All of the records whose month values are blank should now be
assigned the Month value of January. Query #2 performs this
operation (Note: clear the workspace before proceeding using
(Alt-F8):

QUERY #2:

OrdersBËÍCustomer #ËÍÍÍÍÍÍÍÍÍÍÍMonthÍÍÍÍÍÍÍÍÍÍËÍQuantityÍ»
º º BLANK, CHANGETO January º º

Once this is performed, clear Query #2 from the workspace (F8)
and set up Query #1 again. Move the example element Quant in
OrdersA to the next field to be incorporated into the OrdersB
table (in this case the February field) and run the query. The













PRODUCT : Paradox NUMBER : 127
VERSION : All
OS : DOS
DATE : November 4, 1991 PAGE : 3/3

TITLE : Normalizing a non-normalized Paradox table




February values and corresponding Customer #'s are now inserted
into the OrdersB table. Once again all the newly inserted
records have a blank Month value. Clear the workspace and set up
Query #2, this time changing blank values to the new Month value
(February). To complete the process simply step through this set
of queries until all fields have been incorporated into the new
OrdersB table.

NOTES:

1. This is a generic example, however, the concept is applicable
to any database in which repeating fields contain the same type
of data. Keep in mind, the only things that change from query
set to query set is the location of the example element in Query
#1 and the label for the new records in Query #2.

2. Use {Scripts} {QuerySave} to store the two queries the first
time around. This will save you the time and effort involved in
re-creating the queries for each step of the process.

3. See the general Normalization TI "Explanation of Normalized
and Non-Normalized Tables" for further discussion of the theory
behind normalization.