An interactive DSS tool for physical database design

Prashant C. Palvia
1991 Information Sciences  
The design of efficient physical databases is a complex activity involving the consideration of a large number of factors. Because of the complexity, mathematical programming approaches seeking to optimize the physical database have to make many simplifying assumptions; therefore, their applicability is limited. Further, the database designer may want to experiment with design preferences and features not considered by the mathematical optimization approaches. In order to effectively design the
more » ... physical database, this article describes an interactive DSS tool, which aides the database designer in this task. The database design is accomplished in the context of a high-level abstract model which is capable of being implemented in a variety of DBMSs and file systems. Because of this generic nature of the abstract model, the utility of the DSS tool is enhanced. The interactive tool not only lets the designer experiment with his own designs, but also provides several heuristic optimization procedures to enable the generation of many good designs. The heuristic designs may be used for final physical database design as well as for further experimentation. The paper also includes examples of how the physical design selected using the abstract model and the interactive tool may be implemented on several DBMSs and file systems. Article: INTRODUCTION Database design is a challenging and complex activity involving two phases: logical design and physical design. Logical design involves the development of a logical data structure (LDS) for the task domain; and physical design is concerned with developing storage structures for placing data on secondary storage, given a specific LDS. In this paper, we focus on the physical design task and assume that the LDS is given to us based on prior design activity (as in Figure 1 , adapted from [5]). Prior works have generally dealt with and developed design/optimization models for specific aspects of physical database design. These works include: index selection [1, 14, 17] , file structuring and models of file organization [18, 30] , and record segmentation and structuring [15, 17, 23, 24] . Some of these models are reviewed in [29] . Quoting from [19] : "While the attention to individual design problems results in elegant solutions, it is quite plausible that those individual solutions will have to be perturbed when the total database is put together." A GENERIC MODEL FOR PHYSICAL DATABASE DESIGN Generic physical design models for (single) file design have been proposed, in increasing degree of comprehensiveness, in [18] , in [30] , and in [32], respectively. The file design model of [31] was extended in [3] for physical database design. In addition, there are models and concepts [2, 5, 21, 23, 29] which are, per se, for the entire physical database design or significant parts of it (i.e., without building on file design models). In this paper, we have used an abstract and generic physical design model based on some well-recognized principles
doi:10.1016/0020-0255(91)90053-w fatcat:k5lgdlbxvzevrm3lycdw6nian4