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

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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

avatar image

12.1k 30 36 42

(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]



more ▼

answered Oct 19, 2009 at 02:32 PM

avatar image

Raj More
1.8k 83 89 90

(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

avatar image

David 1
1.8k 3 5

(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

avatar image

Scot Hauder 4
54 2 2

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 19, 2009 at 01:22 PM

Seen: 13215 times

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

Copyright 2018 Redgate Software. Privacy Policy