• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1127
  • Last Modified:

3rd Normal Form for Dummies

Can someone explain 3rd normal form for a novice db modeler?  I understand first and second easily but 3rd is more difficult to understand.
0
lcor
Asked:
lcor
5 Solutions
 
jrb1Commented:
I'll give my simple take on 1st and 2nd too....

1st: each record of a table has the same number of fields
2nd: each non-key field must be a fact about the entire key
3rd: each non-key field must not be a fact about another non-key field (for example, if you have an employee record which contains deptid on, and the location is dependent on department, you can't store location on the same table)
0
 
bochgochCommented:
FIRST NORMAL FORM
First normal form deals with the "shape" of a record type.
Under first normal form, all occurrences of a record type must contain the same number of fields.

SECOND AND THIRD NORMAL FORMS
Second and third normal forms deal with the relationship between non-key and key fields.
Under second and third normal forms, a non-key field must provide a fact about the key, us the whole key, and nothing but the key. In addition, the record must satisfy first normal form.
Second normal form is violated when a non-key field is a fact about a subset of a key.
Third normal form is violated when a non-key field is a fact about another non-key field.
0
 
ExpertAtNothingCommented:
Think of it this way:

You have an order database...in 1NF it could be:
     Orders
          OrderNumber
          CustomerName
          Customer Address
          PartNumber
          Price
          Quantity
          Total    

In 2NF it could be
     Orders
          OrderNumber
          CustomerNumber (create customer table to remove multiple rows with the same values)
          PartNumber
          Unit Price
          Quantity
          Total

In 3NF it would need to be
     Orders
         OrderNumber
         CustomerNumber
         PartNumber
         UnitPrice
         Quantity
         (remove total because it is a calculated field...Quantity * UnitPrice)
         
0
 
ptjcbCommented:
I always use an address table for an example:

Addresses
    AddressID (PK)
    LineOne
    LineTwo
    City
    State
    PostalCode


3rd Normal Form means no dependencies on non-key attributes. The key is AddressID. LineOne is meaningless without the addressID. Same with LineTwo, City and State.
PostalCode is something else. If you have the postal code (zip code in US) then you can decipher the state and the city. For example, 55401 would let someone know that the state is Minnesota and the city is Minneapolis. Therefore, postal code is not dependent on addressid. In 3NF you would create a new table and add the addressid and the postal code to that one.
0
 
VBpassionCommented:
hi Icor

Since you understood 2nf, the initial requirement for any Database to be in 3nf is that it should satisfy 2nf conditions PLUS a prime condition that "all attributes(fields) that are not dependent upon the primary key must be eliminated." Thats all , its that simple.....for illustrating an example, i would like to demonstrate the same example of what 'ExpertAtNothing' had described...its correct...remove the quantitiy field as its not dependent on the Primary. This defines 3nf.

Cheers
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now