Identify the requirements of First, Second, and Third Normal Forms
First, second and third normal forms each have their respective requirements.
The three forms and their corresponding characteristics are listed below.
First normal form: When tables are placed into first normal form, the relations between tables cannot contain any repeating groups.
Second normal form: When tables are placed into second normal form, every field in the relation must be functionally dependent upon the entire primary key.
Third normal form: When tables are placed into third normal form, the relation cannot contain any transitive dependencies.
Normalization High Level Overview
Normalization theory arose out of entity relationship analysis when further refinement was needed.
Appropriate decomposition of existing tables into tables which eliminate redundancy and defining foreign keys in the old table and referencing the primary keys of the new ones represents the general process.
The underlying theory is referred to as normalization theory and is based on functional dependencies.
Furthermore, its purpose is to take a poorly designed table and transform it into a table with a sound structure which enable the efficient execution of queries.
After the normalization process has been carried out, one should take a given table and test it against the "definition of normal forms" to determine whether it is properly designed. If it is not designed properly, one should make the appropriate modifications, retest it, and repeat this process until the table structure is sound.
Figure 4-4 outlines this process.
Figure 4-4: A graphic representation of the general normalization process.
2nd Normal Form Definition
A database is in second normal form if it satisfies the following conditions:
It is in first normal form,
All non-key attributes are fully functional dependent on the primary key
In a table, if attribute B is functionally dependent on A, but is not functionally dependent on a proper subset of A,
then B is considered fully functional dependent on A.
Hence, in a second normal form table, all non-key attributes[1] cannot be dependent on a subset of the primary key. Note that if the primary key is not a composite key, all non-key attributes are always fully functional dependent on the primary key.
A table that is in first normal form and contains only a single key as the primary key is automatically in second normal form.
3rd Normal Form Definition
A database is in third normal form if it satisfies the following conditions:
It is in second normal form and
There is no transitive functional dependency
Transitive functional dependency implies that the following relationships in the table exist:
A is functionally dependent on B, and B is functionally dependent on C.
In this case, C is transitively dependent on A by means of B.
[1]Non-key attributes: Non-key attributes are the attributes or fields of a table, other than candidate key attributes/fields in a table.