DE Relational Databases

Relational databases are the most widely used data storage systems in the world. Banks, hospitals, airlines, and e-commerce platforms all run on relational databases. They have existed since the 1970s, and they remain dominant because they solve the problem of storing and retrieving structured data with speed, reliability, and precision.

What Makes a Database "Relational"

A relational database stores data in tables. Each table represents one type of entity — customers, orders, products, employees. Each row in the table represents one instance of that entity. Each column represents one attribute of that entity. The word "relational" comes from the fact that tables can relate to each other through shared keys.

A Classroom Analogy

Picture a school. One notebook contains a list of all students — their student number, name, and grade level. Another notebook contains all exam scores — the student number, subject, and score. The student number appears in both notebooks. That shared number links a score to the correct student without writing the student's full name in the score notebook every time. This is exactly how relational databases work. Related data lives in separate tables and connects through shared identifiers called keys.

Tables, Rows, and Columns

Table: customers
+-----------+--------------+-------------------+----------+
| cust_id   | name         | email             | city     |
+-----------+--------------+-------------------+----------+
| C001      | Aisha Nwosu  | aisha@mail.com    | Lagos    |
| C002      | Raj Mehta    | raj@mail.com      | Mumbai   |
| C003      | Sara Kim     | sara@mail.com     | Seoul    |
+-----------+--------------+-------------------+----------+

Table: orders
+-----------+-----------+------------+--------+
| order_id  | cust_id   | order_date | amount |
+-----------+-----------+------------+--------+
| ORD001    | C001      | 2024-05-01 | 2500   |
| ORD002    | C003      | 2024-05-03 | 800    |
| ORD003    | C001      | 2024-05-07 | 1200   |
+-----------+-----------+------------+--------+

The cust_id column in the orders table points back to the customers table. This relationship lets you find all orders for Aisha Nwosu by looking up her customer ID and finding all order rows with that ID.

Primary Keys and Foreign Keys

Primary Key

Every table should have a primary key — a column whose value uniquely identifies each row. No two rows can have the same primary key value. In the customers table above, cust_id is the primary key. It ensures that each customer has exactly one record with a unique identifier.

Foreign Key

A foreign key is a column in one table that references the primary key of another table. In the orders table, cust_id is a foreign key. It references the primary key in the customers table. This link enforces referential integrity — the database rejects any order that references a customer ID that does not exist in the customers table.

ACID Properties

Relational databases guarantee four critical properties known as ACID. These properties make relational databases trustworthy for sensitive data like financial transactions.

Atomicity

A transaction either completes fully or does not happen at all. If you transfer money from one bank account to another, the debit from account A and the credit to account B happen together. If anything fails mid-way, the database rolls back both changes as if neither happened.

Consistency

Every transaction brings the database from one valid state to another. Rules and constraints always hold. A transaction cannot leave an order without a customer.

Isolation

Multiple transactions running at the same time do not interfere with each other. Two customers buying the last item in stock simultaneously will not both succeed — the database handles the conflict correctly.

Durability

Once a transaction commits, the data persists even if the server crashes immediately afterward. The database writes the change to disk before confirming success.

SQL: The Language of Relational Databases

SQL (Structured Query Language) is the standard language for working with relational databases. It uses simple, English-like commands to read, insert, update, and delete data.

-- Find all orders for customer C001
SELECT order_id, order_date, amount
FROM orders
WHERE cust_id = 'C001';

Result:
+-----------+------------+--------+
| order_id  | order_date | amount |
+-----------+------------+--------+
| ORD001    | 2024-05-01 | 2500   |
| ORD003    | 2024-05-07 | 1200   |
+-----------+------------+--------+

Indexes: How Databases Find Data Fast

Without indexes, a database scans every row in a table to find a match — like reading an entire book to find one paragraph. An index works like a book's index at the back: it points directly to the location of the data. Data engineers add indexes to columns that appear frequently in search conditions to speed up queries significantly.

Popular Relational Databases

Database     | Common Use Case
-------------|------------------------------------------
PostgreSQL   | General-purpose; supports JSON too
MySQL        | Web applications; widely used with PHP
Oracle       | Enterprise systems; banking and ERP
SQL Server   | Microsoft ecosystem; corporate data
SQLite       | Lightweight; mobile apps, local storage

Limitations of Relational Databases

Relational databases perform excellently for structured data at reasonable scale. They struggle with very large datasets distributed across many servers, highly variable data structures, and unstructured content like images and documents. For those scenarios, NoSQL databases or distributed systems provide better solutions — which later topics cover in detail.

Summary

Relational databases store data in tables connected through keys. They enforce data integrity through constraints and ACID properties. SQL provides a powerful way to query and manipulate the data. Every data engineer works with relational databases regularly — understanding how they work forms the foundation for all advanced data engineering concepts.