First Normal Form (1NF)
A table is said to be in First Normal Form (1NF) if and only if each attribute of the relation is atomic. That is,
- Each row in a table should be identified by primary key (a unique column value or group of unique column values)
- No rows of data should have repeating group of column values.
Let’s consider the STUDENT table with his ID, Name address and 2 subjects that he has opted for.
- Look at Chris entry. He has only subject. Hence Subject2 for him is NULL. Here storage space for second entry is simply wasted.
- In the case of Joseph, he has two subjects, Mathematics and Physics, both the columns have values. Imagine if he opts for third subject? There is no column for his third entry. In this case, whole table needs to be altered, which is not good at this stage. Once database is designed, it should be a perfect one. We should not be modifying it as we start adding/updating data.
- One of the requirements of 1NF is, each table should have primary key. This key in the table makes each record unique. In our example we have it already- STUDENT_ID.
- Here, SUBJECT1 and SUBJECT2 are same set of columns, i.e.; it has same kind of information stored – Subject, which is a violation of first rule of 1NF. As it states, there should not be any repeating columns. We have to remove such columns. But think how?
In order to have STUDENT in 1NF, we have to remove multiple SUBJECT columns from STUDENT table. Instead, create only one SUBJECT column, and for each STUDENT enters as many rows as SUBJECT he has. After making this change, the above table will change as follows:
Now STUDENT_ID alone cannot be a primary key, because it does not uniquely determines each record in the table. If we want to records for Joseph, and we query by his ID,100, gives us two records. Hence Student_ID is no more a primary key. When we observe the data in the table, all the four field uniquely determines each record. Hence all four fields together considered as primary key.
Thus, the above table is in 1NF form.