What is instance and schema (DBMS)?

DBMSDatabaseBig Data Analytics

The overall design of the database is called database schema. Schema will not be changed frequently. It is the logical structure of a database. It does not show the data in the database.

The schema is pictorially represented as follows −

Types of Schema

The different types of schemas are as follows −

  • Physical schema − It is a database design at the physical level.It is hidden below the logical schema and can be changed easily without affecting the application programs.

  • Logical schema − It is a database design at the logical level. Programmers construct applications using logical schema.

  • External − It is schema at view level. It is the highest level of a schema which defines the views for end users.

Generally the Database Management System (DBMS) assists one physical schema, one logical schema and several sub or external schemas.

Database schema refers to the format and layout of the database in which the data will be stored. It is the one thing that remains the same throughout unless otherwise modified. It defines the structure of what type of data and how it will be stored.

Example

A database schema for a person will have fields for name, email, phone and address as shown below −

Person

NameEmailPhone no

Instance

Instance or extension or database state is a collection of information that stored in a database at a particular moment is called an instance of the database. The Database instance refers to the information stored in the database at a given point of time. Thus, it is a dynamic value which keeps on changing.

Example

A database instance for the Person database can be (User1,emai.com,11345679,addr) So the person construct will contain their individual entities in the attributes called as instance. This is shown below −

Person

NameEmailPhone no
BOBkksd@yasd.com2343435
JANUwerwr@sdas.in5345464
PRIYAwefrwer@sdf.com2342342

Differences

The major differences between schema and instance are as follows −

Database SchemaDatabase Instance
It is the definition of the database or it is defined as the description of the database.It is a snapshot of a database at a specific moment.
It rarely changes.It changes frequently.
Example” We take two tables emp table and dept table.Emp
Id
Name
Salary
dept
Dept
Dept_id
dname
Emp and dept both called as schemas
It gives database definition
At a moment, what is the value of the database schema is called instance.At t=8 A.M
Empidnamesalarydid
1A5000d1
2B2000d2
At t=9 A.M
3
C
3000
d3
Empid 1 and 2 are called as Instance 1
At time 9 A.M instance 2 changes
This corresponds to the variable declaration of a programming language.The value of the variable in a program at a point in time corresponds to an instance of the database schema.
raja
Published on 06-Jul-2021 13:35:08
Advertisements