Content deleted Content added
Added a bit more context to the section about the atomic values controversy |
m Altered phrasing to "level of database normalization" |
||
| (8 intermediate revisions by 3 users not shown) | |||
Line 1:
{{short description|
{{
'''First normal form''' ('''1NF''') is the
Most relational database management systems, including standard SQL, do not support creating or using table-valued columns, which means most relational databases will be in first normal form by necessity. Otherwise, normalization to 1NF involves eliminating nested relations by breaking them up into separate relations associated with each other using [[foreign key]]s.<ref name="Codd 1970
A database must satisfy 1NF to satisfy further "[[Database_normalization#Normal_forms|normal forms]]", such as [[Second normal form|2NF]] and [[Third normal form|3NF]], which enable the reduction of redundancy and anomalies. Other benefits of adopting 1NF include the introduction of increased [[data independence]] and flexibility (including features like [[Many-to-many (data model)|many-to-many]] relationships) and simplification of the [[relational algebra]] and [[query language]] necessary to describe operations on the database.
Codd considered 1NF mandatory for relational databases, while the other normal forms were merely guidelines for database design.<ref>
==
First normal form was introduced in 1970 by [[Edgar F. Codd]] in
The relational model was proposed as an improvement over [[hierarchical]] databases which were prevalent at the time.{{r|Codd 1970|p=377}} A key difference lies in how relationships between records are represented. In a hierarchical database, one-to-many relationships are represented through containment: a single record may contain sets of records (known as repeating groups) as attribute values. But Codd argued that hierarchy is not flexible and expressive enough for more complex data models. For example many-to-many relationships cannot be represented through hierarchy.{{r|Codd 1970|p=378}} Thus he suggest eliminating nested records and instead represent relationship through [[foreign key|foreign keys]]. This allows richer relationships to be expressed, since a record can now participate in multiple relationships.{{r|Codd 1970|p=378}}
A direct translation of a hierarchical database into relations would represent repeating groups as nested relations. Thus normalization is defined as eliminating nested relations and instead represent the one-to-many relationship through foreign keys. {{r|Codd 1970|p=381}}
Codd distinguishes between "atomic" and "compound" data. Atomic (or "nondecomposable") data includes basic types such as numbers and [[String (computer science)|strings]] – broadly speaking, it "''cannot'' be decomposed into smaller pieces by the [[DBMS]] (excluding certain special functions)". Compound data is made up of structures such as [[Relation (database)|relations]] (or ''[[Table (database)|tables]]'', in [[SQL]]) which contain several pieces of atomic data and thus "''can'' be decomposed by the DBMS".<ref name="Codd 1990">{{Cite book |last=Codd |first=E. F. |title=The relational model for database management: version 2 |publisher=[[Addison-Wesley]] |isbn=978-0-201-14192-4 |publication-date=1 January 1990}}</ref>{{rp|page=6}}
In a relation, each attribute (or [[Column (database)|''column'']]) has a set of allowed values known as its [[Attribute ___domain|___domain]] (e.g., a "Price" attribute's ___domain may be the set of non-negative numbers with up to 2 fractional digits). Each tuple (or [[Row (database)|''row'']]) in the relation contains one value per attribute, and each must be an element in that attribute's ___domain. Codd distinguishes attributes which have "simple domains" containing only atomic data from attributes with "nonsimple domains" containing at least some forms of compound data.{{r|Codd 1970}}{{rp|pages=380}} Nonsimple domains introduce a degree of structural complexity which can be difficult to navigate, to query and to update – for instance, it will be time-consuming to operate across several [[Nested table|nested relations]] (that is, tables containing further tables), which can be found in some [[non-relational database]]s.
First normal form therefore requires all attribute domains to be ''simple'' domains, such that the data in each field is atomic and no relation has relation-valued attributes. Precisely, Codd states that, in the relational model, "values in the domains on which each relation is defined are required to be atomic with respect to the DBMS."<ref name="Codd 1990" />{{rp|page=6}} Normalization to 1NF is thus a process of eliminating nonsimple domains from all relations.
==Examples==
===
This table of customers' credit card transactions does not conform to first normal form, as each customer corresponds to a repeating group of transactions. Such a design can be represented in a [[hierarchical database]], but not in an SQL database, since SQL does not support nested tables.
{{Table alignment}}
{| class="wikitable col1right"
! Customer !! CustomerID !! Transactions▼
|+ Customer
|-
|
||
{{Table alignment}}
{| class="wikitable"▼
{| class="wikitable col1right col3right"
! <u>TransactionID</u> !! Date !! Amount
|-
| 12890 || 2003-10-14 || −87
|-
| 12904 || 2003-10-15 || −50
|}
|-
|
||
{{Table alignment}}
▲{| class="wikitable"
{| class="wikitable col1right col3right"
! <u>TransactionID</u> !! Date !! Amount
|-
| 12898 || 2003-10-14 || −21
|}
|-
|
||
{{Table alignment}}
▲{| class="wikitable"
{| class="wikitable col1right col3right"
! <u>TransactionID</u> !! Date !! Amount
|-
| 12907 || 2003-10-15 || −18
|-
| 14920 || 2003-11-20 || −70
|-
| 15003 || 2003-11-27 || −60
|}
|}
Line 76 ⟶ 72:
For example, in order to find out the monetary sum of all transactions that occurred in October 2003 for all customers, the [[database management system]] (DMBS) would have to first unpack the Transactions field of each customer, then sum the Amount of each transaction thus obtained where the Date of the transaction falls in October 2003.
===
Codd described how a database like this could be made less structurally complex and more flexible by transforming it into a
In this example, CustomerID is the primary key of the containing relation and will therefore be appended as a foreign key to the new relation:
{{Col-float}}
▲{| class="wikitable"
{{Col-float-break|style=margin-right: 20px;}}
{{Table alignment}}
▲{| class="wikitable col1right"
|+ Customer
! <u>CustomerID</u> !! Name
|-
| 1 || Abraham
|-
|
|-
|
|}
{{Col-float-break}}
{{Table alignment}}
{| class="wikitable col1right col2right col4right"
|+ Transaction
! <u>CustomerID</u> !! <u>TransactionID</u> !! Date !! Amount
|-
| 1 || 12890 || 2003-10-14 || −87
Line 108:
| 3 || 15003 || 2003-11-27 || −60
|}
{{Col-float-end}}
In this modified design, the primary key is {CustomerID} in the first relation and {CustomerID, TransactionID} in the second relation.
Now that
It is worth noting that the revised design also meets the additional requirements for [[second normal form|second]] and [[third normal form]].
== Rationale
{{Confusing section|date=May 2025}}▼
Normalization to 1NF is the major theoretical component of transferring a database to the [[relational model]]. Use of a relational database in 1NF brings certain advantages:
*
*
* Representing relationships using foreign keys is more flexible and allows for features such as [[Many-to-many (data model)|many-to-many]] relationships, while a hierarchical model can represent only [[One-to-one (data model)|one-to-one]] or [[One-to-many (data model)|one-to-many]] relationships.
* Since locating data items is not coupled to a parent–child hierarchy, a database in 1NF creates greater [[data independence]] and is more resilient to structural changes over time.{{Clarify|date=May 2025}}
* From 1NF, further normalization becomes possible (for example to [[Second normal form|2NF]] or [[Third normal form|3NF]]), which can reduce data redundancy and anomalies.
There is some discussion about to what extent compound or complex values other than relations (such as [[Array (data structure)|arrays]] or [[XML]] data) are permitted in 1NF.{{Citation needed|date=May 2025}} Codd states that relations are the only type of compound data allowed within the relational model (if not in attribute domains), since any additional type of compound data would add complexity without adding power; nevertheless, the model specifically allows "certain special functions" like <code>SUBSTRING</code> to decompose values otherwise considered atomic.{{r|Codd 1990}}{{rp|page=6,340}}
▲== Controversy about atomic values ==
[[Hugh Darwen]] and [[Christopher J. Date]] have suggested that Codd's concept of an "atomic value" is ambiguous, and that this ambiguity has led to widespread confusion about how 1NF should be understood.<ref>Darwen, Hugh. "Relation-Valued Attributes; or, Will the Real First Normal Form Please Stand Up?", in C. J. Date and Hugh Darwen, ''Relational Database Writings 1989-1991'' (Addison-Wesley, 1992).</ref><ref>{{cite book |last=Date |first=C. J. |author-link=Christopher J. Date |chapter=Chapter 8: What First Normal Form Really Means |date=2007 |title=Date on Database: Writings 2000–2006 |publisher=Apress |isbn=978-1-4842-2029-0 |page=108 |quote='[F]or many years,' writes Date, 'I was as confused as anyone else. What's worse, I did my best (worst?) to spread that confusion through my writings, seminars, and other presentations.'}}</ref> In particular, the notion of an atomic value as a "value that cannot be decomposed" is problematic, as it would seem to imply that few, if any, data types are atomic:
*A [[String (computer science)|
*A [[Fixed-point arithmetic|fixed-point]] number would seem not to be atomic, as an RDBMS typically provides operators to decompose it into integer and fractional components.
* An [[ISBN]] would seem not to be atomic, as it includes various parts, including the ''registration group'', ''registrant'' and ''publication'' elements.
Date suggests that "the notion of atomicity ''has no absolute meaning''":<ref name="Date 2007">{{cite book |last=Date| first=C. J. |author-link=Christopher J. Date |chapter=Chapter 8: What First Normal Form Really Means |date=2007 |title=Date on Database: Writings 2000–2006 |publisher=Apress |isbn=978-1-4842-2029-0
==Cristopher J. Date's definition of 1NF==
According to [[Christopher J. Date]]'s definition, a table is in first normal form if and only if it is "[[isomorphism|isomorphic]] to some relation", which means, specifically, that it satisfies the following five conditions:
# There is no specific top-to-bottom ordering of the rows.
Line 153 ⟶ 147:
Violation of any of these conditions would mean that the table is not strictly relational, and therefore that it is not in first normal form.
This definition of 1NF permits relation-valued attributes (tables within tables), which Date argues are useful in rare cases.
*A table that lacks a [[unique key]] [[constraint (database)|constraint]]. Such a table would be able to accommodate duplicate rows, in violation of condition 3.
*A view whose definition mandates that results be returned in a particular order, so that the row-ordering is an intrinsic and meaningful aspect of the view, in violation of condition 1. The [[tuple]]s in true relations are not ordered with respect to each other (such views cannot be created using [[SQL]] that conforms to the [[SQL:2003]] standard).
*A table with at least one [[Null (SQL)|nullable]] attribute. A nullable attribute would be in violation of condition 4, which requires every column to contain exactly one value from its column's ___domain. This aspect of condition 4 is controversial; it marks an important departure from Codd's later vision of the [[relational model]],<ref>{{cite book |last=Date |first=C. J. |author-link=Christopher J. Date |year=2009 |title=SQL and Relational Theory |publisher=O'Reilly |chapter=Appendix A.2 |quote=Codd first defined the relational model in 1969 and didn't introduce nulls until 1979}}</ref> which made explicit provision for nulls.<ref>{{cite magazine |last=Date |first=C. J. |author-link=Christopher J. Date |date=
==See also==
Line 163 ⟶ 157:
*[[Second normal form]] (2NF)
*[[Third normal form]] (3NF)
*[[Boyce–Codd normal form]] (BCNF or 3.5NF)
*[[Fourth normal form]] (4NF)
*[[Fifth normal form]] (5NF)
Line 172 ⟶ 167:
==Further reading==
{{Refbegin}}
* Date, C. J., & Lorentzos, N., & Darwen, H. (2002). ''[https://archive.today/20121209052842/http://www.elsevier.com/wps/product/cws_home/680662 Temporal Data & the Relational Model]'' (1st ed.). Morgan Kaufmann. {{ISBN|1-55860-855-9}}.
* Date, C. J. (1999), ''[https://web.archive.org/web/20050404010227/http://www.aw-bc.com/catalog/academic/product/0,1144,0321197844,00.html An Introduction to Database Systems]'' (8th ed.). Addison-Wesley Longman. {{ISBN|0-321-19784-4}}.
* 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,
{{Refend}}
| |||