GupShup Study
What is normalisation in database and whats its importants
Moffat Samambwa

what is normalisation in database and whats its importants

12-Apr-2016 | Moffat Samambwa |
Normalization for Relational Databases ,

explain the advantages of normalisation

Share With Friends :  
Pankaj Yadav

Pankaj Yadav

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. Database normalization can essentially be defined as the practice of optimizing table structures. Optimization is accomplished as a result of a thorough investigation of the various pieces of data that will be stored within the database, in particular concentrating upon how this data is interrelated. An analysis of this data and its corresponding relationships is advantageous because it can result both in a substantial improvement in the speed in which the tables are queried, and in decreasing the chance that the database integrity could be compromised due to tedious maintenance procedures. Most importantly it serves to remove duplication from the database records. For example if you have more than one place (tables) where the name of a person could come up you move the name to a separate table and reference it everywhere else. This way if you need to change the person name later you only have to change it in one place. It is crucial for proper database design and in theory you should use it as much as possible to keep your data integrity. However when retrieving information from many tables you're losing some performance and that's why sometimes you could see denormalised database tables (also called flattened) used in performance critical applications. For example, suppose you store everything about Canada in one big table. Then next year, Quebec decides it doesn't want to be part of Canada anymore. How are you going to change the values in your 'one big table' that refer to Quebec? You will have to write code to search the fields to find each instance of the word Quebec and change it to whatever it gets changed to. If, instead, you have a normalized db, the change is quite simple: you go to the table named something like tblProvinces, and make one change to the entry Quebec which then affects every entry that it is related to through table relationships. This concept is called data integrity, and is far more important than this simple example illustrates... When normalising a database you should achieve four goals: 1.Arranging data into logical groups such that each group describes a small part of the whole 2.Minimizing the amount of duplicated data stored in a database 3.Building a database in which you can access and manipulate the data quickly and efficiently without compromising the integrity of the data storage 4.Organising the data such that, when you modify it, you make the changes in only one place. When you normalize a database, you start from the general and work towards the specific, applying certain tests (checks) along the way. Some users call this process decomposition. It means decomposing (dividing/breaking down) a ‘big' un-normalized table (file) into several smaller tables by: Eliminating insertion, update and delete anomalies Establishing functional dependencies Removing transitive dependencies Reducing non-key data redundancy

Total View : 222