CONCEPTUAL, PHYSICAL AND LOGICAL DATABASE MODELS IBM Mainframe

A database is a collection of data designed to be used by different people. It is a collection of interrelated data stored together with controlled redundancy to serve one or more applications in an optimal fashion. The data are stored in such a fashion that they are independent of the programs of people using the data. A common and controlled approach is used in adding new data and modifying and retrieving existing data within the database.

The users of the database do not have to worry about the physical implementation and internal workings of a database. The database management system has different layers and the user (a user can be an interactive user typing in a query or it can be an application program requesting the services of the database) need to interact with the external layer.

At the lowest level the data elements appear as disk storage (physical storage). To be self-describing the DBMS must also store information about the data stored in the database. This information is called metadata. Consider a database containing a BOOK table, which contains information about the books in a library. While the database store the details about different books in the book table, the data dictionary will store the information that a table with name BOOK exists and it contains attributes like ID, Title, Author, Publisher, etc.

Because DBMS is generic and is intended to support a wide variety of database applications, it customizes copies of the general storage structure, to reflect the application's needs. The database designer provides the names of the entities and their attributes in a machine-readable script called conceptual schema. For instance, the conceptual schema for the publisher-book example will describe the publisher and book tables and also how the publisher rows relate to the book rows. The DBMS compiles the schema into a compact form for reference by other parts of the system and stores it in the data dictionary. This data dictionary is then available to resolve references to table and attribute names that arise when the database users requests the database services.

As mentioned earlier, database users can be individuals dealing with an interactive interface or other computer programs requesting services with calls to procedures provided by the DBMS. Although there are some differences between these two, the DBMS should provide as much uniformity as possible to its users. The user interface module of the DBMS responds to the service requests using data dictionary to confirm the existence and compatibility of the data elements mentioned in the requests. A second module processes the storage and retrieval requests, performs query optimizations and carries out the data operations need to respond to the request. The DBMS passes the requested data items and any error messages back to the user through the user interface module. The various components of the DBMS architecture carry out their functions in different ways depending on the database model involved.

Database Environment

Database Environment

There are three levels of database services. They are:

  • External or Logical level
  • Conceptual level
  • Physical level

The figure shows the different database levels.

External or Logical Level

This is the highest and the outermost layer and presents varying external tailored views of the application to the different users. Differing external views can use the same conceptual view to satisfy the differing needs of the different users. The external views, to a certain extent, compensate for changes in the conceptual schema. This de-coupling of the external level and the conceptual level is called logical data independence. The external level (which is based on the conceptual model) is the end users' view of the data environment. The use of external models has some very major advantages:

In the absence of the external model, each application program would have been forced to include the entire set of relationships within a database. This would have made the application development very difficult and cumbersome. The capability to use database subsets (provided by the external model) makes application programming much easier.

The logical independence obtained by creating different views for the different applications simplifies the database designer's task by making it easy to identify specific data required to support each application's operation.

The external model, by creating separate views of the database to the different users helps in ensuring the database security. When the different users have access only to the data that is needed and the access is limited to a subset of the entire database the chances of a security breakdown is minimized.

The Different Database Levels

The Different Database Levels

Logical database design is the process of constructing a model of information used in an enterprise based on a specific data model, but independent of a particular DBMS or other physical considerations. Whereas a conceptual data model is independent of all physical considerations, a logical model is derived knowing the underlying data model of the target DBMS. In other words, we know that the DBMS is, for example, relational, network, hierarchical or object-oriented. However, we ignore any other aspects of the chosen DBMS and, in particular, any physical details such as storage structures or indexes.

The physical or the internal level is the one closest to the physical storage. It is the one most concerned with the way data is physically stored. The external level is the one closest to the users. It is the one concerned with the way data is viewed by the individual users. The conceptual level is the level between the other two and is concerned with what is termed as the 'community view". The community view can be explained as follows. There will be many distinct external views, each varying in detail and representation of some portion of the total database.There will be only one conceptual view, consisting of the abstract representation of the database in its entirety. Similarly there will be only one physical view, representing the total database, as it is physically stored.

Conceptual Level

The conceptual model represents a global view of the data. It is an enterprise-wide representation of data. As we have seen the conceptual model is the basis for the identification and description of the main data objects avoiding the details. The most widely used conceptual model is the entity-relationship (E-R) model. The E-R model is represented using an E-R diagram (ERD). The ERD is a visual representation of the E-R model and thus an example of a conceptual schema (conceptual schema is a visual representation of the conceptual model). Thus the ERD (or the conceptual schema), which provides a visual representation of the conceptual model, gives an easy-to-understand bird's eye view (macro level overview) of the database environment. The conceptual model is independent of both software and hardware. Software independence means that the model is not dependent on the DBMS used to implement the database. Hardware independence means that the model does not depend on the hardware used in the implementation of the database. Thus changes in either hardware or DBMS will not have any effect on the database design at the conceptual level.

Isolating the data storage details to the physical level of the DBMS provides a convenient environment for the next level—the conceptual level. The application objects (tables, views, synonyms, procedures, triggers, etc.) exist at this level. Conceptual database design is the process of constructing a model. of the information used in an enterprise, independent of all physical considerations. Conceptual database design is entirely independent of implementation details such as the target DBMS software, application programs, programming languages, hardware platform or any other physical considerations.

If the underlying hardware and operating system changes, the consequences are limited to the interface between the physical and conceptual layer. In most cases the only thing that has to be done is that the database designer has to re-tune the physical schema for optimum performance in the new environment. In the worst case this may involve purchasing a new DBMS to function in the new environment. For example, if the operating system is changed from UNIX to Windows NT, a new version of the DBMS might be necessary. The beauty of this design is that all applications constructed over the objects in the conceptual level remain valid. The application programs are not affected. The term physical data independence describes the de-coupling of the application programs from the underlying hardware, data and control structures. The conceptual view describes the complete application environment in terms of abstractions supported by the DBMS such as tables, views, objects, rules, etc. The entities together with their relationships, constraints and security mechanisms reside here.

Physical Level

At the lowest level, certain physical components organize and store the raw data. In addition to hardware, these components include control structures that' track the location and format of the stored data elements. Other structures, which help in improving the performance, are also employed here. An example is the buffers that are used for holding frequently used data.

Physical database design is the process of producing a description of the implementation of the database on secondary storage; it describes the storage structures and access methods used to achieve efficient access to the data. It is during the physical database design process that the database designer decides how the database is to be implemented. Although the logical structure is DBMS-independent, it was developed in accordance with a particular data model such as relational, network or hierarchical. However in developing the physical database design, we must first identify the target database system. Therefore, physical design is tailored to a specific DBMS system. There is feedback between the physical and logical design, because decisions are taken during the physical design for improving performance that may affect the structure of the logical data model.

The physical layer typically has parameters that can be tuned for optimal performance under the access patterns of a particular application. Therefore the database designer may want to specify these parameters. However, in the case of relational, object-oriented and deductive database models, the tuning task is usually left to the DBMS. If the database designer does not have access to these physical structures, he specifies appropriate values in the physical schema. The physical schema addresses issues such as what data elements to store in close physical proximity, how to distribute data across multiple files and storage devices, which files to index, etc. In general, the main aim of physical database design is to describe how we intend physically to implement the logical database design. For relational model, this involves:

  • Deriving a set of relational tables and the constraints on these tables from the information presented in the logical data model.
  • Identifying the specific storage structures and access methods for the data to achieve an optimum performance for the database system.
  • Designing security protection for the system.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics