RDBMS: Schema Vs Instance Top Differences

Schema in RDBMS

A 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 qualified 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 how data will be stored in secondary storage etc.

Logical Database Schema: This defines all logical constraints that need to be applied to data stored. It defines tables, views and integrity constraints, etc.

Database Instance

Database instances are a state of operational database with data at any given time. This is a snapshot of the database.

Database instances tend to change with time. DBMS ensures that it is every instance (state) must be a valid state by keeping up to all validation, constraints, and condition that database designers have imposed or it is expected from DBMS itself.

Related Posts

Author: Srini

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