What is a Relational Database?

Technical writer

13.02.2025

Reading time: 8 min

Relational databases are the unsung heroes behind the digital world, managing everything from your favorite e-commerce sites to hospital records. At their core, they turn chaos into order by structuring data into interconnected tables. This article delves deeply into relational databases, which are the most commonly used database systems worldwide. We’ll start with foundational concepts, explore SQL through hands-on examples, and progress to advanced tools like transactions and triggers. By the end, you’ll not only grasp how databases work but also how to optimize them for real-world scenarios.

Relational Databases Explained

A relational database organizes data into tables (think of them as spreadsheets) that relate to each other through shared keys. For example, a bookstore database might have:

Tables: Books, Authors, Customers
Rows: Individual records (e.g., one row per book).
Columns: Attributes like book_id, title, or price.
Keys:
- Primary Key: A unique identifier (e.g., book_id) to distinguish every row.
- Foreign Key: A column linking two tables (e.g., author_id in Books references Authors).

Core features:

Structured Schema: Defines data types (e.g., INT, VARCHAR) and constraints (e.g., NOT NULL).
ACID Compliance: Short for Atomicity, Consistency, Isolation, and Durability. These principles ensure reliable transactions (more on this later).
SQL Support: Uses a universal language for fetching and managing data.

Relational vs. Non-Relational Databases

Non-relational databases (NoSQL) like MongoDB or Cassandra prioritize flexibility over structure. They handle unstructured data (e.g., social media posts) and scale horizontally for massive workloads. However, they lack:

Fixed Schemas: Data formats can vary between records.
ACID Guarantees: Sacrifice consistency for speed in distributed systems.
Complex Joins: Relationships are harder to enforce.

Example:

Relational: A bank using MySQL to track accounts, transactions, and customers with strict integrity.
Non-Relational: A weather app using MongoDB to store varying sensor data formats.

SQL – The Language of Databases

SQL (Structured Query Language) is the standard tool for interacting with relational databases. It allows you to:

Define database structures (e.g., tables).
Manipulate data (add, update, delete).
Query information (e.g., find all books under $20).

Key SQL Commands

DDL (Data Definition Language): Builds the database skeleton.

CREATE TABLE Authors (...);
ALTER TABLE Books ADD COLUMN genre VARCHAR(50);

DML (Data Manipulation Language): Handles data.

INSERT INTO Books VALUES (...);
UPDATE Books SET price = 19.99 WHERE book_id = 1;

DQL (Data Query Language): Retrieves data.

SELECT title, price FROM Books WHERE price< 20;

DCL (Data Control Language): Manages security.

GRANT SELECT ON Books TO 'user123';

Working with Relational Databases

Building a Bookstore Database in MySQL

In this part, we'll create a typical MySQL database describing a Bookstore, in which tables like Authors, Books and Customers will be populated with sample data. Furthermore, we'll show how to perform basic SQL actions on this database.

Step 1: Create the Database

CREATE DATABASE bookstore;  
USE bookstore;  -- Switch to the database

Step 2: Design Tables with Relationships

Authors Table:

CREATE TABLE Authors (  
    author_id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    country VARCHAR(50),  
    birth_year INT CHECK (birth_year > 1800)  
);

Books Table:

CREATE TABLE Books (  
    book_id INT AUTO_INCREMENT PRIMARY KEY,  
    title VARCHAR(200) NOT NULL,  
    price DECIMAL(10, 2) CHECK (price > 0),  -- Prevent negative prices  
    author_id INT,  
    FOREIGN KEY (author_id) REFERENCES Authors(author_id) ON DELETE CASCADE  
);

Customers Table:

CREATE TABLE Customers (  
    customer_id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    email VARCHAR(100) UNIQUE,  -- No duplicate emails  
    join_date DATE DEFAULT (CURRENT_DATE)  
);

Basic SQL Queries in Action

Insert Data

Add authors and books:

INSERT INTO Authors (name, country, birth_year)  
VALUES ('J.K. Rowling', 'UK', 1965),  
       ('George Orwell', 'UK', 1903);  

INSERT INTO Books (title, price, author_id)  
VALUES ('Harry Potter', 19.99, 1),  
       ('1984', 14.99, 2);

Retrieve Data with Joins

Fetch books with author names using an INNER JOIN:

SELECT Books.title, Authors.name AS author, Books.price  
FROM Books  
INNER JOIN Authors ON Books.author_id = Authors.author_id;

Result:

Advanced MySQL Features

Transactions (ACID in Practice)

Transactions group operations into a single unit. For example, processing a book purchase:

-- Add a stock column with default value of 10
ALTER TABLE Books ADD COLUMN stock INT DEFAULT 10;  

-- Create Orders table
CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,  -- Unique ID for each order
    customer_id INT NOT NULL,                 -- ID of the customer placing the order
    book_id INT NOT NULL,                     -- ID of the book being purchased
    order_date DATETIME DEFAULT NOW(),        -- Timestamp of the order
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES Books(book_id) ON DELETE CASCADE
);

-- Insert a customer to work on
INSERT INTO Customers (customer_id, name, email) VALUES (101, 'John Doe', '[email protected]');

START TRANSACTION;  

-- Step 1: Deduct stock  
UPDATE Books SET stock = stock - 1 WHERE book_id = 1;  

-- Step 2: Record the sale  
INSERT INTO Orders (customer_id, book_id, order_date)  
VALUES (101, 1, NOW());  

COMMIT;  -- Save changes  

-- If an error occurs, run ROLLBACK to undo.

Triggers (Automate Workflows)

Triggers execute code when specific events occur. For instance, log price changes:

-- Create table PriceLog  
CREATE TABLE PriceLog (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    old_price DECIMAL(10, 2) NOT NULL,
    new_price DECIMAL(10, 2) NOT NULL,
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Configure the trigger
DELIMITER $$  
CREATE TRIGGER log_price_update  
BEFORE UPDATE ON Books  
FOR EACH ROW  
BEGIN  
    IF NEW.price <> OLD.price THEN  
        INSERT INTO PriceLog (book_id, old_price, new_price)  
        VALUES (OLD.book_id, OLD.price, NEW.price);  
    END IF;  
END$$  
DELIMITER ;

Test the trigger:

UPDATE Books SET price = 24.99 WHERE book_id = 1;

Check the PriceLog table to see the change history.

SELECT * FROM PriceLog;

Optimizing Databases

Indexes: speed up searches. Indexes act like a book’s index, helping the database find data faster. For example, create an index on author names:

CREATE INDEX idx_author_name ON Authors(name);

Trade-off: Indexes speed up reads but slow down writes (e.g., INSERT, UPDATE).
Normalization: reduce redundancy. Normalization organizes data to minimize duplication:
- 1st Normal Form (1NF): Every column holds a single value (e.g., split full_name into first_name and last_name).
- 2nd Normal Form (2NF): All columns depend on the entire primary key.
- 3rd Normal Form (3NF): Eliminate dependencies between non-key columns.

For example, instead of storing the author’s birth year in the Books table, you can link to the Authors table via author_id.

Security and Maintenance

User Permissions

Limit access to protect sensitive data:

CREATE USER 'staff'@'localhost' IDENTIFIED BY 'SecurePass123!';  
GRANT SELECT, INSERT ON bookstore.* TO 'staff'@'localhost';

Backups

Use mysqldump to create backups:

mysqldump -u root -p bookstore > bookstore_backup.sql

Restore from a backup:

mysql -u root -p bookstore < bookstore_backup.sql

SQL in the Real World

SQL databases power platforms like YouTube, Spotify, and LinkedIn. Common use cases include:

Web Applications: Store user profiles, product catalogs, and orders.
Reporting Tools: Generate sales reports using GROUP BY and aggregate functions.
APIs: Connect to backend services using languages like Python or Node.js.

Example: A blog platform uses a Posts table linked to a Comments table via post_id, enabling efficient content management.

The Future of Relational Databases

Relational databases continue to evolve, integrating features like JSON support for semi-structured data and cloud-native scalability. Tools like MySQL HeatWave combine transactional and analytical workloads, enabling real-time insights without compromising performance. As data grows in complexity, relational databases remain quite important for applications demanding precision, reliability, and structured data management.

Conclusion

At the heart of countless applications we rely on everyday lies the structured elegance of relational databases. Through this guide, you’ve gained insight into their core principles—how they harness tables, keys, and relationships to organize and handle data with precision. From crafting basic SQL queries to mastering advanced techniques like transactions for data integrity, triggers for automation, and best practices such as security and backups, you now possess a toolkit for creating systems that are both powerful and dependable. Whether it’s managing inventory for a small bookstore or powering complex real-world platforms, these skills enable you to design solutions that remain clear and scalable in an ever-evolving digital landscape.

Looking ahead, your SQL journey can expand into advanced topics like query optimization, replication for scalability, or integrating databases with programming languages like Python or JavaScript. Whether you’re developing a personal blog or contributing to enterprise-level systems, mastering relational databases opens doors to solving complex data challenges. Continue experimenting, explore SQL’s extensive documentation, and embrace real-world projects—each step deepens your understanding of how data drives modern technology.