Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update, and Deletion Anomalies. It is a multi-step process that puts data into tabular form, removing duplicated data from the relation tables. The process of normalization is divided into several normal forms.

• Understand the concept of normal forms.
• Learn about the different types of normal forms.
• Apply normalization techniques to database schema design.

A table is in the first normal form if:

• It contains only atomic (indivisible) values.
• Each column contains values of a single type.
• Each column contains unique values.

Example:

Consider the following table that is not in 1NF:

To convert it to 1NF, we need to split the multi-valued attribute into separate rows:

A table is in the second normal form if:

• It is in 1NF.
• All non-key attributes are fully functional dependent on the primary key.

Example:

Consider the following table that is in 1NF but not in 2NF:

To convert it to 2NF, we need to remove partial dependencies by creating separate tables:

Students Table:

Courses Table:

A table is in the third normal form if:

• It is in 2NF.
• All the attributes are functionally dependent only on the primary key.

Example:

Consider the following table that is in 2NF but not in 3NF:

To convert it to 3NF, we need to remove transitive dependencies by creating separate tables:

Students Table:

Courses Table:

Instructors Table:

A table is in BCNF if:

• It is in 3NF.
• For every functional dependency (X → Y), X should be a super key.

Example:

Consider the following table that is in 3NF but not in BCNF:

To convert it to BCNF, we need to ensure that every determinant is a candidate key:

Students Table:

Courses Table:

Given the following table, convert it to 1NF:

Solution:

Given the following table in 1NF, convert it to 2NF:

Solution:

Orders Table:

Products Table:

Given the following table in 2NF, convert it to 3NF:

Solution:

Orders Table:

Suppliers Table:

Understanding and applying normal forms is crucial for designing efficient and reliable databases. By following the principles of normalization, you can ensure that your database is free from redundancy and anomalies, making it easier to maintain and query. In the next section, we will delve into the normalization process in more detail.