SQL 2 🛢️ Relational Database Management Systems (RDBMS)

You know what a relational database is: a collection of tables linked by keys. But a database is not a file that sits on disk and manages itself. Something must enforce the keys, validate the data types, process the queries, handle concurrent access from multiple users, and recover from crashes. That something is the Relational Database Management System.

An RDBMS is the software that implements a relational database. It sits between the application and the data, providing a complete solution for storing, retrieving, and modifying records according to the rules defined in the schema . When you write CREATE TABLE or SELECT * FROM customers, you are not talking to the tables directly. You are talking to the RDBMS, and it translates your request into operations on the underlying files.

Key point: The RDBMS is the engine. The relational model is the design. The tables and keys are the structure. The SQL is the language. A relational database without an RDBMS is a set of files with no way to query them. An RDBMS without a schema is a program with nothing to manage. The two are inseparable in practice, which is why the terms are often used interchangeably.

---

Why RDBMS exists

The relational model, published by Codd in 1969, described how data should be organized . But a model is not a program. To use the model, someone had to build software that could store tables, enforce keys, process queries, and manage concurrent access. The RDBMS is that software.

The enforcement problem. The relational model says a foreign key must reference a valid row. But a file on disk does not enforce anything. An RDBMS does. It checks every insert and update against the constraints defined in the schema. It rejects the operation if the constraint would be violated . The integrity of the relational model is guaranteed by the RDBMS, not by the model alone.

The concurrency problem. A database serves many users and applications simultaneously. One user is updating a customer’s address while another is reading the customer’s orders. The RDBMS coordinates these operations so that readers see consistent data and writers do not overwrite each other’s changes . The mechanisms are complex — multi-version concurrency control, row-level locking, isolation levels — but the goal is simple: each transaction appears to run alone.

The durability problem. A power failure occurs mid-transaction. The RDBMS must ensure that either the entire transaction is committed or none of it is. The ACID properties — Atomicity, Consistency, Isolation, Durability — are the guarantee. The RDBMS implements them through transaction logs, checkpoints, and recovery procedures . Without the RDBMS, a partial write could corrupt the data.

The query problem. A user asks: “Which customers in New York placed orders over $500 last month?” The relational model provides the tables and the relationships. The RDBMS provides the query engine that joins the tables, filters the rows, and returns the result. The SQL is the language; the RDBMS is the interpreter.

The trade-off. The RDBMS adds overhead. Every read and write goes through the query parser, the optimizer, the transaction manager, and the storage engine. A raw file access is faster. But the RDBMS provides guarantees that raw file access cannot: integrity, consistency, concurrency, durability, and a standard query language. For any application where data matters, the overhead is the price of correctness.

---

a. The RDBMS Objects

An RDBMS manages more than tables. It provides a set of objects that extend the relational model and support the practical needs of applications .

Tables are the primary object. They store the data in rows and columns. Every table is defined by a schema that specifies the columns, their data types, and the constraints on the values .

Views are virtual tables. A view is a stored query that presents a subset of the data from one or more tables. It looks like a table to the application, but it does not store data. When the underlying tables change, the view reflects the change . Views are used to simplify complex queries, restrict access to sensitive columns, and present a stable interface even when the underlying schema changes.

Indexes are auxiliary structures that improve the speed of data retrieval. An index is a separate data structure — typically a B-tree or hash table — that maps the values in one or more columns to the locations of the corresponding rows. Without an index, the RDBMS must scan the entire table to find a row. With an index, it can jump directly to the row . The trade-off is that indexes consume storage and slow down inserts and updates, because the index must be maintained.

Constraints are rules that limit the data that can be stored. They include primary key constraints, foreign key constraints, unique constraints, check constraints, and not-null constraints. The RDBMS enforces these rules on every insert and update. A check constraint on age, for example, can reject any value that is not a non-negative integer between 1 and 130 .

Stored procedures and functions are programs written in a procedural language (like PL/SQL or PL/pgSQL) that execute inside the RDBMS. They can encapsulate business logic, reduce network traffic, and improve performance by executing close to the data.

Triggers are procedures that fire automatically in response to events on a table — an insert, an update, or a delete. They are used to enforce complex business rules, maintain audit trails, and synchronize related tables.

---

b. The ACID Properties

The ACID properties are the guarantee that database transactions are processed reliably. They are the foundation of the RDBMS’s reputation for correctness .

Atomicity means that a transaction is all-or-nothing. If a transaction consists of three operations, either all three succeed or none of them do. If the second operation fails, the first is rolled back. The database is never left in a partial state.

Consistency means that a transaction brings the database from one valid state to another. All constraints are satisfied before and after the transaction. If a transaction would violate a foreign key constraint, it is rejected, and the database remains consistent .

Isolation means that concurrent transactions do not interfere with each other. Each transaction appears to execute alone, even if many are running simultaneously. The RDBMS implements isolation through locking, multi-version concurrency control, or a combination of both .

Durability means that once a transaction is committed, its changes are permanent. Even if the system crashes immediately after the commit, the changes survive. The RDBMS achieves durability through write-ahead logging: the changes are written to a log and flushed to disk before the commit is acknowledged.

The ACID properties are what distinguish an RDBMS from a file system. A file system provides no guarantee that a sequence of writes will complete as a unit. The RDBMS does. This is why mission-critical applications — banking, e-commerce, inventory — rely on RDBMS .

---

c. The RDBMS Landscape

There are many RDBMS implementations, both open-source and commercial. They all implement the relational model and support SQL, but they differ in features, performance, licensing, and target market.

PostgreSQL is an open-source, object-relational RDBMS. It is highly extensible, supports advanced data types (JSONB, arrays, geometric types), and has strong concurrency through multi-version concurrency control . It is free of licensing fees and is widely used for applications of all sizes, from small projects to large-scale enterprise systems .

MySQL is one of the most popular open-source databases. It is the “M” in the LAMP stack and is known for its ease of use and speed for simple workloads . It supports multiple storage engines and is widely used in web applications. Oracle acquired MySQL through Sun Microsystems, and licensing terms have changed over time .

Oracle Database is a commercial RDBMS with a long history. It is one of the first commercial relational databases and is the backbone of many enterprise applications . It offers advanced features like Real Application Clusters (RAC) for high availability and In-Memory Column Store for analytics, but it requires licensing fees and is expensive to maintain .

Microsoft SQL Server is a commercial RDBMS developed by Microsoft. It is suitable for workloads from small projects to large production applications. It integrates with the Microsoft ecosystem — Active Directory, Power BI, Azure — and provides tools like SQL Server Management Studio . It requires licensing fees for production use .

SQLite is an embedded RDBMS. It is not a client-server database; it is a library that stores the entire database in a single file. It is used in mobile applications, embedded systems, and small-scale projects where a full RDBMS server is unnecessary .

The choice between these systems depends on the application’s needs. PostgreSQL and MySQL are the standard choices for open-source projects. Oracle and SQL Server are the standard choices for enterprises with existing investments in those ecosystems. SQLite is the standard choice for embedded and mobile applications.

---

Complete Example Session

This session demonstrates the RDBMS in action: creating a schema, enforcing constraints, running a transaction, and observing the ACID properties.

-- ============================================
-- PART 1: THE RDBMS ENFORCES THE SCHEMA
-- ============================================

-- Create a table with constraints
CREATE TABLE accounts (
    account_id   INTEGER PRIMARY KEY,
    owner_name   VARCHAR(100) NOT NULL,
    balance      DECIMAL(10, 2) NOT NULL CHECK (balance >= 0),
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- The RDBMS stores the schema.
-- Every operation is checked against it.

-- ============================================
-- PART 2: THE RDBMS ENFORCES THE CHECK CONSTRAINT
-- ============================================

-- This insert succeeds
INSERT INTO accounts (account_id, owner_name, balance)
VALUES (1, 'Alice', 1000.00);

-- This insert fails because balance cannot be negative
INSERT INTO accounts (account_id, owner_name, balance)
VALUES (2, 'Bob', -50.00);

-- Error: CHECK constraint failed: balance >= 0

-- The RDBMS rejected the invalid data.
-- The database remains consistent.

-- ============================================
-- PART 3: THE RDBMS ENFORCES THE NOT NULL CONSTRAINT
-- ============================================

INSERT INTO accounts (account_id, owner_name, balance)
VALUES (3, NULL, 500.00);

-- Error: NOT NULL constraint failed: accounts.owner_name

-- ============================================
-- PART 4: THE ACID TRANSACTION
-- ============================================

-- Transfer $100 from Alice to Bob
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

-- If either update fails, the entire transaction is rolled back.
-- Atomicity guarantees that the transfer is all-or-nothing.

-- ============================================
-- PART 5: THE RDBMS PROVIDES ISOLATION
-- ============================================

-- Session 1 begins a transaction
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Session 2 tries to read the same account
SELECT balance FROM accounts WHERE account_id = 1;

-- The RDBMS returns the balance as it was before Session 1's update
-- (or waits for Session 1 to commit, depending on the isolation level).
-- Isolation ensures that Session 2 does not see uncommitted changes.

-- Session 1 commits
COMMIT;

-- Session 2 now sees the updated balance.

-- ============================================
-- PART 6: THE RDBMS PROVIDES DURABILITY
-- ============================================

-- The RDBMS writes the transaction to a log.
-- The log is flushed to disk before COMMIT returns.
-- If the system crashes after COMMIT, the changes survive.
-- On restart, the RDBMS replays the log and recovers the state.

-- ============================================
-- PART 7: THE RDBMS OPTIMIZES QUERIES
-- ============================================

-- Create an index on the owner_name column
CREATE INDEX idx_accounts_owner ON accounts(owner_name);

-- A query that filters by owner_name now uses the index.
EXPLAIN SELECT * FROM accounts WHERE owner_name = 'Alice';

-- The RDBMS query planner chooses the index over a full table scan.
-- The optimizer decides how to execute the query based on statistics.

-- ============================================
-- PART 8: THE RDBMS PROVIDES A VIEW
-- ============================================

-- Create a view that shows only active accounts
CREATE VIEW active_accounts AS
SELECT account_id, owner_name, balance
FROM accounts
WHERE balance > 0;

-- Query the view like a table
SELECT * FROM active_accounts;

-- The view does not store data.
-- It executes the underlying query each time it is accessed.

-- ============================================
-- PART 9: THE RDBMS PROVIDES A STORED PROCEDURE
-- ============================================

-- Create a procedure that transfers funds
CREATE PROCEDURE transfer_funds(
    from_account INTEGER,
    to_account INTEGER,
    amount DECIMAL(10, 2)
)
LANGUAGE SQL
AS $$
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
$$;

-- Call the procedure
CALL transfer_funds(1, 2, 50.00);

-- The procedure executes inside the RDBMS.
-- It runs as a single transaction.

-- ============================================
-- PART 10: THE RDBMS MANAGED THE ENTIRE OPERATION
-- ============================================

-- The RDBMS:
-- 1. Parsed the SQL statements
-- 2. Validated them against the schema
-- 3. Enforced the constraints
-- 4. Managed the transactions
-- 5. Provided isolation between sessions
-- 6. Guaranteed durability through logging
-- 7. Optimized the queries
-- 8. Executed the stored procedure
-- 9. Maintained the indexes
-- 10. Recovered from any failures

-- The application only wrote SQL.
-- The RDBMS did everything else.

The ten parts cover the RDBMS enforcing the schema, enforcing check constraints, enforcing not-null constraints, the ACID transaction, isolation, durability, query optimization, views, stored procedures, and a summary of the RDBMS’s responsibilities.

---

Quick Reference

The RDBMS Objects

Object	Purpose
Table	Stores data in rows and columns
View	Virtual table defined by a query
Index	Speeds up data retrieval
Constraint	Enforces data integrity rules
Stored Procedure	Program that executes inside the RDBMS
Trigger	Procedure that fires on table events

The ACID Properties

Property	Guarantee
Atomicity	All or nothing
Consistency	Valid state before and after
Isolation	Transactions do not interfere
Durability	Committed changes survive crashes

The RDBMS Implementations

RDBMS	License	Typical Use
PostgreSQL	Open source	General-purpose, extensible
MySQL	Open source	Web applications, LAMP stack
Oracle	Commercial	Enterprise, high-volume OLTP
SQL Server	Commercial	Microsoft ecosystem
SQLite	Open source	Embedded, mobile, small-scale

The RDBMS Responsibilities

Responsibility	Description
Storage	Manages the data on disk
Query processing	Parses, optimizes, and executes SQL
Concurrency control	Coordinates simultaneous access
Transaction management	Ensures ACID properties
Integrity enforcement	Validates constraints
Recovery	Restores state after failures
Security	Controls access to data

---

Best Practices

✅ Do This:

-- Define constraints in the schema
balance DECIMAL(10, 2) NOT NULL CHECK (balance >= 0)          -- ✅

-- Use transactions for multi-step operations
BEGIN TRANSACTION;
-- operations
COMMIT;                                                        -- ✅

-- Create indexes on frequently queried columns
CREATE INDEX idx_name ON table(column);                        -- ✅

-- Use views to simplify complex queries
CREATE VIEW active_users AS SELECT ...;                        -- ✅

❌ Don’t Do This:

-- Don't store invalid data
INSERT INTO accounts (balance) VALUES (-100);  -- violates constraint -- ❌

-- Don't skip transactions for multi-step operations
UPDATE accounts SET balance = balance - 100;  -- no transaction       -- ❌
UPDATE accounts SET balance = balance + 100;

-- Don't create indexes on every column
-- Indexes slow down writes and consume storage.                  -- ❌

-- Don't rely on the application to enforce integrity
-- The RDBMS should enforce it.                                  -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Constraint violation	Invalid data in insert or update	Check the data against the schema
Transaction rollback	One operation failed	Handle the error and retry
Slow queries	No index on filtered columns	Create an index
Deadlock	Two transactions wait for each other	Use consistent lock ordering
Data corruption	Disk failure without proper logging	Enable write-ahead logging

---

Real-World Examples

1. Create a Table with Constraints

CREATE TABLE users (id INTEGER PRIMARY KEY, email VARCHAR(100) UNIQUE NOT NULL, age INTEGER CHECK (age >= 0));

2. Create an Index

CREATE INDEX idx_users_email ON users(email);

3. Create a View

CREATE VIEW adult_users AS SELECT * FROM users WHERE age >= 18;

4. Transaction

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

5. Stored Procedure

CREATE PROCEDURE transfer(from_id INT, to_id INT, amount DECIMAL) AS $$ ... $$;

6. Trigger

CREATE TRIGGER audit_trigger AFTER UPDATE ON accounts FOR EACH ROW EXECUTE FUNCTION audit_log();

7. Query Plan

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

8. Check Constraints

ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

9. Foreign Key

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

10. Recovery

-- The RDBMS handles recovery automatically on startup
-- by replaying the write-ahead log.

---

Visual

The RDBMS Architecture

┌──────────────────────────────────────────────┐
│  APPLICATION                                 │
│    Sends SQL queries                         │
│       │                                      │
│       ▼                                      │
│  RDBMS                                       │
│    ├─ Query Parser                           │
│    ├─ Query Optimizer                        │
│    ├─ Transaction Manager                    │
│    ├─ Concurrency Control                    │
│    ├─ Storage Engine                         │
│    └─ Recovery Manager                       │
│       │                                      │
│       ▼                                      │
│  DATA FILES                                  │
│    Tables, Indexes, Logs                     │
│                                              │
│  The RDBMS is the layer between the          │
│  application and the data.                   │
│                                              │
└──────────────────────────────────────────────┘

The ACID Properties

┌──────────────────────────────────────────────┐
│  ACID                                        │
│                                              │
│  Atomicity:                                  │
│    All or nothing                            │
│    Rollback on failure                       │
│                                              │
│  Consistency:                                │
│    Valid state before and after              │
│    Constraints enforced                      │
│                                              │
│  Isolation:                                  │
│    Transactions do not interfere             │
│    Concurrent access controlled              │
│                                              │
│  Durability:                                 │
│    Committed changes survive                 │
│    Write-ahead logging                       │
│                                              │
└──────────────────────────────────────────────┘

The RDBMS Objects

┌──────────────────────────────────────────────┐
│  TABLES                                      │
│    Store data in rows and columns            │
│                                              │
│  VIEWS                                       │
│    Virtual tables from queries               │
│                                              │
│  INDEXES                                     │
│    Speed up retrieval                        │
│                                              │
│  CONSTRAINTS                                 │
│    Enforce data integrity                    │
│                                              │
│  STORED PROCEDURES                           │
│    Programs inside the RDBMS                 │
│                                              │
│  TRIGGERS                                    │
│    Fire on table events                      │
│                                              │
└──────────────────────────────────────────────┘

The RDBMS Landscape

┌──────────────────────────────────────────────┐
│  OPEN SOURCE                                 │
│    PostgreSQL: extensible, standards-compliant│
│    MySQL: popular, easy, LAMP stack          │
│    SQLite: embedded, single-file             │
│                                              │
│  COMMERCIAL                                  │
│    Oracle: enterprise, RAC, high-volume      │
│    SQL Server: Microsoft ecosystem, SSMS     │
│                                              │
│  The choice depends on:                      │
│    - Budget (license fees)                   │
│    - Features (extensibility, HA, analytics) │
│    - Ecosystem (existing tools, skills)      │
│    - Scale (OLTP, OLAP, embedded)            │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
RDBMS definition	Software that implements a relational database
Core responsibilities	Storage, query processing, concurrency, transactions, integrity, recovery
ACID	Atomicity, Consistency, Isolation, Durability
Objects	Tables, views, indexes, constraints, stored procedures, triggers
Open source	PostgreSQL, MySQL, SQLite
Commercial	Oracle, Microsoft SQL Server
Query language	SQL
History	Emerged in the 1970s; IBM System R and DB2 were precursors

Key takeaways:

• An RDBMS is the software that implements a relational database. It stores the data, enforces the constraints, processes the queries, manages concurrent access, and guarantees the ACID properties. The relational model is the design; the RDBMS is the engine .
• The ACID properties are the foundation of the RDBMS’s reliability. Atomicity ensures all-or-nothing transactions. Consistency ensures valid states. Isolation ensures concurrent transactions do not interfere. Durability ensures committed changes survive crashes .
• The RDBMS provides objects beyond tables. Views are virtual tables. Indexes speed up retrieval. Constraints enforce integrity. Stored procedures and triggers encapsulate logic inside the database .
• The RDBMS enforces integrity at the database level, not the application level. A check constraint on age can reject invalid data before it is stored. A foreign key constraint can prevent dangling references. The application does not need to implement these checks .
• PostgreSQL and MySQL are the leading open-source RDBMS. PostgreSQL is extensible and standards-compliant. MySQL is popular and easy to use. Both are free of licensing fees and suitable for a wide range of applications .
• Oracle and Microsoft SQL Server are the leading commercial RDBMS. They offer advanced features for enterprise workloads — RAC, In-Memory Column Store, Always On — but require licensing fees and are more expensive to maintain .
• The RDBMS abstracts the complexity of data management. The application writes SQL. The RDBMS parses, optimizes, executes, and manages the transaction. The application does not need to know how the data is stored, how the indexes work, or how the concurrency is controlled.

Remember: A relational database is a design. An RDBMS is the software that makes the design work. It enforces the keys, validates the constraints, processes the queries, and guarantees the ACID properties. It provides views, indexes, stored procedures, and triggers. It is the layer between the application and the data, and it is responsible for everything that happens in between. PostgreSQL and MySQL are the open-source standards. Oracle and SQL Server are the commercial standards. The choice depends on the application’s needs, the team’s skills, and the organization’s budget.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!