Instance and schema in DBMS

Instance and Schema are the most irreplaceable terms in Database Management System. These two are the main reason behind the existence of a structured database. Let’s look into Instance and schema in DBMS here.

Instance and schema in DBMS

Instance

An instance is defined as the data or collection of information stored in a database at a particular moment of time. Data changes quite frequently in the case of an instance. That means, whenever we add a new record or data in the database, data will be updated.

The database is alterable through operations like add, deletion of data in the case of an instance. The search query will not make any kind of changes in the instances. It is considered as a state of operational database with data at any given time. It consists of a snapshot of the database.

Every DBMS ensures that it’s every instance (state) is in a valid state, by diligently following all the validations, constraints, and conditions that the database designers have imposed.

Example for Instance

Student

Instance and schema in DBMS - example 1

Schema

Schema is defined as the overall description of the database, or simply it is the design of a database. A schema depicts the basic structure of how the data will be stored in the database.

A database schema is considered as the skeleton structure of a database and it represents the logical view of the entire database. Generally, a scheme is comprised of schema objects like Table, Foreign Key, Primary-Key, Views, Columns, Datatypes, Stored Procedures, etc.

Generally, it is designed when the database doesn’t exist at all, and also, a database schema does not contain any data or information. Suppose, the database is operational, then it is very difficult to make any changes to it.

Structure of a Database Schema

Instance and schema in DBMS - schema structure

Example for Database Schema

Instance and schema in DBMS - database schema example

Database Systems are comprised of complex data structures. Therefore, in order to increase the efficiency of the system and reduce the complexity of the users, developers use the method of Data Abstraction.

There are three levels of data abstraction namely: Internal level/ schema, Conceptual level/ schema, and External level/ schema.

Instance and schema in DBMS - three tier architecture

Types of  Database Schema

There are two types of Schema, such as:

  1. Physical Schema
  2. Logical Schema
  3. View Schema

Logical Schema:

It represents the logical level designing of the database. The corresponding schema defines all the logical constraints required to be applied to the stored data. It also defines Tables, Views, and Integrity Constraints.

Physical Schema:

This schema represents the physical level designing of the database. Physical schema pertains to the actual storage of data and its form of storage like files, indices, etc. It describes how the data will be stored in secondary storage.

View Schema:

The design of the database in view level is known as View Schema. Usually, this describes the end-user interaction with the database systems.

Advantages of Database Schema

  • We can manage data independent of physical storage.
  • Faster migration to new graphical environments.
  • DBMS architecture allows you to make changes on the presentation level without affecting the other two layers such as the physical and logical levels.
  • As each tire is distinct, it is possible to use different sets of developers.
  • If any failure occurred to anyone of the tier, no data loss as you are always secured by accessing the other tier.
  • It is more secure than the client doesn’t have direct access to database business logic.

Tagged in: ,

RELATED CONTENTS