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:
Parses the query: checks the syntax, much like a compiler checks C code.
Validates it against the data dictionary: does table
employeesexist? Does it have a columnsalary? Does this user have permission?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';
Connection and authentication. The client (say, a backend server) has already logged in; the DBMS knows which user is asking.
Parsing. The DML compiler checks syntax. A typo like
SELECfails here.Validation. Using the data dictionary, it confirms that both tables and all columns exist and that the user has
SELECTpermission.Optimization. The optimizer considers plans. Should it start with
departments(only 5 rows) and filter Mumbai first, or start withemployees? Is there an index onemployees.dept_id?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.
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
EXPLAINshows estimates;EXPLAIN ANALYZEactually 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.