Normalization in Sql

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. There are several benefits for using Normalization in Database.
Benefits

  • Eliminate data redundancy
  • Improve performance
  • Query optimization
  • Faster update due to less number of columns in one table
  • Index improvement

The Normal Forms

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you’ll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won’t be discussed in this article.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

  • Eliminate duplicative columns from the same table
  • Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

First Normal Form
Figure 1: First Normal Form(1NF)

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

  • Meet all the requirements of 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 these new tables and their predecessors through the use of foreign keys.

Second Normal Form
Figure 2: Second Normal Form(2NF)

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

  • Meet all the requirements of the second normal form.
  • Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)

Fourth normal form (4NF) has one additional requirement:

  • Meet all the requirements of the third normal form.
  • A relation is in 4NF if it has no multi-valued dependencies. i.e. If PK is composed of multiple columns then all non-key attributes should be derived from FULL PK only. If some non-key attribute can be derived from partial PK then remove it.

The 4NF also known as BCNF NF.

Fourth Normal Form
Figure 3: Fourth Normal Form(4NF)

Summary

Your data model design is both an art and a science. Balance what works best to support the application that will use the database and to store data in an efficient and structured manner. For transaction-based systems, a highly normalized database design is the way to go; it ensures consistent data throughout the entire database and that it is performing well. For reporting-based systems, a less normalized database is usually the best approach. You will eliminate the need to join a lot of tables and queries will be faster. Plus, the database will be much more user friendly for ad hoc reporting needs.

Select distinct record from datatable, vb.net

Recently i shared my ideas on How to merge and sort two datatables. Completition of this task raise an another issue to Select distinct record from the merged datatable. I found many lengthy process but these are not ok. After some goggling i found a very easy process. Just create a dataview object of the datatable and

dt = dt.DefaultView.ToTable(true, param)

Where param will hold the Column Name for which you want to make distinct values.
So there is no need for any change in How to merge and sort two datatables process, we will just update the code on return statement of the function.

Private Function MergeAndSortTables(ByVal Table1 As DataTable, ByVal Table2 As DataTable, ByVal SortBy As String) As DataTable
  Table1.Merge(Table2)
  Dim View As DataView = Table1.DefaultView
  View.Sort = SortBy

  Return View.ToTable(true)
End Function

It’s funny….

How to merge & sort two datatables

There is two datatables with same column & would like to append one of these datatables on to the end of the other. You may use following code :

Private Function MergeAndSortTables(ByVal Table1 As DataTable, ByVal Table2 As DataTable, ByVal SortBy As String) As DataTable
  Table1.Merge(Table2)
  Dim View As DataView = Table1.DefaultView
  View.Sort = SortBy

  Return View.ToTable()
End Function

How to display Indian rupee symbol in web pages using HTML

Even though Government of India suggested the symbol for Indian rupee and Unicode consortium approved the code for it, it is still not available in all the computers. Official Indian currency symbol was added to Unicode at the unicode version 6.0 during October 2010.
The code approved by Unicode consortium for Indian rupee symbol is: U+20B9
Once all the computers and mobile phones in the world update to latest version of Unicode, everyone will be able to use Indian rupee symbol just like $ or € symbols. Until all computers in the world get updated with unicode 6.0, we will be left with few options to type, display and print Indian rupee symbol in HTML web pages.
If the computer supports Unicode 6.0, then Indian rupee symbol can be displayed in the webpage using the HTML code:
Since Unicode 6.0 is not available in the computers everywhere, you will need to use custom tools and scripts to display them.
Using a font to display the rupee symbol will not normally work on HTML pages since the same font is required on the client machines also to display the rupee symbol on the readers web browser.
Some of the options to display the rupee symbol in web pages are as follows :
A. Using Stylesheet from webrupee.com
webrupee.com has provided a CSS styleseet which you can load from any webpages to display rupee symbol in your webpages. To use rupee symbol, follow the below steps:
1. Include the stylesheet with href : http://cdn.webrupee.com/font

<link rel="stylesheet" type="text/css" href="http://cdn.webrupee.com/font">

2. Just adding the above stylesheet reference in the webpage will not display rupee symbol automatically anywhere. In order to show the rupee symbol, you have to use the SPAN tag with class WebRupee in each place you want to display the symbol.Wherever the SPAN tag is used with the “Rs” enclosed within it, it will automatically display the rupee symbol instead of the text “Rs”.

<span class="WebRupee">Rs</span>
 

B. Display the Rs sign in web pages using Javascript from WebRupee
If you don’t want to type the above code everywhere, you may use the Javascript from WebRupee. If you add the below script reference in the HEAD area of your webpage, all uses of “Rs.” or “Rs” will be converted to Indian rupee symbol.

<script src="http://cdn.webrupee.com/js" type="text/javascript"></script>

ACID (Atomicity, Consistency, Integrity, Durability) in SQL Server

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that every database management system must strive to achieve. For a reliable database all these four attributes should be achieved.

Atomicity: stats that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic”. If one part of the transaction fails, the entire transaction fails.

Consistency: stats that a transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its previous state. It means a transaction will never leave your database in a half finished state.

Integrity: keeps transaction separated each other until they are finished. A transaction in process and yet not committed must remain isolated from any other transaction.

Durability: ensures that any transaction committed to the database will not be lost. Committed data are saved by the system in such a way that, even in the event of failure or system restart, the data is available in its correct state.