home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Shareware 1 2 the Maxx
/
sw_1.zip
/
sw_1
/
TEXT
/
PDX_ALL.ZIP
/
TI124.ASC
< prev
next >
Wrap
Text File
|
1991-11-04
|
11KB
|
331 lines
PRODUCT : Paradox NUMBER : 124
VERSION : ALL
OS : DOS
DATE : November 4, 1991 PAGE : 1/5
TITLE : Explanation of Normalized and Non-Normalized Tables
This information sheet is a brief overview of normalization. It
is by no means a complete discussion of this topic but does
contain the basic concepts and how they relate to Paradox.
Normalization is a method of organizing information into discrete
flexible modules, where each record has the least number of
fields necessary to establish a unique identity for the record's
information. Non-normalized structures, on the other hand,
provide as much information (therefore as many different fields)
as possible for each individual record. Although normalization
is not required when creating Paradox table, it will, in general,
make it easier to manipulate your data.
A normalized table is usually easier to analyze and query because
many similar values may be examined in a single field. In
addition it is relatively easy to de-normalize a normalized table
through queries or 'crosstabs'. The opposite is not true.
The normalization process is typically broken up into three
stages or "forms". The first normal form requires the removal of
all identical records (keying a table in Paradox performs this
function), and the removal of repeating groups.
This is a simple example of a non-normalized table. Note that
the different tool fields are actually repeating groups of
quantities:
Orders═╦═Order #═╦═Hammers ╦═Screwdrivers═╦═Saws═╗
║ 0001 ║ 3 ║ 7 ║ 0 ║
║ 0002 ║ 0 ║ 0 ║ 0 ║
║ 0003 ║ 0 ║ 0 ║ 14 ║
║ 0005 ║ 5 ║ 0 ║ 0 ║
This example is keyed only on Order # with the quantities for
each tool in its own separate field. If you had over a hundred
different tools to track it would be a laborious task to locate
each field when entering an order. As you can see, this table
could be very difficult to manage. The table would be quite
unwieldy even if it only contained a few orders. In addition,
queries on this table would be significantly more difficult to
design. Reports and forms will also be far more complex than the
normalized example below. Using calculated fields may not even
PRODUCT : Paradox NUMBER : 124
VERSION : ALL
OS : DOS
DATE : November 4, 1991 PAGE : 2/5
TITLE : Explanation of Normalized and Non-Normalized Tables
be possible, in some cases, if your calculation must be longer
than 175 characters.
This is a normalized version of the Orders table in the first
normal form:
Orders═╦═Order #═╦══Item #═╦ Quantity═╦═════Tool════╗
║ 0001 ║ 1 ║ 3 ║ Hammer ║
║ 0001 ║ 2 ║ 7 ║ Screwdriver ║
║ 0002 ║ 1 ║ 1 ║ Clipper ║
║ 0003 ║ 1 ║ 14 ║ Saw ║
║ 0004 ║ 1 ║ 5 ║ Hammer ║
This example table is keyed on both Order Number and Item Number
so that each Order may have more than one item. Rather than have
a single record for each order with a separate field for each
tool that a customer could purchase, there is a separate record
for each tool purchase. The separate tool fields are now
condensed into two fields: Quantity and Tool.
The second normal form involves tables that have more than one
key field. If a non-key field relies only part of the total key,
it should be moved to a separate table. This means every field
in the table must be directly related to all of the key fields
not just some of them.
The next example is a variation on the Orders table above. The
table below is keyed on both Order # and Tool ID #:
Orders═╦═Order #═╦══Tool ID #═╦════Tool Description════╗
║ 0001 ║ 1 ║ Screwdriver ║
║ 0002 ║ 2 ║ Clipper ║
║ 0003 ║ 3 ║ Saw ║
║ 0004 ║ 2 ║ Clipper ║
This table violates the second normal form. The Tool Description
field is only dependent on the Tool ID # field not the entire
composite key (Order # and Tool ID #). To conform to the second
normal form the Tools Description information must be removed
from the Orders table and placed in a separate tools table with
its corresponding ID #. Here is the correct version:
PRODUCT : Paradox NUMBER : 124
VERSION : ALL
OS : DOS
DATE : November 4, 1991 PAGE : 3/5
TITLE : Explanation of Normalized and Non-Normalized Tables
Orders═╦═Order #═╦══Tool ID #═╗
║ 0001 ║ 1 ║
║ 0002 ║ 2 ║
║ 0003 ║ 3 ║
║ 0004 ║ 2 ║
Tools═╦══Tool ID #═╦════Tool Description════╗
║ 1 ║ Screwdriver ║
║ 2 ║ Clipper ║
║ 3 ║ Saw ║
║ 4 ║ Hammer ║
This structure reduces redundancy of data in your tables. The
description information need only be entered once in the Tools
table. The orders table refers to the tools by ID # alone.
The Third Normal Form requires the table be in second normal form
and that each field has a direct and permanent relationship to
the entire key. If a non-key field is not always related to the
key it should be removed and placed in a new table. Another way
of analyzing whether a field is related to the primary key is to
look for a direct relationship with non-key field. In other
words: does this non-key field relate exclusively to another
non-key field in the table.
The next section discusses the use of the Third Normal Form on
the Tools table. In this case the table is still keyed on Tool
ID #.
Tools═╦═Tool ID #═╦═Tool Descr══╦══Vendor═══╦═══Address════╗
║ 1 ║ Screwdriver ║ Acme Co. ║ 123 Main St ║
║ 2 ║ Clipper ║ Tools R Us║ 456 2nd Ave ║
║ 3 ║ Saw ║ Acme Co. ║ 123 Main St. ║
║ 4 ║ Hammer ║ ABC Tool ║ 900 Hammer Wy║
This table violates the third normal form. The Vendor and
Address are not always related to the key field in the table,
Tool ID #. Consider what would happen if every tool a vendor
distributes were removed from the Tools table. All information
on that vendor would be lost. For example, if every entry for
Hammer were removed from the table, the database would lose the
name and address for ABC Tool. This characteristic is known as a
PRODUCT : Paradox NUMBER : 124
VERSION : ALL
OS : DOS
DATE : November 4, 1991 PAGE : 4/5
TITLE : Explanation of Normalized and Non-Normalized Tables
Delete Anomaly. Although, this may be a business policy, it is
not a recommended practice in database design.
A table that is not in third normal form also has a
characteristic known as an Update Anomaly. For example, if Acme
Co. changed its address, every Address entry for Acme Co. would
have to be changed.
If we use the criteria described previously to analyze the
relationship of the fields (i.e. do any of these fields relate
exclusively to another non-key field) we can easily identify the
offending fields. Note that the Address field only relates to
the Vendor field and has no relationship to the key field Tool ID
#. The following tables demonstrate the correct relationship
between tools and vendors according to the rules of the third
normal form. Vendors are placed into a separate table, and keyed
on Vendor #. Vendor number is a field created to simplify
identification and linking between the two tables.
Tools═╦═Tool ID #═╦═Tool Description═╦══Vendor #══╗
║ 1 ║ Screwdriver ║ 1 ║
║ 2 ║ Clipper ║ 2 ║
║ 3 ║ Saw ║ 1 ║
║ 4 ║ Hammer ║ 3 ║
Vendors═╦═Vendor #═╦═ Name═════╦══Address═════╗
║ 1 ║ Acme Co. ║ 123 Main St ║
║ 2 ║ Tools R Us║ 456 2nd Ave ║
║ 3 ║ ABC Tool ║ 900 Hammer Wy║
In third normal form, the entries for Screwdriver and Saw can be
eliminated and the information for Acme Co. remains intact in the
Vendor table. Also, information on Acme Co. must only be changed
once in the Vendors table to have a global effect on related
tables.
In conclusion, the full normalization process will give us
compact, simple tables with a minimum of redundant data. If
necessary there may be several of these compact tables linked on
common fields. These tables may be accessed simultaneously in
Paradox through multi-table queries, forms, and reports.
PRODUCT : Paradox NUMBER : 124
VERSION : ALL
OS : DOS
DATE : November 4, 1991 PAGE : 5/5
TITLE : Explanation of Normalized and Non-Normalized Tables
Bibliography of books on Normalization and general database
theory:
1. Fabian Pascal, "SQL and Relational Basics", M&T Publishing
Inc. (1990)
2. C.J. Date, "An Introduction To Database Systems - Volume I",
5th ed., Addison-Wesley Publishing Company (1990)
3. C.J. Date, "An Introduction To Database Systems - Volume II",
2nd ed., Addison-Wesley Publishing Company (1985)
4. C.J. Date, "Relational Database -- Selected Writings",
Addison-Wesley Publishing Company (1986)
5. C.J. Date, "Relational Database - Writings 1985-1989",
Addison-Wesley Publishing Company (1990)
6. E.F. Codd, "The Relational Model for Database Management:
Version 2", Addison-Wesley Publishing (1990)
7. G.M. Nijssen, "Conceptual Schema and Relational Database
Design - A fact oriented approach", Prentice Hall (1989)
8. Steve Reeves, "Logic For Computer Science", Addison-Wesley
Publishing (1990)