What is Normalization? How INF and 2NF works in database? Give examples.

Definition of Normalization:

Normalization is a systematic database design technique used in Relational Database Management Systems (RDBMS) to organize tables, minimize data redundancy, and eliminate data anomalies (Insertion, Update, and Deletion anomalies). It involves dividing large, complex tables into smaller, well-structured tables and establishing logical relationships between them using primary and foreign keys.

1. First Normal Form (1NF)

How 1NF Works:
A database table is in First Normal Form (1NF) if it satisfies the following conditions:

  
• Each column/field contains only atomic (indivisible) values.
  
• There are no repeating groups or arrays stored within a single field.
  
• Each entry in a column must be of the same data type.
  
• Each record is uniquely identifiable by a Primary Key.

Example of 1NF:

Unnormalized Table (Contains multi-valued attributes in the 'Subjects' column):

Normalized Table (In 1NF):

2. Second Normal Form (2NF)

How 2NF Works:
A table is in Second Normal Form (2NF) if it satisfies two main criteria:

  
• The table must already be in 1NF.
  
• It must not contain any partial dependency. This means every non-prime attribute (a field not part of any candidate key) must be fully dependent on the complete primary key, rather than just a part of a composite primary key.

Example of 2NF:

1NF Table with Partial Dependency:
Consider a table where the composite Primary Key is (TeacherID, Subject).

Problem: Here, TeacherAge depends only on TeacherID, which is just a part of the composite primary key (TeacherID, Subject). This causes partial dependency.

Decomposition into 2NF: To make the database 2NF compliant, we split the table into two separate tables.

Table 1: Teacher Details (Primary Key: TeacherID)

Table 2: Teacher Subject Mapping (Composite Primary Key: TeacherID, Subject)