x

What is meant by normal form?

Database design techniques often use the terms

  • First Normal Form
  • Second Normal Form
  • Third Normal Form

and so on.

What do these mean, and how do we implement these in SQL Server?

How do you strike the balance between purist design and pragmatic implementation?

more ▼

asked Oct 19, 2009 at 01:22 PM in Default

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

Excellent question and congrats on 5k.
Mar 21, 2010 at 10:12 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

The Normal Forms are rules for creating relations that will attempt to eliminate data anomolies.

It's taking a real world problem, such as an invoice, and creating tables to represent it such that there is no data redundancy, data integrity is enhanced, and dependancies are identified.

  • 1NF ATOMIC entries for every field, no lists.
  • 2NF Every non-key field in a row is dependant on the entire key.
  • 3NF A non-key field should not be determined by another non-key field in the same row.

In SQL we implement these ideas through tables, constaints and relational keys.

There are times when design is denormalized for performance. For instance it may be more efficient, despite the additional space requirements and potential IUD overhead, to add a field to a row rather than joining an additional table to get the information.

more ▼

answered Oct 19, 2009 at 02:52 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left

A very cool line I once read:

The data depends on the Key [1 NF], the Whole Key [2 NF] and nothing but the key [3 NF]

Details:

http://en.wikipedia.org/wiki/1NF
http://en.wikipedia.org/wiki/2NF
http://en.wikipedia.org/wiki/3NF

more ▼

answered Oct 19, 2009 at 02:32 PM

Raj More gravatar image

Raj More
1.7k 80 82 84

(comments are locked)
10|1200 characters needed characters left

Normal Forms are a set of design principles used for analysing and elminating certain types of redudancy in database design. The Second and Third normal forms (2NF and 3NF) are concerned with redundancies that can arise from Functional Dependencies.

2NF and 3NF are subsumed by the more significant and concise Boyce-Codd Normal Form (BCNF) which can be stated as: Every non-trivial determinant is a superkey.

Normalization has nothing to do with performance because it specifies only the logical characteristics of the database, not their physical implementation. However, given the poor degree of Physical Data Independence in many DBMSs it is sometimes necessary to compromise your logical model in order to make corresponding changes to the underlying physical implementation. Another reason to make such compromises is if you need to implement some constraint or other logic that the DBMS cannot otherwise support.

more ▼

answered Oct 19, 2009 at 04:36 PM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

The only glory most of us have to hope for is the glory of being normal - Katherine Fullerton Gerould

In anything at all, perfection is finally attained not when there is no longer anything to add, but when there is no longer anything to take away. - Saint-Exupery, Wind, Sand, and the Stars

The rules leading to and including the third normal form can be summed up in a single statement: Each attribute must be a fact about the key, the whole key, and nothing but the key. - Wiorkowski and Kull

more ▼

answered Dec 09, 2009 at 07:00 AM

Scot Hauder 4 gravatar image

Scot Hauder 4
54 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x108
x4

asked: Oct 19, 2009 at 01:22 PM

Seen: 4755 times

Last Updated: Oct 20, 2009 at 05:55 AM