Database normalization is the process of organizing data in a database in such a way that it reduces data redundancy and dependency. Normalization is a crucial step for designing a well-structured and maintainable database. The normal forms are based on a set of rules that must be followed to make sure that the database is normalized. There are different normal forms, ranging from first normal form (1NF) to fifth normal form (5NF).
1. First Normal Form (1NF): The first normal form requires that a table must have a primary key and all of its columns must be atomic, meaning that each column can contain only one value. For example, if we have an employee table, each column should contain information about the employee, such as the first name or the last name, and not a list of values like phone numbers or email addresses.
2. Second Normal Form (2NF): The second normal form requires that a table should be in 1NF and should not have any partially dependent columns. A column is said to be partially dependent if it depends on only part of the primary key. For example, if we have an employee_project table with the primary key of employee_id and project_id, and a project_cost column, the project_cost column depends only on the project_id and not the employee_id. In this case, we need to split the table into two separate tables.
3. Third Normal Form (3NF): The third normal form requires that a table should be in 2NF and should not have any transitive dependencies. A transitive dependency occurs when a non-key column depends on another non-key column. For example, if we have an employee table with columns for employee_name, department_name, and department_location, we have a transitive dependency between department_name and department_location since department_location depends on department_name. In this case, we can split the table into two separate tables – one for employees and one for departments – and link them with a foreign key.
4. Boyce-Codd Normal Form (BCNF): BCNF is similar to 3NF, but it applies to tables that have more than one candidate key, meaning that there may be more than one way to uniquely identify a row. In BCNF, every determinant must be a candidate key.
5. Fourth Normal Form (4NF): 4NF is a stricter version of BCNF that requires that a table should not have any multi-valued dependencies. A multi-valued dependency occurs when a non-key attribute depends on a subset of the primary key but not on the full primary key. For example, if we have a table with a composite primary key of employee_id and project_id, and a column for skills, where an employee can have multiple skills for a single project, this table would not be in 4NF since the skills column contains multiple values.
6. Fifth Normal Form (5NF): 5NF is also called Projection-Join Normal Form (PJNF) and requires that a table should not have any join dependencies. A join dependency occurs when a table has multiple candidate keys that are joined together. For example, if we have a table with a composite primary key of employee_id and project_id, and a column for employee_skill, where an employee can have multiple skills across multiple projects, the table would not be in 5NF since the combination of employee_id and project_id cannot be separated. In this case, we can split the table into three separate tables – one for employees, one for projects, and one for skills – and link them with foreign keys.
Overall, normalization helps to reduce data redundancy and ensure data integrity by avoiding data inconsistencies and anomalies. The goal of normalization is to make the database more efficient, minimizes update and deletion anomalies, and avoid data anomalies when inserting, updating or deleting data.