What are instance and schema in DBMS?

 

Instances and Schema in Database Management Systems (DBMS):

The database changes over time as data or information is inserted, updated and deleted. An instance of the database can be defined as the collection of information stored in the database at a particular moment.

OR

The state of database at any particular moment of time i.e. the collection of data, information along with various object definitions is called the instance of the database.

 

Whereas, the overall design of the database is called the database schema. In database terminology, the structure is a schema and instance of a schema is a database. We can better describe the concept of schemas and instances by considering an analogy to a program written in a programming language. 

 

A database schema corresponds to the variable declarations along with associated type definitions in a program. Each variable has a particular value at a given instance of time. These values of the variables in a program at a given time correspond to an instance of a database schema.

 

Database systems have several schemas that are divided according to the levels of abstraction. The physical schema describes the database design at the physical level while the logical schema describes the database design at the logical level.

 

A database may also have several schemas at the view level. They are generally called as subschemas that describe different views of the database. The logical schema is the most important in terms of its effect on application programs as programmers construct applications by using the logical schema.

 

The physical schema is hidden beneath the logical schema and can usually be changed easily without affecting application programs. Application programs are said to exhibit Physical Data Independence if they do not depend on the physical schema and so they need not be rewritten if the physical schema changes.

 

 A database schema defines its entities and the relationship among them. The database schema is a descriptive detail of the database which can be depicted by means of schema diagrams.

 Differences between schema and instance:

SN	Schema	Instance
1	The database schema describes the logical view of an entire database.	A database instance is a state of operational database with data at any given time.
2	It tells about how the data is organized and how they are related with each other.	It is a snapshot of database. It tends to change with time.
3	A schema is called as intension.	An instance is called as extension.
4	A database schema does not contain any data or information.	An instance contains information which changes when updates are done.
5	It display only names of record types (entities) and names of data items (attributes) and does not show the relationships among the various files.	An instance will be a set of values of attributes at a given time.
6	The schema will be same but, values in it will change.	An instance of a database changes from time to time.
7	Example:  Student Schema RN Name Address Class Phone Email	Example: Student Instance RN Name Address Class Phone Email 101 Ajay Arvi Naka, Wardha XII 123456 ajay@test.com