DBMS Schema and Instances

DBMS Schema and Instances

Schema

The global design of a database is known as the database schema.

A database schema correlate to the variable statement in a program.

A schema diagram displays only names of record types (entities) and names of data items (attributes) and does not show the relationship among various files.

An example of a supplier schema is shown below.

Types of Schema

1. Physical Schema: Database design at the physical level. Physical schema is also termed as Internal Schema. It tells the user about the physical storage location of data in the database system.

   Example: Consider the case of Supp_Name. Now it has a string data type structure, but then when it is defined with its location also, i.e., String (10) Offset (30); here offset tells you the exact location of Supp_Name where it is stored.

2. Logical Schema: Database design at the logical level. Logical Schema is also called as a Conceptual Schema. It tells you about the data structure of the data used in the database.

   Example: Consider the case of Supp_Name. Now it has data of string type, i.e., it can be defined as the STRING type data structure. So only the data types are given along with data here in this schema.

Subschema

The subschema defines to an application programmer’s view of the records he uses. Many different subschemas can be derived from one schema.

The application programmer or terminal user does not need to know about the entire database schema. It is often highly complex, and we do not want him involved in arguments about that complexity.

The DBA must ensure that the subschemas they use are derivable from the schema.

As shown in the figure, the SUPPLIER-MASTER record of the first application program and it contains additional attributes such as SUP-NAME and SUP-ADD from SUPPLIER record, and the PURCHASE-ORDER-DETAILS record contains additional attributes such as PART-NAME, SUP-NAME, and PRICE from two records PART and SUPPLIER respectively. Similarly, ORDER-DETAILS record of second application program contains other attributes such as SUP-NAME, and QTY-ORDRD from two records SUPPLIER and PURCHASE-ITEM, respectively.

An individual application program can change their respective subschema without effecting subschema views of others. The DBMS software derived the subschema data requested by application programs from schema data. The database administrator (DBA) ensures that the subschema required by application programs is derivable from the schema.

The application program are not concerned about the physical organization of data. The physical organization of data in the database can change without affecting application programs. In other words, with the change in the physical organization of data, application programs for subschema, need not be altered or modified.

Instances

When the schema framework is filled in the data item values or the contents of the database at any point in time (or current contents), it is referred to as an instance of the database. The term instance is also called as state of the database or snapshot. Each variable has a specific cost at a given instant. The costs of the variables in a program at a point in time, corresponding to an instance of a database schema, as shown in fig:

Instance of the database of M/s ABC Company

(a)An instance of the PRODUCT Relation

(b)An instance of the CUSTOMER Relation

(c)An instance of the SALES Relation

The difference between the database schema and database state or instance is very distinct. In the case of the database schema, it is specified to DBMS when a new data database is defined, whereas, at this point in time, the corresponding database state is empty with no data in the database. Once the database is first populated with the initial data, from then on, we get another database state whenever an update operation is applied to the database. At any point in time, the current state of the database is called the instance.

Apply now for Advanced DBMS Course