First Normal Form{ 1st
NF }
A relational table, by definition, is in first normal form. All
values of the columns are atomic. That is, they contain no
repeating values. Table1 shows the 1stNF.
|
Table1 |
|
S# |
Status |
City |
P# |
Qty |
| s1 |
20 |
Landon |
P1 |
300 |
| s1 |
20 |
Landon |
P2 |
200 |
| s1 |
20 |
Landon |
P3 |
400 |
| s1 |
20 |
Landon |
P4 |
200 |
| s1 |
20 |
Landon |
P5 |
100 |
| s1 |
20 |
Landon |
P6 |
100 |
| s2 |
10 |
Paris |
P1 |
300 |
| s2 |
10 |
Paris |
P2 |
400 |
| s3 |
10 |
Paris |
P2 |
200 |
| s4 |
20 |
Landon |
P2 |
200 |
| s4 |
20 |
Landon |
P4 |
300 |
| s4 |
20 |
Landon |
P5 |
400 |
Although the table1 is in 1NF it contains
redundant data.
The following anomalies could occur in FIRST:
-
INSERT. The fact that a certain supplier
(s5) is located in a particular city (Athens) cannot be
added until they supplied a part.
-
DELETE. If a row is deleted, then not
only is the information about quantity and part lost but
also information about the supplier.
-
UPDATE. If supplier s1 moved from London
to New York, then six rows would have to be updated
with this new information.
For example, information about the
supplier's location and the location's status have to be
repeated for every part supplied. Redundancy causes what
are called update anomalies. Update anomalies are
problems that arise when information is inserted, deleted,
or updated.
Second Normal Form{2ndNF}
The definition of second normal form
states that only tables with composite primary keys can be
in 1NF but not in 2NF.
A relational table is in second normal form 2NF if it
is in 1NF and every non-key column is fully dependent upon
the primary key.
That is, every non-key column must be
dependent upon the entire primary key. FIRST is in 1NF but
not in 2NF because status and city are functionally
dependent upon only on the column s# of the composite key
(s#, p#). This can be illustrated by listing the
functional dependencies in the table:
|
s#
|
—> city, status
|
|
city
|
—> status |
|
(s#,p#)
|
—>qty |
The process for transforming a 1NF table
to 2NF is:
-
Identify any determinants other than the composite
key, and the columns they determine.
-
Create and name a new table for each determinant and
the unique columns it determines.
-
Move the determined columns from the original table to
the new table. The determinate becomes the primary key of
the new table.
-
Delete the columns you just moved from the original
table except for the determinate which will serve as a
foreign key.
-
The original table may be renamed to maintain semantic
meaning.
To transform FIRST into 2NF we move the columns s#,
status, and city to a new table called SECOND. The column s#
becomes the primary key of this new table.
|
SUPPLIER CITY |
|
s# |
Status |
City |
|
s1 |
20 |
Landon |
|
s2 |
10 |
Paris |
|
s3 |
10 |
Paris |
|
s4 |
20 |
Landon |
|
s5 |
30 |
Athens |
|
|
|
PARTS |
|
S# |
P# |
Qty |
|
s1 |
P1 |
300 |
|
s1 |
P2 |
200 |
|
s1 |
P3 |
400 |
|
s1 |
P4 |
200 |
|
s1 |
P5 |
100 |
|
s1 |
P6 |
100 |
|
s2 |
P1 |
300 |
|
s2 |
P2 |
400 |
|
s3 |
P2 |
200 |
|
s4 |
P2 |
200 |
|
s4 |
P4 |
300 |
|
s4 |
P5 |
400 |
|
Third Normal Form {3rd
NF}
The third normal form requires that all columns in a relational
table are dependent only upon the primary key. A more formal
definition is:
A relational table is in third normal form (3NF) if it is
already in 2NF and every non-key column is non transitively
dependent upon its primary key. In other words, all nonkey
attributes are functionally dependent only upon the primary
key.
Given Table PARTS is already in 3NF. The non-key column, qty, is
fully dependent upon the primary key (s#, p#). SUPPLIER is in
2NF but not in 3NF because it contains a transitive
dependency.
A transitive dependency is occurs when a
non-key column that is a determinant of the primary key is the
determinate of other columns. The concept of a transitive
dependency can be illustrated by showing the functional
dependencies in SUPPLIER:
|
SUPPLIER.s#
|
—> SUPPLIER.status
|
|
SUPPLIER.s#
|
—> SUPPLIER.city |
|
SUPPLIER.city
|
—> SUPPLIER.status
|
Note that SUPPLIER. status is determined both
by the primary key s# and the non-key column city. The
process of transforming a table into 3NF is:
-
Identify any determinants, other the primary key, and the
columns they determine.
-
Create and name a new table for each determinant and the
unique columns it determines.
-
Move the determined columns from the original table to
the new table. The determinate becomes the primary key of
the new table.
-
Delete the columns you just moved from the original table
except for the determinate which will serve as a foreign
key.
-
The original table may be renamed to maintain semantic
meaning.
To transform SUPPLIER into 3NF, we create a
new table called CITY_STATUS and move the columns city and
status into it. Status is deleted from the original table,
city is left behind to serve as a foreign key to CITY_STATUS,
and the original table is renamed to SUPPLIER CITY to
reflect its semantic meaning. The results are shown in
Figure 3 below.
|
SUPPLIER_CITY |
|
s# |
City |
|
s1 |
Landon |
|
s2 |
Paris |
|
s3 |
Paris |
|
s4 |
Landon |
|
s5 |
Athens |
|
|
CITY_STATUS |
|
City |
Status |
|
Landon |
20 |
|
Paris |
10 |
|
Landon |
30 |
|
Rome |
50 |
|
The results of putting the original table
into 3NF has created three tables. These can be represented
in "psuedo-SQL" as:
PARTS (#s, p#, qty)
Primary Key (s#,#p)
Foreign Key (s#) references SUPPLIER_CITY.s#
SUPPLIER_CITY(s#, city)
Primary Key (s#)
Foreign Key (city) references CITY_STATUS.city
CITY_STATUS (city, status)
Primary Key (city)
Advantages of Third Normal
Form
The advantage of having relational tables in
3NF is that it eliminates redundant data which in turn saves
space and reduces manipulation anomalies. For example, the
improvements to our sample database are:
INSERT. Facts about the status of a city, Rome has a status
of 50, can be added even though there is not supplier in
that city. Likewise, facts about new suppliers can be added
even though they have not yet supplied parts.
DELETE. Information about parts supplied can be deleted
without destroying information about a supplier or a city.
UPDATE. Changing the location of a supplier or the status of
a city requires modifying only one row.
