Database Normalization | Nepal Bank Limited IT Engineer 2078 Question Paper

 Q. What is Database Normalization? Why do we need to normalize database? Explain with examples. (3+4)marks

answer:

Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

 It was first proposed by Edgar F. Codd as part of his relational model.

The objectives of database normalization:

• It reduces the chances for anomalies to occur in a database.
• To avoid creating and updating any unwanted data connections and dependencies.
• To prevent unwanted deletions of data.
• To optimize storage space.
• To reduce the delay and complexity of checking databases when new types of data need to be introduced.
• It is helpful to divide the large database table into smaller tables and link them using relationship.

Databases are classified by their level of normalization like 1NF to 5NF(rarely used). Most commonly used are first 3 types.

• 1NF: Removes duplicates and creates separate tables for groups of related data.
• 2NF: Removes subgroups of data present in multiple rows of a table and creates new tables, with connections between them.
• 3NF: Deletes columns that do not depend on the main key value.
• 4NF: Deletes all dependencies between multiple values 

We can list the some advantages of normalizing the database as:

1. Data Consistency
2. Sound Data Connection
3. Connection to other systems
4. Cost Saving
5. Increased Security

The purpose of normalization is to store each row of data only once, to avoid data anomalies.

A database table looks like a spreadsheet. One item of data, such as a first name, a last name, a phone number, or a street address, appears in each box, or cell, at each intersection of a row and column.

When you normalize a database, you have four goals: arranging data into logical groupings , minimizing the amount of duplicate data , building a database in which you can access and manipulate the data quickly and efficiently without compromising the integrity of the data.

Data normalization helps you design new databases to meet these goals or to test databases to see whether they meet the goals.

Data normalization is primarily important in the transactional, or online transactional processing (OLTP), database world, in which data modifications (e.g., inserts, updates, deletes) occur rapidly and randomly throughout the stored data. 

The need for normalization is basically for making sure that a table contains only data directly related to the primary key, that each data field contains only one item of data, and that redundant (duplicated and unnecessary) data is eliminated.

Data normalization example-

If this table is used for the purpose of keeping track of the price of items and the user wants to delete one of the customers, he or she will also delete the price. Normalizing the data would mean understanding this and solving the problem by dividing this table into two tables, one with information about each customer and the product they bought and the second with each product and its price. Making additions or deletions to either table would 

not affect the other.

 Stay safe. Thank you!