Three Schema Architecture of DBMS -Topperworld

Three Schema Architecture of DBMS

The Three Schema architecture, also known as the ANSI/SPARC architecture, is a framework for organizing the components of a Database Management System (DBMS) into three distinct layers. Each layer serves a specific purpose and abstraction level, allowing for separation of concerns and modularity in database design and management.

In the below PDF we discuss about Three Schema Architecture of DBMS in detail in simple language, Hope this will help in better understanding.

The three layers of the three-schema architecture are:

1. External Layer (View Level):

The external layer, also referred to as the view level or user interface level, is the topmost layer of the architecture.
It represents the part of the database system that interacts directly with end-users, applications, and external systems.
The external layer consists of external schemas, which define the logical view of the data for specific user groups or applications.
Each external schema presents a tailored view of the database, hiding irrelevant details and providing a simplified and intuitive interface for users to access and manipulate data.
External schemas are defined using high-level data models and query languages that are suitable for the needs and preferences of individual user groups.

2. Conceptual Layer (Logical Level):

The conceptual layer, also known as the logical level or conceptual schema, is the intermediate layer of the architecture.
It represents the logical structure and organization of the entire database system, independent of any specific implementation details or physical storage considerations.
The conceptual layer consists of a single conceptual schema, which serves as a global, integrated view of the entire database.
The conceptual schema defines the logical structure of the data, including entities, attributes, relationships, constraints, and integrity rules, using a conceptual data model such as the Entity-Relationship (ER) model or the Unified Modeling Language (UML).
The conceptual schema provides a unified and consistent representation of the underlying data, serving as a common reference point for all users, applications, and external systems accessing the database.

3. Internal Layer (Storage Level):

The internal layer, also called the storage level or internal schema, is the bottommost layer of the architecture.
It represents the physical storage and organization of data within the database system, including data structures, access methods, and storage allocation mechanisms.
The internal layer consists of internal schemas, which define how data is stored, indexed, and accessed on the underlying storage devices (e.g., disks).
Internal schemas are optimized for efficiency, performance, and resource utilization, taking into account factors such as data compression, indexing strategies, and storage allocation algorithms.
Internal schemas are typically implemented using low-level data models and storage structures that are specific to the DBMS platform and underlying hardware architecture.

Advantages and Disadvantages of using Three Schema Architecture:

There are various Advantages to employing a three-schema architecture in database management systems. Some of the benefits include:

One of the primary advantages of a DBMS’s three schemas is data independence. All three levels are separate from one another. As a result, we can update one layer without affecting the others.
Each schema may scale separately, allowing the database to perform better while also handling more traffic.
Because the layers are separated in a three-schema design, it is easier to maintain and alter each one individually.

Disadvantages:

Despite its many advantages, the three schema architecture has a few drawbacks:

This strategy can be challenging and costly for large corporations because it requires a lot of effort to set up and maintain.
It can also cause delays and errors if the data is not properly translated between the various components.
It can also be difficult to ensure that only authorised individuals have access to sensitive information.

Conclusion:

In Conclusion, the three-schema architecture of a DBMS provides a conceptual framework for organizing and separating the database system into three distinct layers: external layer (view level), conceptual layer (logical level), and internal layer (storage level). This architecture promotes data independence, modularity, and flexibility in database design, management, and maintenance.