What is Database Normalization?

What is Database Normalization?

Database normalization is a process of designing a relational database in a way that reduces data redundancy and improves data integrity. Data redundancy means having the same data stored in multiple places, which can lead to inconsistency and waste of storage space. Data integrity means ensuring that the data is accurate, complete, and consistent with the real-world facts.

Normalization involves applying a series of rules or principles, called normal forms, to the structure of the database tables and columns. Each normal form has a specific goal and criteria that must be met to achieve it. By applying these normal forms, the database designer can create a database that is well-organized, efficient, and easy to query and maintain.

Why is Database Normalization Important?

There are many benefits of normalizing a database, such as:

• Minimizing data redundancy, which saves storage space and reduces the risk of data inconsistency.
• Minimizing null values, which can cause problems in querying and manipulating data.
• Minimizing data modification anomalies, which are errors or inconsistencies that occur when inserting, updating, or deleting data in a non-normalized database.
• Simplifying queries, which makes them faster and easier to write and understand.
• Improving database performance, which enhances the speed and reliability of the database system.
• Enhancing data security, which prevents unauthorized access or modification of data by enforcing constraints and relationships.
• Facilitating data analysis, which enables better decision making and reporting based on accurate and consistent data.

How to Normalize a Database?

The process of normalizing a database involves the following steps:

1. Identify the entities and attributes that are relevant to the database. An entity is a real-world object or concept that can be identified uniquely, such as a person, a product, or an order. An attribute is a property or characteristic of an entity, such as a name, a price, or a date.

2. Create a table for each entity and assign a primary key to each table. A primary key is an attribute or a combination of attributes that uniquely identifies each record in a table. For example, an employee ID can be a primary key for an employee table.

3. Identify the relationships between the entities and create foreign keys to link the tables. A foreign key is an attribute or a combination of attributes in one table that refers to the primary key of another table. For example, an order ID can be a foreign key in an order item table that refers to the primary key of an order table.

4. Apply the normal forms to each table and modify the structure as needed. The normal forms are:

   • First Normal Form (1NF): A table is in 1NF if it has no repeating groups or arrays of data. Each attribute must have a single value for each record. For example, if an employee can have multiple phone numbers, they should not be stored in one column separated by commas, but rather in separate rows or in another table.
   • Second Normal Form (2NF): A table is in 2NF if it is in 1NF and has no partial dependencies. A partial dependency is when an attribute depends on only part of the primary key. For example, if an order item table has a composite primary key of order ID and product ID, and also has attributes such as product name and product price, then these attributes depend only on the product ID and not on the whole primary key. To eliminate partial dependencies, these attributes should be moved to another table with product ID as the primary key.
   • Third Normal Form (3NF): A table is in 3NF if it is in 2NF and has no transitive dependencies. A transitive dependency is when an attribute depends on another attribute that is not part of the primary key. For example, if an order table has attributes such as customer ID, customer name, and customer address, then customer name and customer address depend on customer ID and not on order ID. To eliminate transitive dependencies, these attributes should be moved to another table with customer ID as the primary key.
   • Boyce-Codd Normal Form (BCNF): A table is in BCNF if it is in 3NF and has no non-trivial functional dependencies that are not determined by candidate keys. A candidate key is an attribute or a combination of attributes that can uniquely identify each record in a table. A functional dependency is when an attribute determines another attribute. A non-trivial functional dependency is when an attribute determines another attribute that is not itself. For example, if an employee table has attributes such as employee ID, department ID, department name, and department manager, then department name and department manager depend on department ID and not on employee ID. To eliminate non-trivial functional dependencies that are not determined by candidate keys, these attributes should be moved to another table with department ID as the primary key.
   • Fourth Normal Form (4NF): A table is in 4NF if it is in BCNF and has no multi-valued dependencies. A multi-valued dependency is when an attribute has more than one value for a given combination of other attributes. For example, if an employee table has attributes such as employee ID, skill, and project, and an employee can have multiple skills and work on multiple projects, then there is a multi-valued dependency between skill and project. To eliminate multi-valued dependencies, these attributes should be split into separate tables with employee ID as the foreign key.
   • Fifth Normal Form (5NF): A table is in 5NF if it is in 4NF and has no join dependencies. A join dependency is when a table can be decomposed into two or more tables and then reconstructed by joining them without losing any information. For example, if a product table has attributes such as product ID, color, size, and price, and each product can have multiple colors, sizes, and prices, then there is a join dependency between these attributes. To eliminate join dependencies, these attributes should be split into separate tables with product ID as the foreign key.

Example of Database Normalization

To illustrate the process of database normalization, let us consider a simple example of a database that stores information about customers, orders, and products. The database has one table called CustomerOrderProduct that contains the following data:

CustomerID	CustomerName	OrderID	OrderDate	ProductID	ProductName	ProductPrice	Quantity
101	Alice	1001	2023-01-01	P001	Laptop	500	1
101	Alice	1001	2023-01-01	P002	Mouse	10	2
102	Bob	1002	2023-01-02	P003	Keyboard	20	1
103	Charlie	1003	2023-01-03	P001	Laptop	500	2
103	Charlie	1003	2023-01-03	P004	Monitor	100	1

This table is not normalized and has several problems, such as:

• Data redundancy: The customer name, order date, product name, and product price are repeated for each order item.
• Null values: If a customer has not placed any order or a product has not been ordered by any customer, there will be null values in the table.
• Insert anomaly: To insert a new customer or a new product, we need to know the order details as well.
• Update anomaly: If we want to change the price of a product or the name of a customer, we need to update multiple rows in the table.
• Delete anomaly: If we delete an order from the table, we also lose the information about the customer and the product.

To normalize this table, we can apply the normal forms as follows:

• First Normal Form (1NF): The table is already in 1NF, as it has no repeating groups or arrays of data. Each attribute has a single value for each record.

• Second Normal Form (2NF): The table has a partial dependency, as the customer name depends only on the customer ID and not on the whole primary key (customer ID and order ID). To eliminate this partial dependency, we can create two tables: Customer and Order. The Customer table will have customer ID as the primary key and customer name as an attribute. The Order table will have customer ID and order ID as the composite primary key and order date as an attribute. The customer ID in the Order table will be a foreign key that references the customer ID in the Customer table.

  • Customer

    CustomerID (PK)	CustomerName
    101	Alice
    102	Bob
    103	Charlie

  • Order

    CustomerID (PK) (FK)	OrderID (PK)	OrderDate
    101	1001	2023-01-01
    102	1002	2023-01-02
    103	1003	2023-01-03

• Third Normal Form (3NF): The table still has a partial dependency, as the product name and product price depend only on the product ID and not on the whole primary key (customer ID, order ID, and product ID). To eliminate this partial dependency, we can create another table: Product. The Product table will have product ID as the primary key and product name and product price as attributes. The product ID in the OrderItem table will be a foreign key that references the product ID in the OrderItem table. The OrderItem table will have customer ID, order ID, and product ID as the composite primary key and quantity as an attribute.

  + Product
      | ProductID (PK) | ProductName | ProductPrice |
      | ---            | ---         | ---          |
      | P001           | Laptop      | 500          |
      | P002           | Mouse       | 10           |
      | P003           | Keyboard    | 20           |
      | P004           | Monitor     | 100          |
  + OrderItem
      | CustomerID (PK) (FK)  	| OrderID (PK) (FK)  	| ProductID (PK) (FK)  	| Quantity  	|
      |-	|-	|-	|-	|
     	|101  	|1001  	|P001  	|1  	|
     	|101  	|1001  	|P002  	|2  	|
     	|102  	|1002  	|P003  	|1  	|
     	|103  	|1003  	|P001  	|2  	|
     	|103  	|1003  	|P004  	|1  	|

  • Boyce-Codd Normal Form (BCNF): The tables are already in BCNF, as they have no non-trivial functional dependencies that are not determined by candidate keys. Each attribute is fully dependent on the primary key of its table.
  • Fourth Normal Form (4NF): The tables are already in 4NF, as they have no multi-valued dependencies. Each attribute has a single value for each combination of other attributes.
  • Fifth Normal Form (5NF): The tables are already in 5NF, as they have no join dependencies. Each table represents a single entity or relationship and cannot be further decomposed without losing information.

  Conclusion

  Database normalization is a process of designing a relational database that reduces data redundancy and improves data integrity. Normalization involves applying a series of rules or principles, called normal forms, to the structure of the database tables and columns. By applying these normal forms, the database designer can create a database that is well-organized, efficient, and easy to query and maintain. Normalization has many benefits, such as minimizing data modification anomalies, simplifying queries, improving database performance, enhancing data security, and facilitating data analysis. However, normalization also has some drawbacks, such as increasing the number of tables and joins, requiring more complex queries and indexes, and reducing query performance in some cases. Therefore, the database designer should balance the trade-offs between normalization and denormalization based on the requirements and objectives of the database system.

Tweet

Read More..