First normal form

An entity is in the first normal form if it contains no repeating groups. In relational terms, a table is in the first normal form if it contains no repeating columns. Repeating columns make your data less flexible, waste disk space, and make it more difficult to search for data. In the following telephone directory example, the name table contains repeating columns, child1, child2, and child3.
Figure 1: Name entity before normalization

The Name table contains these columns: rec_num, lname, fname, bdate, anniv, email, child1, child2, child3.

You can see some problems in the current table. The table always reserves space on the disk for three child records, whether the person has children or not. The maximum number of children that you can record is three, but some of your acquaintances might have four or more children. To look for a particular child, you must search all three columns in every row.

To eliminate the repeating columns and bring the table to the first normal form, separate the table into two tables. Put the repeating columns into one of the tables. The association between the two tables is established with a primary-key and foreign-key combination. Because a child cannot exist without an association in the name table, you can reference the name table with a foreign key, rec_num.
Figure 2: First normal form reached for name entity

The Name table contains these columns: rec_num, lname, fname, bdate, anniv, email. The child table contains these columns: rec_name, child_name.
Now check the telephone directory structure in Telephone directory diagram with primary and foreign keys added for groups that are not in the first normal form. The name-modem relationship is not in the first normal form because the columns b9600, b14400, and b28800 are considered repeating columns. Add a new attribute called b_type to the modem table to contain occurrences of b9600, b14400, and b28800. The following diagram shows the data model normalized through the first normal form.
Figure 3: The data model of a personal telephone directory

The diagram shows 7 entities. Each entity is a rectangle with the name of the entity above the rectangle. The rectangle of each entity contains the names of the attributes of that entity. When an entity name is followed by the letters PK it indicates that the attribute is a primary key or is part of a composite primary key. When an attribute name is followed by the letters FK it indicates that the attribute is a foreign key. The "name" entity contains these attribute names: rec_num PK, lname, fname, bdate, anniv, and email. It is connected to 5 other entities by relationship lines. These relationship lines are described later in this description. The "voice" entity contains these attribute names: vce_num PK, rec_num FK, and vce_type. It is connected to the "name" entity by a relationship line. The end closest to "name" has no special symbols. The end closest to "voice" has symbols for "optional" and for "many". The "faxname" entity contains these attribute names: fax_num PK FK, and rec_num PK FK. It is connected to the "name" entity by a relationship line. The end closest to "name" has no special symbols. The end closest to "faxname" has symbols for "optional" and "many". The "fax" entity contains these attribute names: fax_num PK, and oper_from, and oper_till. It is connected to the "faxname" entity by a relationship line. The end closest to "fax" has no special symbols. The end closest to "faxname" has the symbol for "many". The "modem" entity contains these attribute names: mdm_num PK, rec_num FK, b_type. It is connected to the "name" entity by a relationship line. The end closest to "name" has a symbol for "exactly one". The end closest to "modem" has symbols for "optional" and "many". The "address" entity contains these attribute names: id_num PK, rec_num FK, street, city, state, and zipcode. It is connected to the "name" entity by a relationship line. The end closest to "name" has the symbol for "many". The end closest to "address" has the symbols for "optional" and "exactly one". The "child" entity contains these attribute names: rec_num FK, and child_name. It is connected to the "name" entity by a relationship line. The end closest to "name" has no special symbols. The end closest to "child" has symbols for "optional" and "many".