Identify the requirements of First, Second, and Third Normal Forms

First, second and third normal forms each have their respective requirements. The three forms and their corresponding characteristics are listed below.

1. First normal form: When tables are placed into first normal form, the relations between tables cannot contain any repeating groups.
2. Second normal form: When tables are placed into second normal form, every field in the relation must be functionally dependent upon the entire primary key.
3. Third normal form: When tables are placed into third normal form, the relation cannot contain any transitive dependencies.

The relationship between the "First Normal Form (1NF)", "Second Normal Form (2NF)", and "Third Normal Form (3NF)" in database design is hierarchical. Each normal form builds upon the previous one, progressively refining the database structure to reduce redundancy, improve data integrity, and eliminate anomalies.
1NF (First Normal Form): Elimination of Repeating Groups

• Definition: A relation is in 1NF if all attributes (columns) contain only atomic values (indivisible) and each value in a column is of the same type.
• Key Principle: Eliminate repeating groups or arrays within a single column.
• Objective: Ensure that the table structure adheres to the tabular format, with unique rows and atomic data.

Example:
Non-1NF Table:

| OrderID | ProductNames        |
|---------|---------------------|
| 1       | Pen, Pencil         |
| 2       | Notebook            |

1NF Table:

| OrderID | ProductName         |
|---------|---------------------|
| 1       | Pen                 |
| 1       | Pencil              |
| 2       | Notebook            |

2NF (Second Normal Form): Removal of Partial Dependencies:

• Definition: A relation is in 2NF if it is in 1NF and every non-prime attribute (a column that is not part of any candidate key) is fully functionally dependent on the entire primary key.
• Key Principle: Remove partial dependencies, where a non-prime attribute depends on part of a composite primary key.
• Objective: Ensure that every non-prime attribute relates to the whole key, not just a part of it.

Example:
1NF Table:

| OrderID | ProductID | ProductName |
|---------|-----------|-------------|
| 1       | P1        | Pen         |
| 1       | P2        | Pencil      |

• Here, `ProductName` depends only on `ProductID`, not on the entire composite key (`OrderID`, `ProductID`).

2NF Tables:
Orders Table:

| OrderID | ProductID |
|---------|-----------|
| 1       | P1        |
| 1       | P2        |

Products Table:

| ProductID | ProductName |
|-----------|-------------|
| P1        | Pen         |
| P2        | Pencil      |

3NF (Third Normal Form): Removal of Transitive Dependencies:

• Definition: A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on the primary key.
• Key Principle: Remove transitive dependencies, where a non-prime attribute depends on another non-prime attribute rather than directly on the primary key.
• Objective: Ensure that every non-prime attribute is directly dependent on the primary key.

Example:
2NF Table:

| OrderID | ProductID | ProductName | ProductCategory |
|---------|-----------|-------------|-----------------|
| 1       | P1        | Pen         | Stationery      |
| 1       | P2        | Pencil      | Stationery      |

• Here, `ProductCategory` depends on `ProductName` rather than directly on `OrderID` and `ProductID`.

3NF Tables:
Orders Table:

| OrderID | ProductID |
|---------|-----------|
| 1       | P1        |
| 1       | P2        |

Products Table:

| ProductID | ProductName | ProductCategory |
|-----------|-------------|-----------------|
| P1        | Pen         | Stationery      |
| P2        | Pencil      | Stationery      |

Relationship Between 1NF, 2NF, and 3NF:

1. Dependency: Each form builds upon the previous one:
   • 1NF ensures atomicity and a tabular format.
   • 2NF eliminates partial dependencies, making attributes dependent on the whole primary key.
   • 3NF removes transitive dependencies, ensuring direct dependency on the primary key.
2. Progressive Refinement: Each form reduces data redundancy and potential anomalies:
   • 1NF addresses structural issues.
   • 2NF removes redundancy caused by composite keys.
   • 3NF further refines relations by focusing on dependencies among non-prime attributes.
3. Prerequisites: A relation must satisfy the conditions of 1NF before moving to 2NF, and it must satisfy 2NF before being evaluated for 3NF.

By adhering to these normal forms, database designers achieve a well-structured schema that supports efficient querying, data integrity, and scalability.

Normalization theory arose out of entity relationship analysis when further refinement was needed. Appropriate decomposition of existing tables into tables which eliminate redundancy and defining foreign keys in the old table and referencing the primary keys of the new ones represents the general process. The underlying theory is referred to as normalization theory and is based on functional dependencies.
Furthermore, its purpose is to take a poorly designed table and transform it into a table with a sound structure which enable the efficient execution of queries. After the normalization process has been carried out, one should take a given table and test it against the "definition of normal forms" to determine whether it is properly designed. If it is not designed properly, one should make the appropriate modifications, retest it, and repeat this process until the table structure is sound. Figure 4-4 outlines this process.

A database is in second normal form if it satisfies the following conditions:

1. It is in first normal form,
2. All non-key attributes are fully functional dependent on the primary key

In a table, if attribute B is functionally dependent on A, but is not functionally dependent on a proper subset of A, then B is considered fully functional dependent on A. Hence, in a second normal form table, all non-key attributes^[1] cannot be dependent on a subset of the primary key. Note that if the primary key is not a composite key, all non-key attributes are always fully functional dependent on the primary key.
A table that is in first normal form and contains only a single key as the primary key is automatically in second normal form.

A database is in third normal form if it satisfies the following conditions:

1. It is in second normal form and
2. There is no transitive functional dependency

Transitive functional dependency implies that the following relationships in the table exist:
A is functionally dependent on B, and B is functionally dependent on C. In this case, C is transitively dependent on A by means of B.

[1]Non-key attributes: Non-key attributes are the attributes or fields of a table, other than candidate key attributes/fields in a table.