Summary of Normalization in Databases

By on December 20, 2016

Normalization helps remove redundancies in Data Design. The redundancies are removed systematically utilizing a 3 or 4 step approach. The 4 important normal forms are 1NF, 2NF, 3NF and BCNF. This tutorial covers the very first three normal forms.

The 3 normal forms affect only Dr Codd’s relational model otherwise known as the relational data model.

 

Within the first normal form (1NF) multi valued posts are disallowed. Should there be multivalued posts for example multiple subjects registered by students inside a course registration system or multiple telephone figures accustomed to call patients inside a patients record system they need to be split up into multiple records one for every value within the multi valued column. Within this type of normalization every record will be able to be identified distinctively and thus would entail using Primary key.

Within the second normal form (2NF) additionally towards the database finding yourself in the first normal form all non key attributes ought to be functionally dependent only around the primary key. In situation of the composite key the non key attributes ought to be fully determined by the whole composite key. For instance think about the following record system for purchasers. The next tuple (Customer ID, Order ID, Order Date, Store Location) has got the Primary key as Customer ID and Order ID. The 2 non key attributes are Order Date and Store Location. Here the position of the store doesn’t rely on the main key or quite simply this non key attribute isn’t functionally based mostly on the main key. Which means this table needs to be split up into two tables to make it in 2NF.

Customer -Order (Customer ID, Order ID, Location ID) Location (Location ID, Location Name).

Within the third normal form, the database ought to be within the second normal form and additionally the standard form helps to ensure that redundancy is further reduced by assuring that each non key attribute doesn’t have any dependency with every other non key attribute or doesn’t have any transitive functional dependency. In popular relational database terminology this means that data model getting referential integrity constraints enabled within the design.

 

To illustrate a database not in 3NF may be the tuple using the attributes (Customer ID, Name, Address line 1, Address line 2, Address line 3, Pincode). This isn’t in 3NF because the Address line elements depend only around the Pincode)To create this in 3NF we use two tuples Customer and Pincode using the following attributes Customer (Customer ID, Name, Pincode), Pincode (Pincode, Address line 1, Address line 3). Here the Pincode attribute within the customer tuple may be the foreign type in the table which fits the main type in the Pincode tuple. This translates to what’s referred to as Referential Integrity constraint in which the foreign type in a table matches the main type in another table.

About admin

You must be logged in to post a comment Login