First Normal Form (1NF)

First normal form is now considered to be part of the formal definition of a relation; historically, it was defined to disallow multi-valued attributes, composite attributes, and their combinations. It states that the domains of attributes must include only atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute.

Practical Rule

“Eliminate Repeating Groups” i.e., make a separate table for each set of related attributes, and give each table a primary key.

What is Normalization? To Know go to my post on Normalization.

Definition of First Normal Form

A relation is in first normal form (1NF) if and only if all underlying simple domains contain atomic values only.

The following relation violates 1NF because the SupplierID forms a repeating group (here and in the following examples and text, primary key fields are in bold).

Repeating groups indicate a one-to-many relationship – in other words, a relationship which in relational databases is treated using foreign keys. Note that the problem of repeating groups cannot be solved by adding any number of fields to a record; even if the number of elements of the vector-valued data was fixed, finite, and predetermined, searching for a value in all these parallel fields in prohibitively cumbersome.

To achieve 1NF, eliminate repeating groups by creating separate tables for each set of related data.

To demonstrate the typical anomalies that occur in tables those are only 1NF, consider the following example:

Note the following problems:

Problem in First Normal Form

Insert: It is not possible to add a record for a customer who has never placed an order.

Update: To change the address for a customer, this change has to be repeated for all of the customer’s existing orders.

Delete: Deleting the last order for a customer loses all information about the customer.

Why first normal form is acceptable for data processing application?

The first normal form (1NF) involves the removal of repeating groups. Examples of such repeating groups are contacts and categories. Thus for a given customer, one or more contacts and one or more categories can exist.

For each repeating group, the repeating group is moved to a separate table. Suppose our primary table is the customer table given below –

Customer (CustomerID, CustomerName, CustomerType)

Contact (ContactID, CustomerID, ContactName)

Category (CategoryID, CategoryName, CustomerID)

The customer table is a parent to the contact and category tables.

The two relations are one to many. This means each customer can have one or more contacts and can be associated with one or more categories.

If we do not create the child tables, the alternative is to add more columns to support multiple contacts and categories. Clearly, creating child tables is the easier, more flexible, and more cost effective solution.

Why the first normal form is acceptable for data processing applications is now obvious. If we do not choose the 1NF, our database design would continuously be changing whenever a specific entity in the database, say customer, has exceeded the number of contacts the main table (Customer table, in our example) can support. A continuously changing database structure and model, can eventually proves harmful for a database application. A solid and stable database model is the foundation on which everything else depends.

After an application has been developed, changing the database structure can be very expensive and time consuming. The entire application has to be tested thoroughly to guarantee no bugs have been introduced due to the modifications. Use of first normal form at the very beginning avoids all the above troubles. Hence 1NF is always acceptable for data processing applications. Also, before the second normal form is taken up it is essential to complete the 1NF of the database.;