Example
Consider a table describing employees' skills:
Employee | Skill | Current Work Location |
---|---|---|
Jones | Typing | 114 Main Street |
Jones | Shorthand | 114 Main Street |
Jones | Whittling | 114 Main Street |
Bravo | Light Cleaning | 73 Industrial Way |
Ellis | Alchemy | 73 Industrial Way |
Ellis | Flying | 73 Industrial Way |
Harrison | Light Cleaning | 73 Industrial Way |
Neither {Employee} nor {Skill} is a candidate key for the table. This is because a given Employee might need to appear more than once (he might have multiple Skills), and a given Skill might need to appear more than once (it might be possessed by multiple Employees). Only the composite key {Employee, Skill} qualifies as a candidate key for the table.
The remaining attribute, Current Work Location, is dependent on only part of the candidate key, namely Employee. Therefore the table is not in 2NF. Note the redundancy in the way Current Work Locations are represented: we are told three times that Jones works at 114 Main Street, and twice that Ellis works at 73 Industrial Way. This redundancy makes the table vulnerable to update anomalies: it is, for example, possible to update Jones' work location on his "Typing" and "Shorthand" records and not update his "Whittling" record. The resulting data would imply contradictory answers to the question "What is Jones' current work location?"
A 2NF alternative to this design would represent the same information in two tables: an "Employees" table with candidate key {Employee}, and an "Employees' Skills" table with candidate key {Employee, Skill}:
Employee | Current Work Location |
---|---|
Jones | 114 Main Street |
Bravo | 73 Industrial Way |
Ellis | 73 Industrial Way |
Harrison | 73 Industrial Way |
Employee | Skill |
---|---|
Jones | Typing |
Jones | Shorthand |
Jones | Whittling |
Bravo | Light Cleaning |
Ellis | Alchemy |
Ellis | Flying |
Harrison | Light Cleaning |
Neither of these tables can suffer from update anomalies.
Not all 2NF tables are free from update anomalies, however. An example of a 2NF table which suffers from update anomalies is:
Tournament | Year | Winner | Winner Date of Birth |
---|---|---|---|
Des Moines Masters | 1998 | Chip Masterson | 14 March 1977 |
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 |
Even though Winner and Winner Date of Birth are determined by the whole key {Tournament, Year} and not part of it, particular Winner / Winner Date of Birth combinations are shown redundantly on multiple records. This leads to an update anomaly: if updates are not carried out consistently, a particular winner could be shown as having two different dates of birth.
The underlying problem is the transitive dependency to which the Winner Date of Birth attribute is subject. Winner Date of Birth actually depends on Winner, which in turn depends on the key Tournament / Year.
This problem is addressed by third normal form (3NF).
A table for which there are no partial functional dependencies on the primary key is typically, but not always, in 2NF. In addition to the primary key, the table may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys.
Multiple candidate keys occur in the following table:
Manufacturer | Model | Model Full Name | Manufacturer Country |
---|---|---|---|
Forte | X-Prime | Forte X-Prime | Italy |
Forte | Ultraclean | Forte Ultraclean | Italy |
Dent-o-Fresh | EZbrush | Dent-o-Fresh BananaBrush-2000 | USA |
Kobayashi | ST-60 | Kobayashi ST-60 | Japan |
Hoch | Toothmaster | Hoch Toothmaster | Germany |
Hoch | X-Prime | Hoch X-Prime | Germany |
Even if the designer has specified the primary key as {Model Full Name}, the table is not in 2NF. {Manufacturer, Model} is also a candidate key, and Manufacturer Country is dependent on a proper subset of it: Manufacturer. To make the design conform to 2NF, it is necessary to have two tables:
Manufacturer | Manufacturer Country |
---|---|
Forte | Italy |
Dent-o-Fresh | USA |
Kobayashi | Japan |
Hoch | Germany |
Manufacturer | Model | Model Full Name |
---|---|---|
Forte | X-Prime | Forte X-Prime |
Forte | Ultraclean | Forte Ultraclean |
Dent-o-Fresh | EZbrush | Dent-o-Fresh BananaBrush-2000 |
Kobayashi | ST-60 | Kobayashi ST-60 |
Hoch | Toothmaster | Hoch Toothmaster |
Hoch | X-Prime | Hoch X-Prime |
Read more about this topic: Second Normal Form
Famous quotes containing the word example:
“Our intellect is not the most subtle, the most powerful, the most appropriate, instrument for revealing the truth. It is life that, little by little, example by example, permits us to see that what is most important to our heart, or to our mind, is learned not by reasoning but through other agencies. Then it is that the intellect, observing their superiority, abdicates its control to them upon reasoned grounds and agrees to become their collaborator and lackey.”
—Marcel Proust (18711922)