SQL Server - third normal form - Asked By Aldo Lin Lin on 04-Aug-08 06:25 AM

what is third normal form in SQL?

Third Normal form - Shailendrasinh Parmar replied to Aldo Lin Lin on 04-Aug-08 06:29 AM

See the following articles ::

http://databases.about.com/od/administration/l/bldef_3nf.htm

http://databases.about.com/od/specificproducts/a/3nf.htm

http://blog.sqlauthority.com/2007/11/26/sql-server-rules-of-third-normal-form-and-normalization-advantage-3nf/

Hope it helps.

Check this - san san replied to Aldo Lin Lin on 04-Aug-08 06:36 AM

Hi

Third Normal Form (3NF): In order to be in Third Normal Form, a relation must first fulfill the requirements to be in second norma formhttp://databases.about.com/library/glossary/bldef-2nf.htm.  Additionally, all attributes that are not dependent upon the primary key must be eliminated.

Example: The following table is NOT in Third Normal Form:

Company City State ZIP
Acme Widgets New York NY 10169
ABC Corporation Miami FL 33196
XYZ, Inc. Columbia MD 21046

In this example, the city and state are dependent upon the ZIP code.  To place this table in 3NF, two separate tables would be created -- one containing the company name and ZIP code and the other containing city, state, ZIP code pairings.

This may seem overly complex for daily applications and indeed it may be. 

Hope this helps

SAN

Check this - san san replied to Aldo Lin Lin on 04-Aug-08 06:40 AM

Hi
3NF  Requirements:
1)  Meets all requirements of 2NF
                                    2) 3NF is violated when a non-key field is a fact about another non-key field.

and look at this link
http://www.anaesthetist.com/mnm/sql/normal.htm

hope this helps
SAN
3NF - Sagar P replied to Aldo Lin Lin on 04-Aug-08 06:40 AM

Definition: In order to be in Third Normal Form, a http://databases.about.com/od/administration/l/bldef-relation.htm must first fulfill the requirements to be in http://databases.about.com/od/administration/l/bldef-2nf.htm.  Additionally, all attributes that are not dependent upon the primary key must be eliminated.

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.

A table is in third normal form (3NF) if and only if it is in 2NF and every non key attribute is non transitively dependent on the primary key (i.e. there are no transitive dependencies).

  1. Anomalies can occur when a relation contains one or more transitive dependencies.
  2. A relation is in 3NF when it is in 2NF and has no transitive dependencies.
  3. A relation is in 3NF when 'All non-key attributes are dependent on the key, the whole key and nothing but the key'.

Go thr these links to know more;

http://databases.about.com/od/administration/l/bldef_3nf.htm

http://www.visualbuilder.com/database/tutorial/pageorder/10/

http://blog.sqlauthority.com/2007/11/26/sql-server-rules-of-third-normal-form-and-normalization-advantage-3nf/

Best Luck!!!!!!!!!!!!!
Sujit.

Third-normal form - Kalit Sikka replied to Aldo Lin Lin on 04-Aug-08 06:52 AM

Although transforming a relation that is not in 2NF into a number of relations that are in 2NF removes many of the anomalies that appear in the relation that was not in 2NF, not all anomalies are removed and further normalization is sometime needed to ensure further removal of anomalies. These anomalies arise because a 2NF relation may have attributes that are not directly related to the thing that is being described by the candidate keys of the relation. Let us first define the 3NF.

A relation R is in third normal form if it is in 2NF and every non-key attribute of R is non-transitively dependent on each candidate key of R.

To understand the third normal form, we need to define transitive dependence which is based on one of Armstrong's axioms. Let A, B and C be three attributes of a relation R such that A -> B and B -> C. From these FDs, we may derive A -> C. As noted earlier, this dependence A -> C is transitive.

The 3NF differs from the 2NF in that all non-key attributes in 3NF are required to be directly dependent on each candidate key of the relation. The 3NF therefore insists, in the words of Kent (1983) that all facts in the relation are about the key (or the thing that the key identifies), the whole key and nothing but the key. If some attributes are dependent on the keys transitively then that is an indication that those attributes provide information not about the key but about a kno-key attribute. So the information is not directly about the key, although it obviously is related to the key.

Consider the following relation

subject (cno, cname, instructor, office)

Assume that cname is not unique and therefore cno is the only candidate key. The following functional dependencies exist

cno -> cname
cno -> instructor
instructor -> office

We can derive cno -> office from the above functional dependencies and therefore the above relation is in 2NF. The relation is however not in 3NF since office is not directly dependent on cno. This transitive dependence is an indication that the relation has information about more than one thing (viz. course and instructor) and should therefore be decomposed. The primary difficulty with the above relation is that an instructor might be responsible for several subjects and therefore his office address may need to be repeated many times. This leads to all the problems that we identified at the beginning of this chapter. To overcome these difficulties we need to decompose the above relation in the following two relations:

s (cno, cname, instructor)
ins (instructor, office)

s is now in 3NF and so is ins.

An alternate decomposition of the relation subject is possible:

s(cno, cname)
inst(instructor, office)
si(cno, instructor)

The decomposition into three relations is not necessary since the original relation is based on the assumption of one instructor for each course.

The 3NF is usually quite adequate for most relational database designs. There are however some situations, for example the relation student(sno, sname, cno, cname) discussed in 2NF above, where 3NF may not eliminate all the redundancies and inconsistencies. The problem with the relation student(sno, sname, cno, cname) is because of the redundant information in the candidate keys. These are resolved by further normalization using the BCNF.

Example 1

Here is the classic employee table example:

Emp_SSN
Emp_Name
Street
City
State
Zip

Here we have a transitive dependency between Zip field and City and State fields - what ever that means.  I guess it means they are a little dependent but no big deal.  Since a zip will tell you what state and what city an address is in.  Therefore, to make this table third normal form we need to split out city state and zip fields into a table of their own and then just have the zip in the employee table:

Emp_SSN
Emp_Name
Street
Zip

The zip tables is as follows:

Zip
City
State

The two tables are now in third normal form.  Some people may argue that there is some transitive relationship between City and State - but we've already gone further in normalizing the Employee table than is customary in an Access database.

This problem might be more obvious if we add mailman to the list:

Emp_SSN
Emp_Name
Street
Zip
Mail_Man

Here you can see that we need a table of Zips defining each Mail_Man.  Here is the resulting tables assuming a mail man only works in one zip

Zip
Mail_Man

Emp_SSN
Emp_Name
Street
Zip

Example 2

Here's a more straightforward example:

Third Normal Form

Above we have a Students table with SSN, Student's Advisor and the Advisor's Phone number.
Clearly, the advisor's phone is dependent upon the advisor and the either candidate primary key for this table (Student_Name and Student_SSN).  To change this table to third normal form we need to break out the Student Advisor and Advisor Phone fields into a separate table.  There resulting third normal form tables look like:

Third Normal Form Tables

Third Normal Form

The two tables are now in third normal form.

third normal form - Partha Mandayam replied to Aldo Lin Lin on 04-Aug-08 06:59 AM
Third Normal Form (3NF) is a database design technique where all attributes must be dependant on the primary key. If any attributes are not dependant on the primary key, move them to a separate table.
3NF - sundar k replied to Aldo Lin Lin on 04-Aug-08 07:32 AM

A table is in Third Normal Form only when every non-key column is directly dependent on key column, i.e. you should not have a single non-key column in your table which is not directly dependent on key column.

An example of a 2NF table that fails to meet the requirements of 3NF is:

Tournament               Year     Winner                      Winner Date of Birth
--------------------    -----    --------------------- ----------------------
Indiana Invitational     1998     Al Fredrickson         21 July 1975
Cleveland Open         1999     Bob Albertson         28 September 1968
Des Moines Masters  1999     Al Fredrickson         21 July 1975
Indiana Invitational     1999     Chip Masterson         14 March 1977

In the above table, primary key is Tournament & Year. But if you look at the table contents, you have Winner Date of Birth which is not directly related to Tournament & Year (i.e. your primary key columns). Winner Date of Birth is related directly Winner alone. So this information should be seperated from the table toa seperate new table.

In order to express the same facts without violating 3NF, it is necessary to split the table into two:


Tournament Winners

Tournament               Year        Winner
-------------            -------       --------------
Indiana Invitational     1998        Al Fredrickson
Cleveland Open         1999        Bob Albertson
Des Moines Masters  1999        Al Fredrickson
Indiana Invitational     1999     Chip Masterson


Player Dates of Birth

Player                      Date of Birth
---------------        --------------
Chip Masterson      14 March 1977
Al Fredrickson        21 July 1975
Bob Albertson         28 September 1968
 

3rd NF - Atul Shinde replied to Aldo Lin Lin on 04-Aug-08 07:54 AM
Every non key attribute is non transitively dependent on the primary key (i.e. there are no transitive dependencies).
  1. A relation is in 3NF when it is in 2NF and has no transitive dependencies.
  2. A relation is in 3NF when 'All non-key attributes are dependent on the key, the whole key and nothing but the key'.
  3. Anomalies can occur when a relation contains one or more transitive dependencies.

For 3 NF It must satisfied with 2NF.

#NF with Example - Sunil Supale replied to Aldo Lin Lin on 04-Aug-08 10:42 AM
plz go through the following link ,u can clerly get the idea of Normalization .
http://www.infocom.cqu.edu.au/Courses/spr2000/95169/Extra_Examples/Normalisation_Example/index.html
will this article help you: - Binny ch replied to Aldo Lin Lin on 04-Aug-08 01:35 PM

Normal Forms

There is an easy mnemonic that describes our usual goal of "third normal form". Here it is:

Data should depend on the key, the whole key, and nothing but the key! ... Rule #3.

What does this mean?

First Normal Form

If a relation contains no repeating groups, then it is in first normal form. Conversely, if we find that for a single primary key, we have two data items that we need to 'squeeze' into one column, then the data cannot be in first normal form. Let's say that we've decided to slap together a database containing information about patients and the (medicinal) drugs they are taking. So we create a table with the following columns, including the primary key "Patient ID Number":

Patient ID Number Patient Name Drug Trade Name Formulation Size Dose Frequency Side Effect

Let's try filling in some information..

Patient ID Number Patient Name Drug Trade Name Formulation Size Dose Frequency Side Effect
6009315011076 Soap,Joe Carbimazole
Carbamazepine
Neomercazole
Tegretol
Tab
Caps
10mg
200mg
30mg
200mg
OD
TDS
Agranulocytosis
nil
6003325011074 Green,Anne Carbimazole Neomercazole Tab 10mg 10mg OD nil

Woops. At the intersection of some rows and columns, we've had to fill in two different data items. It's also obvious that there is considerable redundancy in the table - the drug Carbimazole (with all the associated information) is represented twice.

Fixing to First Normal Form

It should be clear that we can fix things by simply introducing a "composite primary key" - we can group the drug and the patient ID together as a composite primary key, so now each row has a unique primary key, and at no intersection of row and column do we have to put in two data items:

Patient ID Number Drug Patient Name Trade Name Formulation Size Dose Frequency Side Effect
6009315011076 Carbimazole Soap, Joe Neomercazole Tab 10mg 30mg OD Agranulocytosis
6009315011076 Carbamazepine Soap, Joe Tegretol Caps 200mg 200mg TDS nil
6003325011074 Carbimazole Green,Anne Neomercazole Tab 10mg 10mg OD nil

Equally clearly, this is a clumsy fix, and really hasn't changed much. We still have immense data redundancy. We need to go further to..

Second Normal Form

Note that, although the above table is now in 'first normal form', nothing much has changed. Look at the table carefully, and you'll see that there are what we call partial dependencies. In other words, the columns Trade Name, Formulation, and Size depend only on the Drug component of the key, and not on the other part of the key, Patient ID Number. Likewise, the Patient Name depends only on the Patient ID Number, and is not dependent at all on the Drug column! To get rid of this problem, we create two new tables, a Patient Table:

Patient
Patient ID Number Patient Name
6009315011076 Soap, Joe
6003325011074 Green,Anne

and a Drug Table:

Drug
Drug Trade Name Formulation Size
Carbimazole Neomercazole Tab 10mg
Carbamazepine Tegretol Caps 200mg

Finally, we relate the two in a diminished form of our original table, thus:

PatientRx
Patient ID Number Drug Dose Frequency Side Effect
6009315011076 Carbimazole 30mg OD Agranulocytosis
6009315011076 Carbamazepine 200mg TDS nil
6003325011074 Carbimazole 10mg OD nil

Third Normal Form

.. but still we're not finished. Look at the last table carefully. There's an intimate relationship between the the dose and the frequency of giving a drug. Here's where knowledge of your subject comes in. Many people would be happy to stop at this point. There is no absolutely fixed rule that says you have to give your carbamazepine at a dose of 200mg TDS - in fact, if you're religiously doing so, you're probably mistreating a lot of your epileptics. But let's say the whole point of our drug survey is to relate different drug regimens and associated side effect! In this context, we have a strong association between the dose and frequency of a drug. We call such a relationship between two non-key attributes a transitive dependency.

Third normal form is about removing transitive dependencies. How could we do this? Well, we could label each dose + frequency as a "regimen", and proceed to break up the PatientRx table into two, thus:

PatientRx
Patient ID Number Drug Regimen Side Effect
6009315011076 Carbimazole R1 Agranulocytosis
6009315011076 Carbamazepine R3 nil
6003325011074 Carbimazole R2 nil

with a new DrugRegimen table:

DrugRegimen
Regimen Dose Frequency
R1 30mg OD
R2 10mg OD
R3 200mg TDS