Three Schema Architecture of DBMS: Examples

An organized collection of data that is efficiently accessible and simple to modify is called a database. It is a storage place for data in its various forms and is used for storing and retrieving data. 

Databases can be either centralized or decentralized, based on their design and data storage and retrieval methods.

Centralized and Decentralized Databases

• Centralized Database: In a centralized database architecture, all of the data is contained within and managed from one single location or server. Users can access the database remotely via a network connection, yet all of the control and data administrative activities occur within that central point of control. Centralized databases simplify the administration, backup and maintenance of the data, but may present difficulties with respect to scalability and fault tolerance.
• Decentralized Database: A decentralized database is one in which the data is distributed across localized locations or servers, lacking a centralized point of control. Each site may contain its own data copy as well as share data with other sites, which is all enabled by the fact that data is decentralized. The advantage of decentralized is that it enables better scalability and fault tolerance; however, it may also introduce more complexity with regard to data consistency and management.

Instance: An instance of a database is the condition of data stored at a specific time. It is a point-in-time snapshot of the database containing the data stored therein at that moment. When data is inserted or removed from the database, its instance changes accordingly. Instances are dynamic and can be of different sizes and content.

Schema: The Schema of a database is the overall plan or design of a database. A schema determines the structure of the database, the tables, relations, constraints, and what data can be stored. A schema is the basis on which the data will be structured and does not change throughout the system. In contrast to the instance, the schema is very stable and changes very rarely, usually while making design modifications or altering the structure of the database.

Here are the differences between schema and instance:

The Three Schema Architecture describes a database management system (DBMS) separation of data into three layers of abstraction to improve database management, security, and modification without impacting the stability of the entire system. 

The Three Schema Architecture can be thought of as three `layers' of separation that ultimately keep the database manageable, organized and flexible. This could resemble popular full-stack website configurations in which the user interface, backend logic, and a database are clearly defined with a separation between the three layers. 

The Three Schema Architecture consists of the Internal, Conceptual, and External schemas. Each of these three layers has its own function for how the information will be stored, organized, and accessed within the database system. 

1. Internal Schema (Physical Level)

The Internal Schema is the lowest level in the Three Schema Architecture. It describes how the data is actually stored within the physical computer of the database system itself. 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 example, during installation, MySQL gives users the ability to choose between the XA-compliant InnoDB engine and the read-optimised 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 are made up of tables, with various types of connections between tables. Each table is made up of rows of data that are logically related using fields. Designing a database and specifying relationships and organization of the data is not an easy task. However, this is a critically important aspect 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.

For example, in the employee database, the Conceptual Schema defines the entities of employees, departments and projects, and specifies the relationships between entities (e.g., each employee is in a department). 

3. External Schema (View Level)

The External Schema describes how end users, or a user group, will 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.

Quick Recap: What We’ve Learned So Far

• The Three Schema Architecture is like the blueprint of how databases organize, store, and present data across three layers, making them efficient, secure, and adaptable.
• The Internal Schema handles the physical storage of how data is actually stored, indexed, and retrieved from disk.
• The Conceptual Schema focuses on the logical structure defining entities, relationships, and rules that shape the database.
• The External Schema provides user-specific views, showing only the data relevant to each user or role.

Together, these three layers define data independence. When a change occurs in one layer, such as storage, user, or user interface, it does not affect any of the other layers, leading to a database that is stable and easier to maintain. 

In the three schema architecture of DBMS, mappings are critical mechanisms that establish and facilitate data connections and translational operations between the three schemas, namely, the internal, conceptual, and external schemas. These mappings ensure that despite the separation of concerns at each level, the database system operates as a unified whole, maintaining data consistency, independence, and security.

What are Mappings?

Mappings are the defined correspondences and transformations that translate data formats, structures, and constraints from one schema level to another. Mappings act as bridges that allow each of the three schema layers to function independently while maintaining their interaction with each other seamlessly. 

Types of Mappings

1. External–Conceptual Mapping:
   
   • Connects the conceptual schema (logical structure) to each external schema (user view). 
   • Enable the user to view, access and interact with data as it is efficient for the user to do and as if the underlying logical structure and the databases' change. 
   • Support consistency and independence across the schemas. Ensure that if there are optimizations and hardware changes at the internal schema, it does not require changes to the user's access to all the views and security measures. 
2. Conceptual–Internal Mapping:
   
   • Connects the conceptual schema (logical structure) to the internal schema (physical storage).
   • Ensures that changes in the storage method or physical organization of data do not affect the logical structure seen by administrators or users.
   • Supports data independence and consistency, allowing for optimizations or hardware changes without disrupting user operations.

Why are Mappings Important?

• Data Independence:
  Mappings allow the database management system to provide logical and physical data independence. This means that changes could be made to the data, storage structure, or the underlying logical design at the internal (physical) level without really requiring the users to change or to lose sight of the logical structure of the data or their views. Any change made at the logical level does not affect their view or their access to the data. 
• Data Consistency and Integrity:
  By explicitly stating how the data is impacted across levels, mappings help provide consistency and integrity for the data across the system. 
• Customization and Security:
  Mappings allow different user categories to have custom data views; therefore, they can restrict access and expose only relevant sections of what the user can view. 
• Simplified Maintenance:
  Allowing database administrators to modify the storage structure and logical design of the database independently of one another reduces the probability for making errors and or complications when upgrading or moving the database. 

Example:
If an organization decides to change how employee records are stored on disk (internal schema), the conceptual schema and all user views (external schemas) remain unaffected because the mappings handle the necessary translations automatically.

The three schema architecture is a popular option in environments where data abstraction, security, and flexibility are very important. Layers make it versatile in a number of practical cases and systems: 

• Enterprise Database Systems: Large organizations that require the management of complex data for multiple groups of users often find the three schema architecture to be a good option. 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 is particularly beneficial for environments with differing access levels for different user groups. 

Bottom Line

Three Schema Architecture of DBMS is advantageous because it allows for data independence, security, and flexibility across systems. Whether we are working with enterprise data, mobile apps, or BI tools, Three Schema Architecture allows seamless data updates or modifications, user views of the data, and secured access - and this can all be achieved with convenient interfaces. It is a powerful framework for managing complex data with multiple users. 

Data independence, an attribute of database management systems, refers to the ability to change the schema (structure) of the database at one level of the system without impacting the data schema or structure at the other levels of the system. Data independence supplies the abstraction of the data storage and the data storage schemes at various levels of the system, creating additional flexibility, maintenance, and protection from disruption of usage when a change occurs. 

Here are the differences between database architecture and tier architecture:

Note:

Data Independence allows database schemas to change at one level without affecting others, improving flexibility and maintenance.

While Database Architecture manages how data is stored and accessed, Tier Architecture separates application layers to enhance scalability, performance, and modularity.

It’s common to confuse the three schema architecture of DBMS with the three-tier (3-tier) architecture used in application design. Both approaches utilize layering, although they are for different purposes and at different levels of the architecture of the system.

Quick Note:

While Three Schema Architecture manages how data is structured and stored, Three-Tier Architecture manages how data is presented and processed; both are layered, but serve entirely different purposes in system design.

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 (fact table 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 is built on the premise that requirements change. Use modular designs, without hard-coding dependencies among schemas, in order to separate changes and foster scalability when the size of the data or unit of application becomes too great.

4. Maintenance and Updates

Regularly revisit and update schemas as necessary, in order to adapt to changes in business rules, reporting needs, or baseline performance. Try to design schema attributes for easy changes on each layer, so as to minimize interruption and ongoing maintenance burden.

The Three Schema Architecture of DBMS has many practical advantages that help make database systems more efficient, secure, and easier to manage:

1. Data Independence:
   Changes to one schema (e.g. storage structure within a layer or user views) will generally not affect other layers, facilitating flexibility and long-term maintainability.
2. Improved Data Security:
   Through the separation of data into different schemas, a user can only have access to data that they are entitled to view on an external level, protecting information integrity for sensitive data. 
3. Simplified Data Management:
   Each schematic layer serves a specific purpose, both with regard to storage, logical structure and user access or view. Making new or changed data easier to manage, since the existing structure and views remain intact. 
4. Customization for Users:
   Different user groups can have access to a seamless view of the same underlying data in a customized layer, simplify usability, and make each user group more productive.
5. Scalability and Optimization:
   Separation of concerns makes performance tuning of the system easier at each individual level separately, establishing the flexibility to be more scalable and performant.

Despite the benefits that Three Schema Architecture facilitates in terms of abstraction and flexibility, there are still limitations to it, which can impact its practicality.  

1. Complex Design and Maintenance:
   The design complexity is increased by the three schema layers (internal, conceptual, and external). The whole architecture also requires competent Database Administrators to ensure consistency across three schema layers.
2. Performance Overhead:
   The additional layers of abstraction can also lead to more sluggish data access because data processing requires each query to go through multiple layers before reaching the physical storage.
3. Increased Development Effort:
   Establishing and maintaining mappings between these schema layers will lead to a slow increase in time and resource consumption, especially in large or frequently changing systems.
4. Data Segregation Challenges:
   Aside from the benefit of improved data independence and security due to data segregation, data synchronization and integration across schemas will be more challenging. 

To conclude, the Three Schema architecture of DBMS underpins all modern database development by allowing users, application programmers, and data repositories to interact seamlessly. The three layers (internal, conceptual, and external) allow for complete data independence, better security, and maintenance. In fact, you will be able to design databases that are neat, scalable, and appropriate for real-world applications if you master this architecture.

Key Highlights of the Blog

• Clear Understanding of Layers: You learned how the Internal, Conceptual, and External Schemas work together to organize data efficiently in a DBMS.
• Core Concept – Data Independence: The architecture permits changes to one layer independently of others, which leads to better flexibility and ease of database maintenance.
• Practical Relevance: Real-world examples found in enterprise systems, web applications, and data warehouses demonstrate the applicability of this model in providing relatively secure and efficient data access.
• Comparison Made Simple: You explored how the Three Schema Architecture of DBMS differs from the Three Tier Architecture, clarifying database vs. application design roles.
• Modern Importance: The layered design supports today’s needs for scalability, customization, and performance optimization in database systems.

Big Takeaway: Understanding this architecture builds a strong foundation for advanced topics like DB design, query optimization, and data modelling skills every developer and DBA must master.

1. Mention 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 is the primary challenge 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 the compartmentalisation 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.

5. Do all users see the same data in the external schema?

No, the external schema allows each user or user group to have a customized view of the data. For example, an HR manager and a payroll specialist might see different subsets and formats of employee data, even though both access the same underlying database.

6. What is meant by “data independence” in the context of three schema architecture?

Data independence means that changes to the physical storage (internal schema) or logical structure (conceptual schema) of the database do not impact the way users interact with data (external schema). This makes the system more flexible and easier to maintain.

7. Is it possible to have more than one external schema?

Yes, a database can have multiple external schemas. Each external schema defines a unique user view, tailored to the needs and permissions of different user groups or applications.

8. Why might a small or simple database not use three schema architecture?

For small, single-user, or simple databases, implementing all three layers can add unnecessary complexity and overhead. In such cases, a single schema may suffice, and the benefits of abstraction and independence are less critical.

9. What is a common misconception about the three schema architecture?

A common misconception is that the three schema architecture and three-tier architecture are the same. In reality, they address different aspects of system design: one focuses on data organization within the database, while the other structures the overall application.