Instances and Schemas

Databases change over time as information is inserted and deleted. The collection of information stored in the database at a particular moment is called aninstanceof the database. The overall design of the database is called the databaseschema.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.

Database systems have several schemas, partitioned according to the levels of abstraction. The physical schema describes the database design at the physical level, while the logical schema describes the database design at the logical level. A database may also have several schemas at the view level, sometimes called sub schemas,that describe different views of the database.

Of these, the logical schema is by far the most important, in terms of its effect on application programs, since programmers construct applications by using the logical schema. The physical schema is hidden beneath the logical schema, and can usually be changed easily without affecting application programs. Application programs are said to exhibit physical data independence if they do not depend on the physical schema, and thus need not be rewritten if the physical schema changes.

Database Schema –

• Database Schema is the overall Design of the Database. It is the skeleton structure that represents the logical view of the entire database. It tells how the data is organized and how the relations among them are associated.
• It is sometimes also referred to as an overall model of the data, a conceptual model or a conceptual schema. These terms mean essentially the same thing. The Database Schema also formulates all the constraints that are to be applied on the data.
• A schema is a chart of the types of data that are used. It gives the names of the entities and attributes, and specifies the relationbetween them. It is just like a framework into which the values of data items can be fitted/stored. All the constraints that are to be applied on the data is also formulated on the schema.

The Database Schema will look like this :

Before describing the Instance of Schema, let us define some terms –

• Database State: Refers to the content of a database at a moment in time.
• Initial Database State: Refers to the database when it is loaded
• Valid State: A state that satisfies the structure and constraints of the database.
• Distinction
  • The database schema changes very infrequently. The database state changes every time the database is updated.
  • Schema is also called intension, whereas state/instance of schema is called extension.
    The extension and intension are explained in the next post.

Database Instance of Schema –

• The actual content of the database or say the data at a particular instant is called the Instance of Schema.
• Database instances tend to change with time.
• The Database Management software will ensure that the data or instance filled into the database scheme is in a valid state, by checking all the constraints, validations and conditions that the database designers have imposed.

The instance of above Schema will look like this :

A database Schema can be divided into two categories –

• Physical Database Schema :The Schema which is related to actual storage of data or the Schema that describes database design at physical level is called Physical Database Schema. It  defines how the data will be stored in the secondary storage.
• Logical Database Schema :The schema that defines all the logical constraints that need to be applied on the data stored or  the schema that describes the database design at the logical level is called Logical Database Schema. It defines tables, views, and integrity constraints.

A database may also have several schemas at the view level called subschemas, that describe different views of the database.

SubSchemas –

• A subschema inherits the same property that a schema has and is referred to as a subset of the schema.
• It is the application programmer’s (user’s) view of the data which he or she uses and 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.
  

Schema Construct –

A component of the schema or an object within the schema, e.g. PNAME, PRODUCT, PRICE etc.

Relational Schema	Relational Instance
Table Abstraction	Set of tuples
Schema is the overall Design of the Database.	The actual content of the database or say the data at a particular instant is called the Instance of Schema.
Database Schemas will remain the same.	Database instances tend to change with time.
Relational Schema is just like a framework.	When the schema framework is filled in with data item values, it is referred as an instance of the schema.