A table is said to be in 1NF (first normal form) if the following constraints hold:
- Each column must have a single value
- Columns must have unique names
- Each column must have attributes of the same data type
- No two rows can be identical
The constraint here is that every attribute (column) should be a single-valued attribute.
For example, the following hotel reservations table violates the first normal form because there is more than one phone number in the phone column:
|
Room ID |
Hotel ID |
Date |
Hotel name |
Hotel description |
Phone |
Star rating |
Free to cancel |
User ID |
|
1 |
12321 |
01/01/2018 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
No |
abc |
|
1 |
12321 |
01/02/2018 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
No |
pqr |
|
2 |
12321 |
01/01/2018 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
No |
xyz |
|
1 |
456 |
01/01/2018 |
Holiday Inn Menlo Park |
Affordable business hotel |
+1-408-123789 +1-408-123456 |
4 |
Yes |
zzz |
To be in the first normal form, the table would have to restructured as follows:
|
Room ID |
HotelId |
Date |
HotelName |
HotelDescription |
Phone |
Star rating |
FreeCancel |
Userid |
|
1 |
12321 |
01/01/2018 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
No |
abc |
|
1 |
12321 |
01/02/2018 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
No |
pqr |
|
2 |
12321 |
01/01/2018 |
FairField Marriot SFO |
Five-star hotel suitable for leisure and business travelers |
+1-408-123123 |
5 |
No |
xyz |
|
1 |
456 |
01/01/2018 |
Holiday Inn Menlo Park |
Affordable business hotel |
+1-408-123456 |
4 |
Yes |
zzz |
|
1 |
456 |
01/01/2018 |
Holiday Inn Menlo Park |
Affordable business hotel |
+1-408-123789 |
4 |
Yes |
zzz |
The first normal form eliminates the phenomenon of repeating groups—it is a set of attributes that can take multiple values for a given occurrence of an entity type.