Normalization to 2NF and 3NF

Definition

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and defining relationships between them.

Second Normal Form (2NF)

A table is in Second Normal Form if:

• It is in First Normal Form (1NF).
• It has no partial dependency; all non-key attributes must depend on the entire primary key.

Steps to Achieve 2NF

1. Identify the primary key of the table.
2. Check for partial dependencies; if any exist, separate them into a new table.
3. Ensure that all non-key attributes are fully functionally dependent on the primary key.

Example of 2NF

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    CustomerID INT,
    ProductName VARCHAR(255),
    Quantity INT,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Third Normal Form (3NF)

A table is in Third Normal Form if:

• It is in Second Normal Form (2NF).
• It has no transitive dependency; non-key attributes should not depend on other non-key attributes.

Steps to Achieve 3NF

1. Ensure the table is in 2NF.
2. Identify transitive dependencies and separate them into new tables.
3. Ensure all attributes are dependent only on the primary key.

Example of 3NF

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    ContactNumber VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Best Practices

• Always analyze data requirements thoroughly before normalizing.
• Document the normalization process to maintain clarity.
• Consider performance trade-offs; sometimes denormalization may be necessary for performance.
• Regularly review and update the database design as application needs change.

FAQ