DBMS Topics
Schema and Instance
Last Updated : 21 May, 2026
A schema is the overall logical structure or blueprint of a database. It defines:
What is a Schema?
A schema is the overall logical structure or blueprint of a database. It defines:
- What tables exist
- What columns each table has
- Data types of each column
- Constraints (primary keys, foreign keys, NOT NULL, etc.)
- Relationships between tables
The schema is defined at the time the database is designed and changes infrequently. It corresponds to the intension of the database.
Schema Definition (Blueprint):
CREATE TABLE Student (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
dob DATE,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);What is an Instance?
A database instance (also called the database state or database snapshot) is the actual data stored in the database at a particular moment in time. The instance changes constantly as data is inserted, updated, or deleted.
It corresponds to the extension of the database.
Student Table
| student_id | name | dob | dept_id |
|---|---|---|---|
| 1001 | Alice | 2002-05-10 | 1 |
| 1002 | Bob | 2001-11-22 | 2 |
| 1003 | Carol | 2003-02-14 | 1 |
Instance (actual data at time T)
Schema vs. Instance Comparison
| Aspect | Schema | Instance |
|---|---|---|
| Definition | Structure/blueprint of the database | Actual data values at a given time |
| Also called | Intension | Extension |
| Change frequency | Rarely changes | Changes constantly |
| Defined by | DBA / Database designer | Data entry and DML operations |
| Analogous to | Variable declaration in programming | Variable's value at runtime |
| Stored in | System catalog (data dictionary) | Database storage files |
Types of Schemas (Three-Level Architecture)
Corresponding to the three levels of architecture, there are three types of schemas:
External Schema
View LevelMultiple schemas: one per user group or application
Example: HR sees Name, Dept; Payroll sees Salary
Conceptual Schema
Logical LevelSingle schema describing the entire database
Tables, columns, relationships, constraints
Internal Schema
Physical LevelOne schema describing physical storage
File structure, indexes, storage allocation
Subschema
A subschema is a subset of the conceptual schema that defines what a particular user or application can see. It is essentially the same as an external schema or view.
Schema Evolution
Although schemas change less frequently than instances, they do change over time (called schema evolution):
- Adding a new column to a table
- Adding a new table
- Changing a data type
- Adding or dropping constraints
Schema Evolution Example
Student(ID, Name, Dept)Student(ID, Name, Dept, Email)added column
Student(ID, Name, Dept, Email, CGPA)added column
A well-designed DBMS handles schema changes with minimal impact on existing applications.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Schema and Instance.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, schema, and, instance
Related DBMS Topics