Second normal form
From Wikipedia, the free encyclopedia
Second normal form (2NF) is a normal form used in database normalization. It requires that all data elements in a table are functionally dependent on all of the table's primary key. If data elements only depend on part of a primary key, then they are parsed out to separate tables. If the table has a single field as the primary key, it is automatically in 2NF.
A table is in 2NF if and only if
- It is in 1NF
- Each non-primary key attribute is irreducibly dependent on the primary key
[edit] Example
Consider a table describing the source of machine parts, with the following attributes:
| Part's ID (primary key) | Supplier's ID (primary key) | Supplier's name | Price | Supplier's address |
|---|---|---|---|---|
| 65 | 2 | Stylized Parts | 59.99 | VA |
| 73 | 2 | Stylized Parts | 20.00 | VA |
| 65 | 1 | ACME Industries | 69.99 | CA |
This table is in the first normal form, as all values in the table are atomic. The part's ID and the supplier's ID form the composite primary key because the same part can be supplied by multiple suppliers.
The relationship of the primary keys to price is correct: price is fully dependent on the primary key since different suppliers may charge a different price for the same part.
However, the supplier's name and address are only dependent on the supplier's ID, and thus this table breaks 2NF. Note the redundancy, where the name "Stylized Parts" and address "VA" is recorded twice. What if they relocate or merge with another company? This attribute should be placed in a second table like:
| ID | Name | Address |
|---|---|---|
| 1 | ACME Industries | CA |
| 2 | Stylized Parts | VA |
The original "Parts Source" table from above would then be modified thusly:
| Part's ID (primary key) | Supplier's ID (primary key) | Price |
|---|---|---|
| 65 | 2 | 59.99 |
| 73 | 2 | 20.00 |
| 65 | 1 | 69.99 |
In order to find if a table is in 2NF, examine each non-key column of the table and ask whether or not it is fully dependent on every key in the composite key. If it isn't, it should be moved to its own table. Note that the next step, making sure that each non-key column is not dependent on any other non-key column, constitutes the third normal form.
[edit] Further reading
- Litt's Tips: Normalization
- Rules Of Data Normalization
- Date, C. J., & Lorentzos, N., & Darwen, H. (2002). Temporal Data & the Relational Model (1st ed.). Morgan Kaufmann. ISBN 1-55860-855-9.
- Date, C. J. (1999), An Introduction to Database Systems (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
- Kent, W. (1983) A Simple Guide to Five Normal Forms in Relational Database Theory, Communications of the ACM, vol. 26, pp. 120-125
- Date, C.J., & Darwen, H., & Pascal, F. Database Debunkings
- H.-J. Schek, P.Pistor Data Structures for an Integrated Data Base Management and Information Retrieval System
[edit] External links
- Database Normalization Basics by Mike Chapple (About.com)
- An Introduction to Database Normalization by Mike Hillyer.
- Normalization by ITS, University of Texas.
- A tutorial on the first 3 normal forms by Fred Coulson
- Free PDF poster available by Marc Rettig
- Description of the database normalization basics by Microsoft
| Topics in Database normalization |
|
First normal form | Second normal form | Third normal form |
