7.3: Define first (1NF), second (2NF), and third normal (3NF) form

Normalization is a technique in database design used to organize tables in a way that reduces redundancy and dependency of data. The use of this technique splits first into a larger table then into smaller tables, and links all by using entity relationships. The designer of the relational model, Edgar Codd, came up with this theory of normalization that introduces the First Normal (1NF) form and then continues to extend the theory into a second (2NF) and third normal form (3NF). The main idea with this theory is that a table is about a specific topic and only supporting topics are included, which minimizes duplicate data, avoids data modification issues, and simplifies queries. 

The first normal form (1NF), like all normal forms, has specific requirements to be validated as 1NF. A table, as below, must be two dimensional with rows and columns, and each row must contain data that relates to something. Each column must contain data for a single attribute of the thing it’s describing, each cell of the table must have only a single value, entries in any column must have the same type of data (Ex. If the entry in one row of a column contains hair color, all the other rows must contain hair color as well), each column must have a unique name, all rows should be uniquely identified (has to have some unique ID), and the order of the columns and rows must have no significance. Tables in first normal form are subject to deletion and insertion anomalies; they may prove useful in some applications but can be unreliable in others.  

Figure \(\PageIndex{1}\): 1^st Normal Form ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

Second normal form (2NF) must have all attributes or non-key columns dependent on the key. For example, if the data is based on making an order as shown in Figure 1, the price of the item isn’t determined by the primary key or unique identifier of the customer, so this would break the second normal form. In this case, the table can be split into two, with one table having the primary key as Customer_ID and all of their personal information and the second being a table with the primary key as the item name and the supplier information and prices. All of this information in each table is now correctly dependent on the primary key.  

Figure \(\PageIndex{2}\): 2^nd Normal Form. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

Since the second normal form has two separate tables, a junction table is necessary where both keys are represented in the same table to show who bought what. This can also be used as a compound key where two primary keys are conjoined to represent each other. 

Figure \(\PageIndex{3}\): Junction Table. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

In the third normal form (3NF), all columns can be determined only by the key in the table and no other column. Having this type of normalization gets rid of redundancy and is especially vulnerable to some types of modification anomalies. Take a look at Figure \(\PageIndex{2}\) where the items table has duplicate supplier phone numbers and supplier names. If Sony were to have more products under their item key, the supplier phone number would keep repeating because it’s directly related to the supplier name and will always be that same phone number. 

Figure \(\PageIndex{4}\): Split this table. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

Separating these two tables into individual representations makes all of the columns dependent on only the key in the table and no other column, making the third normal form valid. 

Figure \(\PageIndex{1}\): 3^rd Normal Form. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

• Some researchers discovered that anomalies could occur, and the conditions for Boyce-Codd Normal Form (BCNF) were identified. These normal forms are well-defined so the relation in BCNF is in 3NF, a relation in 3NF is in 2NF, and a relation in 2NF is in 1NF can occur. 
• Therefore, the normal forms 2NF through BCNF concern anomalies that happen from functional dependencies. They led to the definition of fourth normal form (4NF) and fifth normal form (5NF). 
• Table \(\PageIndex{1}\) shows other anomalies which occurred because of another kind of dependency called a multivalued dependency. Those anomalies could be eliminated by placing each multivalued dependency in a relation of its own, a condition known as 4NF. 

Anomaly	Norma Forms	Design Principles
Functional Dependencies	1NF, 2NF, 3NF, BCNF	BCNF: Design tables so that every determinant is a candidate key.
Multivalued Dependencies	4NF	4NF: Move each multivalued dependency to a table of its own.
Data Constrained	5NF, DK/NF	DK/NF: Make every constraint a logical consequence of candidate keys and domains.

Table \(\PageIndex{1}\): Form of Normalizations Theory . ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

The fifth normal form (5NF), also known as Project-Join Normal Form (PJ/NF) can lead to an anomaly where a table can be split apart but not correctly joined back together. However, the conditions this condition is rather complex, and generally, if a relation is in 4NF it is in 5NF. For more information about 5NF, consult this Wikipedia article.