The goal of normal forms is to remove redundant data from relational database tables by splitting the tables into smaller tables without losing any of the data. It is necessary that the decomposition is lossless, meaning that you can simply come back to the base table by combining the newly created tables with joins.
A relational table is in First Normal Form (1NF) if:
You will see from the above that the rules have nothing to do with redundancy, but they do almost follow the rules of relational tables. A table is said to be relational if it is in First Normal Form (1NF)
You should now understand the principles of the Primary Key. Let's now have a quick look at the atomicity of columns. We'll begin by looking at the following table that lists Book Authors and the Books that they have written:
| AuthorID | Book_Title |
|---|---|
| 001 | Access 2000 Bible |
| 002 | Access Step-By-Step | Access Inside out |
| 003 | Excel For Dummies |
| 004 | Introduction to ASP.Net | Advanced ASP |
| 005 | HTML Bible | Beginning XML |
| 006 | Access Step-By-Step |
| 007 | Access 2000 Visual Basic for Applications |
| 008 | Database Design for Mere Mortals |
You can see from the above that the Book_Title column can contain multiple values. We can see that Author 002 has written two books. We can also see that Author 002 co-authored one of them with another Author - 006 (Access Step-By-Step).
It may become difficult to query the above table to find information about a specific Book Title. The first thing that comes to mind is to split the Book_Titles column into two columns to allow us to separate the information into single columns as shown below:
| AuthorID | Book_Title_1 | Book_Title_2 |
|---|---|---|
| 001 | Access 2000 Bible | <Null> |
| 002 | Access Step-By-Step | Access Inside Out |
| 003 | Excel For Dummies | <Null> |
| 004 | Introduction to ASP.Net | Advanced ASP |
| 005 | HTML Bible | Beginning XML |
| 006 | Access Step-By-Step | <Null> |
| 007 | Access 2000 Visual Basic for Applications | <Null> |
| 008 | Database Design for Mere Mortals | <Null> |
The above solution addresses the issue of atomicity, but may not help in the problem of querying the data. It may be difficult to find a specific Book Title written by one or many Authors, or to find the number of co-authors of one Book Title.
Now, there would be even further issues if an Author writes a third Book Title. Where would we now store this value? We could create a third Book Title column, but what if they wrote a fourth, fifth and so on. Even if we create 20 or 30 Book Title columns, it would be a massive waste of space for those Authors who only wrote one or two Books.
If you want to put this table in First Normal form, we could introduce a new column, TitleID, identifying each Book and create a composite primary key as shown in the table below:
| AuthorID | TitleID | Book_Title |
|---|---|---|
| 001 | 111-01 | Access 2000 Bible |
| 002 | 125-08 | Access Step-By-Step |
| 003 | 144-48 | Excel For Dummies |
| 004 | 179-00 | Introduction to ASP.Net |
| 005 | 181-05 | HTML Bible |
| 006 | 125-08 | Access Step-By-Step |
| 007 | 123-23 | Access 2000 Visual Basic for Applications |
| 008 | 199-09 | Database Design for Mere Mortals |
Now the above table is in First Normal Form, since the Primary Key identifies every row, and every column is atomic. The earlier problems discussed are now solved; an Author can write as many Books as he wishes, and it is simple to group the table by Book Title to list every co-author.