SQL SERVER – Rules of Third Normal Form and Normalization Advantage – 3NF

I always ask question about Third Normal Form in interviews I take.

Q. What is Third Normal Form and what is its advantage?
A. Third Normal Form (3NF) is most preferable normal form in RDBMS. Normalization is the process of designing a data model to efficiently store data in a database. The rules of 3NF are mentioned here

  • Make a separate table for each set of related attributes, and give each table a primary key.
  • If an attribute depends on only part of a multi-valued key, remove it to a separate table
  • If attributes do not contribute to a description of the key, remove them to a separate table.

Normalization is very close to concept of object oriented schema’s and it stores one data at only one place by removing all the redundant data. It also helps to draw the schema easier. Normalization comes at the cost of performance.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , DataModel.org.

32 thoughts on “SQL SERVER – Rules of Third Normal Form and Normalization Advantage – 3NF

  1. Hi…
    i need to know my pending jobs for the day…
    can u give me some idea on this…

    pending means… jobs are scheduled to run for today, but not run for the day.. yet to run… on the day…!

    i tried a lot using sysjobschedules table.. but no clue…!

    regards,
    Raghavendra

    Like

  2. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authority with Pinal Dave

  3. SQL Server Agent provides a new stored procedure for enumerating real-time job activity based on the current session.
    Performing the query sp_help_jobactivity reveals a list of all active SQL Server Agent jobs.

    This new stored procedure for enumerating real-time job activity obtains some data from the sysjobactivity table in msdb.
    The sysjobactivity table is where SQL Server Agent stores job status information for the current session and all previous sessions of SQL Server Agent.

    If the SQL Server Agent Service fails unexpectedly, users can determine which SQL Server Agent jobs were in the middle of being executed by looking that the sysjobactivity table for the previous session.

    Like

  4. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 1 Journey to SQL Authority with Pinal Dave

  5. I have 98 lac row in a table. To fetch a query it takes too much time. give me some idea to improve the performance.
    here we have already used non-clustered index on required column and also repairing time to time.

    thanks

    Like

    • to sakendra kumar.
      what u can do is

      1.limit the no of columns in ur select query

      2.check the fragmentation of indexes on that table
      and accordingly rebuild or reorganize.

      3.use recommendations from db tuning advisor.

      4. check he execution plan and find out to get ur results
      how many rows it is scanning

      Like

  6. Hi Pinal,

    Can you explain me the normalization process through an example?(All the normal forms 1NF, 2NF, 3NF, BCNF, 4NF, 5NF).

    Like

  7. How can i know in which NF the tabe is?

    For example I have a table created like this:

    CREATE TABLE customers
    (
    cCustomer ID char(4) not null constraint pkCustomerID primary key,
    CCustName char(20) not null,
    vCustName varchar(20) not null,
    CAddress char(40) not null,
    CCity char(20) null,
    CState char(20) null,
    CPhone char(10) null,
    )

    Like

  8. Pinal Sir,
    i’m new to ur blog sir. Recently found u sir. It’s Very useful and helpful to us sir.
    And i’m a fresher for Sql.
    I need simple examples how to use Stored procedure and Trigger.

    Kindly Reply me sir..

    Like

  9. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31 Journey to SQLAuthority

  10. hi pinal sir

    I am new to this site and also i am a fresher..
    can u explaine me SP,trigers and Views with small example sir..
    pls do the needful

    Like

  11. Hi Pinal sir,
    your articles are great!, can you please explain with example If an attribute depends on only part of a multi-valued key, remove it to a separate table, what is multi-valued key means?
    Thanks.

    Like

  12. Hi Pinal,
    I am also New to Sql Sever But i am interesting go to database side .
    Basically I am developer I know the Syntax in Sqlserver .
    But Now i want know depth in sql server .
    what should i do please help me sir
    will give you any Reference liks or videos .

    Like

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s