The first normal form (1NF) is the initial step in the normalization process of database design. The purpose of 1NF is to ensure that a database table has a single value for each attribute, and that each attribute is atomic, meaning it cannot be further divided into smaller pieces of data.
In other words, a table is in 1NF if:
- All attributes contain only atomic values.
- Each attribute has a unique name.
- Each record in the table is unique and identified by a primary key.
To understand this better, let’s look at some examples.
Example 1
Suppose we have a table called “Customers” with the following columns: Customer ID, Name, Phone Numbers. In this table, the Phone Numbers column contains multiple phone numbers separated by commas.
| Customer ID | Name | Phone Numbers |
|---|---|---|
| 1 | John Doe | 555-1234,555-5678 |
| 2 | Jane Doe | 555-9876,555-4321 |
This table is not in 1NF because the Phone Numbers column violates the atomicity rule. Instead of a single value for each phone number, there are multiple phone numbers separated by commas. To bring this table into 1NF, we need to split the Phone Numbers column into separate columns, each containing a single phone number.
| Customer ID | Name | Phone Number 1 | Phone Number 2 |
|---|---|---|---|
| 1 | John Doe | 555-1234 | 555-5678 |
| 2 | Jane Doe | 555-9876 | 555-4321 |
Example 2
Suppose we have a table called “Orders” with the following columns: Order ID, Order Date, Customer Name, Item Name, Quantity. In this table, the Customer Name and Item Name columns contain multiple values for each attribute.
| Order ID | Order Date | Customer Name | Item Name | Quantity |
|---|---|---|---|---|
| 1 | 2022-01-01 | John Doe, Jane Doe | Book, CD | 1, 2 |
| 2 | 2022-01-02 | Jane Doe, Bob Smith | DVD, Book | 1, 3 |
This table is also not in 1NF because the Customer Name and Item Name columns contain multiple values separated by commas. To bring this table into 1NF, we need to split these columns into separate tables and use a foreign key to link them to the Orders table.
| Order ID | Order Date | Customer ID | Item ID | Quantity |
|---|---|---|---|---|
| 1 | 2022-01-01 | 1 | 1 | 1 |
| 1 | 2022-01-01 | 2 | 2 | 2 |
| 2 | 2022-01-02 | 2 | 3 | 1 |
| 2 | 2022-01-02 | 3 | 1 | 3 |
| Customer ID | Name |
|---|---|
| 1 | John Doe |
| 2 | Jane Doe |
| 3 | Bob Smith |
| Item ID | Item Name |
|---|---|
| 1 | Book |
| 2 | CD |
| 3 | DVD |
By splitting the Customers and Items columns into separate tables, we have eliminated the multiple values problem and ensured that each attribute contains only atomic values. We can now link the Customers and Items tables to the Orders table using foreign keys.
1NF is the first step in the normalization process of database design. It ensures that a table has a single value for each attribute and that each attribute is atomic. By bringing a table into 1NF, we can avoid data redundancy and improve data integrity.
Leave a Reply