Explain normalization in SQL Server

Normalization is the process of organizing data into a related table; it also eliminates redundancy and increases the integrity which improves performance of the query. Database normalization can essentially be defined as the practice of optimizing table structures. Optimization is accomplished as a result of a thorough investigation of the various pieces of data that will be stored within the database, in particular concentrating upon how this data is interrelated.

Normalization avoids:
  • Avoids Duplicates - it makes sure that duplicate data is not stored
  • Insert Anomaly - A record about an entity cannot be inserted into the table without first inserting information about another entity - Cannot enter a customer without a sales order
  • Update Anomaly - Cannot update information without changing information in many places. To update customer information, it must be updated for each sales order the customer has placed
  • Delete Anomaly - A record cannot be deleted without deleting a record about a related entity. Cannot delete a sales order without deleting all of the customer's information
De-normalization is a process whereby redundant columns are added to the table to speed up the execution time and complicity of using joins.

Example Table
Items Colors Price Tax
Pen Red, Blue 20 0.20
Scale Green, Orange 30 0.30

Normalization stages:
  • First Normal Form - 1 NF
    Items Colors Price Tax
    Pen Red 20 0.20
    Pen Blue 20 0.20
    Scale Green 30 0.30
    Scale Orange 30 0.30
    • The table cell should be of single value
    • Duplicate data should be removed
    • Each row should be identified by a primary key

  • Second Normal Form - 2 NF
    Items Colors
    Pen Red
    Pen Blue
    Scale Green
    Scale Orange

    Items Price Tax
    Pen 20 0.20
    Scale 30 0.30
    • Removes partial dependencies
    • Functional Dependency - the value of one attribute is determined entirely by another attribute
    • Partial Dependency - the value of one attribute is partially dependent on a primary key
    • Create a separate table with the functional dependent data and the partial section of the primary key
  • Third Normal Form - 3 NF
    Items Colors
    Pen Red
    Pen Blue
    Scale Green
    Scale Orange

    Items Price
    Pen 20
    Scale 30

    Price Tax
    20 0.20
    30 0.30
    • Removes transitive dependencies
    • Transitive Dependency - whereby an attribute is dependent on another attribute other than the primary key

  • Boyce-Codd Normal Form - BCNF
  • Fourth Normal Form - 4 NF
  • Fifth Normal Form - 5 NF

Comments

Post a Comment

Popular posts from this blog

jQuery Basics

jQuery Introduction The files needed to run a jQuery function can be downloaded from the URL mentioned below: http://jquery.com/download/ There are actually two versions of the file that is available, the first one id the minified and compressed version, jquery-1.10.1.min.js , which is basically used for the production environment and the other is the full version, uncompressed and readable, which is used for the development environment. If somebody does not want to download the file, they can use it from the CDN (Content Delivery Network) provided both by Google and Microsoft, the details are here under: Google - http://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js Microsoft - http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.1.min.js Note:  If you look at the Google URL above - the version of jQuery is specified in the URL (1.10.1). If you would like to use the latest version of jQuery, you can either remove a number from the end of the version st...
Read more

What is the difference between a Page Layout and Master Page in SharePoint?

A Page Layout is nothing but a template which when used in conjuncture with the Master Page gives the look and feel and contents of the page. Each page layout has an associated content type that determines the kind of content that can be stored on pages based on that page layout. Master Pages and Page Layouts dictate the overall look and feel of your SharePoint site. Master Pages contain controls that are shared across multiple page layouts, such as navigation, search, or language-preference for multilingual sites. Page layouts contain field controls and Web Parts. All page layouts reference a master page that is based on the CustomMasterUrl property of the SPWeb class. The top-level SharePoint Server site for a site collection hosted on SharePoint Server 2010 has a special document library called the Master Page and Page Layout Gallery. All Page Layouts and Master Pages are stored in this document library. Reference: http://blog.beckybertram.com/Lists/Posts/Post...
Read more

Accessing data from SharePoint 2010 to an ASP.NET application

Image
In order to access the SharePoint lists and document libraries data from a native ASP.NET application we need to make use of the web services that have been provided. The full list of the web services available from within SharePoint 2010 can be found here http://www.etechplanet.com/blog/sharepoint-web-services-7c-list-of-all-web-services-available-with-wss-30moss-2007.aspx The steps to access the web service are as follows: Create a native ASP.NET web application Add a reference of the web service Create an object of the web site Pass on to the web service object the credentials using which the access will be authorized Retrieve the results using the appropriate method
Read more