Database Schema
The overall design (blueprint) of a database is called the database schema. It defines:
- Tables
- Columns (attributes)
- Data types
- Relationships
- Constraints
The schema does not contain actual data it is just the structure.
The schema is decided once, at the time of designing the database. It rarely changes after that.
Easy way to remember: It’s just like a variable’s data type in programming. The data type doesn’t hold the value it just tells what type of value is allowed. In the same way, the schema tells what structure the data must follow.
Types of Schema: Three-Schema Architecture
Physical Schema
Describes the database at the physical level, explaining how data is actually stored on storage devices such as disks. It includes details like files, storage blocks, indexes, and access methods.
Logical (Conceptual) Schema
Describes the database at the logical level, including tables, columns, relationships, and constraints. It represents the complete logical structure of the database without focusing on how the data is physically stored.
View (External) Schema
Describes the different views of the database created for different groups of users. Each user or group can see only the data that is relevant to their requirements.
Database Instance
The actual data present in the database at a particular moment is called a database instance. It is also known as the database state. Unlike the schema, the database instance changes frequently whenever data is inserted, updated, or deleted. Therefore, the database can have a different instance or state at different points in time.
Key Difference
| Basis | Schema | Instance |
|---|---|---|
| Meaning | Structure/design of database | Actual data stored at a given time |
| Change frequency | Rarely changes | Changes frequently (with every insert/update/delete) |
| Also known as | Intension of database | Extension / state / snapshot of database |
| Analogy | Like a variable’s declared type | Like the value stored in that variable |