Three Schema Architecture of DBMS: Examples

The Three Schema Architecture in DBMS is a conceptual framework used to describe how data is organized in a database system. It divides the database into three different levels:

• Internal Schema (Physical Level)
• Conceptual Schema (Logical Level)
• External Schema (View Level)

Let’s explore each of these layers with examples and diagrams for better understanding.

1. Internal Schema (Physical Level)

The Internal Schema is the lowest level in the Three Schema Architecture. It describes how the data is stored in the physical computer the database system. This schema defines the file structures, indexing methods, and the actual storage mechanisms used by the DBMS. It focuses on the efficiency of data retrieval and the physical storage devices (e.g., hard disks, SSDs).For examples, during installation, MySQL gives users the ability to choose between the XA-compliant InnoDB engine and the read-optimized MyISAM engine. After the installation, the physical DBMS schema choice should be hidden from the administrators who manage database schemas, as well as developers who may need to query or update the data

The physical DBMS schema level is responsible for the following tasks:

• Index tables.
• Identify which types of storage media to support.
• Determine where to write log files.
• Perform low-level authentication.
• Select what data types to implement and how.

Example: Consider a database storing information about employees. The Internal Schema would describe how employee data is stored in a specific file format or how the data is indexed for fast retrieval (e.g., B-trees, hash indexing).

2. Conceptual Schema (Logical Level)

Databases consist of tables that are interconnected in various ways, with each table containing rows of data that are logically related through fields. Designing a database and determining the relationships between tables and data organization is a complex task, which plays a crucial role in the success of any software development project.

The conceptual schema layer of a Database Management System (DBMS) defines how data is structured within a database. It provides the tools for database administrators to create the logical layer of the database.

The Conceptual Schema sits between the Internal Schema and the External Schema and represents the logical view of the entire database while hiding the details of physical storage structures. It only defines the structure of the data, including relationships, constraints, and entities, And what kind of data is to be stored in the database. Database administrators and programmers often use this schema to define the database's core structure.

The administrator's choice of data types is an interesting aspect of the logical layer. All databases support a limited number of data types. In the logical layer, an administrator can choose between 32-bit integers, 64-bit floating point numbers or even binary large objects, or BLOBs, and character large objects, or CLOBs. However, those options are available at the logical layer only if they are implemented in the physical layer.

‍Example: In the case of the employee database, the Conceptual Schema would define entities like employees, departments, and projects, and how these entities are related (e.g., each employee belongs to a department).

3. External Schema (View Level)

The External Schema defines how individual users or user groups view the data. It represents the user perspective and focuses on the specific requirements of users while hiding the rest of the database from that user group. Depending on their access needs, users may have different views of the same data. The view schema describes the end-user interaction with database systems.

‍Example: For an employee database, one user may need to view the employees' personal information, while another may need to access employee performance data. Each of these user views is a part of the External Schema.

Data Independence is a key concept in database management systems (DBMS) that refers to the ability to change a database's schema (structure) at one level without affecting the schema at higher levels. It provides abstraction between different levels of data storage, making the system more flexible, easier to maintain, and less prone to disruptions when changes occur.

Here is the differences between database architecture and tier architecture:

Here are the advantages of the three schema architectures:

• Separates logical and physical aspects of a database, allowing modifications to one layer without impacting others
• Provides data independence, enabling changes to the internal schema without affecting the external or conceptual schema
• Enhances security by controlling access to data through the conceptual schema
• Improves performance by optimizing data retrieval and manipulation through the internal schema
• Supports database evolution and scalability
• Facilitates modular development and maintenance

Here are the disadvantages of the three schema architecture:

• Introduces complexity, requiring additional development effort and a learning curve
• Potential inconsistencies between the external, conceptual, and internal schemas
• May require additional resources for schema management and maintenance
• This can lead to increased overhead and latency due to the added layers of abstraction

In conclusion, the Three-Schema Architecture is a crucial component of DBMS design, enabling data independence, security, customization, and optimization. By understanding and implementing this architecture, database designers and administrators can create robust, scalable, and maintainable database systems that meet the evolving needs of users and applications.

1. What are the advantages of using the Three Schema Architecture in DBMS? 

The Three Schema Architecture offers data independence, better security, and data abstraction while providing flexibility for changes at different schema levels without disrupting the whole system.

2. What are the main challenges in implementing the Three Schema Architecture in DBMS? 

The Three Schema Architecture can increase system complexity and performance overhead. Implementing and managing multiple schemas requires skilled administrators and designers, which can be resource-intensive.