Instance and Schema in DBMS

What is DBMS Schema?

Here the DBMS schema means designing the database. For example, if we take the example of the employee table. The employee table contains the following attributes. These attributes are EMP_ID, EMP_ADDRESS, EMP_NAME, EMP_CONTACT. These are the schema of the employee table.

Schema is further divided into three types. These three are as follows.

1. Logical schema.
2. View schema.
3. Physical schema.

The schema defines the logical view of the database. It provides some knowledge about the database and what data needs to go where.

In DBMS, the schema is shown in diagram format.

We can understand the relationship between the data present in the database. With the help of this schema, we can implement the DBMS function such as delete, insert, search, update, etc.

Let us understand this by the below diagram. There are three diagrams, i.e., section, course, and student. This diagram shows the relationship between the section and the course diagram. Schema is the only type of structural view of the database that is shown below.

1. Physical schema:

In the physical schema, the database is designed at the physical level. At this level, the schema describes how the data block is stored and how the storage is managed.

2. Logical schema:

In the logical schema, the database is designed at a logical level. At this level, the programmer and data administrator perform their work. Also, at this level, a certain amount of data is stored in a structured way. But the internal implementation data are hidden in the physical layer for the security proposed.

3. View schema:

In view schema, the database is designed at the view level. This schema describes the user interaction with the database system.

Moreover, Data Definition Language (DDL) statements help to denote the schema of a database. The schema represents the name of the table, the name of attributes, and their types; constraints of the tables are related to the schema. Therefore, if users want to modify the schema, they can write DDL statements.

What is DBMS Instance?

In DBMS, the data is stored for a particular amount of time and is called an instance of the database. The database schema defines the attributes of the database in the particular DBMS. The value of the particular attribute at a particular moment in time is known as an instance of the DBMS.

For example, in the above example, we have taken the example of the attribute of the schema. In this example, each table contains two rows or two records. In the above schema of the table, the employee table has some instances because all the data stored by the table have some instances.

Let's take another example: Let's say we have a single table student in the database; today, the table has 100 records, so today, the instance of the database has 100 records. We are going to add another 100 records to this table by tomorrow, so the instance of the database tomorrow will have 200 records in the table. In short, at a particular moment, the data stored in the database is called the instance; this change over time as and when we add, delete or update data in the database.

Differences between Database Schema and Instance

Both of these help in describing the data available in a database, but there is a fundamental difference between Schema and Instance in DBMS. Schema refers to the overall description of any given database. Instance basically refers to a collection of data and Information that the database stores at any particular moment.

The major differences between schema and instance are as follows: