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.

In the domain of database design, normalization is a systematic approach used to organize data in a database efficiently. This process is structured into several "normal forms" (NF), each designed to reduce data redundancy and improve data integrity. The relationship between the first, second, and third normal forms is both hierarchical and foundational, with each form building upon the principles established by its predecessors.

1. First Normal Form (1NF): The journey of database normalization begins with the first normal form. A table is in 1NF if it meets two basic criteria: Firstly, each column must contain atomic values, meaning that the values are indivisible (no repeating groups or arrays). Secondly, each record needs to be unique. This form eliminates duplicate rows and ensures that each piece of data is stored in its own field and row. The implementation of 1NF sets the stage for more advanced normalization by creating a clean, organized structure.
2. Second Normal Form (2NF): Once a table is in 1NF, the next step is to advance it to the second normal form. A table is in 2NF if it is already in 1NF and, additionally, all non-key attributes are fully functionally dependent on the primary key. This means that each column value is fully dependent on the entire primary key, not just a part of it. This step is particularly relevant in tables with composite primary keys (keys made up of more than one column). 2NF aims to reduce redundancy and dependency issues by ensuring that each piece of information is related to the whole key, thereby streamlining the relationship between data points and the primary key.
3. Third Normal Form (3NF): Building upon the principles of 1NF and 2NF, the third normal form adds another layer of refinement. A table is considered to be in 3NF if it is already in 2NF and all of its attributes are not only fully functionally dependent on the primary key but also non-transitively dependent. In simpler terms, no non-key attribute should depend on another non-key attribute. This form addresses the issue of transitive dependency, where non-key attributes depend on other non-key attributes, leading to indirect relationships within the table. By achieving 3NF, the design further eliminates redundancy, ensuring that each non-key attribute is directly related to the primary key, thus enhancing data integrity and consistency.

The progression from 1NF through 3NF is a process of increasing refinement, with each form addressing specific types of redundancy and dependency issues. This hierarchical structure ensures that a database designed up to the third normal form is efficient, streamlined, and robust, reducing redundancy and improving data integrity. The implementation of these normal forms is crucial for relational database design, as it shapes the way data is stored, accessed, and maintained, directly impacting the database's performance and utility.

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.