Normalization in Database with example
What is Normalization ?
Normalization : In relational database management design, Normalization is a technique in which the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. In other words Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data. It divides larger tables to smaller tables and links them using relationships.
Why Database Normalization is important ?
Normalization basically affords you to store data with little duplication - how much depends on the actual degree of normalization.Duplication makes the data difficult to maintain without additional work, like creating triggers or manual conventions to store data in lock steps. Normalization prevents anomalies. Anomalies are nothing but problems that can occur in poorly planned or un-normalized databases.There are three types of anomalies; update, deletion and insertion anomalies. These anomalies are caused by having duplicate values in a table, or having no enforcement for uniqueness and referential integrity.Data without normalization contains duplicate values, which leads to anomalies.
According to E. F. Codd the objectives of normalization were stated as follows:
- 1. To free the collection of relations from undesirable insertion, update and deletion dependencies.
- 2. To reduce the need for restructuring the collection of relations as new types of data are introduced, and thus increase the life span of application programs.
- 3. To make the relational model more informative to users.
- 4. To make the collection of relations neutral to the query statistics, where these statistics are liable to change as time goes by.
As of now there are total 8 normal forms, but to keep our data consistent & non-redundant the first 3 Normal Forms are sufficient.
Types of Database Normalization
- 1 : First Normal Form (1 NF)
- 2 : Second Normal Form (2 NF)
- 3 : Third Normal Form (3 NF)
- 4: Boyce-Codd Normal Form (3.5 NF)
- 5: Fourth Normal Form (4 NF)
- 6: Fifth Normal Form (5 NF)
In this Database management system tutorial i will discuss about First Normal Form (1 NF) , Second Normal Form (2 NF) and Third Normal Form (3 NF) .
1NF (First Normal Form) Rules
- 1: There are no repeating or duplicate fields
- 2: Each cell contain only a single value
- 3: Each record is unique
The below table is not 1NF-
After completing database normalization (1 NF) we will get two table which are look like
2NF (Second Normal Form) Rules
- 1: Be in 1NF
- 2: All non-key fields depend on all components of the primary key .
- 3: Guaranteed when primary key is a single field.
What is a Primary Key?
A primary is a single column value used to identify a database record uniquely. It has following attributes
- 1: A primary key cannot be NULL
- 2: A primary key value must be unique
- 3: The primary key values cannot be changed
- 4: The primary key must be given a value when a new record is inserted.
The below table is not 2NF-
After completing database normalization (2 NF) we will get two table which are look like
3NF (Third Normal Form) Rules
- 1: No non-key field depends upon another
- 2: All non-key fields depend only on the primary key .
- 3: Other words ; There can be no independencies among non-key attribute
- 4: Has no transitive functional dependencies
- 5: Be in 2NF.
The below table is not 3NF-
transitive functional dependencies
A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change . Consider the table 1. Changing the non-key column Full Name may change Salutation.
After completing database normalization (3 NF) we will get two table which are look like
If you would like to know more about that , you can visit click here . That’s it. hope you will like it. If you have any query about this topic then you can leave a comments and Of course don’t forget to share with your friends.