Very first Normalization removes the multivalued attribute, That means there will be single tuple value for one attribute. In simple language, one column will have one value. for example, if we store the phone number in an employee table then each employee will have only one phone number in one single record.
Normalization is a process to make our database schema in such a way that we can access our data from the database very easily without any conflict and complication.
Normalization is a process to break a complex table structure into smaller tables to make easy access to data from the table.
Normalization is a process to remove redundancy attribute value form the database and also remove the functional dependency from the data table or database.
Redundancy in Normalization:
Redundancy is multiple presences of one record with the different-2 attribute value. For example, if an employee has two phone number thus we store both phone number with the use of two records in one table.
Functional Dependency in Normalization:
When one attribute uniquely identified by another attribute then one functional dependency exists here. I will discuss this section in detail in my upcoming blogs.
To achieve this normalization many normal forms have been defined. Normal form (1NF, 2NF, 3NF) were defined by ”Codd’ . The below-given fig. shows that all normalized relations are in 1NF. Some 1 NF relations are also in 2NF, and some 2NF relations are also in 3NF. So the motivation behind the Codd definition was that 2NF was more desirable than 1NF, and in turn, 3NF was more desirable than 2NF. Thus the database designers should aim for a design involving relations in 3NF, not ones that are merely in 2NF or 1NF.
But Codd’s original definition of 3NF turned out to be suffering from certain inadequacies. a stronger definition of 3NF, called Boyce-Codd normal form (BCNF) was proposed later by Boyce and Codd. All these normal forms based on the functional dependencies among the attribute of a relation subsequently, Fagin defined a fourth normal form 4NF and a fifth normal form 5NF based on the concept of multivalued dependencies and join dependencies respectively. the 5NF is also known as a projection-join normal form(PJ/NF)
Thus the normalization of data is a process of analyzing the given schema based on their functional dependencies and primary keys to achieving desirable properties of minimizing the redundancy and minimizing the insertion, deletion and update anomalies.
Need of Redundancy:
Normalization is the process of converting relation into a standard form.
for example lets we have a relation suppliers with attributes name, address, item, and price.
Suppliers(Name, Address, Item, Price)
Now the problem that can we face due to this relation is detailed below.
The address of the supplier is repeated once for each item supplied. So this causes repetition of the same information many times.
As we have redundancy in our relationship that is the same information, some get updated while others remain fixed, which will result in inconsistency. As in the above example if we update the address of supplier in one tuple while leaving it fixed in another. Thus we would not have a unique address for each supplier.
We can not insert a tuple in a relationship if that tuple does not have any value for any of the attribute in relation.
For example, We can not record an address for suppliers, if that supplier does not currently supply at least one item. we might put null values in the item and price components of a tuple for that supplier but then, when entering an item for that supplier, we will remember to delete the tuple with null.
On deleting some information, we lose some other information too. For example, if we delete all item supplied by one supplier, we unintentionally lose track of its address.
So, due to the all above problems we normalize a relation. In this example, if we replace supplier by two relation schemas
SA (name, address)
SIP(name, Item, price)
Then in SA, for each supplier, we have an address separately without any redundancy. At the same time, we can enter an address for a supplier even if it currently supplies no item.
If we conclude this Normalization here then we can say that Normalization is a process for making our database or table or relation or entity as simple as possible, I mean to say that attribute in a relation should be unique pointing a full record detail without any redundancy of it. While performing an action like insertion, update and delete no conflict and data loss should happen. In my next blog post, I will discuss all the types of normalization in detail with the appropriate example.
Hope! you would have found this post very useful regarding Normalization basic concept. Please feel free to contact us if you have any doubts about the normal form. You can simply write to us at firstname.lastname@example.org.