Normalization in RDBMS

(1) : What is the key difference between First Normal Form (1NF) and Second Normal Form (2NF) 

The key difference between First Normal Form (1NF) and Second Normal Form (2NF) in database normalization lies in the types of redundancy they address:

First Normal Form (1NF):

• Purpose: Ensures that the data in a table is organized into rows and columns, and each column contains atomic (indivisible) values.
• Key Points:
  • No repeating groups or arrays within a column.
  • Each cell must hold a single value (no lists or sets).
  • All entries in a column must be of the same type.

Example of a Table Not in 1NF:

StudentID	Courses
1	Math, Science
2	History

This table violates 1NF because the Courses column contains multiple values.

Table in 1NF:

StudentID	Course
1	Math
1	Science
2	History

Now each cell contains atomic values.

---

Second Normal Form (2NF):

• Purpose: Builds on 1NF by eliminating partial dependencies. This means every non-prime attribute (non-key column) must depend on the whole primary key, not just a part of it.
• Key Points:
  • The table must already satisfy 1NF.
  • Ensures that all non-key attributes are fully functionally dependent on the primary key.

Example of a Table in 1NF but Not in 2NF:

StudentID	Course	Instructor
1	Math	Dr. Smith
1	Science	Dr. Jones
2	History	Dr. Smith

• Primary Key: (StudentID, Course)
• Problem: Instructor depends only on Course, not on the full (StudentID, Course) composite key.

Table in 2NF:

• Split the table into two:

1. Student-Course Table:

   StudentID	Course
   1	Math
   1	Science
   2	History

2. Course-Instructor Table:

   Course	Instructor
   Math	Dr. Smith
   Science	Dr. Jones
   History	Dr. Smith

---

Summary of the Key Difference:

• 1NF ensures the table has no repeating groups and all values are atomic.
• 2NF eliminates partial dependencies, ensuring all non-key attributes are fully dependent on the entire primary key.

(2) : The key difference between Second Normal Form (2NF) and Third Normal Form (3NF) ?

The key difference between Second Normal Form (2NF) and Third Normal Form (3NF) lies in how they address different types of data redundancy:

Second Normal Form (2NF):

• Purpose: Eliminates partial dependencies.
• Requirement: Every non-prime attribute (non-key column) must be fully functionally dependent on the entire primary key, not just part of it.
• Applicability: Applies only when the table has a composite primary key (i.e., a key with two or more attributes).

Example of a Table in 1NF but Not in 2NF:

OrderID	ProductID	ProductName	Quantity
1	101	Widget	10
1	102	Gadget	5

• Primary Key: (OrderID, ProductID)
• Issue: ProductName depends only on ProductID, not on the entire composite key (OrderID, ProductID).

Table in 2NF:

• Split into two tables:

1. Order Details:

   OrderID	ProductID	Quantity
   1	101	10
   1	102	5

2. Product Table:

   ProductID	ProductName
   101	Widget
   102	Gadget

Now, all non-prime attributes are fully dependent on the entire primary key.

---

Third Normal Form (3NF):

• Purpose: Eliminates transitive dependencies.
• Requirement: Every non-prime attribute must depend only on the primary key and not on any other non-prime attribute.

Example of a Table in 2NF but Not in 3NF:

EmployeeID	DepartmentID	DepartmentName
1	D101	HR
2	D102	IT

• Primary Key: EmployeeID
• Issue: DepartmentName depends on DepartmentID, which in turn depends on EmployeeID. This is a transitive dependency.

Table in 3NF:

• Split into two tables:

1. Employee Table:

   EmployeeID	DepartmentID
   1	D101
   2	D102

2. Department Table:

   DepartmentID	DepartmentName
   D101	HR
   D102	IT

Now, every non-prime attribute depends directly on the primary key, eliminating transitive dependencies.

---

Summary of Key Differences:

Aspect	2NF	3NF
Addresses	Partial dependencies.	Transitive dependencies.
Focus	Ensures non-prime attributes depend on the entire key.	Ensures non-prime attributes depend only on the key.
Elimination of Redundancy	Reduces redundancy caused by partial dependencies.	Reduces redundancy caused by transitive dependencies.
Precondition	Table must already be in 1NF.	Table must already be in 2NF.