Database Normalization Explained
Uploaded by
ScribdTranslationsDatabase Normalization Explained
Uploaded by
ScribdTranslationsBut what is normalization in databases?
Well, it is a process where rules are applied to all entities (tables) of the database,
in order to avoid failures in the project, such as data redundancy, mixing of different subjects
in the same entity, among other problems. The most well-known normal forms are the
first, second, and third normal forms. Basically, applying and respecting the rules of
with each of these normal forms, we can ensure a more consistent database,
a great possibility of success in your project.
1NF–First Normal Form
An entity will be in 1NF if and only if all its attributes (columns) are atomic.
that is, not to contain repetitive groups or columns that have more than one value.
Steps to be applied for the consolidation of 1NF:
Identification of the primary key of the table.
Identification of the column that contains duplicate data and removing them.
Creation of a new table with a primary key for storing the duplicate data.
Create a relationship between the main table and the secondary table
Ex.
In the table above, we can identify that the Phones field contains information for more than 1
customer's phone. In this case, we will have to create another table, so that we can
correctly store customer phone numbers. Below is how the tables should look after the
normalization.
2NF - Second Normal Form
An entity is in 2NF if and only if it is in 1NF and all its attributes (columns) are not
keys depend solely on the primary key. If any attribute depends only on one
part of the primary key, this is considered a violation of 2NF.
Steps to be applied for the consolidation of 2NF:
Identify columns that are not functionally dependent on the primary key of the table
Remove the column from the table and create a new table with that data.
Ex.
In this example, we have a table that stores product sales data. To apply the
2NF, we need to separate the product data into an entity that contains only
product data, and in this orders table only data related to the orders. Here it is.
example below after the application of 2NF.
3NF - Third Normal Form
An entity is in 3NF if and only if it is in 2NF and all attributes (columns) are not
key, are mutually independent, that is, there is no functional dependence between them, and
All depend solely and exclusively on the primary key in an irreducible manner.
Steps to be applied for the consolidation of 3NF:
Identify the columns that are functionally dependent on the other non-key columns
Remove these columns
Ex.
Como podemos ver no exemplo acima, temos duas colunas indicando os valores do produto. A
The TotalValue column depends on another non-key column. This is an example of information.
redundant, that is, we can know the total value of the products without the need for
store it. In this case, we will remove the TotalValue column. After applying the 3NF, the table will look like
like this:
There are other normal forms, such as the fourth and fifth, however, with the application of these three
normal forms, we will already be able to guarantee a healthier database and thus prevent
possible problems in the future.
[Link]
verify
[Link]