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

Feeds:: Posts; Comments

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

November 26, 2007 by pinaldave

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.

Posted in DBA, Data Warehousing, Database, Pinal Dave, SQL, SQL Authority, SQL Interview Questions and Answers, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology | 7 Comments

7 Responses

on January 29, 2008 at 3:24 pm | Reply Raghavendra

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
on May 9, 2008 at 7:47 pm | Reply ggc

i ges its being kept hanging / waiting
on September 23, 2008 at 7:00 am | Reply SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authority with Pinal Dave

[...] Normalize Database structure based on 3rd Normalization Form. Normalization is the process of designing a data model to efficiently store data in a database. (Read More Here) [...]
on September 26, 2008 at 12:11 am | Reply Jyothi P

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.
on February 19, 2009 at 5:53 pm | Reply Surendra H

Hi Pinal,

Very well explained…

Thanks and keep it up.

-Surendra
on February 26, 2009 at 5:23 pm | Reply SQL SERVER - 2008 - Interview Questions and Answers - Part 1 Journey to SQL Authority with Pinal Dave

[...] If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key. (Read More Here) [...]
on September 12, 2009 at 12:13 pm | Reply Pravin Patel

Hi Pinal,

Can you please explain 3rd NF with example?

Thanks & Regards

Pravin Patel.