Normalization in Databases

Normalization in Databases

Definition - Normalization is a systematic approach of organizing data in a database to reduce redundancy and improve data integrity.

Purpose - The primary goal of normalization is to: - Eliminate redundant data (for example, storing the same data in more than one table). - Ensure data dependencies make sense (only storing related data in a table).

Eliminating Redundancy - Reducing the duplication of data in the database to save storage and ensure consistency.

Functional Dependency - A functional dependency is a relationship that exists when one attribute uniquely determines another attribute.

Various Types of Normal Forms in Databases

1. First Normal Form (1NF)    - Ensures the table has no repeating groups or arrays. Each cell is single-valued, and each record needs to be unique.

2. Second Normal Form (2NF)      - Achieved when it is in 1NF and all non-key attributes are fully functionally dependent on the primary key.

3. Third Normal Form (3NF)       - A table is in 3NF if it is in 2NF and all the attributes are functionally dependent only on the primary key.

Explaining 1NF with Examples

Before 1NF (Table Example): Student Table - Columns: Student_No, Student_name, Student_Phone (multiple entries), Student_state, Student_Country

Issues - The Student_Phone column contains multiple values, violating 1NF's rule of atomicity.

After Applying 1NF - Modify the Student_Phone column to ensure that each cell contains only a single value. This might involve creating separate records for each phone number or redesigning the table structure to accommodate multiple phone numbers more appropriately.

Explaining 2NF with Examples

Scenario with Student and Teacher Tables - Suppose we have a Student table linked to Courses through a CourseID, and each course is taught by a Teacher.

Before 2NF - The Student table might include columns for Course details and Teacher information, leading to partial dependency on the primary key (Student_No).

After Applying 2NF - Remove the partial dependency by separating the Course and Teacher information into a separate table. Ensure that all non-key attributes in the Student table are fully functionally dependent on the Student_No.

Explaining 3NF with Examples

Before 3NF (Table Example): Student Table - Columns: Student_No, Student_name, Student_Phone, Student_state, Student_Country

Issues - Suppose the Student_state is functionally dependent on Student_Country, not just on the primary key (Student_No). This transitive dependency violates 3NF.

After Applying 3NF - Remove the transitive dependency by ensuring that all attributes are only dependent on the primary key. This might involve creating a separate table to hold the relationship between states and countries, removing the dependency of Student_state on Student_Country from the Student table.

These examples illustrate how normalization progresses through its various forms to ensure the database structure is optimized for efficiency, reducing redundancy, and enhancing data integrity.