First normal form

First normal form (1NF) is the simplest form of database normalization defined by English computer scientist Edgar F. Codd, the inventor of the relational database. A relation (or a table, in SQL) can be said to be in first normal form if each field is atomic, containing a single value rather than a set of values or a nested table. In other words, a relation complies with first normal form if no attribute domain (the set of values allowed in a given column) has relations as elements.^[1]

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 keys.^[2]^: 381 This process is a necessary step when moving data from a non-relational (or NoSQL) database, such as one using a hierarchical or document-oriented model, to a relational database.

A database must satisfy 1NF to satisfy further "normal forms", such as 2NF and 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 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.^[3]^: 439

Background

First normal form was introduced in 1970 by Edgar F. Codd in his paper "A relational model of data for large shared data banks",^[2] although initially it was simply referred to as "normalization" or "normal form". It was renamed to "first normal form" when Codd introduced additional normal forms in his paper "Further Normalization of the Data Base Relational Model" in 1971.^[4]

Codd distinguishes between "atomic" and "compound" data. Atomic (or "nondecomposable") data includes basic types such as numbers and 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 relations (or tables, in SQL) which contain several pieces of atomic data and thus "can be decomposed by the DBMS".^[5]^: 6

In a relation, each attribute (or column) has a set of allowed values known as its 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) 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.^[2]^: 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 relations (that is, tables containing further tables), which can be found in some non-relational databases.

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."^[5]^: 6 Normalization to 1NF is thus a process of eliminating nonsimple domains from all relations.

Examples

Design that violates 1NF

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.

Customer

CustomerID

Name

Transactions

1

Abraham

TransactionID	Date	Amount
12890	2003-10-14	−87
12904	2003-10-15	−50

2

Isaac

TransactionID	Date	Amount
12898	2003-10-14	−21

3

Jacob

TransactionID	Date	Amount
12907	2003-10-15	−18
14920	2003-11-20	−70
15003	2003-11-27	−60

The evaluation of any query relating to customers' transactions would broadly involve two stages:

unpacking one or more customers' groups of transactions, allowing the individual transactions in a group to be examined, and
deriving a query result from the results of the first stage.

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.

Design that complies with 1NF

Codd described how a database like this could be made less structurally complex and more flexible by transforming it into a relational database in first normal form. To normalize the table so it complies with first normal form, attributes with nonsimple domains must be extracted to separate, stand-alone relations. Each extracted relation gains a foreign key referencing the primary key of the relation which initially contained it. This process can be applied recursively to nonsimple domains nested in multiple levels (i.e., domains containing tables within tables within tables, and so on).^[2]^{: 380–381}

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:

Customer
CustomerID	Name
1	Abraham
2	Isaac
3	Jacob

Transaction
CustomerID	TransactionID	Date	Amount
1	12890	2003-10-14	−87
1	12904	2003-10-15	−50
2	12898	2003-10-14	−21
3	12907	2003-10-15	−18
3	14920	2003-11-20	−70
3	15003	2003-11-27	−60

In this modified design, the primary key is {CustomerID} in the first relation and {CustomerID, TransactionID} in the second relation.

Now that a single, "top-level" relation contains all transactions, it will be simpler to run queries on the database. To find the monetary sum of all October transactions, the DMBS would simply find all rows with a Date falling in October and sum the Amount fields. All values are now easily exposed to the DBMS, whereas previously some values were embedded in lower-level structures that had to be handled specially. Accordingly, the normalized design lends itself well to general-purpose query processing, whereas the unnormalized design does not.

It is worth noting that the revised design also meets the additional requirements for second and third normal form.

Rationale and drawbacks

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:

It enables data to be stored in regular two-dimensional arrays; supporting nested relations would require more complex data structures.^[2]^: 381
It allows for the use of a simpler query language, like SQL, since any data item can be identified using only a relation name, attribute name and key; addressing nested data items would require a more complex language with support for hierarchical data paths.
Representing relationships using foreign keys is more flexible and allows for features such as many-to-many relationships, while a hierarchical model can represent only one-to-one or 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.^{[clarification needed]}
From 1NF, further normalization becomes possible (for example to 2NF or 3NF), which can reduce data redundancy and anomalies.

The use of 1NF also comes with certain drawbacks:

Performance worsens for certain operations. In a hierarchical model, nested records are physically stored after the parent record, which means a whole subtree can be retrieved in a single read operation. In 1NF, this will require a join operation per record type, which can be costly, especially for complex trees. For this reason, document-oriented databases eschew 1NF.
Object-oriented languages represent runtime state as trees or directed graphs of objects connected by pointers or references. This does not map cleanly to a 1NF relational database, creating a gap sometimes called the object–relational impedance mismatch, which object–relational mapping (ORM) libraries try to bridge.
1NF has been interpreted as not allowing complex data types for values. This is open to interpretation though, and Christopher J. Date has argued that values can be arbitrarily complex objects.^{[citation needed]}

Controversy about compound values

There is some discussion about to what extent compound or complex values other than relations (such as arrays or XML data) are permitted in 1NF.^{[citation needed]} 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 SUBSTRING to decompose values otherwise considered atomic.^[5]^: 6,340

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.^[6]^[7] 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 would seem not to be atomic, as an RDBMS typically provides operators to decompose it into substrings.
A 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":^[8]^: 112^[9]^{[pages needed]} a value may be considered atomic for some purposes, but may be considered an assemblage of more basic elements for other purposes. If this position is accepted, 1NF cannot be defined with reference to atomicity. Columns containing any conceivable data type (from strings and numeric types to arrays and tables) are then acceptable in a 1NF table,^{[citation needed]} although perhaps not always desirable – for example, it may be desirable to separate a CustomerName column into two columns, FirstName and Surname.

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 "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:^[8]^{: 127–128}

There is no specific top-to-bottom ordering of the rows.
There is no specific left-to-right ordering of the columns.
There are no duplicate rows.
Every field (or intersection of a row and a column) contains exactly one value from the applicable domain and nothing else.
All columns are regular (i.e., rows have no hidden components such as row IDs, object IDs, or hidden timestamps).

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.^[8]^{: 121–126} Examples of tables (or views) that would not meet this definition of first normal form are:

A table that lacks a unique key 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 tuples 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 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,^[10] which made explicit provision for nulls.^[11]

References

^ Codd, E. F. (1972). "Further Normalization of the Data Base Relational Model". p. 27
^ ^a ^b ^c ^d ^e Codd, E. F. (1970). "A relational model of data for large shared data banks". Communications of the ACM. 13 (6): 377–387. doi:10.1145/362384.362685.
^ Codd, E. F. (1979). "Extending the database relational model to capture more meaning". ACM Transactions on Database Systems. 4 (4): 397–434. doi:10.1145/320107.320109.
^ Codd, E. F. (1971). "Further Normalization of the Data Base Relational Model". Data Base Systems. Courant Computer Science Symposium 6 edited by Rustin, R.
^ ^a ^b ^c Codd, E. F. (1 January 1990). The relational model for database management: version 2. Addison-Wesley. ISBN 978-0-201-14192-4.
^ 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).
^ Date, C. J. (2007). "Chapter 8: What First Normal Form Really Means". Date on Database: Writings 2000–2006. Apress. p. 108. ISBN 978-1-4842-2029-0. '[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.'
^ ^a ^b ^c Date, C. J. (2007). "Chapter 8: What First Normal Form Really Means". Date on Database: Writings 2000–2006. Apress. ISBN 978-1-4842-2029-0.
^ Date, C. J. (6 November 2015). SQL and Relational Theory: How to Write Accurate SQL Code. O'Reilly Media. pp. 50–. ISBN 978-1-4919-4115-7. Retrieved 31 October 2018.
^ Date, C. J. (2009). "Appendix A.2". SQL and Relational Theory. O'Reilly. Codd first defined the relational model in 1969 and didn't introduce nulls until 1979
^ Date, C. J. (14 October 1985). "Is Your DBMS Really Relational?". Computerworld. Null values ... [must be] supported in a fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type. (the third of Codd's 12 rules)