DBMS Architecture

Definition

DBMS architecture is the structural design of a database system that defines how its components are arranged and how they communicate to store, retrieve, update, and manage data efficiently and securely.

In simple words, it is the blueprint of how a database system works from the user's request to the actual storage and retrieval of data.

Main Content

1. Three-Level Architecture

The three-level architecture is the most important conceptual model of DBMS architecture. It separates the database system into three levels so that users can work with data without needing to know how it is physically stored.

External level, Conceptual level, and Internal level

External level: This is the user view level. Different users may see different parts of the database depending on their needs. For example, a student may see only marks and attendance, while an administrator may see fees, admission details, and personal records.
Conceptual level: This is the logical structure of the entire database. It describes what data is stored, what relationships exist, and what constraints apply, but not how the data is physically stored.
Internal level: This is the physical storage level. It explains how data is stored on disk, including file organization, indexes, record placement, and access paths.

Data abstraction and data independence

Data abstraction means hiding unnecessary details from users. A user does not need to know the file structure or storage layout to query data.
Data independence means changes at one level should not affect other levels. For example, if the physical storage method changes, the conceptual schema should remain the same. This makes DBMS flexible and easier to modify.

Example: In a university database, a teacher may only access student names, subject marks, and attendance. The conceptual level stores all the university data structure, while the internal level decides whether student records are stored in heap files or indexed files.

ASCII diagram for three-level architecture:

+---------------------------+
|      External Level       |
|  User Views / Subschemas  |
+---------------------------+
             |
             v
+---------------------------+
|     Conceptual Level      |
|  Logical Structure / Schema|
+---------------------------+
             |
             v
+---------------------------+
|       Internal Level      |
| Physical Storage / Files   |
+---------------------------+

2. Two-Tier and Three-Tier Architecture

DBMS architecture can also be explained using client-server tiers, especially in real-world applications. These architectures describe how the database is accessed over a network.

Two-tier architecture

In two-tier architecture, the client directly communicates with the database server.
The client usually contains the user interface and application logic, while the server stores and manages the database.
It is simple and suitable for small systems, but it can become difficult to manage when many users connect simultaneously.

Example: A desktop billing application connected directly to an SQL Server database.

Three-tier architecture

In three-tier architecture, an application server is added between the client and the database server.
The three layers are: presentation layer, application logic layer, and database layer.
This improves security, scalability, and maintainability because clients do not directly access the database.

Example: An online shopping website where the browser is the client, the web/application server processes business rules, and the database server stores customer and product data.

ASCII diagram for two-tier and three-tier architecture:

Two-Tier:
Client  <------->  Database Server

Three-Tier:
Client  <------->  Application Server  <------->  Database Server

Why tiered architecture matters

It supports distributed computing.
It allows better load management.
It makes system updates easier because business logic can be changed at the application server without changing client software.

3. Internal DBMS Components

A DBMS is made up of several internal modules that work together to process queries and manage data. Understanding these components is essential to understanding how the architecture functions practically.

Query processor and storage manager

Query processor handles SQL queries. It parses the query, checks syntax and meaning, optimizes it for efficient execution, and then creates an execution plan.
Storage manager manages the physical data stored in disk files. It handles file organization, buffer management, access methods, and interaction with the operating system.

Transaction manager and authorization manager

Transaction manager ensures correctness in multi-user environments. It controls concurrent access and maintains database consistency using concepts such as ACID properties.
Authorization manager checks user permissions before allowing operations like insert, update, delete, or select. It helps enforce security and access control.

Database catalog and data dictionary

The database catalog stores metadata, which is data about data. It contains information about tables, columns, constraints, indexes, users, and privileges.
The data dictionary helps the DBMS understand the structure and rules of the database.

Example: When a user runs:

SELECT name FROM Student WHERE marks > 80;

the query processor interprets the statement, the optimizer chooses the best access path, the storage manager reads the required pages from disk, and the result is returned after checking permissions.

ASCII diagram for DBMS internal components:

Users / Applications
        |
        v
+-------------------+
|   Query Processor  |
+-------------------+
        |
        v
+-------------------+
|  Transaction Mgr   |
+-------------------+
        |
        v
+-------------------+
|  Storage Manager   |
+-------------------+
        |
        v
+-------------------+
|    Database File   |
+-------------------+

Working / Process

1. User submits a request

A user or application sends a query, such as retrieving records, inserting a new row, or updating existing data.
The request may come from a form, a program, a web page, or a SQL client.
Example: SELECT * FROM Employee WHERE department = 'HR';

2. DBMS processes the query

The query processor checks the SQL statement for correctness.
It translates the query into an internal representation and determines the best execution strategy.
The transaction manager ensures the operation is safe in a multi-user environment, and the authorization manager checks whether the user has permission.

3. Data is accessed and returned

The storage manager retrieves the required data from disk or memory.
If indexes are available, they are used to speed up access.
The DBMS returns the result to the user, and if the operation changes data, it also updates the database while preserving consistency and durability.

Advantages / Applications

Data independence and abstraction

Users can work with data without knowing physical storage details.
Changes in storage structure often do not require changes in application programs.

Efficient data management and multi-user support

A DBMS architecture supports large volumes of data and many users at the same time.
It provides concurrency control, recovery, and optimization mechanisms.

Used in real-world systems

Banking systems, railway reservation systems, hospital management, university portals, e-commerce websites, and inventory control systems all depend on DBMS architecture.
It ensures secure, organized, and fast access to critical data.

Summary

DBMS architecture is the design structure that explains how a database system stores and manages data.
The three-level architecture divides the system into external, conceptual, and internal levels.
DBMS components such as the query processor, storage manager, and transaction manager work together to process requests efficiently.
Important terms to remember
Data abstraction
Data independence
External level
Conceptual level
Internal level
Query processor
Storage manager
Transaction manager