What are the key principles of database normalization?

2 Rule Two

Building upon the first, the Second Normal Form (2NF) takes database design a step further by addressing the concept of partial dependency. A table is in 2NF if it's already in 1NF and all non-key columns are fully functionally dependent on the primary key. This means that no column value should be dependent on only a part of the primary key if it's composite. To achieve 2NF, you often need to split tables into two or more tables and establish relationships between them. This reduces data redundancy and improves data integrity.

3 Rule Three

The Third Normal Form (3NF) is about eliminating transitive dependencies from your tables. A table is in 3NF if it's in 2NF and all its columns are not only fully functionally dependent on the primary key but also non-transitively dependent. In simpler terms, no non-primary key column should depend on another non-primary key column. This principle ensures that each piece of information is stored only once, reducing redundancy and the potential for inconsistencies.

4 Rule Four

Beyond 3NF, there are more advanced forms like the Boyce-Codd Normal Form (BCNF), which deals with certain types of functional dependencies that are not addressed by 3NF. A table is in BCNF if, for every one of its non-trivial functional dependencies, X → Y, X is a superkey—a set of attributes that uniquely identifies a row. BCNF is stricter than 3NF and is used to handle anomalies that may not be covered by the earlier normal forms.

5 Rule Five

The Fourth Normal Form (4NF) addresses multi-valued dependencies, which occur when one attribute in a table uniquely determines another attribute, and both are independent of all other attributes. To satisfy 4NF, a table must be in BCNF, and it must not contain any non-trivial multi-valued dependencies unless they are a result of a primary key constraint. This form ensures that each record models one fact about your data, preventing scenarios where certain facts could imply the presence of other unrelated facts.

6 Rule Six

Finally, the Fifth Normal Form (5NF), also known as "Projection-Join Normal Form" (PJNF), deals with composite dependencies and is concerned with reconstructing information from smaller pieces without redundancy. A table is in 5NF if it's in 4NF and, after decomposing it into smaller, related tables, you can reconstruct it using joins without introducing redundancy. Achieving 5NF often requires complex design considerations and is typically pursued when dealing with intricate data relationships.