Describe Normalization and Why It Is Used

Imagine you have a closet full of clothes that are messy, disorganized, and mixed up. It would be hard to find what you need, and you might buy duplicates or lose track of what you have. You would also waste a lot of space, make your closet look cluttered, and spend a lot of time searching for what you are looking for and putting together an outfit.

To solve this problem, you could apply the principles of normalization to your closet. You could sort your clothes by type, such as shirts, trousers, dresses, shoes, ties, scarves, and so on. Then, you could create separate hanging rails, shelves, or drawers for each type of clothing and categorize them accordingly. This way, you would avoid repetition, remove redundancy, and make your closet more efficient, organized, and easy to select for any occasion or need.

Normalization in relational data is like organizing your closet but for data. By moving data into separate tables and columns based on their attributes, you can avoid storing the same information in multiple places, reduce the risk of errors or inconsistencies, and improve the performance and quality of your database. Normalization is used to make data more manageable, reliable, and understandable.

The following describes the process of normalization:

1. Each entity is split (moved) out into its own table.
2. Each discrete attribute is created as its own column within the table.
3. Each entity instance (row) uses a primary key to identify it uniquely.
4. Related entities are linked with foreign key columns.

To conclude the previous steps in the normalization process, this data management technique organizes and represents data in a relational database more efficiently and consistently. By following the normalization steps, you can eliminate data redundancy, ensure data integrity, and simplify data manipulation and querying.

In Figure 2.4, you can see this process in action; the scenario you will explore is where there is a Sales data table with two rows containing duplicate entries for the order number and customer details; the Product column has an entry that is not a single-value character; it combines the attributes of name and price. You can change how the data is organized and represented in the database using normalization.

In Figure 2.4, you can see how the single Sales data table has been altered, so each data entity has been split into tables; duplicate data has been removed from the tables, such as the order number and customer details from the Sales data table, and each attribute of product name and price has been moved to its own column.

Figure 2.4 – Normalized data example

The benefit of normalization is that data manipulation is more efficient; in the scenario shown in Figure 2.4, should you wish to update the customer’s address details, you only need to change a single row value. In contrast, with the first table of data in Figure 2.4, with no normalization, you would need to change each row.

In this section, you learned about normalization. The following section looks at the SQL statements that can be used to query and manipulate relational data.