DBMS- Database Schema and Instance

SrinimfjobsA schema is a collection of named database objects. Schemas provide a way to logically classify objects such as tables, views, triggers, routines, or packages. A schema name is used as the first part of a two-part object name. For example, if the EMPLOYEE table is created under the schema name JENNIFER, the qualifed name of this table object is JENNIFER.EMPLOYEE, and other tables named EMPLOYEE can be created in the same database, as long as they are created under different schemas. Schemas are used to enforce uniqueness within a database. If users will be sharing database objects, grouping those objects under a separate schema can be a good idea. If no schema name is explicitly specified when a database object is created, the default schema name is used.

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

Database Instance:

Database instances, is a state of operational database with data at any given time. This is a snapshot of database. Database instances tend to change with time. DBMS ensures that its every instance (state) must be a valid state by keeping up to all validation, constraints and condition that database designers has imposed or it is expected from DBMS itself.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.