Database schema


  • In any data model, it is important to distinguish between the description of the database and the database itself. The description of a database is called the database schema, which is specified during database design and is not expected to change frequently.
  • Most data models have certain conventions for displaying schemas as diagrams. A displayed schema is called a schema diagram.
  • Databases change over time as information is inserted and deleted. The collection of information stored in the database at a particular moment is called an instance of the database.
  • The overall design of the database is called the database schema. Schemas are changed infrequently, if at all.
  • The concept of database schemas and instances can be understood by analogy to a program written in a programming language.
  • A database schema corresponds to the variable declarations (along with associated type definitions) in a program.
  • Each variable has a particular value at a given instant. The values of the variables in a program at a point in time correspond to an instance of a database schema.
  • Schema changes are usually needed as the requirements of the database applications change. Newer database systems include operations for allowing schema changes, although the schema change process is more involved than simple database updates.
  • The overall design of the database is called the “Schema” or “Meta Data”. A database schema corresponds to the programming language type definition. The value of a variable in programming language corresponds to an “Instance” of a database Schema.

Three Schema Architecture:

The goal of this architecture is to separate the user applications and the physical database. In this architecture, schemas can be defined at the following three levels:

  • The internal level has an internal schema, which describes the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database.
  • The conceptual level has a conceptual schema, which describes the structure of the whole database for a community of users. The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints. A high-level data model or an implementation data model can be used at this level.
  • The external or view level includes a number of external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group. A high-level data model or an implementation data model can be used at this level.