Java Reference
In-Depth Information
Since there is no relationship between the different phone numbers in a given row,
this table violates the fourth normal form, in that there are two or more independent
multivalued facts (or phone numbers) for the customer on each row. The
combinations
of land line, fax, and cell phone numbers on a given row are not
meaningful.
The main problem with violating the fourth normal form is that there is no obvious way
to maintain the data. If, for example, the customer decides to give up the cell phone
listed in the first row, should the cell p hone number in the second row be moved to
the first row, or left where it is? If he or she gives up the land line phone in the second
row and the cell phone in the first row, should all the phone numbers be consolidated
into one row? Clearly, the maintenance of this database could become very
complicated.
The solution is to design around this problem by deleting the phone, fax, and cell
columns from the original table, and creating an additional table containing
Customer_ID as a foreign key, and phone number and type as data fields (see
Table
1-15
). This will allow you to handle several phone numbers of different types for each
customer without violating the fourth normal form.
Table 1-15: Phone Numbers Table
CUSTOMER_ID
NUMBER
TYPE
100
123-234-3456
PHONE
100
123-234-3457
PHONE
100
123-234-3460
FAX
100
121-345-5678
CELL
100
121-345-5679
CELL
Fifth normal form
The requirements of the fifth normal form are as follows:
The table must be in fourth normal form.
It must be impossible to break down a table into smaller tables unless those tables logically have
the same primary key as the original table.
The fifth normal form is similar to the fourth normal form, except that where the fourth
normal form deals with independent multivalued facts, the fifth normal form deals with
interdependent multivalued facts. Consider, for example, a dealership handling
several similar product lines from different vendors. Before selling any product, a
salesperson must be trained on the product.
Table 1 -16
summarizes the situation.
