Mastering Database Normalization

The normal form defined in relational database theory represents guidelines for record design. The Normalization rules are designed to prevent upto date anomalies and data inconsitencies. It involves breaking down larger tables into smaller related tables and defining relationship between them.

First Normal Form (1NF)

1NF deals with the 'shape' of a record type. All records are required to have the same number of fields. 1NF excludes variable repeating fields and groups.

• Each table must have a primary key, which uniquely identifies each row.
• Each column in the table must contain atomic (indivisible) values.
• The values in each column must be of the same data type

Second Normal Form (2NF)

2NF deals with the relationship between non-key and key fields. A non-key must provide a fact about the key, the whole key and nothing but the key. Thus no non-key attribute is functionally dependent on part of the primary key. That means no partial dependency should exist.

• The table must be in 1NF.
• All non-key attributes(columns) must be fully functionally dependent on the entire primary key. This means that every non-key attributes must depend on the primary key, not just part of it.

Third Normal Form(3NF)

3NF is violated when a non-key field is a fact about another non-key field.

• The table must satisfy 2NF.
• There should be no transitive, dependencies, meaning that non-key attributes should not depend on other non-key attribute.

Boyce-Codd Normal Form(BCNF)

A relation schema R is in BCNF with resoect to a set F of functional dependencies if, for all functional dependencies they are fom the form α -> β wnere α, β ≤ R, at least one of the following holds.

• α -> β is a trivial FD
• α is the superkey for schema R. A relation is in BCNF if every determinant is a candidate key.
• The table must be 3NF.
• Every non-key attribute must be functionally dependent on the superkey, which is any set of attributes that uniquely identifies a row.

Fourth Normal Form (4NF)

Unlike 2NF and 3NF which deals with single-valued facts, 4NF steps into the world of multivalued facts. "A record type should not contain two or more independent multivalued fact about an entity, and on top of that should satisfy 3NF."

• The table must be in BCNF.
• It deals with multivalued dependencies, ensuring that no non-key attribute is dependent on other non-key attributes in a way that creates unnecesary duplication of data.

Fifth Normal Form (5NF)

Achieving 5NF ensures that tables are organized to minimize the need for complex joins in queries. To reach 5NF, a table must meet the following requirements:

• The table must be in 4NF.
• It addresses join dependencies, ensuring that tables are organized to minimize the need for complex joins in queries.

5NF aims to eliminate any need for joining tables to obtain information. This level of normalization is particularly relevant in scenarios where minimizing data redundancy and maintaining data integrity are paramount.