Architecture of Database Management System

The DBMS architecture defines how users see the data in the database. It’s crucial for implementing, designing, and maintaining databases for businesses. DBMS architecture can be centralized, decentralized, or hierarchical.

The architecture of DBMS can be defined at three levels. The three-schema database architecture is a framework for organizing and managing data in a database system

The external schema
The conceptual schema
The internal schema

schema refers to the overall structure and organization of a database. It defines the logical and physical arrangement of the data, including tables, relationships, attributes, and constraints.

Constraints are rules or conditions that are defined and enforced on the data within a database. They ensure the integrity, accuracy, and consistency of the data by imposing restrictions or requirements on the values that can be stored in the database tables.

There are different types of constraints commonly used in databases

Primary Key Constraint
Foreign Key Constraint
Unique Constraint
Not-Null Constraint

Table of Contents

Architecture of Database Management System

External Schema

This is the layer that deals with how individual users or applications view and interact with the database.
The external schema represents the customized views or subsets of data that are relevant to each user or application.
For example, one user might view sales data, while another user view employee records.
This layer helps in providing data security and confidentiality to different users.
External/conceptual mapping refers to the relationship or mapping between the external schema and the conceptual schema.

Conceptual Schema

Conceptual schema at the conceptual level to describe the structure and constraints for the entire database.
It defines the relationships and logical connections between different data entities (such as tables or objects) and their attributes within the database.
It focuses on the meaning of the data rather than the physical storage details.
Conceptual/ Internal Mapping defines how the conceptual schema is represented and implemented at the internal schema level.
We can modify the conceptual structure of the database without affecting the access or operations of other users.

Internal Schema

The internal schema deals with the physical storage and implementation details of the database. It describes how the data is stored on the underlying hardware, such as hard drives or memory.
It includes details like file structures, indexing techniques, and access methods. It optimizes the performance of the database system by determining the most efficient way to store and retrieve the data.
It determines the storage format for different data types and data Partitioning, uses compression techniques to reduce storage space, and employs indexing to speed up data retrieval.

Three Schema Architecture – Library Example

The external schema is like the library card catalog, where each user has their own catalog customized to their interests. They can see and access specific books based on their preferences.
The conceptual schema is like the library’s classification system, which categorizes and organizes all the books logically. It determines how books are related to each other, like grouping them by genre or subject.
The internal schema is like the physical storage of the books in the library. It determines where each book is placed on the shelves, how they are organized, and how they can be efficiently retrieved when needed.