Editing First normal form (section)

==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. 

{{Table alignment}}
{| class="wikitable col1right"
|+ Customer
! <u>CustomerID</u> !! Name !! Transactions
|-
| 1 || Abraham
||
{{Table alignment}}
{| class="wikitable col1right col3right"
! <u>TransactionID</u> !! Date !! Amount
|-
| 12890 || 2003-10-14 || &minus;87
|-
| 12904 || 2003-10-15 || &minus;50
|}
|-
| 2 || Isaac
||
{{Table alignment}}
{| class="wikitable col1right col3right"
! <u>TransactionID</u> !! Date !! Amount
|-
| 12898 || 2003-10-14 || &minus;21
|}
|-
| 3 || Jacob
||
{{Table alignment}}
{| class="wikitable col1right col3right"
! <u>TransactionID</u> !! Date !! Amount
|-
| 12907 || 2003-10-15 || &minus;18
|-
| 14920 || 2003-11-20 || &minus;70
|-
| 15003 || 2003-11-27 || &minus;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).{{r|Codd 1970}}{{rp|pages=380&ndash;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:

{{Col-float}}
{{Col-float-break|style=margin-right: 20px;}}
{{Table alignment}}
{| class="wikitable col1right"
|+ Customer
! <u>CustomerID</u> !! Name 
|-
| 1 || Abraham
|-
| 2 || Isaac 
|-
| 3 || Jacob 
|}
{{Col-float-break}}
{{Table alignment}}
{| class="wikitable col1right col2right col4right"
|+ Transaction
! <u>CustomerID</u> !! <u>TransactionID</u> !! Date !! Amount
|-
| 1 || 12890 || 2003-10-14 || &minus;87
|-
| 1 || 12904 || 2003-10-15 || &minus;50
|-
| 2 || 12898 || 2003-10-14 || &minus;21
|-
| 3 || 12907 || 2003-10-15 || &minus;18
|-
| 3 || 14920 || 2003-11-20 || &minus;70
|-
| 3 || 15003 || 2003-11-27 || &minus;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 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 normal form|second]] and [[third normal form]].