Introduction
Databases are an essential part of modern application development. Almost every application needs a way to store, organize, retrieve, and manage information efficiently.
PostgreSQL is a powerful, open-source relational database management system (RDBMS) that helps developers manage structured data using SQL (Structured Query Language).
In this blog, we will explore the relational model, database keys, relationships, SQL joins, aggregate functions, normalization forms (1NF–3NF), and ACID properties.
1. Understanding the Relational Model
The relational model organizes information into tables consisting of rows and columns.
For example, an Employee Management System may contain an Employees table:
| employee_id | employee_name | department_id |
|---|---|---|
| 1 | Ravi | 101 |
| 2 | Swaroop | 102 |
| 3 | Kiran | 101 |
Here:
- Table: A collection of related information.
- Row: Represents a single record.
- Column: Represents an attribute of the record.
- Cell: Contains an individual value.
The relational model helps organize data and establish connections between different tables.
2. Database Keys: Primary Key and Foreign Key
Keys help identify records and establish relationships between tables.
Primary Key (PK)
A Primary Key uniquely identifies every record in a table.
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
age INT
);
The employee_id column uniquely identifies each employee.
A primary key must contain unique and non-NULL values.
Foreign Key (FK)
A Foreign Key establishes a relationship between two tables.
Example:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department_id INT REFERENCES departments(department_id)
);
Here, department_id in the Employees table references the Departments table.
Foreign keys help maintain referential integrity by ensuring that referenced records exist.
3. Types of Database Relationships
Relationships describe how records in different tables are connected.
One-to-One (1:1)
One record in a table is associated with one record in another table.
Example: One person has one passport.
One-to-Many (1:N)
One record in a table can be associated with multiple records in another table.
Example: One department can have multiple employees.
Many-to-Many (N:N)
Multiple records in one table can be associated with multiple records in another table.
Example: Multiple students can enroll in multiple courses.
A junction table is used to implement many-to-many relationships.
Example:
CREATE TABLE enrollments (
student_id INT REFERENCES students(student_id),
course_id INT REFERENCES courses(course_id),
PRIMARY KEY (student_id, course_id)
);
4. SQL Joins
SQL joins combine information from multiple tables using related columns.
Consider two tables: Employees and Departments.
INNER JOIN
Returns only records that have matching values in both tables.
SELECT e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
LEFT JOIN
Returns all records from the left table and matching records from the right table. Unmatched right-side values appear as NULL.
SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;
RIGHT JOIN
Returns all records from the right table and matching records from the left table.
SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;
FULL OUTER JOIN
Returns all records from both tables, including matching and unmatched records.
SELECT e.employee_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
Joins are useful when application data is distributed across multiple related tables.
5. SQL Aggregate Functions
Aggregate functions perform calculations on multiple records and return a single result per group or query.
Important aggregate functions include:
- COUNT(): Counts records.
- SUM(): Calculates the total.
- AVG(): Calculates the average.
- MAX(): Returns the highest value.
- MIN(): Returns the lowest value.
Example:
SELECT AVG(salary)
FROM employees;
This calculates the average employee salary.
GROUP BY
GROUP BY groups records with the same value.
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
This query counts employees in each department.
HAVING
HAVING filters groups based on aggregate conditions.
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;
This returns departments having more than two employees.
6. Database Normalization (1NF, 2NF and 3NF)
Normalization is the process of organizing database tables to reduce unnecessary duplication and improve data integrity.
It helps prevent insertion, update, and deletion anomalies.
First Normal Form (1NF)
A table satisfies 1NF when:
- Every cell contains a single atomic value.
- Each column contains values of a consistent type.
- Each record can be uniquely identified.
Example of a table that violates 1NF:
| Student | Subjects |
|---|---|
| Ravi | Java, Python |
After normalization:
| Student | Subject |
|---|---|
| Ravi | Java |
| Ravi | Python |
Each cell now contains one value.
Second Normal Form (2NF)
A table satisfies 2NF when:
- It already satisfies 1NF.
- Every non-key attribute depends on the whole candidate key, not just part of a composite candidate key.
For example, in a student-course enrollment table, student_name depends only on student_id, while course_name depends only on course_id.
We can separate the information into Students, Courses, and Enrollments tables.
This eliminates partial dependencies.
Third Normal Form (3NF)
A table satisfies 3NF when:
- It already satisfies 2NF.
- Non-key attributes have no transitive dependencies on candidate keys.
For example, employee_id determines department_id, and department_id determines department_name.
Instead of storing department_name repeatedly in the Employees table, we create a separate Departments table.
This reduces duplication and improves consistency.
Normalization Summary
- 1NF: Removes repeating groups and ensures atomic values.
- 2NF: Removes partial dependencies.
- 3NF: Removes transitive dependencies.
Normalization is important because it helps maintain accurate and consistent data.
7. ACID Properties in PostgreSQL
ACID properties ensure reliable database transactions.
ACID stands for Atomicity, Consistency, Isolation, and Durability.
Atomicity
A transaction is treated as a single unit. Either all operations succeed or none of them are applied.
Example: During a bank transfer, money must be deducted from one account and credited to another.
Consistency
Every transaction must preserve database rules and constraints.
Example: A foreign key should not reference a department that does not exist.
Isolation
Concurrent transactions should not interfere with each other in ways that produce incorrect results.
Example: Two customers attempting to book the same available seat should not both successfully reserve it.
Durability
Once a transaction is committed, its changes persist even after a system failure, subject to the database's durability configuration.
Example: A successfully committed payment transaction remains recorded.
Example of a PostgreSQL Transaction
BEGIN;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;
COMMIT;
If an operation fails before committing, the transaction can be rolled back:
ROLLBACK;
Transactions help protect data and maintain database consistency.
8. Practical Implementation: Employee Management Database
To understand these concepts practically, we can create an Employee Management Database.
Create the Departments table:
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
Create the Employees table:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
age INT,
salary NUMERIC(10,2),
department_id INT REFERENCES departments(department_id)
);
Insert sample records:
INSERT INTO departments (department_name)
VALUES ('IT'), ('HR'), ('Finance');
INSERT INTO employees
(employee_name, age, salary, department_id)
VALUES
('Ravi', 23, 30000, 1),
('Swaroop', 22, 40000, 1),
('Kiran', 24, 35000, 2);
Retrieve employee and department information:
SELECT
e.employee_name,
d.department_name,
e.salary
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
This practical example demonstrates table creation, primary keys, foreign keys, relationships, data insertion, and joins.
9. Conclusion
PostgreSQL provides a strong foundation for managing structured application data.
Understanding relational models, database keys, relationships, SQL joins, aggregate functions, normalization, and ACID properties helps developers design reliable and maintainable databases.
These concepts are especially important when developing backend applications using technologies such as NestJS, Node.js, and PostgreSQL.
Learning these fundamentals prepares developers to work with real-world database systems and build applications with better data integrity, consistency, and reliability.
Key Takeaways:
- Relational models organize data into connected tables.
- Primary and foreign keys establish identity and relationships.
- SQL joins retrieve related information from multiple tables.
- Aggregate functions perform calculations on records.
- Normalization reduces duplication and improves data integrity.
- ACID properties ensure reliable database transactions.
Practice makes database concepts easier to understand. Start by creating tables, inserting records, writing joins, and experimenting with transactions in PostgreSQL.
Top comments (0)