Database Normalization and Referential Integrity

Normalization is the process of reducing a complex data structure into a simple, stable one.
Generally this process involves removing redundant attributes, keys, and relationships from
the conceptual data model.
First Normal Form
First normal form (1NF) is definitive of a relational database. If we are to consider a database
relational, then all relations in the database must be in 1NF.
We say a relation is in 1NF if all fields within that relation are atomic. We sometimes refer to
this concept as the elimination of repeating groups from a relation. Furthermore, first normal
form allows no hierarchies of data values.

Second Normal Form
Second normal form (2NF) deals with the elimination of circular dependencies from a
relation. We say a relation is in 2NF if it is in 1NF and if every non-key attribute is fully
dependent on the entire Primary Key.
A non-key attribute is any attribute that is not part of the Primary Key for the relation.

Third Normal Form:
Third normal form (3NF) deals with the elimination of non-key attributes that do not
describe the Primary Key.
For a relation to be in 3NF, the relationship between any two non-Primary Key columns, or
groups of columns, in a relation must not be one-to-one in either direction.
We say attributes are mutually independent if none of them is functionally dependent on any
combination of the others. This mutual independence ensures that we can update individual
attributes without any danger of affecting any other attribute in a row.
The following list of benefits summarizes the advantages of implementing a normalized
logical model in 3NF.
• Greater number of relations
• More PI choices
• Optimal distribution of data
• Fewer full table scans
• More joins possible

Referential Integrity:
Traditional referential integrity is the concept of relationships between tables, based on the
definition of a primary key and a foreign key. The concept states that a row cannot exist in a
table with a non-null value for a referencing column if an equal value does not exist in a
referenced column.
Using referential integrity, you can specify columns within a referencing table that are foreign
keys for columns in some other referenced table. You must define referenced columns as either
primary key columns or unique columns.
Referential integrity is a reliable mechanism that prevents accidental database inconsistencies
when you perform INSERTS, UPDATES, and DELETES.