First normal form is a property of a relation in a relational database.
A relation is in first normal form if the domain of each attribute contains only atomic
values, and the value of each attribute contains only a single value from that domain.
Edgar Codd, in a 1971 conference paper, defined a relation in first normal form to be one
such that none of the domains of that relation should have elements which are themselves
sets.
First normal form is an essential property of a relation in a relational database.
Database normalization is the process of representing a database in terms of relations in standard
normal forms, where first normal is a minimal requirement.
Examples The following scenario illustrates how a database
design might violate first normal form.
Domains and values Suppose a designer wishes to record the names
and telephone numbers of customers.
He defines a customer table which looks like this:
The designer then becomes aware of a requirement to record multiple telephone numbers for some
customers.
He reasons that the simplest way of doing this is to allow the "Telephone Number" field
in any given record to contain more than one value:
Assuming, however, that the Telephone Number column is defined on some telephone number-like
domain, such as the domain of 12-character strings, the representation above is not in
first normal form.
A design that complies with 1NF In the first normal form, the previous table
can be represented in the following way.
However, this database design does not meet the more stringent requirements of second
normal form.
A design that also complies with higher normal forms
Another design for the same data makes use of two tables: a Customer Name table and a
Customer Telephone Number table.
This design is in first normal form.
Indeed, repeating groups of telephone numbers do not occur in this design.
Instead, each Customer-to-Telephone Number link appears on its own record.
With Customer ID as key, a one-to-many relationship exists between the two tables.
A record in the "parent" table, Customer Name, can have many telephone number records in
the "child" table, Customer Telephone Number, but each telephone number belongs to one,
and only one customer.
It is worth noting that this design meets the additional requirements for second and
third normal form.
Atomicity 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."
Codd defines an atomic value as one that "cannot be decomposed into smaller pieces by the DBMS"
meaning a field 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 field.
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.
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:
A character string would seem not to be atomic, as the RDBMS typically provides operators
to decompose it into substrings.
A fixed-point number would seem not to be atomic, as the RDBMS typically provides operators
to decompose it into integer and fractional components.
An ISBN would seem not to be atomic, as it includes language and publisher identifiers.
Date suggests that "the notion of atomicity has no absolute meaning": 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 are then acceptable in a 1NF table—although perhaps
not always desirable; for example, it would be more desirable to separate a Customer Name
field into two separate fields as First Name, Surname.
First normal form, as defined by Chris Date, permits relation-valued attributes.
Date argues that relation-valued attributes, by means of which a field within a table can
contain a table, are useful in rare cases.
1NF tables as representations of relations 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:
There's no top-to-bottom ordering to the rows.
There's no left-to-right ordering to the columns.
There are no duplicate rows.
Every row-and-column intersection contains exactly one value from the applicable domain.
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.
Examples of tables that would not meet this definition of first normal form are:
A table that lacks a unique key.
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.
This violates condition 1.
The tuples in true relations are not ordered with respect to each other.
A table with at least one nullable attribute.
A nullable attribute would be in violation of condition 4, which requires every field
to contain exactly one value from its column's domain.
It should be noted, however, that this aspect of condition 4 is controversial.
It marks an important departure from Codd's later vision of the relational model, which
made explicit provision for nulls.
References
Further reading