Get a skill gap analysis, personalised roadmap, and AI-powered resume optimisation.
Introduction
Schemas and instances determine the structure and content of a database in DBMS.
An instance is a particular set of data that aligns with the schema, whereas a schema is the logical structure of a Database. Knowing the difference between schema and instance is crucial to design an effective database system.
Let's use computer programming to help us understand schemas and instances. In computer programming, a data type is like a schema, as it defines the structure of stored data and operations we can perform.
Similarly, a variable is like an instance, as it represents a specific set of data that follows the data type. This article will explore the differences between schema and instance in detail.
What is a Schema?
A schema represents the logical structure of a database. It is a blueprint that defines how data should be stored and organised in a database. A schema also describes what data can be stored in which table and how these tables are related to each other. Schemas ensure consistency and integrity of data across the database. A database schema is typically designed by a DBA (Database Administrator).
For example, the following image shows a schema for a database that maintains a record of students, courses and teachers.
Types of Schema
There are three types of schema in DBMS based on the level of abstraction.
Let’s discuss them one by one:
Internal Schema (Physical Schema)
It includes the database design at a physical level. It defines how data is physically stored in storage devices, the indexing methods used, etc. This schema is hidden from the users and managed by the DBMS.
Conceptual Schema (Logical Schema)
This schema describes the logical structure of the database, including the objects in the database, the relationship between them, and constraints applying to the data.
The conceptual schema is independent of any changes to the physical schema.
External Schema (View Schema)
This schema defines the database views relevant to specific users. It represents how the data is visible to users. Most users only need access to a small subset of the database, so the view level of abstraction simplifies their interaction with the database.
What is an Instance?
An Instance is the collection of information stored in the database at a particular time. For example, if we have a table schema that stores student information, an instance would be the data stored in the table, i.e., all students' names, roll numbers, email addresses, etc. Instances change when insertion or deletion operations are performed on the database. Without an instance, a schema is useless as it contains no data.
For example, the following table shows an instance of the Students table (its schema is mentioned above):
Comparing Schema and Instance in DBMS
The following table shows the differences between a Schema and an Instance:
Parameters
Schema
Instance
Definition
A schema describes how data is stored in the database and its relationships.
An instance represents the data stored in the database at a particular instant.
The analogy with programming languages
It is analogous to a data type in a programming language.
It is analogous to a variable in a programming language.
The Language Used
We can create a schema using the Data Definition Language (DDL).
We can create a new instance using the Data Manipulation Language (DML).
Lifespan
A schema stays the same until the organisation of the database is changed.
An instance changes when any insertion, deletion, or similar operation occurs.
Who defines it?
The Database Administrator is responsible for determining the database schema.
Any user with appropriate permissions can modify an instance.
Visibility
It is not visible to a user.
It is visible to a user.
How to view it?
In MySQL, we can use the following query to view a schema:
DESCRIBE Students;
In MySQL, we can use the following query to view an instance:
Yes, a database can have multiple instances, each representing a different set of data that conforms to the same schema.
How is schema different from database design?
The schema describes the logical structure of the database, while database design involves creating the schema and implementing it in a physical database system.
What is the purpose of an external schema in DBMS?
An external schema, or a user schema or view schema, defines how a particular user or group views the data in the database. It offers a personalised view of the database that considers the user's requirements and preferences.
How do database administrators use schema and instance information to optimise database performance?
Database administrators can use schema information to identify performance obstacles like ineffective queries or poorly designed tables. By indexing frequently accessed data and reducing disc I/O operations, instance information can enhance query performance and speed up response times.
Can we change a schema without affecting the data stored in the instance?
Yes, we can change a schema without affecting the data stored in the instance if the modifications do not violate any data constraints or relationships.
Conclusion
This article discussed the "Difference between Schema and Instance". By mastering the concepts of schema and instance, database designers can create robust and reliable systems that meet the needs of their users.
7+ registered 
