PostgreSQL Guide: SQL, Normalization & ACID
Краткое содержание
Pixel shares an introduction to PostgreSQL and relational database fundamentals, covering tables, primary and foreign keys, relationships, SQL joins, and aggregate functions. It also explains 1NF–3NF normalization and ACID properties, helping the community build a stronger foundation for designing reliable, well-structured databases.
DEV Community
PostgreSQL: Relational Model, SQL, Normalization and ACID Properties
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 ...

Powered by Readybot.io