First Normal Form 1NF in SQL

The First Normal Form (1NF) is one of the fundamental rules or guidelines to design a relational database. A table is considered to be in 1NF if:

It has a primary key: A unique identifier for each row.
All columns contain atomic, indivisible values. In other words, each column should contain only one value per row, of a specific data type.
All entries in a column are of the same data type.
The order in which the data is stored doesn't affect the table's ability to meet the First Normal Form criteria.

Why Was 1NF Invented?

1NF was invented to ensure that data is stored in a logical and efficient manner. Before the concept of 1NF, databases could have structures that were inefficient and difficult to query. 1NF ensures that each piece of data is stored in its most granular form, thus simplifying the database design.

This granularity aids in:

Consistency: Storing one piece of information in one place ensures that it remains consistent across the database.
Query Efficiency: A normalized structure is easier to query using SQL.
Data Integrity: The chances of data anomalies are reduced.

What Does 1NF Solve in SQL?

In SQL databases, applying 1NF helps to:

Eliminate redundancy: It ensures that the same piece of data is not recorded more than once.
Improve query performance: Atomic data is easier and faster to query.
Simplify data management: Ensures that each piece of data is stored in its most logical place.

Issues with non-1NF compliant tables

Non-1NF tables can lead to several issues, such as redundancy, inconsistency, and difficulty in querying and updating data. Let's explore these problems through an example using the Non1NFTable.

Example: Non1NFTable Schema

Suppose we have a table with the following schema and data:

CREATE TABLE Non1NFTable (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    PhoneNumbers VARCHAR(100)
);

INSERT INTO Non1NFTable (Name, PhoneNumbers)
VALUES ('Alice', '123-456,789-101'),
       ('Bob', '111-222,333-444');

Issues with Non-1NF Table

Querying Complexity

Let's say you want to find all the phone numbers associated with 'Alice'. You would get a single string '123-456,789-101', and you'd have to split this string in your application to work with individual numbers. This adds extra processing overhead.
```
 SELECT PhoneNumbers FROM Non1NFTable WHERE Name='Alice';
```
Updating Anomalies

If Alice changes one of her phone numbers, you have to replace the entire string '123-456,789-101' with the updated numbers. This can lead to accidental data loss or errors if not done carefully.
```
 UPDATE Non1NFTable SET PhoneNumbers='123-457,789-101' WHERE Name='Alice';
```
Deletion Anomalies

If Alice decides to remove one of her phone numbers, you'll have to fetch the existing string, remove the particular phone number, and then update the row with the new string. This is cumbersome and prone to errors.
```
 UPDATE Non1NFTable SET PhoneNumbers='789-101' WHERE Name='Alice';
```
Insertion Anomalies

If you need to insert a new person without a phone number, you would either have to insert a NULL or an empty string, both of which could lead to inconsistencies when querying.
```
 INSERT INTO Non1NFTable (Name, PhoneNumbers) VALUES ('Charlie', NULL);
```
Data Redundancy and Inconsistency

Storing multiple values in a single column leads to repetition of data ('Alice' and 'Bob' are repeated for each phone number). This increases the risk of inconsistencies; for example, if 'Alice' changes her name to 'Alicia', all rows with 'Alice' would need to be updated.

Conclusion

Non-1NF tables are often not suitable for complex queries and can lead to issues in data management. It's generally a good idea to normalize such tables to eliminate these issues.

Workshop Exercise: Test 1NF in MySQL

In this exercise, we'll create a table that initially violates the 1NF and then we will modify it to comply with the First Normal Form.

Step 1: Install and Setup MySQL

If you haven't already, download and install MySQL on your machine.

Step 2: Create a Non-1NF Table

Connect to your MySQL server and execute the following SQL command to create a table that violates 1NF. This table contains a column PhoneNumbers where multiple phone numbers are stored as a comma-separated list, thus violating 1NF.

CREATE DATABASE WorkshopDB;
USE WorkshopDB;

CREATE TABLE Non1NFTable (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    PhoneNumbers VARCHAR(100)
);

INSERT INTO Non1NFTable (Name, PhoneNumbers)
VALUES ('Alice', '123-456,789-101'),
       ('Bob', '111-222,333-444');

Step 3: Query the Non-1NF Table

You can use the following SQL command to view the table.

SELECT * FROM Non1NFTable;

Step 4: Create a 1NF Compliant Table

Now, let's create a new table that complies with the 1NF.

CREATE TABLE In1NFTable (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    PersonID INT,
    Name VARCHAR(50),
    PhoneNumber VARCHAR(50)
);

INSERT INTO In1NFTable (PersonID, Name, PhoneNumber)
VALUES (1, 'Alice', '123-456'),
       (1, 'Alice', '789-101'),
       (2, 'Bob', '111-222'),
       (2, 'Bob', '333-444');

Step 5: Query the 1NF Table

Use the following SQL command to view the 1NF compliant table.

SELECT * FROM In1NFTable;

Notice that the table no longer has any columns that contain sets of values, and each row uniquely represents a person and a phone number associated with them. Thus, the table is in 1NF.

That wraps up this exercise. By completing it, you should have a clearer understanding of what 1NF is, why it's important, and how to achieve it in MySQL.

References

First Normal Form (1NF)