?
Solved

3rd Normal Form for Dummies

Posted on 2005-03-17
5
Medium Priority
?
1,110 Views
Last Modified: 2012-08-13
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
Comment
Question by:lcor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 400 total points
ID: 13570598
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
 
LVL 13

Assisted Solution

by:bochgoch
bochgoch earned 400 total points
ID: 13575770
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
 
LVL 2

Accepted Solution

by:
ExpertAtNothing earned 400 total points
ID: 13576713
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
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 400 total points
ID: 13577038
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
 
LVL 1

Assisted Solution

by:VBpassion
VBpassion earned 400 total points
ID: 13581305
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question