home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Monster Media 1993 #2
/
Image.iso
/
database
/
p4w_all.zip
/
TI1492.ASC
< prev
next >
Wrap
Text File
|
1993-05-12
|
6KB
|
199 lines
PRODUCT : Paradox for Windows NUMBER : 1492
VERSION : 1.0
OS : WIN
DATE : May 12, 1993 PAGE : 1/3
TITLE : Normalizing a Non-normalized Paradox Table
This Technical Information Sheet demonstrates the best
interactive 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, suppose you have a table which tracks
orders placed by a number of customers over a single year. You
want this table to display the customer number and the number of
products purchased for each month. For simplicity's sake, assume
the year only has three months (January, February, and March).
Below is the non-normalized version of this table (OrdersA):
OrdersA═╦═Customer #═╦═January═╦═February═╦═March═╗
║ 0001 ║ 3 ║ 7 ║ 0 ║
║ 0002 ║ 0 ║ 1 ║ 0 ║
║ 0003 ║ 0 ║ 0 ║ 14 ║
Below is the normalized version (the table is called OrdersB):
OrdersB═╦═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 you were
dealing with a full year), one for each month, and that month's
corresponding quantity. It is necessary to perform a query or
possibly a series of queries to convert the data from the first
table to the normalized format of the second table. To begin the
process, create a new table with the proper structure (one
structured like the OrdersB table). Once this is done, set up
the query as shown on the next page:
PRODUCT : Paradox for Windows NUMBER : 1492
VERSION : 1.0
OS : WIN
DATE : May 12, 1993 PAGE : 2/3
TITLE : Normalizing a Non-normalized Paradox Table
Bring up the tables, OrdersA and OrdersB, by choosing File | New
| Query, multi-selecting the tables OrdersA and OrderB
(Ctrl+click), and choosing OK.
OrdersA═╦═Customer #═╦═January═╦═February═╦═March═══╗
║ _Cust ║ _Quant1 ║ ║ ║
║ _Cust ║ ║ _Quant2 ║ ║
║ _Cust ║ ║ ║ _Quant3 ║
OrdersB═╦═Customer #═╦═══Month═══╦═Quantity═╗
Insert ║ _Cust ║ January ║ _Quant1 ║
Insert ║ _Cust ║ February ║ _Quant2 ║
Insert ║ _Cust ║ March ║ _Quant3 ║
In the query forms above, example elements are represented by an
underscore "_" preceding the label of the example element (i.e.
_Cust and _Quant1). Example elements are created by pressing
[F5] and then typing a label for the example element. On row 1,
the insert query links the OrdersA Customer # field to the
OrdersB Customer # field (by the example element Cust) and the
OrdersA January Quantity field to the OrdersB Quantity field (by
the example element Quant1). On row 2, the OrdersA Customer #
field is linked to the OrdersB Customer # field (by the example
element Cust) and the OrdersA February Quantity field is linked
to the OrdersB Quantity field (by the example element Quant2).
Use a new row for each field from the source that is being
inserted into the single target field. For example, if the table
has a full year (twelve months), then the query would have twelve
rows of links. The example element representing the quantity
must be different for each line in the query. In this example,
the example elements are numbered (i.e. Quant1, Quant2, Quant3).
The key word "Insert" tells Paradox to insert new records in the
OrdersB table by copying the data from the OrdersA fields which
contain example elements (i.e., Cust and Quant1) to their
corresponding fields in OrdersB.
Notes:
1. This is a generic example, however, the concept is
applicable to any database in which the repeating fields
contain the same type of data.
PRODUCT : Paradox for Windows NUMBER : 1492
VERSION : 1.0
OS : WIN
DATE : May 12, 1993 PAGE : 3/3
TITLE : Normalizing a Non-normalized Paradox Table
2. After setting up the query, save it by choosing File |
Save. To run the query, press [F8]. The technique
outlined in this Technical Information Sheet enables you
to use a non-normalized table for data-entry purposes and
to create a query to quickly convert the data to a
normalized version for reporting purposes.
3. For further discussion of the theory behind normalization,
refer to Technical Information Sheet 124. For additional
information about queries, refer to Chapter 6 and 7 of the
User's Guide.
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.