Rules of Data Normalization

1NF	Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2NF	Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF	Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
BCNF	Boyce-Codd Normal Form - If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF	Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF	Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF	Optimal Normal Form - a model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF	Domain-Key Normal Form - a model free from all modification anomalies.

Important Note! All normal forms are additive, in that if a model is in 3rd normal form, it is by definition also in 2nd and 1st.

http://web.archive.org/web/20080805014412/http://www.datamodel.org/NormalizationRules.html#five

Source: Datamodel.org

SQL SERVER – Difference Between Candidate Keys and Primary Key

Introduction

Not long ago, I had an interesting and extended debate with one of my friends regarding which column should be primary key in a table. The debate instigated an in-depth discussion about candidate keys and primary keys. My present article revolves around the two types of keys.

Let us first try to grasp the definition of the two keys.

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

One needs to be very careful in selecting the Primary Key as an incorrect selection can adversely impact the database architect and future normalization. For a Candidate Key to qualify as a Primary Key, it should be Non-NULL and unique in any domain. I have observed quite often that Primary Keys are seldom changed. I would like to have your feedback on not changing a Primary Key.

An Example to Understand Keys

Let us look at an example where we have multiple Candidate Keys, from which we will select an appropriate Primary Key.

Given below is an example of a table having three columns that can qualify as single column Candidate Key, and on combining more than one column the number of possible Candidate Keys touches seven. A point to remember here is that only one column can be selected as Primary Key. The decision of Primary Key selection from possible combinations of Candidate Key is often very perplexing but very imperative!

On running the following script it will always give 504 rows in all the options. This proves that they are all unique in database and meet the criteria of a Primary Key.

Run the following script to verify if all the tables have unique values or not.

USE AdventureWorks
GOSELECT *FROM Production.Product
GOSELECT DISTINCT ProductIDFROM Production.Product
GOSELECT DISTINCT NameFROM Production.Product
GOSELECT DISTINCT ProductNumberFROM Production.Product
GO

All of the above queries will return the same number of records; hence, they all qualify as Candidate Keys. In other words, they are the candidates for Primary Key. There are few points to consider while turning any Candidate Key into a Primary Key.

Select a key that does not contain NULL

It may be possible that there are Candidate Keys that presently do not contain value (not null) but technically they can contain null. In this case, they will not qualify for Primary Key. In the following table structure, we can see that even though column [name] does not have any NULL value it does not qualify as it has the potential to contain NULL value in future.

CREATE TABLE [Production].[Product]([ProductID] [int] IDENTITY(1,1) NOT NULL,[Name] [dbo].[Name] NULL,[ProductNumber] [nvarchar](25) NOT NULL,[Manufacturer] [nvarchar](25) NOT NULL
)

Select a key that is unique and does not repeat

It may be possible that Candidate Keys that are unique at this moment may contain duplicate value. These kinds of Candidate Keys do not qualify for Primary Key. Let us understand this scenario by looking into the example given above. It is absolutely possible that two Manufacturers can create products with the same name; the resulting name will be a duplicate and only the name of the Manufacturer will differ in the table. This disqualifies Name in the table to be a Primary Key.

Make sure that Primary Key does not keep changing

This is not a hard and fast rule but rather a general recommendation: Primary Key values should not keep changing. It is quite convenient for a database if Primary Key is static. Primary Keys are referenced in numerous places in the database, from Index to Foreign Keys.  If they keep changing then they can adversely affect database integrity, data statistics as well as internal of Indexes.

Selection of Primary Key

Let us examine our case by applying the above three rules to the table and decide on the appropriate candidate for Primary Key. Name can contain NULL so it disqualifies as per Rule 1 and Rule 2. Product Number can be duplicated for different Manufacturers so it disqualifies as per Rule 2. ProductID is Identity and Identity column cannot be modified. So, in this case ProductID qualifies as Primary Key.

Please note that many database experts suggest that it is not a good practice to make Identity Column as Primary Key. The reason behind this suggestion is that many times Identity Column that has been assigned as Primary Key does not play any role in database. There is no use of this Primary Key in both application and in T-SQL. Besides, this Primary Key may not be used in Joins. It is a known fact that when there is JOIN on Primary Key or when Primary Key is used in the WHERE condition it usually gives better performance than non primary key columns. This argument is absolutely valid and one must make sure not to use such Identity Column. However, our example presents a different case. Here, although ProductID is Identity Column it uniquely defines the row and the same column will be used as foreign key in other tables. If a key is used in any other table as foreign key it is likely that it will be used in joins.

Quick Note on Other Kinds of Keys

The above paragraph evokes another question – what is a foreign key? A foreign key in a database table is a key from another table that refers to the primary key in the table being used. A primary key can be referred by multiple foreign keys from other tables. It is not required for a primary key to be the reference of any foreign keys. The interesting part is that a foreign key can refer back to the same table but to a different column. This kind of foreign key is known as “self-referencing foreign key”.

Summary

A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key. Candidate keys that follow all the three rules – 1) Not Null, 2) Unique Value in Table and 3) Static – are the best candidates for Primary Key. If there are multiple candidate keys that are satisfying the criteria for Primary Key, the decision should be made by experienced DBAs who should keep performance in mind.

Reference: Pinal Dave (http://blog.sqlauthority.com), DNS

Creating Read and Write Methods with IBinarySerialize

When you choose UserDefined serialization format, you also must implement the IBinarySerialize interface and create your own Read and Write methods. The following procedures from the Currency UDT use the System.IO.BinaryReader and System.IO.BinaryWriter to read from and write to the UDT

// IBinarySerialize methods
// The binary layout is as follow:
//    Bytes 0 - 19:Culture name, padded to the right 
//    with null characters, UTF-16 encoded
//    Bytes 20+:Decimal value of money
// If the culture name is empty, the currency is null.
public void Write(System.IO.BinaryWriter w)
{
    if (this.IsNull)
    {
        w.Write(nullMarker);
        w.Write((decimal)0);
        return;
    }

    if (cultureName.Length > cultureNameMaxSize)
    {
        throw new ApplicationException(string.Format(
            CultureInfo.InvariantCulture, 
            "{0} is an invalid culture name for currency as it is too long.", 
            cultureNameMaxSize));
    }

    String paddedName = cultureName.PadRight(cultureNameMaxSize, '\0');
    for (int i = 0; i < cultureNameMaxSize; i++)
    {
        w.Write(paddedName[i]);
    }

    // Normalize decimal value to two places
    currencyValue = Decimal.Floor(currencyValue * 100) / 100;
    w.Write(currencyValue);
}
public void Read(System.IO.BinaryReader r)
{
    char[] name = r.ReadChars(cultureNameMaxSize);
    int stringEnd = Array.IndexOf(name, '\0');

    if (stringEnd == 0)
    {
        cultureName = null;
        return;
    }

    cultureName = new String(name, 0, stringEnd);
    currencyValue = r.ReadDecimal();
}

CLR Integration

Requirements for Implementing UDTs

To run in SQL Server, your UDT must implement the following requirements in the UDT definition:

The UDT must specify the Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute. The use of the System.SerializableAttribute is optional, but recommended.

• The UDT must implement the System.Data.SqlTypes.INullable interface in the class or structure by creating a public static (Shared in Microsoft Visual Basic) Nullmethod. SQL Server is null-aware by default. This is necessary for code executing in the UDT to be able to recognize a null value.
• The UDT must contain a public static (or Shared) Parse method that supports parsing from, and a public ToString method for converting to a string representation of the object.
• A UDT with a user-defined serialization format must implement the System.Data.IBinarySerialize interface and provide a Read and a Write method.
• The UDT must implement System.Xml.Serialization.IXmlSerializable, or all public fields and properties must be of types that are XML serializable or decorated with the XmlIgnore attribute if overriding standard serialization is required.
• There must be only one serialization of a UDT object. Validation fails if the serialize or deserialize routines recognize more than one representation of a particular object.
• SqlUserDefinedTypeAttribute.IsByteOrdered must be true in order to ensure that the server uses byte-ordered comparisons for UDT values.
• A UDT defined in a class must have a public constructor that takes no arguments. You can optionally create additional overloaded class constructors.
• The UDT must expose data elements as public fields or property procedures.
• Public names cannot be longer than 128 characters, and must conform to the SQL Server naming rules for identifiers as defined in Identifiers.
• sql_variant columns cannot contain instances of a UDT.
• Inherited members are not accessible from Transact-SQL because the SQL Server 2005 type system is not aware of the inheritance hierarchy among UDTs. However, you can use inheritance when you structure your classes and you can call such methods in the managed code implementation of the type.
• Members cannot be overloaded, except for the class constructor. If you do create an overloaded method, no error is raised when you register the assembly or create the type in SQL Server. Detection of the overloaded method occurs at run time, not when the type is created. Overloaded methods can exist in the class as long as they are never invoked. Once you invoke the overloaded method, an error is raised.
• Any static (or Shared) members must be declared as constants or as read-only. Static members cannot be mutable.
• The serialized UDT cannot be larger than 8000 bytes

Sample:

using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
     IsByteOrdered=true, ValidationMethodName = "ValidatePoint")]
public struct Point : INullable
{
    private bool is_Null;
    private Int32 _x;
    private Int32 _y;

    public bool IsNull
    {
        get
        {
            return (is_Null);
        }
    }

    public static Point Null
    {
        get
        {
            Point pt = new Point();
            pt.is_Null = true;
            return pt;
        }
    }

    // Use StringBuilder to provide string representation of UDT.
    public override string ToString()
    {
        // Since InvokeIfReceiverIsNull defaults to 'true'
        // this test is unneccesary if Point is only being called
        // from SQL.
        if (this.IsNull)
            return "NULL";
        else
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(_x);
            builder.Append(",");
            builder.Append(_y);
            return builder.ToString();
        }
    }

    [SqlMethod(OnNullCall = false)]
    public static Point Parse(SqlString s)
    {
        // With OnNullCall=false, this check is unnecessary if 
        // Point only called from SQL.
        if (s.IsNull)
            return Null;

        // Parse input string to separate out points.
        Point pt = new Point();
        string[] xy = s.Value.Split(",".ToCharArray());
        pt.X = Int32.Parse(xy[0]);
        pt.Y = Int32.Parse(xy[1]);

        // Call ValidatePoint to enforce validation
        // for string conversions.
        if (!pt.ValidatePoint()) 
            throw new ArgumentException("Invalid XY coordinate values.");
        return pt;
    }

    // X and Y coordinates exposed as properties.
    public Int32 X
    {
        get
        {
            return this._x;
        }
        // Call ValidatePoint to ensure valid range of Point values.
        set 
        {
            Int32 temp = _x;
            _x = value;
            if (!ValidatePoint())
            {
                _x = temp;
                throw new ArgumentException("Invalid X coordinate value.");
            }
        }
    }

    public Int32 Y
    {
        get
        {
            return this._y;
        }
        set
        {
            Int32 temp = _y;
            _y = value;
            if (!ValidatePoint())
            {
                _y = temp;
                throw new ArgumentException("Invalid Y coordinate value.");
            }
        }
    }

    // Validation method to enforce valid X and Y values.
    private bool ValidatePoint()
    {
        // Allow only zero or positive integers for X and Y coordinates.
        if ((_x >= 0) && (_y >= 0))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    // Distance from 0 to Point method.
    [SqlMethod(OnNullCall = false)]
    public Double Distance()
    {
        return DistanceFromXY(0, 0);
    }

    // Distance from Point to the specified point method.
    [SqlMethod(OnNullCall = false)]
    public Double DistanceFrom(Point pFrom)
    {
        return DistanceFromXY(pFrom.X, pFrom.Y);
    }

    // Distance from Point to the specified x and y values method.
    [SqlMethod(OnNullCall = false)]
    public Double DistanceFromXY(Int32 iX, Int32 iY)
    {
        return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
    }
}

http://chiragrdarji.wordpress.com/2008/03/11/clr-stored-procedure-in-sql-server-2005/

SQL Service Brokers

SQL Service Brokers
http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/

ervice Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a “conversation.” Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.

Service broker find applications when single or multiple SQL server instances are used. This functionality helps in sending messages to remote databases on different servers and processing of the messages within a single database. In order to send messages between the instances, the Service Broker uses TCP/IP.

This transaction message queuing system enables the developers to build secure and reliable applications, which are scalable. The developers can design applications from independent components known as “services.” If the applications need to avail the functionality of these services, then it sends message to the particular “service.”

Loosely coupled applications (programs that exchange messages independently) are supported by the Service broker. The three components of the Service broker are as follows: conversation components (which consist of the conversation groups, conversations and messages); service definition components (which define the conversations); and networking and security components (defines the infrastructure used for exchanging messages between instances)

The maintenance of Service Broker is easy and it is a part of the routine database administration procedure. This is because this functionality forms a part of the Database Engine. Service Broker also provides security by preventing unauthorized access from networks and by message encryption.

Let us understand Service Broker with simple script. Script contains necessary comments to explain what exactly script is doing.

---------------------------- Service Broker -----------------------
-- In this exercise we will learn how to cofigure Servie Broker and send and recieve messages.
-------------------------------------------------------------------CREATE DATABASE ServiceBrokerTest
GOUSE ServiceBrokerTest
GO-- Enable Service BrokerALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
GO-- Create Message TypeCREATE MESSAGE TYPE SBMessage
VALIDATION = NONE
GO-- Create ContractCREATE CONTRACT SBContract(SBMessage SENT BY INITIATOR)GO-- Create Send QueueCREATE QUEUE SBSendQueue
GO-- Create Receive QueueCREATE QUEUE SBReceiveQueue
GO-- Create Send Service on Send QueueCREATE SERVICE SBSendServiceON QUEUE SBSendQueue (SBContract)GO-- Create Receive Service on Recieve QueueCREATE SERVICE SBReceiveServiceON QUEUE SBReceiveQueue (SBContract)GO-- Begin Dialog using service on contractDECLARE @SBDialog uniqueidentifierDECLARE @Message NVARCHAR(128)BEGIN DIALOG CONVERSATION @SBDialogFROM SERVICE SBSendServiceTO SERVICE 'SBReceiveService'ON CONTRACT SBContractWITH ENCRYPTION = OFF-- Send messages on DialogSET @Message = N'Very First Message';SEND ON CONVERSATION @SBDialogMESSAGE TYPE SBMessage (@Message)SET @Message = N'Second Message';SEND ON CONVERSATION @SBDialogMESSAGE TYPE SBMessage (@Message)SET @Message = N'Third Message';SEND ON CONVERSATION @SBDialogMESSAGE TYPE SBMessage (@Message)GO-- View messages from Receive QueueSELECT CONVERT(NVARCHAR(MAX), message_body) AS MessageFROM SBReceiveQueue
GO-- Receive messages from Receive QueueRECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS MessageFROM SBReceiveQueue
GO-- Receive messages from Receive QueueRECEIVE CONVERT(NVARCHAR(MAX), message_body) AS MessageFROM SBReceiveQueue
GO-- Clean UpUSE master
GODROP DATABASE ServiceBrokerTest
GO

You can download the above script from here.

Let me know what do you think of this script and how simply one can configure service broker.

Reference : Pinal Dave (http://blog.sqlauthority.com)