Three Schema Architecture of DBMS: Examples

The Three Schema Architecture of 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.

The three schema architecture is widely used in environments where data abstraction, security, and flexibility are critical. Its layered approach makes it suitable for a variety of practical scenarios and system types:

• Enterprise Database Systems: Large organizations often use the three schema architecture to manage complex data with multiple user groups. For example, in an employee management system, HR, payroll, and management each require different user views (external schemas) while sharing the same underlying data structure (conceptual schema) and storage methods (internal schema).
• Web and Mobile Applications: Modern web browsers and mobile apps interact with databases using specific external schemas tailored to user roles and permissions. The architecture allows developers to modify the user interface or business logic without affecting how data is stored or structured at lower levels.
• Business Intelligence and Data Warehousing: In dimensional schema designs, such as star schemas (fact tables and dimension tables), the three schema architecture supports the separation of analytical user views from the underlying logical and physical data organization. This is especially useful in environments with complex reporting and analytics needs.
• Distributed and Decentralized Systems: One of the advantages of decentralized databases and network schema designs is that they can coordinate multiple user views as well as logical schemas that are distributed in different nodes, hence guaranteeing consistency as well as data independence.
• Security-Sensitive Applications: The use of external schemas for access control allows for the protection of sensitive information from being accessed by unauthorized individuals. This feature is especially advantageous in situations where the user groups have different access levels to the data.

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:

It’s common to confuse the three schema architecture of DBMS with the three-tier (3-tier) architecture used in application design. While both use a layered approach, they serve different purposes and operate at different levels of system architecture.

Three Schema Architecture (Database Focus):

• Organizes the internal structure of a database into three layers: internal (physical storage), conceptual (logical structure), and external (user views).
• Focuses on how data is stored, structured, and accessed within the database.
• Aims to provide data abstraction, independence, and security at the database level.

Three-Tier Architecture (Application Focus):

• Divides an application into three distinct tiers:
  
  1. Presentation Tier: The user interface, such as web browsers or desktop apps.
  2. Application Tier: Business logic and processing, often handled by application servers or middleware.
  3. Data Management Tier: The backend where data is stored and managed, typically involving the database.
• Used in client-server architectures to improve scalability, maintainability, and security.
• Tiers communicate through APIs, middleware, and web servers, allowing each layer to scale or be updated independently.

Dimensional schema design is a type of schema design that utilises star schemas (tables comprising facts and dimensions) for reporting and querying data, as well as for analytical purposes, within a warehouse.

Hierarchical schema design and network schema design are models used for specific applications, such as tree models and graphs.

Key Considerations

1. Logical Schema Design

The conceptual (logical) schema describes the overall structure, relationships, and constraints of the data within the database system. The most common schema design approaches to logical schema design include: 

• Relational schema design: This approach organizes the data into tables that can be related to one another, providing for normalization and data integrity.
• Dimensional schema design: This type of schema design will use star schemas (table fact with table dimensions) for reporting and querying of data and for analytical purposes, in a warehouse.
• Hierarchical and network schema design: These models are used for specific applications such as tree models, and graphs.

2. Internal Schema Planning

The internal schema defines the physical layer and how and where data will be stored inside the database. For the internal schema:

• Choose the best data formats and indexing techniques for fast performance.
• Partition large tables to enhance scalability and manageability.
• Plan for the future for expansion opportunities to minimize disruption.

3. Flexibility and Scalability

Good schema design anticipates evolving requirements. Use modular structures and avoid hard-coding dependencies between schemas to allow for independent updates and scaling as data volume or application needs grow.

4. Maintenance and Updates

Regularly review and update schemas to reflect changes in business logic, reporting requirements, or system performance. Design for easy modification at each layer to minimize disruption and facilitate ongoing maintenance.

The following are the benefits of three schema architecture of dbms:

• Represents logical and physical characteristics of a database in a manner that changes to one layer will not affect others
• Offers data independence, i.e., changes to the internal schema are not implemented on the external or conceptual schema
• Security is improved since data access is controlled through the conceptual schema
• Improves performance as data retrieval and manipulation are maximized through the internal schema
• It facilitates the evolution and scalability of databases.Enables modular development and maintenance

Below are the disadvantages of three schema architecture of dbms:

• Increases complexity, with an increased development cost and learning curve
• Possible inconsistencies between the external, conceptual, and internal schemas
• May demand extra resources for schema management and development
• This may result in extra overhead and latency since the number of abstraction layers has grown

In conclusion, the three schema architecture in DBMS design is a crucial component of 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.

3. What is the DBMS 3 schema architecture?

The 3-schema model of DBMS segregates data into three tiers: internal, conceptual, and external. Such a model facilitates data abstraction and autonomy through compartmentalization of user views, logical schema, and physical storage of data.

4. What are the three architectures of a database?

The three architectures are 1-tier, 2-tier, and 3-tier. They determine the manner in which users access databases: directly (1-tier), via applications (2-tier), or via presentation, logic, and data layers (3-tier) with more scalability and manageability.