What is Normalization??

Normalization in the simplest terms:
Normalization is the process of organizing data in a database. The target is to eliminate redundant data (for example, storing the same data in more than one table) and ensure sensible data dependencies in other words, only storing related data in a table.

First Normal Form (1NF)
First normal form (1NF) is the basic step:
    * Eliminate duplicate columns from the same table (mind it not duplicate rows)
    * Create separate tables for each group of related data and identify each row with a unique column i.e. set of columns (primary key).

Second Normal Form (2NF)
Second normal form (2NF) further removes duplicate data:
    * Should meet the first normal form.
    * Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
    * Create relationships between the new tables and their parents through the use of foreign keys.

Third Normal Form (3NF)
Third normal form (3NF) goes one step further:
    * Should meet the second normal form.
    * Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)
Finally, fourth normal form (4NF) has one additional requirement:
    * Meet the third normal form requirements.
    * There should be no multi-valued dependencies.

Significance is high upto the 3rd normal form. Thereafter the need & existence dries down a little. However we are not to forget the importance of Denormalized databases on OLAP segments. Please refer to MS SQL Server BoK for more details.

Some more benefits of normalization:
a) Optimized queries (because normalized tables produce rapid, efficient joins)
b) Faster index creation and sorting (because the tables have fewer columns)
c) Data integrity (because there is no redundant, neglected data)
d) Faster UPDATE performance (because there are fewer indexes per table)
e) Improved concurrency resolution (because table locks will affect less data)

Comments

Post a Comment

Popular posts from this blog

Automatic Replenishment ~ Warehouse Management (WM) in SAP

Image
This article is intended for SAP functional consultants to configure automatic replenishment of a particular storage type for transfer orders. Whenever the stock in a bin falls below the MIN value (predefined minimum quantity), it will trigger replenishment or transfer request (TR) which will in turn get converted into a transfer order, so that the workers in the warehouse can carry out the actual physical transfer of stock. We have a provision of assigning a definite storage type from where the stock will be picked up for replenishment.   Step 1 : Material Master has to be maintained with Warehouse Views. Under Warehouse Mgmt 2 Storage bin, Maximum bin Quantity, Minimum bin Quantity and Replenishment Quantity has to be maintained. Configuration Changes: Step 2 : Movement type 320 is copied from existing movement type 319 under Logistics Execution ->Warehouse management->Activities->Transfers->Define Movement Types. Movement type 320 is configured as the following
Read more

Teradata - Month End Date

Sql Server: Calculating the last day of the month in SQL Server is pretty simple. Run the following piece of code and its done. SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) CurrentMonthEnd_Day ResultSet: CurrentMonthEnd_Day ———————– 2010-08-31 23:59:59.000 Teradata: However, with reference to Teradata SQL, in our day to day queries in a datawarehouse environment very frequently we need to play with the month end dates that are mostly kept in the date dimension table with one field, i.e. the month end date field. You might require to fetch the month end date which is 4 years back. usually the simplest way in this case will be to calculate 48 months back from the current month end date (in case the monthe end date that long is not stored into the database). This is fair. The piece of code required to do is simple. SELECT (ADD_MONTHS(CAST('2012/08/31' AS DATE),-48)) Result: 2008/08/31 Now trying this simple thing will not work if its a leap year a
Read more

Cross Domain Webix Post

Recently, we were working with Spring Boot and Webix framework using Ajax. We had quite some trouble to POST data back to the Server with Spring Boot.  Error: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:7100' is therefore not allowed access. We realized the problem was because of our AJAX cross-domain requests that are forbidden by default by the same-origin security policy. Since browsers need to “preflight” any asynchronous or rather HTTP request methods that modify data and also fetch an “approval” the problem had to be addressed both way i.e. Server side and Browser i)                     We had to enable the CORS assertion in Spring Boot i.e. @CrossOrigin(*) ii)                    Enable Cross-origin resource sharing (CORS) from the Chrome browser. You need to add an extension or CORS enabler from the Chrome Web Store for this. Thereafter using the following code the JSON data wa
Read more