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:
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)
Here are the same tables presented graphically, with a line representing the link between the tables.
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:
- Identify repeating groups in the relation
- Create separate relations to house the repeating groups
The next lesson discusses the limitations of first normal form.