Schema and Instance

Introduction

Understanding the distinction between schema and instance is fundamental to working with databases. These two concepts represent the difference between the structure of a database (its design) and the actual data stored in it at any given moment. This distinction is analogous to the difference between a building's blueprint and the building itself — the blueprint defines the structure, while the building contains the actual occupants and furniture at any point in time.

Instance: A Snapshot of Data

A database instance (also called the database state) is the collection of actual data stored in the database at a particular moment in time. Every time you insert, update, or delete records, the instance changes. The instance must always conform to the constraints defined in the schema.

The schema (Student table with its four columns) remained the same, but the instance changed — a new tuple was added.

Key Characteristics of Instance

• Changes frequently (with every INSERT, UPDATE, DELETE operation)
• Must satisfy all schema constraints at all times
• Represents the current state of the data
• Also called the extension of the database
• A valid instance is one that satisfies all integrity constraints

The Analogy: Schema vs Instance

Another useful analogy: consider a variable declaration in programming. The declaration int age; defines the schema (name and type). The value age = 25; is an instance. The variable can hold many different values over time (instances change), but its type declaration (schema) remains constant.

Schema at Three Levels of Architecture

The ANSI-SPARC three-level architecture defines schemas at each level:

External Schema (View Level)

Each user group has its own external schema — a subset or transformation of the full database.

Conceptual Schema (Logical Level)

This is what most people mean when they say "database schema" — the full logical design.

Internal Schema (Physical Level)

The internal schema defines storage structures, indexes, file organization, and access paths.

Schema Modification (Schema Evolution)

While schemas are designed to be stable, they sometimes need modification:

Schema changes are expensive because they may require:

• Reorganizing physical storage
• Updating application code that references the old schema
• Migrating existing data to conform to the new structure
• Updating views and stored procedures

Key Points to Remember

1. Schema defines structure; instance contains actual data — never confuse the two
2. A database has one schema but undergoes many instance changes throughout its lifetime
3. Every valid instance must satisfy all constraints specified in the schema
4. Schema exists at three levels (external, conceptual, internal) in the ANSI-SPARC architecture
5. Schema is created using DDL and stored in the system catalog; instances are modified using DML and stored in data files
6. The distinction between schema and instance enables data independence — you can change the internal schema (physical storage) without affecting the conceptual schema or the instances that users see