Jump to content

Second normal form: Difference between revisions

From Wikipedia, the free encyclopedia
Browse history interactively
← Previous edit
Content deleted Content added
VisualWikitext
Bluelinking 1 books for verifiability.) #IABot (v2.1alpha3
mNo edit summary
 
(44 intermediate revisions by 25 users not shown)
Line 1: Line 1:
{{Short description|Term in database normalization}}{{More footnotes needed|date=June 2024}}
⚫
'''Second normal form''' ('''2NF''') is a [[Database normalization#Normal forms|normal form]] used in [[database normalization]]. 2NF was originally defined by [[E. F. Codd]] in 1971.<ref name="Codd">Codd, E. F. "Further Normalization of the Data Base Relational Model". (Presented at Courant Computer Science Symposia Series 6, "Data Base Systems", New York City, May 24–25, 1971.) IBM Research Report RJ909 (August 31, 1971). Republished in Randall J. Rustin (ed.), ''Data Base Systems: Courant Computer Science Symposia Series 6''. Prentice-Hall, 1972.</ref>


A relation is in the second normal form if it fulfills the following two requirements:
'''Second normal form''' ('''2NF'''), in [[database normalization]], is a [[Database normalization#Normal forms|normal form]]. A relation is in the second normal form if it fulfills the following two requirements:
# It is in [[first normal form]].
# It is in [[first normal form]].
# It does not have any [[non-prime attribute]] that is [[Functional dependency|functionally dependent]] on any [[proper subset]] of any [[candidate key]] of the relation. '''A non-prime attribute of a relation''' is an attribute that is not a part of any candidate key of the relation.
# It does not have any [[non-prime attribute]] that is [[Functional dependency|functionally dependent]] on any [[proper subset]] of any [[candidate key]] of the relation (i.e. it lacks partial dependencies). A ''non-prime attribute of a relation'' is an attribute that is not a part of any candidate key of the relation.


Put simply, a relation (or table) is in 2NF if:
Put simply, a relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key. Note that it does not put any restriction on the non-prime to non-prime attribute dependency. That is addressed in [[third normal form]].
# It is in 1NF and has a single attribute [[unique identifier]] (UID) (in which case every non key attribute is dependent on the entire UID), or
# It is in 1NF and has a multi-attribute unique identifier, and every regular attribute (not part of the UID) is dependent on ''all attributes'' in the multi-attribute UID, not just one attribute (or part) of the UID.
If any regular (non-prime) attributes are predictable (dependent) on another (non-prime) attribute, that is addressed in [[third normal form]].


== History ==
==2NF and candidate keys==
⚫
The second normal form was originally defined by [[E. F. Codd]] in 1971.<ref name="Codd">Codd, E. F. "Further Normalization of the Data Base Relational Model". (Presented at Courant Computer Science Symposia Series 6, "Data Base Systems", New York City, May 24–25, 1971.) IBM Research Report RJ909 (August 31, 1971). Republished in Randall J. Rustin (ed.), ''Data Base Systems: Courant Computer Science Symposia Series 6''. Prentice-Hall, 1972.</ref>
A [[functional dependency]] on part of any candidate key is a violation of 2NF. In addition to the [[primary key]], the relation 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.


== Decomposition of 1NF into 2NF ==
⚫
The following relation ''does not'' satisfy 2NF because multiple {Manufacturer country} is dependent on part of a candidate key:
To make a 1NF relation a 2NF relation, remove the functionally dependent attributes in the partial dependencies of the first normal form relation, and place those partial dependency dependent attributes in a relation where their corresponding determinant attributes are an entire candidate key.

=== Example ===
⚫
The following relation does not satisfy 2NF because:
* There is a multi-attribute unique identifier/candidate key: "Manufacturer" and "Model".
* {Manufacturer country} is functionally dependent (predictable) on {Manufacturer}.
* {Manufacturer} is a proper subset of the {Manufacturer, Model} candidate key.
* {Manufacturer country} is not part of a candidate key, so it is a non-prime attribute. (It is assumed that it is possible for two manufacturers in the same country to make a toothbrush with the same model name, so {Manufacturer country, Model} is not a candidate key even though in the current table the pair uniquely identify rows.)


In other words, since {Manufacturer country} is a non-prime attribute functionally dependent on a proper subset of a candidate key, the relation is in violation of 2NF.


{| class="wikitable"
{| class="wikitable"
|+ Electric toothbrush models
|+ Electric toothbrush models
! <u>Manufacturer</u> !! <u>Model</u> !! Model full name !! Manufacturer country
! Manufacturer !! Model !! Manufacturer country
|-
|-
|Forte||X-Prime||Forte X-Prime||Italy
|Forte||X-Prime||Italy
|-
|-
|Forte||Ultraclean||Forte Ultraclean||Italy
|Forte||Ultraclean||Italy
|-
|-
|Dent-o-Fresh||EZbrush||Dent-o-Fresh EZbrush||USA
|Dent-o-Fresh||EZbrush||USA
|-
|-
|Brushmaster||SuperBrush||Brushmaster SuperBrush||USA
|Brushmaster||SuperBrush||USA
|-
|-
|Kobayashi||ST-60||Kobayashi ST-60||Japan
|Kobayashi||ST-60||Japan
|-
|-
|Hoch||Toothmaster||Hoch Toothmaster||Germany
|Hoch||Toothmaster||Germany
|-
|-
|Hoch|| X-Prime||Hoch X-Prime||Germany
|Hoch|| X-Prime||Germany
|}
|}


Even if the designer has specified the primary key as {Model full name}, the relation is not in 2NF because of the other candidate keys. {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 relations:
To make the design conform to 2NF, it is necessary to have two relations. To create these relations:
# Remove the functionally dependent attributes in the partial dependencies of the first normal form relation. In this example, {Manufacturer country} is the functionally dependent attribute which will be removed.
# Place those partial dependency-dependent attributes (i.e. {Manufacturer country}) in a relation where their corresponding determinant attributes are a candidate key (i.e. {Manufacturer}).
As seen below, {Manufacturer country} is removed from the original table:


{| class="wikitable"
{| class="wikitable"
|+ Electric toothbrush manufacturers
|+ Electric toothbrush models
! <u>Manufacturer</u> !! Manufacturer country
! Manufacturer !! Model
|-
|-
|Forte||Italy
|Forte||X-Prime
|-
|-
⚫
|Forte||Ultraclean
⚫
|Dent-o-Fresh||USA
|-
|-
|Dent-o-Fresh||EZbrush
⚫
|Brushmaster||USA
|-
|-
|Brushmaster||SuperBrush
⚫
|Kobayashi||Japan
|-
|-
|Kobayashi||ST-60
⚫
|Hoch||Germany
⚫
|-
⚫
|Hoch||Toothmaster
⚫
|-
⚫
|Hoch|| X-Prime
|}
|}

As seen below, the partial dependency is put into a new relation where the dependency can exist without being a partial dependency:


{| class="wikitable"
{| class="wikitable"
|+ Electric toothbrush models
|+ Electric toothbrush manufacturers
! <u>Manufacturer</u> !! <u>Model</u> !! Model full name
! Manufacturer !! Manufacturer country
|-
|-
|Forte||X-Prime||Forte X-Prime
|Forte||Italy
|-
|-
⚫
|Dent-o-Fresh||USA
⚫
|Forte||Ultraclean||Forte Ultraclean
|-
|-
⚫
|Brushmaster||USA
⚫
|Dent-o-Fresh||EZbrush||Dent-o-Fresh EZbrush
|-
|-
⚫
|Kobayashi||Japan
⚫
|Brushmaster||SuperBrush||Brushmaster SuperBrush
|-
|-
⚫
|Hoch||Germany
⚫
|Kobayashi||ST-60||Kobayashi ST-60
⚫
|-
⚫
|Hoch||Toothmaster||Hoch Toothmaster
⚫
|-
⚫
|Hoch|| X-Prime||Hoch X-Prime
|}
|}

It may now be desirable to add a numeric identifier to each row in the "Electric toothbrush models" table to ensure it remains in 1NF, or, the table may be left as is, allowing each unique combination of values in each row to be usable as a primary key, thus allowing the columns {Manufacturer, Model} in this table to be used as the candidate key of the table.


==See also==
==See also==
{{Hatnote|For other normal forms, see the navigation bar at the bottom of the page.}}
*[[Attribute-value system]]
*[[Attribute-value system]]


Line 77: Line 97:
{{Refbegin}}
{{Refbegin}}
* [http://www.troubleshooters.com/littstip/ltnorm.html Litt's Tips: Normalization]
* [http://www.troubleshooters.com/littstip/ltnorm.html Litt's Tips: Normalization]
* Date, C. J., Lorentzos, N., Darwen, H. (2002). ''[http://www.elsevier.com/wps/product/cws_home/680662 Temporal Data & the Relational Model]''{{dead link|date=May 2018 |bot=InternetArchiveBot |fix-attempted=yes }} (1st ed.). Morgan Kaufmann. {{ISBN|1-55860-855-9}}.
*{{cite book |last1=Date |first1=C. J. |authorlink1=Christopher J. Date |last2=Lorentzos |first2=N. |last3=Darwen |first3=H. |year=2002 |title=Temporal Data & the Relational Model |edition=1st |publisher=Morgan Kaufmann |isbn=1-55860-855-9 |url=http://www.elsevier.com/wps/product/cws_home/680662 |url-status=dead |access-date=2006-08-16 |archive-date=2012-12-09 |archive-url=https://archive.today/20121209052842/http://www.elsevier.com/wps/product/cws_home/680662 }}
* {{cite book | author = C. J. Date | authorlink = Christopher J. Date | title = Introduction to Database Systems | edition = 8th | year = 2004 | publisher = Addison-Wesley | location = Boston | isbn = 978-0-321-19784-9 | url-access = registration | url = https://archive.org/details/introductiontoda0000date }}
*{{cite book |last=Date |first=C. J. |authorlink=Christopher J. Date |year=2004 |title=Introduction to Database Systems |edition=8th |publisher=Addison-Wesley |location=Boston |isbn=978-0-321-19784-9 |url-access=registration |url=https://archive.org/details/introductiontoda0000date }}
* Kent, W. (1983) ''[http://www.bkent.net/Doc/simple5.htm A Simple Guide to Five Normal Forms in Relational Database Theory]'', Communications of the ACM, vol. 26, pp.&nbsp;120–125.
*{{cite journal |last=Kent |first=W. |year=1983 |title=A Simple Guide to Five Normal Forms in Relational Database Theory |journal=Communications of the ACM |volume=26 |issue=2 |pages=120–125 |doi=10.1145/358024.358054 |url=http://www.bkent.net/Doc/simple5.htm |doi-access=free }}
{{Refend}}
{{Refend}}


Latest revision as of 14:33, 30 October 2024

Second normal form (2NF), in database normalization, is a normal form. A relation is in the second normal form if it fulfills the following two requirements:

  1. It is in first normal form.
  2. It does not have any non-prime attribute that is functionally dependent on any proper subset of any candidate key of the relation (i.e. it lacks partial dependencies). A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation.

Put simply, a relation (or table) is in 2NF if:

  1. It is in 1NF and has a single attribute unique identifier (UID) (in which case every non key attribute is dependent on the entire UID), or
  2. It is in 1NF and has a multi-attribute unique identifier, and every regular attribute (not part of the UID) is dependent on all attributes in the multi-attribute UID, not just one attribute (or part) of the UID.

If any regular (non-prime) attributes are predictable (dependent) on another (non-prime) attribute, that is addressed in third normal form.

History

[edit]

The second normal form was originally defined by E. F. Codd in 1971.[1]

Decomposition of 1NF into 2NF

[edit]

To make a 1NF relation a 2NF relation, remove the functionally dependent attributes in the partial dependencies of the first normal form relation, and place those partial dependency dependent attributes in a relation where their corresponding determinant attributes are an entire candidate key.

Example

[edit]

The following relation does not satisfy 2NF because:

  • There is a multi-attribute unique identifier/candidate key: "Manufacturer" and "Model".
  • {Manufacturer country} is functionally dependent (predictable) on {Manufacturer}.
  • {Manufacturer} is a proper subset of the {Manufacturer, Model} candidate key.
  • {Manufacturer country} is not part of a candidate key, so it is a non-prime attribute. (It is assumed that it is possible for two manufacturers in the same country to make a toothbrush with the same model name, so {Manufacturer country, Model} is not a candidate key even though in the current table the pair uniquely identify rows.)


In other words, since {Manufacturer country} is a non-prime attribute functionally dependent on a proper subset of a candidate key, the relation is in violation of 2NF.

Electric toothbrush models
Manufacturer Model Manufacturer country
Forte X-Prime Italy
Forte Ultraclean Italy
Dent-o-Fresh EZbrush USA
Brushmaster SuperBrush USA
Kobayashi ST-60 Japan
Hoch Toothmaster Germany
Hoch X-Prime Germany

To make the design conform to 2NF, it is necessary to have two relations. To create these relations:

  1. Remove the functionally dependent attributes in the partial dependencies of the first normal form relation. In this example, {Manufacturer country} is the functionally dependent attribute which will be removed.
  2. Place those partial dependency-dependent attributes (i.e. {Manufacturer country}) in a relation where their corresponding determinant attributes are a candidate key (i.e. {Manufacturer}).

As seen below, {Manufacturer country} is removed from the original table:

Electric toothbrush models
Manufacturer Model
Forte X-Prime
Forte Ultraclean
Dent-o-Fresh EZbrush
Brushmaster SuperBrush
Kobayashi ST-60
Hoch Toothmaster
Hoch X-Prime

As seen below, the partial dependency is put into a new relation where the dependency can exist without being a partial dependency:

Electric toothbrush manufacturers
Manufacturer Manufacturer country
Forte Italy
Dent-o-Fresh USA
Brushmaster USA
Kobayashi Japan
Hoch Germany

It may now be desirable to add a numeric identifier to each row in the "Electric toothbrush models" table to ensure it remains in 1NF, or, the table may be left as is, allowing each unique combination of values in each row to be usable as a primary key, thus allowing the columns {Manufacturer, Model} in this table to be used as the candidate key of the table.

See also

[edit]

References

[edit]
  1. ^ Codd, E. F. "Further Normalization of the Data Base Relational Model". (Presented at Courant Computer Science Symposia Series 6, "Data Base Systems", New York City, May 24–25, 1971.) IBM Research Report RJ909 (August 31, 1971). Republished in Randall J. Rustin (ed.), Data Base Systems: Courant Computer Science Symposia Series 6. Prentice-Hall, 1972.

Further reading

[edit]
  • Litt's Tips: Normalization
  • Date, C. J.; Lorentzos, N.; Darwen, H. (2002). Temporal Data & the Relational Model (1st ed.). Morgan Kaufmann. ISBN 1-55860-855-9. Archived from the original on 2012-12-09. Retrieved 2006-08-16.
  • Date, C. J. (2004). Introduction to Database Systems (8th ed.). Boston: Addison-Wesley. ISBN 978-0-321-19784-9.
  • Kent, W. (1983). "A Simple Guide to Five Normal Forms in Relational Database Theory". Communications of the ACM. 26 (2): 120–125. doi:10.1145/358024.358054.
[edit]
Retrieved from "https://en.wikipedia.org/enwiki/w/index.php?title=Second_normal_form&oldid=1254337278"