Databases Reference
In-Depth Information
There's more...
In relational database theory, normalization is a mandatory step, in order to minimize
redundancy and avoid several anomalies, such as update and insertion and deletion. The
advantage of a normalized database, is its general-purpose structure, that is, on a normalized
table we can execute any type of query—even those that are least expected at the time of
database design.
Several Normal Forms (NF) are defined in relational database theory. Identified by a number and
the abbreviation NF, the normal forms with higher numbers are less exposed to anomalies.
The first three Normal Forms were developed by E. F. Codd, and often we say that a table is
"normalized" when it is in 3NF, that is, when the following rules are met:
F
There are no multi-value attributes
F
There are no non-prime attributes, functionally dependent on a subset of a
candidate key
F
There are no non-prime attributes, transitively dependent on a candidate key
A candidate key is a minimal set of attributes such that there are no two
distinct tuples with the same value for the candidate key attributes and there
is no subset of the candidate key for which the preceding statement is true.
Although a 3NF table can be queried easily in many ways, there can be performance issues
even when we use a highly normalized database, which is theoretically very well designed but
is not practical to use and even worse, it can lead to poor performance.
We can apply denormalization even when creating materialized views with refresh on demand,
and take advantage of normalization with no extra cost in OLTP performance.
Not 1NF structures
With Oracle databases we can have tables that don't follow the 1NF, also called 0NF. These
tables use
nested
tables
or
varray
fields to store information, which are multi-value
attributes, for example, a table similar to the following:
CREATE OR REPLACE TYPE PHONE AS OBJECT (
PHONEKIND VARCHAR2(20),
AVAILABILITY VARCHAR2(30),
PHONENUMBER VARCHAR2(20));
/
CREATE OR REPLACE TYPE TAB_PHONES AS TABLE OF PHONE;
/
