Designing of database is most important responsibility of the software professionals who are dealing with the database related projects. For this they follow the Design Methodology. It helps the designer to plan, manage, control, and evaluate database development projects.
Design methodology: A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design.
A design methodology consists of phases each containing a number of steps, which guide the designer in the techniques appropriate at each stage of the project.
We’ll be covering the following topics in this tutorial:
Phases of Design Methodology
The database design methodology is divided into three main phases. These are:
• Conceptual database design
• Logical database design
• Physical database design
Conceptual database design
The process of constructing a model of the information used in an enterprise, independent of all physical considerations.
The conceptual database design phase begins with the creation of a conceptual data model of the enterprise, which is entirely independent of implementation details such as the target DBMS, application programs, programming languages, hardware platform, performance issues, or any other physical considerations.
Logical database design
It is a process of constructing a model of the information used in an enterprise based on specific data model, but independent of a particular DBMS and other physical considerations.
The logical database design phase maps the conceptual model on to a logical model, which is influenced by the data model for the target database (for example, the relational model). The logical data model is a source of information for the physical design phase.
The output of this process is a global logical data model consisting of an Entity- Relationship diagram, relational schema, and supporting documentation that describes this model, such as a data dictionary. Together, these represent the sources of information for the physical design process, and they provide the physical database designer with a vehicle for making tradeoffs that are so important to an efficient database design.
Physical database design
It is a description of the implementation of the database on secondary storage; it describes the base relations, file organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security measures.
Whereas logical database design is concerned with the what, physical database design is concerned with the how. The physical database design phase allows the designer to make decisions on how the database is to be implemented. Therefore, physical design is tailored to a specific DBMS. There is feedback between physical and logical design, because decisions taken during physical design for improving performance may affect the logical data model.
For example, decisions taken during physical for improving performance, such as merging relations together, might affect the structure of the logical data model, which will have an associated effect on the application design.
Steps of physical database design methodology
After designing logical database model, the steps of physical database design methodology are as follows:
Step 1: Translate global logical data model for target DBMS It includes operations like the Design of base relation, derived data and design of enterprise constraints.
Step 2: Design physical representation
It includes operations like analyzing of transactions, selection offile organizations, selection of indexes and estimates the disk space requirements.
Step 2 is most important part in designing of physical design of database. It is used to determine the optimal file organizations to store the base relations and the indexes· that are required to achieve acceptable performance, that is, the way in which relations and tuples will be held on secondary storage.
One of the main objectives of physical database design is to store data in an efficient ay. There are a number of factors that we may use to measure efficiency:
• Transaction throughput
This is the number of transactions that can be processed in a given time interval.
In some systems, such as airline reservations, high transaction throughput is critical to the overall success of the system.
• Disk storage
This is the amount of disk space required to store the database files. The designer may wish to minimize the amount of disk storage used.
Response time
It is the time required for the completion of a single transaction. From a user’s point of view, we want to minimize response time as much as possible.
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular