What is Database Normalization?
– Data base normalization is the process that involves the organization of the tables and fields of an RDB i.e., relational data base in order to minimize the dependency as well as redundancy.
– The process of normalization actually involves the division of larger tables in to smaller ones which are less redundant as compared to their larger counterparts.
– After the division of the tables, the relationships between them are defined.
– The main goal here is to achieve the isolation of data so that the modifications, additions and deletions etc of a filed can be made in one place i.e., to say a field.
– The changes then can be propagated to the entire data base via the relationships that have been already defined.
What are the First, 2nd and 3rd Normal Form?
– The concept of normalization was introduced in the year of 1970 by the same person who invented the relational data base model i.e., Edgar F. Codd. It has now been termed as the 1NF or the first normal form.
– The following year i.e., 1971 saw Codd defining two more forms namely 2NF or second normal form or 3NF or third normal form.
– Three years later one more form of normalization was introduced that was termed as Boyce – Codd normal form or BCNF which Codd defined with Raymond F. Boyce.
When can be a relational database table is called normalized?
– A relational data base table can be called normalized informally only if it is in the third normal form i.e., 3NF.
– It has been found that there are no anomalies of insertion, deletion and update in the tables having third normal form.
– A standard guidance concerning normalization is that only a fully normalized design should be created by the designer and in the subsequent stages selective de-normalization can be performed based up on certain performance reasons.
– To permit to manipulate and query the data was the defined as the basic objective of the first normal form by Codd himself in the year of 1970.
– But this manipulation and querying is to be done in a language grounded in first order logic called as the universal data sub language (such as SQL).
– For the normalization forms above, the first normal form forms the objectives that have been defined as follows:
1. Freeing the collection relations from undesirable dependencies of update, deletion and insertion etc.
2. Reducing the need for restructuring of the relation collection with the introduction of the new data types in order to increase the life of the software systems and applications.
3. Making relational model more informative for the users.
4. Neutralizing the collection of relations against the query statistics although these statistics may change by the time.
Whenever it is tried to modify or manipulate (, insert into, update or delete from) a table or field it could result in some undesired side-effects. However, it is not necessary for all the tables to suffer from these side-effects; however the tables that have not been normalized to a good extent are likely to suffer from these side effects.
What are characteristics of insufficient normalized table?
Following are the characteristics of an insufficiently normalized table:
1. Multiple rows might be having the same information and so updates that are made to that particular table may cause the data base to suffer from logical inconsistencies. If in case the update could not be successful for some reason then the state of the table will become inconsistent. To say, specifically when the table provides conflicting answers to a question the phenomenon is called an update anomaly.
2. Sometimes there arise circumstances in which recording certain facts becomes quite troublesome. Such a phenomenon is called as an insertion anomaly.
3. There are other certain circumstances in which the deletion of data representing a particular fact makes it necessary that the data representing a completely different fact is deleted. This phenomenon has been termed as a deletion anomaly.