Database: Normalization

Database Normalization: A Comprehensive Guide

Database normalization is the process of organizing a relational database in a way that reduces redundancy and dependency by dividing large tables into smaller tables and defining relationships between them. This helps improve data integrity and efficiency in data retrieval.

1. First Normal Form (1NF)

1NF involves eliminating duplicate data and ensuring that each piece of data is atomic. Here is an example of a table not in 1NF:

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone_numbers VARCHAR(255)
);

This table violates 1NF because the phone_numbers column contains multiple values. To normalize it, we can create a new table for phone numbers:

CREATE TABLE phone_numbers (
  phone_id INT PRIMARY KEY,
  student_id INT,
  phone_number VARCHAR(15),
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

2. Second Normal Form (2NF)

2NF involves eliminating partial dependencies by ensuring that every non-key attribute is fully functional dependent on the primary key. Here is an example of a table not in 2NF:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  product_name VARCHAR(50),
  unit_price DECIMAL(10, 2),
  quantity INT,
  total_price DECIMAL(10, 2)
);

In this case, total_price is partially dependent on the primary key (order_id, product_id). To normalize it, we can split the table into two:

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  unit_price DECIMAL(10, 2),
  quantity INT,
  total_price DECIMAL(10, 2),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50)
);

3. Third Normal Form (3NF)

3NF involves eliminating transitive dependencies by ensuring that every non-key attribute is only dependent on the primary key. Here is an example of a table not in 3NF:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(50),
  department_id INT,
  department_name VARCHAR(50),
  location VARCHAR(50)
);

In this case, department_name and location are dependent on department_id, which is not the primary key. To normalize it, we can split the table into three:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(50),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50),
  location VARCHAR(50)
);

Common Use Cases

Database normalization is crucial in scenarios where data integrity is paramount, such as financial applications, healthcare systems, and e-commerce platforms. By organizing data efficiently, normalization reduces the risk of anomalies and inconsistencies in the database.

Importance in Interviews

Understanding database normalization is essential for database administrators, data analysts, and software developers. Employers often assess candidates' knowledge of normalization techniques during interviews to gauge their proficiency in database design and optimization.

Conclusion

Database normalization is a fundamental concept in relational database management that helps maintain data integrity and optimize database performance. By following the normalization process, developers can ensure efficient data storage and retrieval, ultimately improving the overall quality of their database systems.