How is a database schema different from a database instance?
Interview preparation resource from Gate Smashers.
A database schema is the design or structure of a database (tables, columns, data types, relationships, keys and constraints); a database instance is the actual data stored in that database at a particular moment. The schema is the blueprint and remains relatively stable, while the instance is the current content that changes when rows are inserted, updated, or deleted.
Schema — the design
The schema is the database's design or structure. It defines what objects exist and how they relate to each other.
- Defines: table names, columns, data types, relationships, keys, and constraints
Instance — the current data
The instance is the actual data stored in the database at a particular moment in time. It consists of the rows and values that conform to the schema.
- Contents: actual rows and values
- Changes: modifies whenever rows are inserted, updated, or deleted
Concrete example
An example helps distinguish the two: the schema specifies the structure, while the instance holds specific records.
- Schema example: Student(id, name, email)
- Instance example: (101, Asha, a@x.com)
Analogy
A simple analogy: the schema is the blueprint of a building; the instance is the current state of what is inside that building.