Chapter 03 of 41

DBMS Architecture

In the previous chapters we treated the DBMS as a black box: you send it a query and it returns a result. In this chapter we open the box. We will look at the main components inside a DBMS, follow one query through them step by step, and then see how databases are deployed in 1-tier, 2-tier and 3-tier architectures.

When someone says "DBMS architecture", they can mean two different things:

  • Internal architecture: which software modules are inside the DBMS and how they work together.

  • Tier (deployment) architecture: how users, application code and the database server are arranged across machines.

Interviewers and university exams ask about both, so we will cover both. (A third view, the three-schema architecture of external, conceptual and internal levels, has its own chapter next.)


Internal Architecture of a DBMS

A DBMS is a large program, but it can be understood as two big modules sitting on top of disk storage:

  • The Query Processor understands what you asked and decides how to do it.

  • The Storage Manager actually reads and writes the data safely.

  Naive users      App programmers     Analysts          DBA
  (UPI app,        (backend code)      (SQL queries)     (DDL, admin)
   web forms)            |                  |                 |
       |                 |                  |                 |
       v                 v                  v                 v
 +------------------------------------------------------------------+
 |                         QUERY PROCESSOR                          |
 |   DDL interpreter    DML compiler / parser    Query optimizer    |
 |                     Query evaluation (execution) engine          |
 +------------------------------------------------------------------+
                                  |
                                  v
 +------------------------------------------------------------------+
 |                         STORAGE MANAGER                          |
 |   Authorization &        Transaction manager                     |
 |   integrity manager      (concurrency control + recovery)        |
 |   File manager           Buffer manager                          |
 +------------------------------------------------------------------+
                                  |
                                  v
 +------------------------------------------------------------------+
 |                           DISK STORAGE                           |
 |  Data files   Indexes   Data dictionary   Statistics   Log files |
 +------------------------------------------------------------------+

Let us understand each part.


1. Query Processor

DDL Interpreter

Statements like CREATE TABLE or ALTER TABLE describe the structure of data. The DDL interpreter processes them and records the resulting definitions (tables, columns, types, constraints) in the data dictionary.

DML Compiler (Parser and Translator)

Statements like SELECT, INSERT, UPDATE and DELETE go to the DML compiler. It:

  1. Parses the query: checks the syntax, much like a compiler checks C code.

  2. Validates it against the data dictionary: does table employees exist? Does it have a column salary? Does this user have permission?

  3. Translates it into an internal form, typically a tree of relational-algebra-like operations (select, project, join).

Query Optimizer

There are usually many ways to run the same query. Should the DBMS scan the whole table or use an index? Which table should it read first in a join? Which join method should it use?

The optimizer estimates the cost of different plans, using statistics such as table sizes and how many distinct values a column has, and picks the cheapest one it finds. This is why SQL can be declarative: you say what, the optimizer decides how.

Query Evaluation Engine

Also called the execution engine. It takes the chosen plan and executes it step by step, asking the storage manager for the rows it needs and producing the final result.


2. Storage Manager

The storage manager is the interface between the high-level queries and the low-level data on disk. It is responsible for storing, retrieving and updating data efficiently and safely.

Authorization and Integrity Manager

  • Authorization: checks that the user is allowed to do the operation (for example, only HR may update salaries).

  • Integrity: checks constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL and CHECK. The errors we saw in earlier chapters (duplicate key, foreign key failure) are raised here.

Transaction Manager

It makes sure the database stays correct even when many transactions run at once and even if the system crashes. It has two important parts:

  • Concurrency-control manager: controls how simultaneous transactions interleave, for example using locks, so that they do not corrupt each other's work.

  • Recovery manager: writes a log of changes and uses it to undo incomplete transactions and redo committed ones after a failure.

File Manager

Manages how space is allocated on disk and how the database is laid out in files, pages (blocks) and records.

Buffer Manager

Disks (even SSDs) are far slower than RAM. The buffer manager keeps frequently used disk pages in a region of main memory called the buffer pool. When a page is needed:

  • If it is already in the buffer pool, it is used directly (a hit, very fast).

  • If not, it is read from disk into the pool (a miss). If the pool is full, some page is chosen for eviction (using an LRU-like policy), and if that page was modified ("dirty") it is written back first.

In MySQL's InnoDB storage engine, data is read and written in pages of 16 KB by default, and the buffer pool size is set by innodb_buffer_pool_size, which is 128 MB by default. On a dedicated database server it is usually raised to a large part of the machine's RAM. You can check both values:

SHOW VARIABLES LIKE 'innodb_page_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

134217728 bytes = 128 MB. (Your server may show a different buffer pool size if it has been configured.)


3. Disk Storage

The storage manager maintains several kinds of data structures on disk:

Structure

What it holds

Data files

The actual table rows

Indexes

Extra structures (usually B+ trees) for finding rows quickly by key

Data dictionary

Metadata: schemas, tables, columns, types, constraints, users, privileges

Statistics

Row counts, value distributions and index cardinality used by the optimizer

Log files

Records of changes used for crash recovery

The data dictionary is itself queryable. In MySQL it is exposed through information_schema. For example, here is what MySQL knows about the departments table of our sample company database:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'company_db' AND TABLE_NAME = 'departments'
ORDER BY ORDINAL_POSITION;
+-------------+-------------+-----------+-------------+------------+
| TABLE_NAME  | COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_KEY |
+-------------+-------------+-----------+-------------+------------+
| departments | dept_id     | int       | NO          | PRI        |
| departments | dept_name   | varchar   | NO          | UNI        |
| departments | location    | varchar   | NO          |            |
+-------------+-------------+-----------+-------------+------------+

This is exactly the information the DML compiler uses to validate your queries.


Life of a Query: Step by Step

Let us follow one query through the DBMS:

SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = 'Mumbai';
  1. Connection and authentication. The client (say, a backend server) has already logged in; the DBMS knows which user is asking.

  2. Parsing. The DML compiler checks syntax. A typo like SELEC fails here.

  3. Validation. Using the data dictionary, it confirms that both tables and all columns exist and that the user has SELECT permission.

  4. Optimization. The optimizer considers plans. Should it start with departments (only 5 rows) and filter Mumbai first, or start with employees? Is there an index on employees.dept_id?

  5. Execution. The evaluation engine runs the plan, requesting pages from the buffer manager, which reads them from disk only if they are not already in memory.

  6. Result. Rows are sent back to the client.

MySQL lets us peek at step 4 with EXPLAIN. Here is the plan MySQL 8.4 chose for this query on our sample database (output of EXPLAIN FORMAT=TREE):

-> Nested loop inner join  (cost=1.45 rows=2)
    -> Filter: (d.location = 'Mumbai')  (cost=0.75 rows=1)
        -> Table scan on d  (cost=0.75 rows=5)
    -> Index lookup on e using dept_id (dept_id=d.dept_id)  (cost=0.7 rows=2)

Read it from the inside out: scan departments, keep only Mumbai, and for each such department use the index on employees.dept_id (created automatically for the foreign key) to find its employees. The cost and rows values are the optimizer's estimates, which is why it picked the small table first. On your machine the numbers, or even the plan, may differ slightly because they depend on table statistics.

And the result:

+--------------+-----------+
| name         | dept_name |
+--------------+-----------+
| Sneha Kapoor | Sales     |
| Vikram Singh | Sales     |
| Ananya Das   | Sales     |
+--------------+-----------+

We will study this pipeline in depth in Query Processing & Optimization.


Tier Architectures (How a DBMS Is Deployed)

Now let us zoom out. Where do the user, the application logic and the DBMS physically sit?

1-Tier Architecture

The user, the application and the database are all on the same machine. The user works directly with the DBMS.

+-----------------------------+
|        Single machine       |
|  User -> App -> Database    |
+-----------------------------+

Examples: a developer running the MySQL command-line client on their own laptop to practise SQL; a mobile app storing its data in an embedded SQLite database on the phone.

  • Pros: simple, fast, no network needed.

  • Cons: data cannot easily be shared by many users; not suitable for multi-user business systems.

2-Tier Architecture (Client-Server)

The application runs on the client machine and talks directly to the database server over the network, using an API such as ODBC or JDBC.

+-------------------+                 +-------------------+
|      Client       |  SQL over the   |   Database Server |
| (UI + app logic)  | --------------> |      (DBMS)       |
+-------------------+     network     +-------------------+

Example: a desktop billing application in a shop, installed on 5 counters, all connecting directly to one MySQL server in the back office.

  • Pros: simple to build; data is shared; fast for a small number of clients.

  • Cons: every client holds database credentials and business logic; changing the logic means updating every client; it does not scale well to thousands of users; it is less secure because clients talk straight to the database.

3-Tier Architecture

An application server (middle tier) sits between the client and the database. The client never talks to the database directly.

+----------------+      +----------------------+      +----------------+
|  Presentation  | HTTP |  Application tier    | SQL  |   Data tier    |
|  (browser /    | ---> |  (business logic,    | ---> |    (DBMS)      |
|   mobile app)  |      |   e.g. Java/Node/    |      |                |
|                | <--- |   Python server)     | <--- |                |
+----------------+      +----------------------+      +----------------+

Example: when you book a ticket in a train-booking app, the app sends a request to the company's servers; those servers check seat availability, apply business rules (quota, fare, payment), and only they run SQL on the database.

  • Pros: better security (the database is hidden from the internet, and only the app server has credentials); business logic is in one place; each tier can be scaled separately (add more app servers behind a load balancer); clients stay thin.

  • Cons: more components to build, deploy and monitor; an extra network hop.

Almost every modern web and mobile application uses 3-tier (or more, "n-tier") architecture. Real systems often add more layers such as caches (Redis), message queues and many small services, which is why you will also hear the term n-tier architecture.

Comparison of Tier Architectures

Basis

1-Tier

2-Tier

3-Tier

Where the app logic runs

Same machine as DB

Client

Application server

Client talks to DB directly?

Yes (same machine)

Yes

No

Users supported

Usually one

Tens to hundreds

Thousands to millions

Security

Depends on the machine

Weaker (DB exposed to clients)

Stronger (DB hidden behind app tier)

Maintenance

Easy

Update every client

Update the server once

Typical example

SQLite in a mobile app

Desktop billing software

Web and mobile apps


Centralized, Parallel and Distributed Systems

One more way to classify database architecture is by how the DBMS itself runs:

  • Centralized: the DBMS runs on a single computer.

  • Client-server: one or more server machines serve many client machines (the 2-tier and 3-tier designs above).

  • Parallel: many processors and disks work together on the same database to speed up big queries.

  • Distributed: the data itself is spread across multiple sites (machines or data centres), connected by a network. This is covered in Distributed Databases & CAP Theorem.


Common Mistakes Students Make

  • Confusing tier architecture with three-schema architecture. Tiers are about machines and deployment; the three schemas (external, conceptual, internal) are about levels of data abstraction.

  • Calling the browser the "application tier". In 3-tier, the browser is the presentation tier; the business logic runs on the server.

  • Thinking the buffer manager is the operating system's cache. The DBMS manages its own buffer pool because it knows better which pages to keep and when pages must be written for recovery.

  • Thinking EXPLAIN shows actual times. Plain EXPLAIN shows estimates; EXPLAIN ANALYZE actually runs the query and reports measured values.


Interview Questions

1. What are the main components of a DBMS?

The query processor (DDL interpreter, DML compiler, query optimizer, evaluation engine) and the storage manager (authorization and integrity manager, transaction manager, file manager, buffer manager), working over disk storage (data files, indexes, data dictionary, statistics, logs).

2. What does the query optimizer do?

It generates alternative execution plans for a query, estimates their cost using statistics, and chooses the cheapest plan it finds, for example deciding between an index lookup and a full scan or choosing the join order.

3. What is the role of the buffer manager?

It caches disk pages in main memory (the buffer pool), decides which pages to evict when memory is full, and writes modified pages back to disk, cooperating with the recovery manager.

4. Difference between 2-tier and 3-tier architecture?

In 2-tier, the client application connects directly to the database. In 3-tier, the client talks to an application server, which alone talks to the database. 3-tier is more secure, scalable and easier to maintain.

5. Which architecture does a typical web application use, and why?

3-tier (n-tier): browser or mobile app, application servers, database. It keeps the database off the public internet, centralises business logic and lets each tier scale independently.

6. What is the data dictionary?

The DBMS's internal catalog of metadata: tables, columns, types, constraints, indexes, users and privileges. The query processor consults it to validate and optimize queries.


Summary

  • Internally, a DBMS has a query processor (parse, validate, optimize, execute) and a storage manager (authorization/integrity, transactions, files, buffers) on top of disk storage.

  • Disk storage holds data files, indexes, the data dictionary, statistics and logs.

  • A query flows: parse, validate against the catalog, optimize, execute via the buffer manager, return results.

  • For deployment, 1-tier keeps everything on one machine, 2-tier connects clients directly to the database server, and 3-tier adds an application server in between.

  • Modern web and mobile apps use 3-tier or n-tier designs.

Next, in Three-Schema Architecture & Data Independence, we will see how a DBMS separates what users see from how data is actually stored.