Introduction
Database Management System is one of the most important topics for coding interviews. The three schema design arranges software systems in database management systems (DBMS). The three-schema architecture divides the data in a database into an external layer, a conceptual layer, and an internal layer.
The three schema architecture of DBMS separates its conceptual and physical layers. Because of this, modifications can be made to one layer without affecting the other layers. Let’s now dive into the concept behind the three schema architectures of DBMS and understand every layer of it in detail.
Three Schema Architecture
In database management system (DBMS), schema refers to the logical aspect or logical structure of the Database, which tells us about how data is stored and accessed. Architecture refers to the overall design and organization of the database. The three schema architecture separates the logical and physical aspects of the DBMS. This allows changing one layer of the database without affecting the other layers. It also helps to maintain data integrity and consistency.
The three layers of a three-schema architecture are:
- External layer
- Conceptual layer
- Internal layer
External Schema
In DBMS, the External layer provides a logical view of the database. The External layer is the database portion that users can access and use. It is the topmost layer designed to provide a user-friendly database interface.
Let us understand this through an example of an Employee Management system, where an employee login into the system, and the system shows the employee’s details.
Conceptual schema
The Conceptual schema is the database section that tells the difference between all the different data sets. It represents the structure of a database. In an employee database, it describes columns or attributes of the table.
It can also be called a high-level representation of a database. The conceptual schema is mostly represented by the Entity-relationship Model ( ER Model ), which uses symbols to represent the data elements and relationships visually for a specific system. In an ER Model, the database is represented by ER Diagram.
Now Let us consider the Employee management system. The ER Diagram for the system would look like the following.
This ER Diagram illustrates the relationships among the Employee, Department, Employee's Role, and Login System.
Internal schema
The Internal schema, also known as Physical Schema, is a database section where all the data is kept and arranged. Here we need to decide where data should be stored and how it should be stored.
The Key Features of Internal Schema include the following.
- It determines how the data is stored in the database.
- It creates indexes to the data so the records can be accessed quickly.
- It compresses the data in a form such that the quality of data is not lost and it takes up less space.
- It divides large tables into smaller partitions for better management and performance.
- It also includes security features so the data is never breached or hacked.
Also See, File System vs DBMS
568+ registered 
