1st, 2nd, 3rd Normal Forms used in Relational Databases

Database Normalisation is a technique of organizing the data in the database. Tables are normalized to eliminate redundant information, to make updates easier, and to save storage space. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like

1. insertion,
2. update and
3. deletion

anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.
Normalization is used for mainly two purpose,

1. Eliminating reduntant (useless) data.
2. Ensuring data dependencies make sense i.e data is logically stored.

There are three different normalization levels, or forms. The following series of images describes the three normal forms.

1. first,
2. second, and
3. third normal forms

Database tables that are in First Normal Form (1NF) exhibit specific characteristics that are foundational to relational database normalization, aimed at enhancing data integrity and reducing redundancy. The adherence to 1NF is the first step in applying normalization rules to database design. Tables in 1NF must meet the following criteria:

1. Atomicity of Data:
   • Each cell or field in a 1NF table must contain atomic, indivisible values. This means that the data cannot be further divided into smaller meaningful units. For instance, a column meant to store phone numbers must contain a single phone number in each row, rather than a list or an array of numbers.
2. Homogeneity of Data:
   • Each column in a 1NF table must contain data of a single type or format. This ensures consistency in the values stored in each column, facilitating accurate data retrieval and manipulation. For example, a date column must only contain date values, adhering to a consistent date format.
3. Uniqueness of Rows:
   • Every row in a 1NF table must be unique, which is typically ensured by the presence of a primary key. The primary key can consist of one or more columns whose combined value uniquely identifies each row in the table, thereby eliminating duplicate records.
4. Non-Repeating Groups or Columns:
   • Tables in 1NF must not contain repeating groups or columns, such as multiple columns for phone numbers (Phone1, Phone2, etc.) or embedded tables. If a design requires storing multiple values for a single attribute (like multiple phone numbers for a contact), this should be managed through a separate table and a relational link rather than multiple columns in the same table.
5. Consistency in Column Definitions:
   • Each column must have a clear definition and constraint that enforces the type, format, and possibly the range of data values it can store. This ensures that all entries in a column are consistent in terms of data type and format, which is crucial for maintaining data integrity and facilitating reliable data operations.
6. Order Independence:
   - The order of rows and columns in a 1NF table is immaterial. The table's design should not rely on the order of rows or columns for data integrity or relationship representation. Data retrieval and manipulation operations should be based on data values and keys, not on the position of data within the table.

Meeting these criteria, a table in First Normal Form provides a solid foundation for further normalization. It addresses the most basic forms of data redundancy and inconsistency, setting the stage for applying higher normal forms to resolve more complex types of data dependencies and anomalies.
An entity is in First Normal Form (1NF) when all tables are two-dimensional with no repeating groups. A row is in first normal form (1NF) if all underlying domains contain atomic values only. 1NF eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.

Make a separate table for each set of related attributes and uniquely identify each record with a primary key.

1. Eliminate duplicative columns from the same table.
2. Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

An entity is in Second Normal Form (2NF) when it meets the requirement of being in First Normal Form (1NF) and additionally:

1. Does not have a composite primary key. Meaning that the primary key can not be subdivided into separate logical entities.
2. All the non-key columns are functionally dependent on the entire primary key.
3. A row is in second normal form if, and only if, it is in first normal form and every non-key attribute is fully dependent on the key.
4. 2NF eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key. An example is resolving many to many many relationships using an intersecting entity as described here Convert Many-to-many to one-to-many