PostgreSQL

Chapter 8 - RDBMS Concepts

Normalization

Normalization is a process used to organize a database into tables and columns, minimizing redundancy and dependency. The First Normal Form (1NF) is the initial step in this process, ensuring that a table's columns contain only atomic (indivisible) values and that each column contains values of a single type.

In 1NF, a table should have:

A primary key: Each table must have a unique identifier for each row.
Atomic columns: Each column should hold only one value, and there should be no repeating groups or arrays.
Consistent data types: All entries in a column must be of the same data type.

Let's illustrate 1NF with an example of tables related to banking. We’ll create tables for customers, accounts, and transactions:

Create the customers table

The customers table stores information about each customer. Each row represents a unique customer, and columns should have atomic values.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

Create the accounts table

The accounts table stores information about customer accounts. Each row represents a unique account and contains atomic values.

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    account_type VARCHAR(50) NOT NULL,
    balance DECIMAL(15, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Create the transactions table

The transactions table logs all transactions. Each transaction is recorded in a single row, with atomic details.

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INT NOT NULL,
    transaction_date DATE NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    description TEXT,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

Key Points

Atomic Columns:
- In the customers table, first_name, last_name, and email are atomic values.
- In the accounts table, account_type and balance are atomic values.
- In the transactions table, transaction_date, amount, and description are atomic values.
No Repeating Groups:
- Each table structure avoids repeating groups or arrays. For example, transactions are stored in a separate table rather than having multiple transaction columns in the accounts table.
Primary Keys:
- each table has a primary key (customer_id, account_id, and transaction_id) to uniquely identify each row.

By following these principles, the tables conform to the First Normal Form (1NF), which helps in reducing redundancy and improving data integrity.

Feel free to adjust or expand the content as needed!

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:

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.
No Duplicates: There should be no duplicate rows in a table. This means that each row in the table should be unique.
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.
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.
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.