First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if no attribute domain has relations as elements.[1] Or more informally, that no table column can have tables as values. Database normalization is the process of representing a database in terms of relations in standard normal forms, where first normal is a minimal requirement. SQL-92 does not support creating or using table-valued columns, which means that using only the "traditional relational database features" (excluding extensions even if they were later standardized) most relational databases will be in first normal form by necessity. Database systems which do not require first normal form are often called NoSQL systems. Newer SQL standards like have started to allow so called non-atomic types, which include composite types. Even newer versions like allow JSON.
In a hierarchical database, a record can contain sets of child records ― known as repeating groups or table-valued attributes. If such a data model is represented as relations, a repeating group would be an attribute where the value is itself a relation. First normal form eliminates nested relations by turning them into separate "top-level" relations associated with the parent row through foreign keys rather than through direct containment.
The purpose of this normalization is to increase flexibility and data independence, and to simplify the data language. It also opens the door to further normalization, which eliminates redundancy and anomalies.
Most relational database management systems do not support nested records, so tables are in first normal form by default. In particular, SQL does not have any facilities for creating or exploiting nested tables. Normalization to first normal form would therefore be a necessary step when moving data from a hierarchical database to a relational database.
The rationale for normalizing to 1NF:[2]
First normal form was introduced in 1970 by Edgar F. Codd in the paper A Relational Model of Data for Large Shared Data Banks, although it was initially just called "Normal Form". It was renamed to "First Normal Form" when additional normal forms were introduced in the paper Further Normalization of the Relational Model in 1971.[3]
The following scenarios first illustrate how a database design might violate first normal form, followed by examples that comply.
This table over customers' credit card transactions does not conform to first normal form:
| Customer | Customer ID | Transactions | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Abraham | 1 |
| ||||||||||||||
| Isaac | 2 | |||||||||||||||
| Jacob | 3 |
|
To each customer corresponds a 'repeating group' of transactions. Such a design can be represented in a hierarchical database but not a SQL database, since SQL does not support nested tables.
The automated evaluation of any query relating to customers' transactions would broadly involve two stages:
For example, in order to find out the monetary sum of all transactions that occurred in October 2003 for all customers, the system would have to know that it must first unpack the Transactions group of each customer, then sum the Amounts of all transactions thus obtained where the Date of the transaction falls in October 2003.
One of Codd's important insights was that structural complexity can be reduced. Reduced structural complexity gives users, applications, and DBMSs more power and flexibility to formulate and evaluate the queries. A more normalized equivalent of the structure above might look like this:
To bring the model into the first normal form, we can perform normalization. Normalization (to first normal form) is a process where attributes with non-simple domains are extracted to separate stand-alone relations. The extracted relations are amended with foreign keys referring to the primary key of the relation which contained it. The process can be applied recursively to non-simple domains nested in multiple levels.[4]
In this example, Customer ID is the primary key of the containing relations and will therefore be appended as foreign key to the new relation:
| Customer | Customer ID | |
|---|---|---|
| Abraham | 1 | |
| Isaac | 2 | |
| Jacob | 3 |
| Customer ID | Transaction ID | 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 the modified structure, the primary key is in the first relation, and in the second relation.
Now each row represents an individual credit card transaction, and the DBMS can obtain the answer of interest, simply by finding all rows with a Date falling in October, and summing their Amounts. The data structure places all of the values on an equal footing, exposing each to the DBMS directly, so each can potentially participate directly in queries; whereas in the previous situation some values were embedded in lower-level structures that had to be handled specially. Accordingly, the normalized design lends itself to general-purpose query processing, whereas the unnormalized design does not.
It is worth noting that this design meets the additional requirements for second and third normal form.
Edgar F. Codd's definition of 1NF makes reference to the concept of 'atomicity'. Codd states that the "values in the domains on which each relation is defined are required to be atomic with respect to the DBMS."[5] Codd defines an atomic value as one that "cannot be decomposed into smaller pieces by the DBMS (excluding certain special functions)"[6] meaning a column should not be divided into parts with more than one kind of data in it such that what one part means to the DBMS depends on another part of the same column.
Hugh Darwen and Chris 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.[7] [8] In particular, the notion of a "value that cannot be decomposed" is problematic, as it would seem to imply that few, if any, data types are atomic:
Date suggests that "the notion of atomicity has no absolute meaning":[9] [10] 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 of any conceivable data type (from string types and numeric types to array types and table types) are then acceptable in a 1NF table—although perhaps not always desirable; for example, it may be more desirable to separate a Customer Name column into two separate columns as First Name, Surname.
According to 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:[11]
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.
Examples of tables (or views) that would not meet this definition of first normal form are: