Understanding Normalizations in Database(RDBMS)

Let’s try understanding normalization

How many normal forms are there?

There are seven normal forms.

They are

  • First Normal Form
  • Second Normal Form
  • Third Normal Form
  • Boyce-Codd Normal Form
  • Fourth Normal Form
  • Fifth Normal Form
  • Sixth or Domain-key Normal form

Why do we need to do normalization?

To eliminate redundancy of data i.e. having same information stored at multiple places, which eventually be difficult to maintain and will also increase the size of our database.

With normalization we will have tables with fewer columns which will make data retrieval and insert, update and delete operations more efficient.

What do we mean when we say a table is not in normalized form?

Let’s take an example to understand this,

Say I want to create a database which stores my friends name and their top three favorite artists.

This database would be quite a simple so initially I’ll be having only one table in it say friends table. Here FID is the primary key.

FID FNAME FavoriteArtist
1 Srihari Akon, The Corrs, Robbie Williams.
2 Arvind Enigma, Chicane, Shania Twain

This table is not in normal form why?

FavoriteArtist column is not atomic or doesn’t have scalar value i.e. it has having more that one value.

Let’s modify this table

FID FNAME FavoriteArtist1 FavoriteArtist2 FavoriteArtist3
1 Srihari Akon. The Corrs Robbie Williams.
2 Arvind Enigma Chicane Shania Twain

This table is also not in normal form why?

We have now changed our table and now each column has only one value!! (So what’s left?)

Because here we are having multiple columns with same kind of value.

I.e. repeating group of data or repeating columns.

So what we need to do to make it normal or at least bring it in First Normal Form?

  1. We’ll first break our single table into two.
  2. Each table should have information about only one entity so it would be nice if we store our friend’s information in one table and his favorite artists’ information in another

(For simplicity we are working with few columns but in real world scenario there could be column like friend’s phone no, email , address and favorites artists albums, awards received by them, country etc. So in that case having two different tables would make complete sense)

1 Srihari
2 Arvind
FID Favorite Artist
1 Akon.
1 The Corrs
1 Robbie Williams
2 Enigma
2 Chicane
2 Shania Twain

FID foreign key in FavoriteArtist table which refers to FID in our Friends Table.

Now we can say that our table is in first normal form.

Remember For First Normal Form

Column values should be atomic, scalar or should be holding single value

No repetition of information or values in multiple columns.

So what does Second Normal Form means?

For second normal form our database should already be in first normal form and every non-key column must depend on entire primary key.

Here we can say that our Friend database was already in second normal form l.


Because we don’t have composite primary key in our friends and favorite artists table.

Composite primary keys are- primary keys made up of more than one column. But there is no such thing in our database.

But still let’s try to understand second normal form with another example

This is our new table

Gadgets Supplier Cost Supplier Address
Headphone Abaci 123$ New York
Mp3 Player Sagas 250$ California
Headphone Mayas 100$ London

In about table ITEM+SUPPLIER together form a composite primary key.
Let’s check for dependency

If I know gadget can I know the cost?

No same gadget is provided my different supplier at different rate.

If I know supplier can I know about the cost?

No because same supplier can provide me with different gadgets.

If I know both gadget and supplier can I know cost?

Yes than we can.

So cost is fully dependent (functionally dependent) on our composite primary key (Gadgets+Supplier)

Let’s start with another non-key column Supplier Address.

If I know gadget will I come to know about supplier address?

Obviously no.

If I know who the supplier is can I have it address?


So here supplier is not completely dependent on (partial dependent) on our composite primary key (Gadgets+Supplier).

This table is surely not in Second Normal Form.

So what do we need to do to bring it in second normal form?
Here again we’ll break the table in two.

Gadgets Supplier Cost
Headphone Abaci 123$
Mp3 Player Sagas 250$
Headphone Mayas 100$
Supplier Supplier Address
Abaci New York
Sagas California
Mayas London

We now how to normalize till second normal form.

But let’s take a break over here and learn some definitions and terms.

Composite Key: -Composite key is a primary key composed of multiple columns.

Functional Dependency – When value of one column is dependent on another column.

So that if value of one column changes the value of other column changes as well.

e.g. Supplier Address is functionally dependent on supplier name. If supplier’s name is changed in a record we need to change the supplier address as well.


“In our s table supplier address column is functionally dependent on the supplier column”

Partial Functional DependencyA non-key column is dependent on some, but not all the columns in a composite primary key.

In our above example Supplier Address was partially dependent on our composite key columns (Gadgets+Supplier).

Transitive DependencyA transitive dependency is a type of functional dependency in which the value in a non-key column is determined by the value in another non-key column.

With these definitions in mind let’s move to Third Normal Form.

For a table in third normal form

  • It should already be in Second Normal Form.
  • There should be no transitive dependency, i.e. we shouldn’t have any non-key column depending on any other non-key column.

Again we need to make sure that the non-key columns depend upon the primary key and not on any other non-key column.

Album Artist No. of tracks Country
Come on over Shania Twain 11 Canada
History Michael Jackson 15 USA
Up Shania Twain 11 Canada
MCMXC A.D. Enigma 8 Spain
The cross of changes Enigma 10 Spain

Although the above table looks fine but still there is something in it because of which we will normalize it further.

Album is the primary key of the above table.

Artist and No. of tracks are functionally dependent on the Album(primary key).

But can we say the same of Country as well?

In the above table Country value is getting repeated because of artist.

So in our above table Country column is depended on Artist column which is a non-key column.

So we will move that information in another table and could save table from redundancy i.e. repeating values of Country column.

Album Artist No. of tracks
Come on over Shania Twain 11
History Michael Jackson 15
Up Shania Twain 11
MCMXC A.D. Enigma 8
The cross of changes Enigma 10
Artist Country
Shania Twain Canada
Michael Jackson USA
Enigma Spain

Normally this is considered enough and we don’t really go on applying the other normal forms.

Most of real-world application has databases which are in third normal forms.



Head First SQL,

Murach Sql for Sql server,


Using Dhtml inside CRM


We can make use of Dhtml inside our CRM onLoad, onSave and onChange javascripts.

Using Dhtml, number of possible ways of customizing CRM increases.

But the only thing with them is that they are unsupported.

Just for fun try putting this code in form load of your contact form and see what it does.

window.status=”Hello CrmUser!”;
function G_GlowField()
var p=window.createPopup()
var pbody=p.document.body
pbody.style.border=”solid black 1px”
pbody.innerHTML=”Mandatory Field”

function G_RevertField()

Or Better check this link

Using the attachEvent method to show users context sensitive help


Link Entity, Query Expression and FetchXml Wizard

Let say we need to make use of CrmService to give us the name of the opportunity where owner’s fullname=’someName’;

Name is an attribute of Opportunity Entity.
FullName is an attribute of SystemUser Entity.

So we need to make use of LinkEntity class over here to link from opportunity entity to systemuser entity.

Now let’s use queryExpression for the same.


But plzzzz don’t write it yourself, use FetchXmlBuilder to write it for you.
Plzz understand it and download it from this link

(The best site for all the CrmDevelopers)

This is how the queryExpression code will look like


QueryExpression query = new QueryExpression();

query.EntityName = “opportunity”;

ColumnSet columns = new ColumnSet();
columns.Attributes = new string[] { “name” };
query.ColumnSet = columns;

LinkEntity linkEntity1 = new LinkEntity();
linkEntity1.JoinOperator = JoinOperator.Natural;
linkEntity1.LinkFromEntityName = “opportunity”;
linkEntity1.LinkFromAttributeName = “owninguser”;
linkEntity1.LinkToEntityName = “systemuser”;
linkEntity1.LinkToAttributeName = “systemuserid”;

linkEntity1.LinkCriteria = new FilterExpression();
linkEntity1.LinkCriteria.FilterOperator = LogicalOperator.And;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = “fullname”;
condition1.Operator = ConditionOperator.Equal;
condition1.Values = new object[] { “Nishant Rana” };

linkEntity1.LinkCriteria.Conditions = new ConditionExpression[] { condition1 };

query.LinkEntities = new LinkEntity[] { linkEntity1 };

Now say we have a requirement where we need

The name of all the opportunity along with the fullname of the owner.

Let’s try doing it with a queryExpression class and using the FetchXmlBuilder.

This is the code which we’ll get


LinkEntity linkEntity1 = new LinkEntity();

linkEntity1.JoinOperator = JoinOperator.Natural;

linkEntity1.LinkFromEntityName = “opportunity”;

linkEntity1.LinkFromAttributeName = “owninguser”;

linkEntity1.LinkToEntityName = “systemuser”;

linkEntity1.LinkToAttributeName = “systemuserid”;

//You have specified columns for this link-entity. This is not supported in query expressions


This means it’s not possible using queryExpression

So in this case we need to make use of FetchXml to fetch use the fullname of the owning user.

The fetch xml looks like this

string fetchXml = @”
<fetch mapping=””logical”” count=””50″”>
<entity name=””opportunity””>
<attribute name=””name”” />
<link-entity name=””systemuser”” from=””systemuserid”” to=””owninguser””>
<attribute name=””fullname”” />

// Finally the code

CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
string fetchXml = @”
<fetch mapping=””logical”” count=””50″”>
<entity name=””opportunity””>
<attribute name=””name”” />
<link-entity name=””systemuser”” from=””systemuserid”” to=””owninguser””>
<attribute name=””fullname”” />
String result = service.Fetch(fetchXml);


Now the only problem is we need to parse the xml on our own.

We can use something like this

if this is the result we are receiving

<resultset morerecords=”0″><result><new_id>GP0677</new_id></result></resultset>

to fetch the new_id we can do this

XmlDocument doc = new XmlDocument();
XmlNodeList xnodlist=doc.GetElementsByTagName(“
XmlNode xnodRoot=xnodlist.Item(0);
string val=xnodRoot.InnerText;





Understanding Relationship in Database (RDBMS)

There are three type of relationships

1) One to one

2) One to many

3) Many to many

Say we have table1 and table2

For one to one relationship, a record(row) in table1 will have at most one matching record or row in table2

I.e. it mustn’t have two matching records or no matching records in table2.

For one to many, a record in table1 can have more than one record in table2 but not vice versa

Let’s take an example,

Say we have a database which saves information about Guys and whom they are dating.

We have two tables in our database Guys and Girls

Guy id

Guy name







Girl id

Girl name







Here in above example Guy ID and Girl ID are primary keys of their respective table.

Say Andrew is dating Girl1, Bob – Girl2 and Craig is dating Girl3.

So we are having a one to one relationship over there.

So in this case we need to modify the Girls table to have a Guy id foreign key in it.

Girl id

Girl name

Guy id










Now let say one guy has started dating more than one girl.

i.e. Andrew has started dating Girl1 and say a new Girl4

That takes us to one to many relationships from Guys to Girls table.

Now to accommodate this change we can modify our Girls table like this

Girl Id Girl Name Guy Id
1 Girl1 1
2 Girl2 2
3 Girl3 3
4 Girl4 1

Now say after few days, comes a time where girls have also started dating more than one boy i.e. many to many relationships

So the thing to do over here is to add another table which is called Junction Table, Associate Table or linking Table which will contain primary key columns of both girls and guys table.

Let see it with an example

Guy id

Guy name







Girl id

Girl name







Andrew is now dating Girl1 and Girl2 and

Now Girl3 has started dating Bob and Craig

so our junction table will look like this

Guy ID

Girl ID











It will contain primary key of both the Girls and Boys table.

Why do we need one to one relationship when we can easily accommodate the values in a single row?

For isolating some sensitive data. Employee table with its salary information stored in a separate table.

Putting large data in a separate table say a BLOB or Image type in which we are saving an image or document.

This type of relationship is typically used to improve performance.

And for our One to Many and Many to Many relationship, they help in maintenance.

If we had been storing multiple values in a column it would have been very difficult to query that table.

For e.g. say we have hobby column in our student table

And hobby has multiple values

Student ID Student Name Hobby
1 Sandra Swimming, Reading
2 Jans Swimming, Rowing

What would be select query to find student name having hobby as reading or update swimming with dancing. It would be too difficult to write such a query.

So if we have these values separated in another table we can easily query it.


Javascript and Asp.net

Let’s see how we can use javascript with our server side controls with very simple examples

1) One way is using

controlname.attributes.add(string key,string value);

Let’s see an example

1. Create a new webpage

2. Add a textbox to it.

<asp:TextBox ID=”TextBox1″ runat=”server”></asp:TextBox>

3. Now let us add some javascript for onMouseOver event so that whenever mouse is over the textbox it should alert Hello World message.

4. For this add this code on page_load event handler

protected void Page_Load(object sender, EventArgs e)
TextBox1.Attributes.Add(“onMouseOver”,”alert(‘Hello World’);”);

OnMouseOver is the name of the JavaScript event followed by our javascript code to run on occurence of that event.

5. If small code of javascript is there than we can write it over directly in attributes.add but otherwise it’s better we write the code within our script block and put the name of the function over there

<script type=”text/javascript”>
function mHover()
alert(‘Hello World’);


protected void Page_Load(object sender, EventArgs e)
TextBox1.Attributes.Add(“onMouseOver”, “mHover()”);

Normally we can insert javascript by adding attributes to the control.

But for our button server control we can use OnClientClick property for handling button clicks with Javascript code

<asp:Button ID=”Button1″ runat=”server” Text=”Button” OnClientClick=”return confirm(‘Post back to the server?’);”/>

Whatever is written on OnClientClick fires first followed by the postback event.

In above case if the method returns false i.e. user clicks cancel no post back occurs.

Some common javascript events which we can use inside attributes.add are

onChange, onClick, onMouseOver, onMouseOut, onKeyUp, onKeyDown, onSelect, onFocus, onBlur,onAbort, onError, onLoad ,onUnload

In asp.NET we can make use of Page.ClientScript property for managing script blocks

RegisterClientScriptCallBlock() : It writes the script block right after the <form runat=server> tag

RegisterStartupScript() : It writes the script block right before closing </form> tag

We will modify the textbox example we first created

1: Remove the script section from the aspx page and put it on the page load like this

protected void Page_Load(object sender, EventArgs e)
TextBox1.Attributes.Add(“onMouseOver”, “mHover()”);
string script=@”<script type=’text/javascript’>
function mHover()
alert(‘Hello World’);
TextBox1.Attributes.Add(“onMouseOver”, “mHover()”);

2: Now run the web page and see the source we will find the script added just after the <form> start tag

3: Now replace




4: We will be able to see the script added just before the </form> tag


Working with ListBox in .Net Windows Application

Say we want to implement some functionality as shown in the image below in our windows formListBox

Using >> button we want to move values from optional sections to mandatory sections list box and vice versa using << button. But we don’t want the sections which are mandatory to move to optional list box.

Finally we can rearrange items in the Mandatory list box using up and down buttons.

Let’s see the code for all this

Our listboxes are named lstOptional and lstMandatory

>> button is named btnSelect

<< button is named btnDeSelect

When button >>(btnSelect is clicked)

private void btnSelect_Click(object sender, EventArgs e)

// if there are no item in lstOptional Listbox return

if (lstOptional.Items.Count == 0)


// if some item is selected in the lstOptional Listbox check if we already have it in lstMandatory

// if it is there or nothing is selected than return

int g = lstMandatory.FindStringExact(lstOptional.Text);
if(g>-1||lstOptional.Text ==””)

// finally add that item to lstMandatory, refresh it to make it appear and remove it from lstOptional

lstMandatory.Items.Add(lstOptional.Text );
lstMandatory.Text = “”;

When button << DeSelect is clicked

private void btnDeSelect_Click(object sender, EventArgs e)

// if nothing is selected in lstMandatory just put the focus over the listbox

if (lstMandatory.Text == “”)

// checking if the selected item is mandatory on.

// Here we have saved the mandatory items in a hidden listbox on form load

int g = lstHidden.FindStringExact(lstMandatory.Text);
if (g > -1)
MessageBox.Show(“This section is mandatory”,”Information”);

When button Up is clicked we need to move the selected item in upward direction

int selectedItemIndex = lstMandatory .SelectedIndex;
String selectedItemText = lstMandatory .Text;
if (selectedItemIndex != 0)
lstMandatory .Items.RemoveAt(selectedItemIndex);
lstMandatory .Items.Insert(selectedItemIndex – 1, selectedItemText);
lstMandatory .SelectedIndex = selectedItemIndex – 1;
lstMandatory .Refresh();
lstMandatory .Focus();

Finally when button Down is clicked

int selectedItemIndex = lstMandatory.SelectedIndex;
String selectedItemText = lstMandatory.Text;
int total = lstMandatory.Items.Count;
if (selectedItemIndex < total – 1)
lstMandatory.Items.Insert(selectedItemIndex + 1, selectedItemText);
lstMandatory.SelectedIndex = selectedItemIndex + 1;


Using Ajax in ASP.NET application -3 (JSON)

As we saw in our previous post


How easy it becomes to parse the response and the values if it is in the XML rather than plain text.

But that is not the only way there is something called JSON as well

What is JSON?

JSON – Javascript object notation is a format for sending and recieving data similar to XML.

It’s a lightweight data fomat that’s easy to work with in JavaScript.

Check this site for more info http://json.org/

Let’s understand it by an example

We’ll here again modify the application created in our previous post to make use of JSON in place of XML

This time we will be returing an Object to our javascript code in JSON as response.

Let’s say we have on class like this which we would like to return as response to our calling ajax scripts

public class Emp


public Emp(){}

private string firstName;

public string FirstName{get { return firstName; } set { firstName = value; }}

private string lastName;

public string LastName{ get { return lastName; } set { lastName = value; }} 

private string[] skills = new string[3]; 

public string[] Skills{ get { return skills; }set { skills = value; }


To convert it to JSON let’s make use of a library.

We’ll make use of LitJSON here.

We can find all the information and the library for LitJSON here http://litjson.sourceforge.net/doc/manual.html 

(add reference to LitJson.dll in the project) 

Now make this change in our Default2.aspx page’s page Load

protected void Page_Load(object sender, EventArgs e)


Emp emp = new Emp();

emp.FirstName = “Donald”;

emp.LastName = “Duck”;

emp.Skills[0] = “ASP.NET”;

emp.Skills[1] = “WCF”;

emp.Skills[2] =”WPF”;

string json_Emp = JsonMapper.ToJson(emp);



Response.Write(json_Emp) will give us this


The changes that we need to make in our Default.aspx page are again in doUpdate function 

function doUpdate()





 var jsonData=eval(‘(‘+xmlHttp.responseText+’)’);

var firstName=jsonData.FirstName;

var lastName=jsonData.LastName; 

var skill1=jsonData.Skills[0];

var skill1=jsonData.Skills[1];


This way we can parse the recieved as JSON which seems much easier comapred to XMLDOM. 


Using Ajax in ASP.NET application -2

In part 1 of this post we saw our response returning us a simple text which was holding a single value.

But what if it is returing multiple values and it using some characters like | (pipe) or say semicolon for separating one value from another.

In this case, we need to make use of javascript string functions to get each and every value.

Here then our application should understand what ; and | or say , mean and what if their order changes.

So now we can have our responses send as XML so that we can make use of XMLDOM to parse through the response. Which infact would be quite easy and efficient.

Let’s update our application


Things we need to change are

1)  Change doUpdate function

function doUpdate()



{ // replacing reponseText with responseXML to get the xml DOM tree reference 

var xmlDoc=xmlHttp.responseXML; // parsing through the response using dom methods

var firstName=xmlDoc.getElementsByTagName(“first”)[0].firstChild.nodeValue;

var lastName=xmlDoc.getElementsByTagName(“last”)[0].firstChild.nodeValue;

document.getElementById(“lblInfo”).firstChild.nodeValue=firstName+” “+lastName; }


In the above case the response we are recieving from server is this, which is in xml

<?xml version=”1.0″?>

2) Now we will change Default2.aspx to send us response back in xml.

protected void Page_Load(object sender, EventArgs e){ // this will inform our webpage about responses being sent as XML and not text

 Response.AddHeader(“Content-Type”, “text/xml;charset=UTF-8”);

Response.Write(“<?xml version=\”1.0\” encoding=\”utf-8\”?><name><first>Mickey</first><last>Mouse</last> </name>”);  


Now we can run our application and see our label’s text changing to Mickey Mouse. 


Using Ajax in ASP.NET application -1


Here we’ll change the small application that we had written to get reponse from ASP.NET webpage rather than a webservice.


Here we are using a label server control whose value we will be replacing with response from our asp.net web page through ajax call

1)  Create a new ASP.NET WebApplication  

2)  Put a label control on the form (Default.aspx)

<asp:Label ID=”lblInfo” runat=”server” Height=”49px” Text=”Label” Width=”209px”>ReplaceIt</asp:Label>

 3) Put this script code in the head section of the aspx page

var xmlHttp;

function getMessage()


xmlHttp=new ActiveXObject(“Microsoft.XMLHTTP”);

xmlHttp.open(“post”, “Default2.aspx”, true);


xmlHttp.send();  return false; }

function doUpdate() { if(xmlHttp.readyState==4)


var exch; exch=xmlHttp.responseText;document.getElementById(“lblInfo”).firstChild.nodeValue=exch;



4) Call the getMessage function in the body’s onLoad eventHandler

 <BODY onload=”getMessage()”>

5) Create another page Default2.aspx which will return us  the “Hello World” repsonse.

6) Write this in our Default2.aspx’s page load event

protected void Page_Load(object sender, EventArgs e)


Response.Write(“Hello World”);


7) Now comes the most imp part i.e. Deleting all the html tags  from our Default2.aspx leaving only this, otherwise the html will also come as a part of response. But we need only the Hello World text.

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default2.aspx.cs” Inherits=”Default2″ %>

8- Now run the application, we should see our label’s text changing to “Hello World”


Using ALLColumns() in Dynamics CRM


To retireve the values for all the attributes of an entity we can make use of ALLColumns ,

CrmService service=new CrmService();

service.Credentials = System.Net.CredentialCache.DefaultCredentials;

QueryExpression query = new QueryExpression();

query.EntityName = EntityName.new_entityname.ToString();

query.ColumnSet=new AllColumns();

BusinessEntityCollection entities= service.RetrieveMultiple(query);

for(int i=0;i<entities.BusinessEntities.Length;i++)


new_entityname nEntity=(new_entityname) entities.BusinessEntities[i];

Response.Write(“<b>”+nEntity.new_id+” -></b>”);

Response.Write(“<i>” + nEntity.createdby.name + ” –></i>”);



Or by using QueryByAttribute  

It can be used when our query is very simple i.e. multiple equals conditions that are combined using AND.

But remember it doesn’t support OR
QueryByAttribute myQuery=new QueryByAttribute();
myQuery.ColumnSet=new AllColumns();

    // Find all the opportunities with topic name test1 and city as Redmond
myQuery.Attributes=new string[]{“name”,”address1_city”};
myQuery.Values=new string[]{“Test1″,”Redmond”};
BusinessEntityCollection retrieved=service.RetrieveMultiple(myQuery);