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
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.10 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
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.