Data normalization is a process in database design that organizes data to minimize redundancy and avoid data anomalies. The main idea is to divide large tables into smaller ones and link them using relationships. The purpose of normalization is to eliminate undesirable characteristics like insertion, update and deletion anomalies.

There are several types of normalization, each referred to as a "normal form." Here are the primary normal forms:

1. First Normal Form (1NF): In this form, the table has a primary key, and each column contains atomic (indivisible) values, with no repeating groups or arrays.
   Example: A table with columns student_id, student_name, subject, where each student_id is unique, and there are no repeating groups or arrays.
   
   
2. Second Normal Form (2NF): In this form, the table is in 1NF and all non-key columns are fully dependent on the primary key. This means that if a table has a composite primary key, then every other column should be dependent on the full set, not just part of it.
   Example: In a table with columns student_id, subject_id (composite key), and subject_name, subject_name depends on the full primary key (student_id and subject_id), not just one part of it.
   
   
3. Third Normal Form (3NF): In this form, the table is in 2NF and there are no transitive dependencies of non-key attributes.
   Example: In a table with columns student_id, subject_id, and subject_teacher, subject_teacher is not dependent on student_id.
   
   
4. Boyce-Codd Normal Form (BCNF): This is a stronger version of 3NF. In this form, every non-trivial functional dependency is on a superkey.
   Example: A table with columns student_id, subject_id, and subject_teacher, where subject_id -> subject_teacher, and subject_id is a superkey.
   
   
5. Fourth Normal Form (4NF): In this form, the table is in BCNF and there are no multi-valued dependencies.
   
   
6. Fifth Normal Form (5NF) or Project-Join Normal Form (PJNF): In this form, the table is in 4NF and every join dependency is implied by the candidate keys.
   
   

   These are some of the basic normal forms.
   There are even more advanced ones like Domain/Key Normal Form (DKNF) and Sixth Normal Form (6NF), which are used less frequently.

   Let's consider a simple database of a school. A table that hasn't been normalized might look something like this:
   
   

   

   In this table, there are several problems due to lack of normalization:

   
   Redundancy: You can see that the names of subjects and teachers are repeated for different students. If a teacher changes, you would need to change that teacher's name for every student they teach. This is inefficient and error-prone.
   
   Update Anomalies: Suppose John drops out of the Math class. The row would have to be updated to remove Math from John's subjects and Mrs. Smith from his teachers. This could potentially lead to errors and inconsistencies.
   
   Insertion Anomalies: If a new subject is introduced, but no student has yet enrolled in it, we cannot record the subject in this table until at least one student enrolls.
   
   Deletion Anomalies: If a student leaves the school, their record will be removed, but this might result in loss of information. For instance, if Alex is the only student taking History, deleting Alex would result in loss of the information that Mr. White teaches History.
   Atomicity: The 'Subjects' and 'Teacher' columns contain multiple values (non-atomic), which is against the rules of the first normal form (1NF).

   To normalize the above table, we would first need to break it down into smaller, related tables. Each of these tables would represent a distinct entity, and we would eliminate the redundancies and anomalies we pointed out before. Here's what that might look like:
   
   

   

   In the new design, each table now represents one entity type: Students, Subjects, and Teachers. The Enrollments table is a junction table that represents the many-to-many relationship between Students and Subjects (taught by specific Teachers). Each row in the Enrollments table represents one student enrolled in one subject taught by a specific teacher, which removes redundancy and anomalies, and ensures data integrity.

   This design is in Third Normal Form (3NF) as it satisfies the conditions of 1NF (each cell contains a single value), 2NF (all non-key columns are fully dependent on the primary key), and 3NF (there are no transitive dependencies).