Home Three-Schema Architecture

Three-Schema Architecture

Understanding the fundamental architecture that provides data independence through multiple abstraction levels

Architecture Overview

The Three-Schema Architecture, also known as the ANSI/SPARC architecture, is a framework that separates the database into three distinct levels of abstraction. This separation provides data independence and allows different users to view the same data in different ways.

External Level

User Views

Individual user perspectives and applications

Student View
Faculty View
Admin View
External/Conceptual Mapping

Conceptual Level

Logical Structure

Complete logical view of the entire database

Entity Relationships
Constraints
Security Rules
Conceptual/Internal Mapping

Internal Level

Physical Storage

Physical storage structure and access methods

File Organization
Indexing
Storage Allocation

Data Independence Benefits

Logical Data Independence

Changes to the conceptual schema don't affect external schemas or application programs.

Example Scenario:

Adding a new table or modifying existing table structure without affecting user views.

Physical Data Independence

Changes to internal schema don't affect conceptual or external schemas.

Example Scenario:

Changing file organization or adding indexes without affecting logical structure.

Interactive University Database Example

Explore how the same university database appears at different schema levels:

Student Information System View

Student ID Name Major GPA
S001 Rajesh Kumar Computer Science 3.8
S002 Priya Sharma Mathematics 3.9
S003 Arjun Patel Data Science 3.7
S004 Ananya Reddy Information Technology 3.95
S005 Vikram Singh Artificial Intelligence 3.85

This view shows only information relevant to student services staff.

Complete Logical Database Schema

STUDENT

  • StudentID (PK)
  • FirstName
  • LastName
  • Email
  • PhoneNumber
  • Address
  • MajorID (FK)
  • AdmissionYear
  • CGPA
enrolls in
Many-to-Many

COURSE

  • CourseID (PK)
  • CourseName
  • Credits
  • DepartmentID (FK)
  • Semester
  • Prerequisites
  • MaxEnrollment

DEPARTMENT

  • DepartmentID (PK)
  • DepartmentName
  • HOD_Name
  • Building
  • ContactEmail

Complete logical structure with all entities, relationships, and constraints.

Physical Storage Implementation

File Organization

STUDENT table stored as heap file with clustered index on StudentID for efficient data access in Indian university system

Index Structures

B+ tree index on StudentID, hash index on Email for quick student lookup across multiple campuses

Buffer Management

LRU replacement policy, 64KB page size optimized for handling large student datasets efficiently

Data Security & Backup

Encrypted storage with daily backups to ensure student data privacy compliance with Indian IT regulations

Physical implementation details invisible to upper levels.

Real-World Business Benefits

Enhanced Security

Users only see data relevant to their role, improving security and reducing complexity.

System Flexibility

Database structure can evolve without breaking existing applications.

Multiple User Views

Different departments can have customized views of the same data.

Performance Optimization

Physical storage can be optimized without affecting logical design.