Achieving First Normal Form

Question: Which steps need to be taken in order to put a table in first normal form?
To put a table in first normal form (1NF), you should take the following steps:

1. Identify the repeating groups: Look for any columns in the table that contain lists or multiple values for a single row. These are known as repeating groups and should be removed.
2. Create a new table: For each repeating group, create a new table and move the repeating data to the new table. The new table should have a foreign key that references the original table.
3. Identify the primary key: Determine the column or columns that uniquely identify each row in the table. This column or set of columns should be the primary key of the table.
4. Remove non-atomic columns: Look for any columns in the table that contain multiple values, and split them into separate columns. This will ensure that each column holds only a single value.
5. Remove partial dependencies: Identify any columns in the table that are dependent on only part of the primary key, and move them to a separate table. This will ensure that each column depends on the entire primary key.

By following these steps, you can transform a table into first normal form (1NF), which is the first step towards achieving a normalized database desig

For a table to be in first normal form, the table must not contain any repeating groups. The easiest way to put a relation with repeating groups into 1NF is to represent the repeating groups in a separate relation.Let us take another look at the Books table from the earlier lesson, represented this time as a relation:

Relational databases use the terms index and key to indicate similar concepts. An index is like an index in a book, used to find specific topics, on specific pages, in a book, very quickly (without having to read the entire book). Similarly, an index in a relational database is a copy of a part of a table, perhaps structured in a specific format such as a BTree index. An index can be created on any field in a table. A key, on the other hand, is more of a concept than a physical thing because a key is also an index. In a relational database, a key is a term used to describe the fields in tables linking tables together to form relationships (such as a one-to-many relationship between two tables). A key is both a key and an index. A key is an index because it copies fields in a table into a more efficient searching structure. A key is also a key, its namesake, because it creates a special tag for a field, allowing that field to be used as a table relationship field, linking tables together into relations. There are three types of keys:

1. primary key,
2. unique key, and
3. foreign key.

This relation is not in 1NF because the Author1 and Author2 fields are repeating groups, they both contain information about authors. To normalize the book relation to 1NF, break it into two relations, book and author:

Book (BookID, Title, Date, Pages, Publisher, City, ISBN)
Author (AuthorID, BookID, AuthorName)

The two tables in the diagram above are linked by the BookID field which is the primary key in the new BOOK relation and the foreign key in the new AUTHOR relation. The link informs the RDBMS that the two tables are related. The two new relations, labeled Books and Authors, have no repeating groups and are therefore in 1NF. To sum up, to normalize a relation to 1NF you:

1. Identify repeating groups in the relation
2. Create separate relations to house the repeating groups

The next lesson discusses the limitations of first normal form.

Before you move on to the next lesson, click the exercise link below to reinforce your understanding of first normal form.
Achieving First Normal Form - Exercise