DBMS ARCHITECTURE

There are many different framework have been suggested for the DBMS over the last several year. The generalized architecture of a database system is called ANSI/SPARC (American National Standards Institute/Standards Planning and Requirements Committee) model.

      In 1972, a final report about database is submitted by ANSI (American National Standard Institute) and SPARC (Standard Planning And Requirement Committee). According to this approach, three levels of a database system was suggested and they are:

      • External view (Individual user view)

      • Conceptual View (Global or community user view)

      • Internal level (physical or storage view).

      For the system to be usable, it must retrieve data efficiently. This concern has led to the design of complex data structures for the representation of data in the database. Since many database systems users are not computer trained, developers hide the complexity from users through several levels of abstraction, to simplify users’ interactions with the system.

       These three views or levels of the architecture are as shown in the diagram as follows:

                          

OBJECTIVES OF THREE LEVEL ARCHITECTURE

      The database views were suggested because of following reasons or objectives of levels of a database:

      1. Make the changes easy in database when some changes needed by environment.

      2. The external view or user views do not depend upon any change made ii other view. For example changes in hardware, operating system or internal view should not change the external view.

      3. The users of database should not worry about the physical implementation and internal working of database system.

      4. The data should reside at same place and all the users can access it as per their requirements.

      5. DBA can change the internal structure without effecting the user’s view.

      6. The database should be simple and changes can be easily made.

      7. It is independent of all hardware and software.

All the three levels are shown below

               

External/View level

      The highest level of abstraction where only those parts of the entire database are included which are of concern to a user. Despite the use of simpler structures at the logical level, some complexity remains, because of the large size of the database. Many users of the database system will not be concerned with all this information. Instead, such users need to access only a part of the database. So that their interaction with the system is simplified, the view level of abstraction is defined. The system may provide many views for the same database.

      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.

      Database systems have several schemas, partitioned according to the levels of abstraction that we discussed. At the lowest level is the physical schema; at the intermediate level is the logical schema and at the highest level is a sub schema.

      The features of this view are

      • The external or user view is at the highest level of database architecture.

      • Here only one portion of database will be given to user.

      • One portion may have many views.

      • Many users and program can use the interested part of data base.

      • By creating separate view of database, we can maintain security.

      • Only limited access (read only, write only etc) can be provided in this view.

      For example: The head of account department is interested only in accounts but in library information, the library department is only interested in books, staff and students etc. But all such data like student, books, accounts, staff etc is present at one place and every department can use it as per need.

Conceptual/Logical level

     Database administrators, who must decide what information is to be kept in the database, use this level of abstraction. One conceptual view represents the entire database. There is only one conceptual view per database.

      The description of data at this level is in a format independent of its physical representation. It also includes features that specify the checks to retain data consistence and integrity.

The features are:

      • The conceptual or logical view describes the structure of many users.

      • Only DBA can be defined it.

      • It is the global view seen by many users.

      • It is represented at middle level out of three level architecture.

      • It is defined by defining the name, types, length of each data item. The create table    

        commands of Oracle creates this view.

     • It is independent of all hardware and software.

Internal/Physical level

      The lowest level of abstraction describes how the data are stored in the database, and what relationships exist among those data. The entire database is thus described in terms of a small number of relatively simple structures, although implementation of the simple structures at the logical level may involve complex physical-level structures, the user of the logical level does not need to be aware of this complexity.

      The features are :

     • It describes the actual or physical storage of data.

     • It stores the data on hardware so that can be stored in optimal time and accessed

       in optimal time.

     • It is the third level in three level architecture.

     • It stores the concepts like:

           • B-tree and Hashing techniques for storage of data.

           • Primary keys, secondary keys, pointers, sequences for data search.

           • Data compression techniques.

           • It is represented as

                       FILE EMP [

                                    INDEX ON EMPNO

                                                FIELD = {

                                                             (EMPNO: BYTE (4),

                                                             ENAME BYTE(25))]

          Mapping between views

      • The conceptual/internal mapping:

         o defines conceptual and internal view correspondence

      • specifies mapping from conceptual records to their stored counterparts

         o An external/conceptual mapping:

      • defines a particular external and conceptual view correspondence

      • A change to the storage structure definition means that the conceptual/internal            

        mapping must be changed accordingly, so that the conceptual schema may remain  

        invariant, achieving physical data independence.

       • A change to the conceptual definition means that the conceptual/external mapping        

         must be changed accordingly, so that the external schema may remain invariant,  

         achieving logical data independence.