Chapter 6: DBMS and SQL — The Mechanism and Common Language for Operating Data

What you'll learn in this chapter

• List five major functions of a DBMS
• Explain the differences between the four kinds of SQL (DDL / DML / DCL / TCL)
• Understand the characteristics of the major RDBMS products and choose between them by use case

Prerequisites

You should understand Chapter 2: The Relationship Between a System and Its Database and Chapter 4: CRUD and ACID.

The DBMS Is the "Data Professional"

A DBMS (Database Management System) is software that sits between the application and the data, single-handedly taking on SQL parsing, optimization, execution, data storage, concurrent-access control, and more.

If There Were No DBMS

Without a DBMS, a developer would need to write all of the following logic themselves.

Handling data without a DBMS (an example to avoid)

def find_user_by_email(email):
    # Open the file and read it line by line
    with open('users.txt', 'r') as f:
        lines = f.readlines()

    # Linear search over every row (no index)
    for line in lines:
        data = line.split(',')
        if data[2] == email:
            return {
                'id': data[0],
                'name': data[1],
                'email': data[2],
            }

    # No concurrent-access control -> breaks if someone else is writing at the same time
    # No data-integrity checks
    # No security considerations (anyone can read the file directly)

With a DBMS, all of this collapses into a single line: SELECT * FROM users WHERE email = ?.

Major Functions of a DBMS

These are the functions needed to "handle data safely, quickly, and fairly," and it would take months to implement them yourself. Using a DBMS lets you focus right away on the business logic you actually want to build.

Major RDBMS Products

Open Source

Product	Characteristics	Typical uses
PostgreSQL	Feature-rich, highly extensible, strong SQL-standard compliance	Web apps, data analysis, geospatial data (PostGIS)
MySQL	Widely used around the world, a strong track record in web apps	CMSs like WordPress, the LAMP stack
SQLite	File-based (no server needed), easy to embed	Local DB for mobile apps, small tools

Comparing PostgreSQL and MySQL's market share

In the Stack Overflow Developer Survey 2025, PostgreSQL ranks first as the "most used" database, used by 58.2% of professional developers, with MySQL second at 39.6%. That said, MySQL has a stronger track record in systems already in operation, such as WordPress and existing LAMP stacks, so you'll also often encounter MySQL on existing projects. New projects tend to favor PostgreSQL, and this guide's code examples adopt PostgreSQL as the default too.

Commercial

Product	Vendor	Characteristics	Main uses
Oracle Database	Oracle	Feature set and performance for large-scale systems	Core systems in banking, telecom, and large enterprises
Microsoft SQL Server	Microsoft	Strong affinity with Windows environments	Enterprise business systems in general
Db2	IBM	Strong affinity with mainframes	Legacy systems in banking and insurance

SQL — The Language for Talking to a Database

SQL (Structured Query Language) is the standard language for operating a relational database. SQL is classified into four categories by purpose.

Category	Purpose	Main commands
DDL (Data Definition)	Defining the structure of data	CREATE TABLE / ALTER TABLE / DROP TABLE
DML (Data Manipulation)	Operating on data	SELECT / INSERT / UPDATE / DELETE
DCL (Data Control)	Managing permissions	GRANT / REVOKE
TCL (Transaction Control)	Controlling transactions	BEGIN / COMMIT / ROLLBACK

The one a junior engineer typically uses most at first is DML (data manipulation), followed by learning DDL (creating and altering tables). DCL comes up during operations, and TCL is used for the transaction management covered in Chapter 4.

Basic SQL Examples

DDL: Creating a Table and an Index

Creating an employees table (PostgreSQL)

CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(255) UNIQUE NOT NULL,
    department    VARCHAR(50),
    salary        NUMERIC(10, 2),
    hire_date     DATE NOT NULL,
    is_active     BOOLEAN NOT NULL DEFAULT TRUE
);

-- Indexes to speed up searches
CREATE INDEX idx_employees_department ON employees(department);
CREATE INDEX idx_employees_hire_date  ON employees(hire_date);

DML: Inserting, Searching, Updating, and Deleting Data

Operating on employee data

-- INSERT: add data
INSERT INTO employees (name, email, department, salary, hire_date)
VALUES ('Taro Yamada', 'yamada@example.com', 'sales', 450000, '2026-01-15');

-- SELECT: search for data
SELECT name, department, salary
FROM   employees
WHERE  department = 'sales'
  AND  salary > 400000
ORDER BY hire_date DESC;

-- UPDATE: update data
UPDATE employees
SET    salary = salary * 1.1  -- 10% raise
WHERE  department = 'sales'
  AND  hire_date < '2025-01-01';

-- DELETE: delete data
DELETE FROM employees
WHERE  is_active = FALSE
  AND  hire_date < '2022-01-01';

Terminology

Index: a data structure for speeding up searches. Just like an index at the back of a book, it lets you quickly find a record's location from a particular value. You create one on columns commonly used in search conditions, like WHERE department = 'sales'. The design of how to place indexes is covered in Chapter 19 (the internal implementation of B-Trees and hashes is out of scope for this guide).

Query: a request made to a database. Generally, one SQL statement equals one query.

How a DBMS Executes SQL

From the moment you issue a single SELECT statement to when the result comes back, the DBMS internally runs through the following steps.

A developer only has to write SQL, and the DBMS takes care of all this optimization. That said, if you "write SQL with no usable index," the optimization engine has no choice but to run it slowly anyway, so it helps even a junior engineer to stay aware that indexes exist.

How to Choose a DBMS

Criterion	Options
Budget	Ample → commercial (Oracle / SQL Server) / limited → open source (PostgreSQL / MySQL)
Scale	Large-scale, mission-critical → Oracle / SQL Server / medium scale → PostgreSQL / MySQL / small, embedded → SQLite
Use case	New web development → PostgreSQL / existing web, WordPress → MySQL / embedded, mobile apps → SQLite

If a junior engineer is trying this for the first time

For personal projects or learning purposes, SQLite is the easiest to pick up (no install, just one file). If you're getting serious about learning web development, spinning up PostgreSQL or MySQL in something like Docker is the standard path.

Exercises

Exercise 1: Decide the DBMS's role

Does the DBMS take care of the following, or does it need to be written on the application side?

1. Controlling the order when 100 people try to write to the same table at the same time
2. Checking whether a user's form input "looks like a valid email address"
3. Quickly retrieving records that match a search condition
4. Determining whether the logged-in user is "an admin or a regular user"

Sample answer

#	Verdict	Reason
1. Ordering concurrent writes	DBMS	Concurrency control (exclusive locking, MVCC, etc.) is a core DBMS function
2. Email-format check	Mostly the app	Format validation leans toward business logic. A simple check is possible with a DB CHECK constraint, but rigorous validation is usually done in the app
3. Fast record search	DBMS	Handled by the index plus the query optimization engine. The app just writes SQL
4. Admin vs. regular-user check	Both	A column representing the role (role) is stored in the DB, and the app decides what to show on screen

A common mistake: assuming "the DBMS just executes SQL." Concurrency control, optimization, permission management, and more — the DBMS takes on a lot more work than just running SQL.

Exercise 2: SQL classification quiz

Which of DDL / DML / DCL / TCL does each of the following SQL statements belong to?

1. INSERT INTO users (name) VALUES ('Yamada');
2. CREATE INDEX idx_email ON users(email);
3. GRANT SELECT ON users TO analyst_user;
4. BEGIN;
5. ALTER TABLE users ADD COLUMN age INTEGER;
6. SELECT COUNT(*) FROM users;

Sample answer

#	Category	Reason
1. INSERT	DML	Manipulating data (adding it)
2. CREATE INDEX	DDL	Defining a data structure (an index is a kind of object too)
3. GRANT	DCL	Managing permissions
4. BEGIN	TCL	Starting a transaction
5. ALTER TABLE	DDL	Changing a table's structure
6. SELECT	DML	Retrieving data

Additional note: TRUNCATE TABLE (deleting everything) is often classified as DDL. Even though both DELETE (DML) and TRUNCATE (DDL) "delete data," they differ in nature: DELETE works row by row, while TRUNCATE clears the entire table at once. Whether it can be rolled back varies by RDBMS — PostgreSQL's TRUNCATE can be safely rolled back inside a transaction, but MySQL and Oracle run an implicit commit, so it can't be rolled back there.

Summary

Here's a recap of what this chapter covered.

• A DBMS is software that takes on the tedious work of handling data — SQL parsing, optimization, transaction management, permission management, and more
• The major RDBMSs are PostgreSQL, MySQL, SQLite, Oracle, and SQL Server. This guide defaults to PostgreSQL
• SQL splits into four categories: DDL (definition), DML (manipulation), DCL (permissions), and TCL (transactions)
• Understanding what a DBMS takes care of helps you see the line between code you need to write yourself and code you don't

The next chapter covers the basic terminology used in relational databases (table / column / record / primary key / foreign key) and naming conventions.

Next chapter

Chapter 7: Basic Terminology and Naming Conventions