Solved

3rd Normal Form for Dummies

Posted on 2005-03-17
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
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

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

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

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

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

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

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.
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
Course of the Month8 days, 18 hours left to enroll

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.