3-Schema Architecture

3-Schema Architecture of DBMS

What is the 3-Schema Architecture?

The 3-Schema Architecture (also called ANSI/SPARC architecture) is a framework for database systems that separates the database into three different layers or levels. This design was proposed by the American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC) in 1975.

The purpose of this architecture is to separate the user applications from the physical database, providing different views of the database to different users while keeping the underlying structure hidden.

The Three Levels of the 3-Schema Architecture

The architecture consists of three levels:

1. External Level (User View Level)

Definition: The level closest to the users, describing how individual users or user groups see the data.

Characteristics:

  • Multiple external views can exist for a single database
  • Each view shows only the data relevant to a specific user or application
  • Hides complexity and details not needed by the user
  • Can present data in different formats for different users
  • Acts as a security mechanism by restricting what users can see

Example:

  • A student might see only their own academic records and course information
  • A professor might see all students enrolled in their courses and their grades
  • An administrator might see financial and registration data

2. Conceptual Level (Logical Level)

Definition: The community view of the database, describing what data is stored and the relationships between data elements.

Characteristics:

  • Represents the entire database in a logical structure
  • Independent of both physical storage details and individual user views
  • Includes all entities, attributes, and relationships
  • Contains integrity and security constraints
  • Serves as the integration point for all external views

Example: A university database’s conceptual level might include entities like Students, Courses, Enrollments, Professors, and Departments, with all their attributes and relationships defined logically without implementation details.

3. Internal Level (Physical Level)

Definition: The level closest to physical storage, describing how data is actually stored and accessed.

Characteristics:

  • Deals with physical storage structures
  • Covers file organization and access methods
  • Includes storage allocation, compression techniques, and encryption
  • Focuses on performance optimization
  • Describes indexes, record sequences, and other physical arrangements

Example: The internal level might specify that the Student table is stored as a B-tree structure, with certain fields indexed for faster access, and particular pages allocated on disk.

Key Components in Each Level

External Level Components

  • User views
  • Derived tables/views
  • Query interfaces
  • Form-based interfaces
  • Application-specific representations

Conceptual Level Components

  • Entity definitions
  • Relationship definitions
  • Attribute definitions
  • Integrity constraints
  • Security rules

Internal Level Components

  • Storage structures
  • File organizations
  • Access paths
  • Indexes
  • Hashing schemes
  • Compression techniques

Mappings Between Levels

To make the 3-Schema Architecture work, mappings are needed between the levels:

1. External/Conceptual Mapping

Purpose: Transforms requests and results between external views and the conceptual schema.

Function:

  • Translates user queries into operations on the conceptual schema
  • Converts data from conceptual format to the format expected by users
  • Handles different data types or units between levels
  • Implements security by filtering data

2. Conceptual/Internal Mapping

Purpose: Transforms requests and results between the conceptual schema and the internal schema.

Function:

  • Converts logical operations to physical operations
  • Maps logical data structures to physical storage structures
  • Translates logical addresses to physical addresses
  • Implements optimization strategies

Benefits of the 3-Schema Architecture

1. Data Independence

One of the primary advantages of this architecture is data independence:

Logical Data Independence

  • Changes to the conceptual schema don’t affect external views
  • New logical structures can be added without modifying applications
  • Relationships between entities can change without disrupting users

Physical Data Independence

  • Changes to the internal schema don’t affect the conceptual schema
  • Storage structures can be modified for performance without affecting logical data representation
  • New storage technologies can be adopted without changing the data model

2. Simplified User Interaction

  • Users only need to understand their own view
  • Complex database structures are hidden
  • Data can be presented in the most useful format for each user

3. Enhanced Security

  • Only authorized data is visible in user views
  • Sensitive data can be filtered out at the external level
  • Different security policies can be applied to different views

4. Better Change Management

  • Changes at one level minimally impact other levels
  • Database evolution is easier to manage
  • Legacy applications can continue to work with new storage structures

5. Centralized Control

  • Database administrators can manage the entire system from the conceptual level
  • Consistent policies can be applied across all data
  • Better integration of different data sources

Practical Implementation

While the 3-Schema Architecture is a theoretical framework, modern DBMS implementations incorporate many of its principles:

Examples in Real DBMS

  1. Views in Relational Databases:

    • SQL views provide external schemas
    • Base tables and their relationships form the conceptual schema
    • Storage engines and indexing implement the internal schema

  2. Object-Relational Mapping (ORM):

    • Application objects represent external views
    • Database tables represent the conceptual schema
    • Physical storage is handled at the internal level

  3. Data Warehousing:

    • OLAP cubes and reporting views form external schemas
    • Star or snowflake schemas form the conceptual level
    • Partitioning and indexing strategies form the internal level

Challenges in Implementation

Despite its benefits, implementing a pure 3-Schema Architecture faces challenges:

  1. Performance Overhead: Multiple transformations between levels can impact performance
  2. Complexity: Maintaining mappings between levels adds complexity
  3. Synchronization: Keeping all levels consistent when changes occur
  4. Tool Support: Limited tools specifically designed around this architecture

Summary

The 3-Schema Architecture provides a conceptual framework for organizing database systems that:

  1. Separates the user’s view from the logical structure
  2. Separates the logical structure from physical implementation
  3. Enables data independence at multiple levels
  4. Simplifies database management and evolution
  5. Enhances security and user customization

By understanding this architecture, database designers can create more flexible, maintainable, and user-friendly database systems that can evolve over time with minimal disruption.