MySQL

First Normal Form (1NF) is the initial step in the normalization process, aimed at organizing data within a relational database to reduce redundancy and improve data integrity. A table is in First Normal Form if it meets the following criteria:

• Atomicity: Each column contains only atomic (indivisible) values. This means each field must hold a single value, not a list or set of values.
• Unique Rows: Each row in the table must be unique, which is typically ensured by having a primary key.
• Consistent Columns: Each column must contain values of a single type and have the same meaning across all rows.

Here’s a more detailed look at First Normal Form:

1. Atomicity

   • Ensure that each column contains only one value per row.
   • Avoid storing multiple values or lists in a single column.

   -- Example of a table not in 1NF due to non-atomic values
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FullName VARCHAR(100), -- This column stores both first and last names
       PhoneNumbers VARCHAR(255) -- This column may store multiple phone numbers
   );

   -- To bring it into 1NF, split FullName into FirstName and LastName, and create a separate table for phone numbers
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50)
   );
   
   CREATE TABLE PhoneNumbers (
       EmployeeID INT,
       PhoneNumber VARCHAR(20),
       FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
       PRIMARY KEY (EmployeeID, PhoneNumber)
   );

2. Unique Rows

   • Each row should be identifiable uniquely, often achieved through a primary key.
   • Ensure no duplicate rows exist in the table.

   -- Adding a primary key to ensure unique rows
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50)
   );

3. Consistent Columns

   • Each column should contain data of a single type and meaning.
   • Avoid mixing different types of data in a single column.

   -- Example of a column containing consistent data types
   CREATE TABLE Departments (
       DepartmentID INT PRIMARY KEY,
       DepartmentName VARCHAR(50),
       Location VARCHAR(100) -- All values in this column should be consistent and describe the location
   );

4. Example of Applying 1NF

   • Let’s say you have a table storing employee information where each employee’s projects are listed in a single column. To apply 1NF, you would create separate rows for each project.

   -- Original table not in 1NF
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       EmployeeName VARCHAR(100),
       Projects VARCHAR(255) -- Storing multiple projects in a single column
   );
   
   -- Applying 1NF by splitting projects into a separate table
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       EmployeeName VARCHAR(100)
   );
   
   CREATE TABLE EmployeeProjects (
       EmployeeID INT,
       ProjectName VARCHAR(100),
       FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
       PRIMARY KEY (EmployeeID, ProjectName)
   );

5. Benefits of 1NF

   • Reduces redundancy by ensuring each piece of data is stored only once.
   • Improves data integrity by eliminating the potential for inconsistent or duplicate data entries.
   • Simplifies querying and managing the data.

Applying First Normal Form is a fundamental step in database design, laying the groundwork for further normalization steps that enhance data organization and integrity.

FIRST NORMAL FORM (1NF)

The First Normal Form (1NF) is a property of a relation (table) in a relational database. A relation is said to be in First Normal Form if it satisfies the following rules:

1. Unique Identifier: A unique identifier, often referred to as a primary key, is a column or set of columns whose values uniquely identify each row in a table. In 1NF, it's essential to have a unique identifier for each row.
2. No Duplicates: There should be no duplicate rows in a table. This means that each row in the table should be unique.
3. Atomicity: Each cell in the table must contain only atomic (indivisible) values. In other words, the value in each column must be indivisible as far as the relational model is concerned. Complex data types such as arrays, lists, or composite objects are not allowed.
4. No Repeating Groups: A table should not contain repeating groups of columns. If a table contains columns that repeat the same kind of information (e.g., phone1, phone2, phone3 for multiple phone numbers), it violates 1NF. Instead, such data should be stored in a separate table with a relationship between the two tables.
5. Consistent Schema: The schema or structure of the table (i.e., the columns and data types) must be consistent in all rows. Each column must have a unique name, and the data type of values within each column must be the same.

EXAMPLE 1 - Unique identifier

EXAMPLE 2 - DUPLICATES

EXAMPLE 3 - ATOMICITY & REPEATING GROUPS

EXAMPLE 4

EXAMPLE 5 - REPEATING COLUMNS