Three schema Architecture

by

The Three Schema Architecture, also known as the ANSI-SPARC Architecture, is a framework for organizing and managing data in a database system. It provides a conceptual and logical structure for representing data, separating the user’s view of the data (external schema) from the physical storage of the data (internal schema) and the overall organization of the data (conceptual schema).

The architecture consists of three main components or schemas:

  1. External Schema/View:
    • This schema represents the individual user’s or application’s view of the database.
    • It defines how data is organized and presented to specific users or applications.
    • Users interact with the database through this schema by executing queries or transactions.
    • Each user or application may have a different external schema tailored to their specific needs.
  2. Conceptual Schema:
    • The conceptual schema represents the overall logical structure of the entire database.
    • It defines the entities, their relationships, and the constraints on the data.
    • It provides a high-level view of the database, independent of any specific user or application.
    • Changes made to the conceptual schema affect the entire database.
  3. Internal Schema:
    • The internal schema represents the physical storage and implementation details of the database on the underlying hardware and software platform.
    • It describes how the data is actually stored and accessed.
    • It includes information such as file structures, indexes, and access paths.
    • Changes made to the internal schema are transparent to the users and applications interacting with the external schema.

The Three Schema Architecture provides a clear separation between the different levels of abstraction, allowing for flexibility, data independence, and modularity in database design and development. It also enables changes to be made in one schema without affecting the others, promoting easier maintenance and evolution of the database system.

Objectives of Three schema Architecture:

The Three Schema Architecture aims to achieve several objectives in the design and management of a database system. Some of the key objectives include:

  1. Data Independence: The architecture aims to achieve data independence, separating the way data is stored and accessed (internal schema) from how it is presented to users or applications (external schema). This allows changes to be made to one schema without affecting the others, providing flexibility and reducing the impact of modifications.
  2. Modular Design: The architecture promotes a modular design approach, where the conceptual schema serves as an intermediary between the external and internal schemas. Each schema represents a distinct level of abstraction, allowing for changes or enhancements to be made independently in each layer.
  3. Improved Database Maintenance: By separating the different schemas, the architecture simplifies the process of database maintenance. Changes made to the internal schema, such as modifications to storage structures or indexing techniques, do not require modifications to the external schema or the applications using the database. This reduces the potential for errors and streamlines maintenance activities.
  4. Enhanced Data Integration: The architecture enables the integration of data from multiple sources or databases by providing a common conceptual schema. Different external schemas can be defined to accommodate the specific requirements of different users or applications, while the conceptual schema ensures consistency and integrity of the data across the system.
  5. Database Evolution and Scalability: The architecture supports the evolution of the database system over time. As the requirements of users or applications change, modifications can be made to the external schema without impacting the conceptual or internal schemas. This flexibility allows the database to adapt and scale as the organization’s needs grow or change.
  6. Security and Access Control: The architecture facilitates the implementation of security measures and access controls at different levels. Access privileges can be assigned based on the external schema, providing granular control over the data users can access and the operations they can perform.

By achieving these objectives, the Three Schema Architecture provides a framework for effective database design, management, and evolution, promoting flexibility, data integrity, and efficient use of resources.

Mapping between Views:

In the context of the Three Schema Architecture, mapping between views refers to the process of defining the relationships and transformations between the external schemas (views) and the conceptual schema. These mappings ensure that data presented in different views remains consistent and accurate, despite being derived from a common underlying conceptual schema.

When multiple users or applications have their own customized views of the data, mapping between views helps in achieving data integration and maintaining data integrity. Here are a few key points regarding mapping between views:

  1. View Definitions: Each view or external schema is defined independently, specifying the subset of data and the organization required by a particular user or application. The view definition includes the selection of relevant tables, columns, and any filtering or joining conditions.
  2. Conceptual Schema Mapping: The mappings between views and the conceptual schema establish the relationship between the external schema and the underlying data model. This mapping ensures that the data presented in different views aligns with the overall structure defined in the conceptual schema.
  3. Data Transformation: In some cases, views may require data to be transformed or aggregated before being presented. The mapping between views specifies any necessary transformations, such as calculations, aggregations, or formatting, to derive the desired view data from the underlying data model.
  4. Data Consistency and Integrity: Mapping between views helps maintain data consistency and integrity across different views. It ensures that any modifications, insertions, or deletions made through one view are reflected in other views and the underlying conceptual schema.
  5. Security and Access Control: Mapping between views also plays a role in enforcing security and access controls. The mapping defines the access privileges and restrictions associated with each view, determining the level of data visibility and the allowed operations for specific users or applications.

Overall, mapping between views in the Three Schema Architecture enables data integration, consistency, and customization while ensuring adherence to the underlying conceptual schema. It helps bridge the gap between different user perspectives and the centralized data model, facilitating effective data management and usage in a multi-user or multi-application environment.