Quick Contact

    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.

    S# Supp_Name Status City
    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.

    DBMS Schema and Instances

    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
    PRODUCT
    PROD-ID PROD-DESC UNIT-COST
    A12345 Steel Almirah 4000
    B23412 Dryer 4500
    B44332 Freeze 6000
    A98765 Steel Table 3500
    A29834 Steel Chair 4800
    C11008 Iron Moulding 5100
    (a)An instance of the PRODUCT Relation
    CUSTOMER
    CUST-ID CUST-NAME CUST-STREET CUST-CITY CUST-BAL
    1001 Rohan 523/4 Karnal 65000.00
    1000 Aditya Sec 18,G-12 Noida 40000.00
    1005 Nancy Sec 9,A-12 Delhi 84000.00
    1010 Gaurav Sec 32, C-35 Chandigarh 10500.00
    1006 Naira Sec 24, B-41 Gurugram 11200.00
    (b)An instance of the CUSTOMER Relation
    SALES
    CUST-ID PROD-ID PROD-QTY PROD-PRICE
    1001 A12345 100 6700
    1000 B23412 250 4000
    1010 B44332 120 14000
    1005 A98765 110 5500
    1001 A29834 300 12999
    (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.

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.