Explain the concept of DBMS schema with examples?

Before learning the concepts of schema let’s understand the definition of schema and sub-schema.


The overall design of the database is called schema or description of database.

  • Schema is a physical representation of data which is present in the database management system.

  • In simple words we can call a schema the structure of any database.

  • It defines how the data was stored in a database and also shows the relationship among those data, but it does not show the data present in those tables.

  • The database schema includes the definition of the database, entities and the components.

  • Schema of a database can only modify the DDL statement but does not change by performing certain operations like insertion, updating, and deletion.

  • Database schema explains the integrity constraints of the database, domains of all attributes, foreign, and primary key of all the relations.

Sub schema

It is the subset of the schema and inherits the same property that a schema has. It gives the users a window through which he/she can view only that part of the database which he wants.

For example − Student table in a database the programmer can access all fields of table, but the user can access only two or three fields of it. Subschema describes both views of the database.

Types of Schema

Schema is of three types, which are as follows −

  • View Schema − The design of a database at a view level is called view schema. This schema generally shows the user interaction with the database system.

  • Logical Schema − The design of a database at the logical level is called a logical schema. A database administrator (DBA) and the programmers used to work at this level. This level describes all the entities, attributes and their relationship with the integrity constraints.

  • Physical Schema − The design of a database at the physical level is called a physical schema. This schema describes how the data is stored in the secondary storage devices. There is only 1 logical and 1 physical schema per database and more than 1 view schema.

Schema is also called Intention and is shown as below −