Instances, Schemas and Subschema in DBMS

Database Schema

The overall design of a database is called the database schema. In other words, schema is an overall structure of a database. Schema defines how database is stored, relationship between entities and attributes and so on.  

Database systems have several schemas, partitioned according to the level of abstraction.

Physical Schema: The physical schema describes database design at physical level. Physical schema defines how database is stored. It defines how data is stored in secondary storage.

Logical schema: The database design at the logical level is called logical schema. This schema defines logical structure of database. Usually programmer and DBA work at this level. Logical schema defines tables, constraints of a database.

Database Instance

The collection of information stored in a database at a particular moment is called database instance. Database changes every time when information is inserted and deleted to/from database. A database schema is variable declarations in a program. Variable has particular value at a given instant. Then, the value of variable at particular instant is called database instance.

Subschema

Subschema is a sub part of a schema and inherits same properties of schema. Subschema describes different view of the database. Subschema is an application programmer’s or user view of data item types and records type.

For example: Suppose a table employee, programmer has access of all columns of table employee but user has access of only two or three columns of table employee. Subschema describes different view of the database.