What Is Database Normalization?Normalization is a multi-step process used to organize a relational database into tables and columns and to:. Eliminate redundant (useless) data. Ensure data dependencies make sense. Eliminate undesirable characteristics like insertion, update and deletion anomaliesWhy would you want to normalize your database?The purpose of normalization is to reduce the amount of space a database consumes and ensure that data is logically stored. Database normalization is a manual process of rearranging the data, and it requires time and effort, so why would you do it?Data with duplicate values leads to anomalies, but when information is stored in one place and one place only, the possibility of inconsistent data is reduced.Properly normalized database will:. require less storage space. be easier to handle and update, without facing data loss.
be more informative to usersAre there any disadvantages of normalization?After normalization, data is not duplicated, so table joins are required. This makes queries more complicated.
Since joins are required, queries can take more time - especially if indexes aren't well thought out.For read-intensive applications it may make sense to denormalize tables in strategic places to ensure performance level we need.What are normal forms?Normal forms are rules that transform the data into a certain structure. Every normal form is achieved by following its specific rules. By transforming the data into the higher normal form, it becomes more normalized. The more normalized it is, less duplication we get - but it is usually perfectly fine to allow some duplication instead of complicating our applications and schema for conceptual purity.Informally, there are 11 normal forms, but first three are usually enough to describe relation database as 'normalized.' . UNF: Unnormalized form. 1NF: First normal form.
2NF: Second normal form. 3NF: Third normal form.
EKNF: Elementary key normal form. BCNF: Boyce–Codd normal form. 4NF: Fourth normal form. ETNF: Essential tuple normal form. 5NF: Fifth normal form. DKNF: Domain-key normal form. 6NF: Sixth normal formWhat are undesirable anomalies you want to avoid by normalization?
Table Invoice: 0NF. An update anomaly is a data inconsistency that results from data redundancy and partial update. In the table, we have two rows for client Liam as he bought two products. If Liam changes his phone number we have to update it in two rows else Liam will appear to have two different phone numbers. Omission to update the Liam's phone number in all rows leads to inconsistent data. A deletion anomaly is caused by unintended loss of data due to deletion of other data. If Liam doesn't want to be this shop’s client anymore and the owner deletes him in only one instance (one row of the shown table), then deletion is incomplete because it leaves some residual instances.
More importantly, if the owner wants to delete all of the Liam's orders for some reason, then all of the Liam's basic data (name, phone) will also be lost. An insertion anomaly is the inability to insert data to the database due to an absence of other data. Let's suppose that the owner wants to add a new product to the shop's inventory - he or she would not be able to insert the product data into the table until someone buys it first.First normal formThe table will be in 1NF when you apply the first rule of normalization: 1.An attribute (column) of a table cannot store multiple values. It should store only atomic values.Any column of your table should not contain multiple values. Non-key attributes must be functionally dependent on the primary key.
Database Normalization Pdf
2.There are no repeating groups of columnsAvoiding repeating groups means that a table should not contain repeating groups of columns such as Clientname1,Clientname2, Clientname3.