Schemas, Sub Schemas and Instances

A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data.

A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It’s the database designers who design the schema to help programmers understand the database and make it useful.

A database schema can be divided broadly into two categories:

• Physical Database Schema: This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage.
• Logical Database Schema: This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.

A schema can be defined as the design of a database. The overall description of the database is called the database schema. It can be categorized into three parts. These are:

• Physical Schema
• Logical Schema
• View Schema

A physical schema can be defined as the design of a database at its physical level. In this level, it is expressed how data is stored in blocks of storage.

A logical schema can be defined as the design of the database at its logical level. In this level, the programmers as well as the database administrator (DBA) work. At this level, data can be described as certain types of data records which can be stored in the form of data structures. However, the internal details (such as an implementation of data structure) will be remaining hidden at this level.

View schema can be defined as the design of the database at view level which generally describes end-user interaction with database systems.

For example: Let suppose, you are storing students’ information on a student’s table. At the physical level, these records are described as chunks of storage (in bytes, gigabytes, terabytes or higher) in memory, and these elements often remain hidden from the programmers. Then comes the logical level; here in logical level these records can be illustrated as fields and attributes along with their data type(s), their relationship with each other can be logically implemented. Programmers generally work at this level because they are aware of such things about database systems. At view level, a user can able to interact with the system, with the help of GUI and enter the details on the screen. The users are not aware of the fact how the data is stored and what data is stored; such details are hidden from them.

Sub-schema 

A subschema is a subset of the schema and inherits the same property that a schema has. The plan (or scheme) for a view is often called subschema. Subschema refers to an application programmer’s (user’s) view of the data item types and record types, which he or she uses. It gives the users a window through which he or she can view only that part of the database, which is of interest to him. Therefore, different application programs can have different view of data.

Instances

The environment of database is said to be instance. A database instance or an ‘instance’ is made up of the background processes needed by the database software. These processes usually include a process monitor, session monitor, lock monitor, etc.  They will vary from database vendor to database vendor.

A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The  memory  structure  that  are  used  to  store  most  queried data from database.  This helps up to improve database performance by decreasing the amount of I/O performed against data file. 

It includes RDBMS software, table structure, stored procedures and other functions. It is normally used when administrators tend to describe multiple instances of the same database. It is also called as environment in technical terms. You can better understand its workings with the help of an example. The example is of an organization that has an employee database. This database will have three instances, which are:

• Production that is used for storing live data.
• Pre-production which is used to test new functionality prior to release for production
• Development which is used by database developers in order to create new functionality