Java Reference
In-Depth Information
In the example of
Table 1-11
, the Location column describes the location of the
Department. The employee is located there because he or she belongs to that
department. As in the example for the second normal form, columns that do not
contain data describing the primary key should be removed to a separate table. In this
instance, that means that you should create a separate Departments table,
containing the Department name and location, using the Department column in the
Employees table as a foreign key to point to the Departments table. The resulting
tables are shown in
Tables 1-12
and
1-13
.
Table 1-12: Normalised Employee Table
Name
Department
Jones
Sales
Smith
Production
Williams
Shipping
Table 1-13: Departments Table
Department
Location
Sales
43 Elm
Production
17 Main
Shipping
123 Pine
Fourth normal form
The requirements of the fourth normal form are as follows:
The table must be in third normal form.
The table cannot contain two or more independent multivalued facts about an entity.
For example, if you wanted to keep track of customer phone numbers, you could
create a new table containing a Customer_ID number column, a phone number
column, a fax number column, and a cell-phone number column. As long as a
customer has only one of each listed in the table, there is no problem. However, if a
customer has two land line phones, a fax, and two cell phones, you might be tempted
to enter the numbers as shown in
Table 1-14
.
Table 1-14: Phone Numbers Table which violates 4NF
CUSTOMER_ID
PHONE
FAX
CELL
100
123-234-3456
123-234-3460
121-345-5678
100
123-234-3457
<NULL>
121-345-5679
