Automated Database Design Tools

Automated Database Design Tools

The database design activity predominantly spans Phase 2 (conceptual design), Phase 4 (data model mapping, or logical design), and Phase 5 (physical database design) in the design process that we discussed in Section 10.2. Discussion of Phase 5 is deferred to Chapter 20 after we present storage and indexing techniques, and query optimization. We discussed Phases 2 and 4 in detail with the use of the UML notation in Section 10.3 and pointed out the features of the tool Rational Rose, which supports these phases, in Section 10.4. As we mentioned, Rational Rose is more than just a database design tool. It is a software development tool and does database modeling and schema design in the form of class diagrams as part of its overall object-oriented application development methodology. In this section, we summarize the features and shortcomings of the set of commercial tools that are focused on automating the process of conceptual, logical, and physical design of databases.

When database technology was first introduced, most database design was carried out manually by expert designers, who used their experience and knowledge in the design process. However, at least two factors indicated that some form of automation had to be utilized if possible:

As an application involves more and more complexity of data in terms of relationships and constraints, the number of options or different designs to

model the same information keeps increasing rapidly. It becomes difficult to deal with this complexity and the corresponding design alternatives manually.

        The sheer size of some databases runs into hundreds of entity types and relationship types, making the task of manually managing these designs almost impossible. The meta information related to the design process we described in Section 10.2 yields another database that must be created, maintained, and queried as a database in its own right.

The above factors have given rise to many tools that come under the general category of CASE (computer-aided software engineering) tools for database design. Rational Rose is a good example of a modern CASE tool. Typically these tools consist of a combination of the following facilities:

Diagramming. This allows the designer to draw a conceptual schema dia-gram in some tool-specific notation. Most notations include entity types (classes), relationship types (associations) that are shown either as separate boxes or simply as directed or undirected lines, cardinality constraints shown alongside the lines or in terms of the different types of arrowheads or min/max constraints, attributes, keys, and so on.¹⁰ Some tools display inheritance hierarchies and use additional notation for showing the partial-versus-total and disjoint-versus-overlapping nature of the specialization/ generalization. The diagrams are internally stored as conceptual designs and are available for modification as well as generation of reports, cross-reference listings, and other uses.

        Model mapping. This implements mapping algorithms similar to the ones we presented in Sections 9.1 and 9.2. The mapping is system-specific—most tools generate schemas in SQL DDL for Oracle, DB2, Informix, Sybase, and other RDBMSs. This part of the tool is most amenable to automation. The designer can further edit the produced DDL files if needed.

        Design normalization. This utilizes a set of functional dependencies that are supplied at the conceptual design or after the relational schemas are produced during logical design. Then, design decomposition algorithms (see Chapter 16) are applied to decompose existing relations into higher normal-form relations. Generally, many of these tools lack the approach of generating alternative 3NF or BCNF designs (described in Chapter 15) and allowing the designer to select among them based on some criteria like the minimum number of relations or least amount of storage.

Most tools incorporate some form of physical design including the choice of indexes. A whole range of separate tools exists for performance monitoring and measurement. The problem of tuning a design or the database implementation is still mostly handled as a human decision-making activity. Out of the phases of design described in this chapter, one area where there is hardly any commercial tool support is view integration (see Section 10.2.2).

We will not survey database design tools here, but only mention the following characteristics that a good design tool should possess:

        An easy-to-use interface. This is critical because it enables designers to focus on the task at hand, not on understanding the tool. Graphical and point-and-click interfaces are commonly used. A few tools like the SECSI design tool use natural language input. Different interfaces may be tailored to beginners or to expert designers.

        Analytical components. Tools should provide analytical components for tasks that are difficult to perform manually, such as evaluating physical design alternatives or detecting conflicting constraints among views. This area is weak in most current tools.

Heuristic components. Aspects of the design that cannot be precisely quantified can be automated by entering heuristic rules in the design tool to evaluate design alternatives.

        Trade-off analysis. A tool should present the designer with adequate comparative analysis whenever it presents multiple alternatives to choose from. Tools should ideally incorporate an analysis of a design change at the conceptual design level down to physical design. Because of the many alternatives possible for physical design in a given system, such tradeoff analysis is difficult to carry out and most current tools avoid it.

        Display of design results. Design results, such as schemas, are often dis-played in diagrammatic form. Aesthetically pleasing and well laid out dia-grams are not easy to generate automatically. Multipage design layouts that are easy to read are another challenge. Other types of results of design may be shown as tables, lists, or reports that should be easy to interpret.

        Design verification. This is a highly desirable feature. Its purpose is to verify that the resulting design satisfies the initial requirements. Unless the requirements are captured and internally represented in some analyzable form, the verification cannot be attempted.

Currently there is increasing awareness of the value of design tools, and they are becoming a must for dealing with large database design problems. There is also an increasing awareness that schema design and application design should go hand in hand, and the current trend among CASE tools is to address both areas. The popularity of tools such as Rational Rose is due to the fact that it approaches the two arms of the design process shown in Figure 10.1 concurrently, approaching database design and application design as a unified activity. After the acquisition of Rational by IBM in 2003, the Rational suite of tools have been enhanced as XDE (extended development environment) tools. Some vendors like Platinum (CA) provide a tool for data modeling and schema design (ERwin), and another for process modeling and functional design (BPwin). Other tools (for example, SECSI) use expert system technology to guide the design process by including design expertise in the form of rules. Expert system technology is also useful in the requirements collection and analysis phase, which is typically a laborious and frustrating process. The trend is to use both meta-data repositories and design tools to achieve better designs for complex databases. Without a claim of being exhaustive, Table 10.1 lists some popular database design and application modeling tools. Companies in the table are listed alphabetically.