Database Normalization

In this blog we will learn about Database Normalization, it’s uses and different types of Normal Forms.

Before jumping straight towards the Normalization part let us first know what is a Database Management System(DBMS) and why we need Normalization.

Database Management System

Simply said a DBMS is software that is used to create, retrieve, manipulate and manage data. Some common DBMS are MySql, Oracle, Microsoft SQL Server, Postgresql, and many more.

It provides a set of rules and an Interface to do various operations like creating a database, storing data in it, updating the stored data, creating a table, etc.

Types of DBMS System

1.Heirarchical DBMS

In this type of model, data is structured in a tree-like layout. Data is stored in a hierarchical format either a top-down or bottom-top approach and is displayed using a parent-child arrangement. Parents can have multiple kids but a kid can have only one parent.

2. Network Model

The Network model allows multiple parents for any child. This helps us in creating a model for more complex scenarios like many-to-many relationships of order/parts. Here Entities are arranged in a graph that can be accessed from many points.

3. Relational Model

This model is the most popular among all the types. Here there is a relationship between data and is stored in a table made up of rows and columns. SQL is used for the manipulation of data stored.

4. Object-Oriented Model

This model uses objects which are themselves stored in the database. It’s based on object-oriented programming. It is used in applications that require high performance and faster results.

Advantages Of DBMS

1. A DBMS uses various powerful functions to effectively store and retrieve data.
2. The DBMS implies integrity constraints to get a high level of protection against prohibited access to data.
3. It provides backup and recovery subsystems that create an automatic backup of data from hardware and software failures and restores the data if required.
4. A DBMS schedules concurrent access to the data in such a manner that only one user can access the same data at a time.

Dis-Advantages Of DBMS

1. The cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization.
2. In certain organizations, all information is integrated into a common database that may be destroyed due to electrical loss or the storage medium database is compromised.

Need For Normalization

The need for Normalization is to make sure that the table contains data only directly dependent on the primary key, that each data field contains the only item and that redundant data is eliminated. It helps in properly organizing the data. In Normalization, we divide the larger table into small ones and link them through relations.

Types of Normal Forms

1. First Normal Form — 1NF

A table is in a 1NF if the column field in each tuple contains only an atomic value. This means that table will only hold single-valued attributes and dis-allows multi-valued attributes.

Before

After

2. Second Normal Form- 2NF

A table is in 2NF if first, it is already in First Normal Form and secondly, it does not have a non-prime attribute which is functionally dependent on a subset of any of the candidate key. Here a non-prime attribute is an attribute that is not part of any candidate key in the relation.

Before

After

3. Third Normal Form-3NF

A table which is an example of database relation is said to be in 3NF if first, the table is already in Second Normal Form and secondly if all the non-prime attributes are functionally dependent solely on the Primary key and do not have a transitive dependency on any other key.

Before

4. Boyce Code Normal Form-4NF

BCNF is a slightly stronger version of the Third Normal Form. For a table to be in BCNF first, it should be already in Third Normal Form, and secondly, for any Functional dependency let’s say A-> B, A should always be a Super key.

5. Fourth Normal Form-4NF

A table is in 4NF if first, it’s already in the Boyce-Codd Normal Form, and secondly, there is no multi-valued dependency. Multi-valued dependency means 2 or more independent columns are depending on the third column in a table.

Before

After

6. Fifth Normal Form-5NF

A table is said to be in 5NF if first, it’s already in the Fourth Normal Form, and secondly, if every non-trivial join dependency in the table is implied by the candidate keys, and joining should be lossless.

Before

After