9 CASE Tools for Logical Database Design
Database design is just one part of the analysis and design phase of creating effective business application software (see Figure 9.1), but it is often the part that is the most challenging and the most critical to performance. In the previous chapters, we explored the classic ways of creating efficient and effective database designs, including ER modeling and the transformation of the ER models into constructs by transformation rules. We also examined normalization, normal forms and denormalization, and specific topologies used in warehousing, such as star schema. All this information may leave your head spinning!
This chapter focuses on commercially available tools to simplify these design processes. These computer-aided system engineering, or CASE, tools provide functions that assist in system design. CASE tools are widely used in numerous industries and domains, such as circuit design, manufacturing, and architecture. Logical database design is another area where CASE tools have proven effective. This chapter explores the offerings of the major vendors in this space: IBM, Computer Associates, and Sybase. Each of these companies offers powerful, feature-rich technology for developing logical database designs and transitioning them into physical databases you can use.
Although it is impossible to present information on software products without some subjectivity and comment, we have sincerely attempted to discuss the capabilities of these products with minimal product bias or critique. Also, it is impossible to describe the features of these products in great detail in a chapter of this sort (a user manual of many hundred pages could be written describing each), so we have set the bar slightly lower, with the aim of surveying these products to give the reader a taste for the capabilities they provide. Further details can be obtained from the manufacturer’s Web sites, which are listed in the Literature Summary at the end of the chapter.
Figure 9.1 Business system life cycle (courtesy IBM Corp.)
9.1 Introduction to the CASE Tools
In this chapter, we will introduce some of the most popular and powerful products available for helping with logical database design: IBM’s Rational Data Architect, Computer Associate’s AllFusion ERwin Data Modeler, and Sybase’s PowerDesigner. These CASE tools help the designer develop a well-designed database by walking through a process of conceptual design, logical design and physical creation, as shown in Figure 9.2.
Computer Associates’ AllFusion ERwin Data Modeler has been around the longest. A stand-alone product, AllFusion ERwin’s strengths stem from relatively strong support of physical data modeling, the broadest set of technology partners, and third-party training. What it does it does well, but in recent years it has lagged in some advanced features. Sybase’s PowerDesigner has come on strong in the past few years, challenging AllFusion ERwin. It has some advantages in reporting and some advanced features that will be described later in this chapter. IBM’s Rational Data Architect is a new product that supplants IBM’s previous product, Rational Rose Data Modeler. Its strength lies in strong design checking; rich integration with IBM’s broad software development platform, including products from their Rational, Information Management, and Tivoli divisions; and advanced features that will be described below.
Figure 9.2 Database design process
In previous chapters, we discussed the aspects of logical database design that CASE tools help design, annotate, apply, and modify. These include, for example, ER, and UML modeling, and how this modeling can be used to develop a logical database design. Within the ER relationship design, there are several types of entity definitions and relationship modeling (unrelated, one-to-many, and many-to-many). These relationships are combined and denormalized into schema patterns known as normal forms (e.g., 3NF, star schema, snowflake schema). An effective design requires the clear definition of keys, such as the primary key, the foreign key, and unique keys within relationships. The addition of constraints to limit the usage (and abuses) of the system within reasonable bounds or business rules is also critical. The effective logical design of the database will have a profound impact on the performance of the system, as well as the ease with which the database system can be maintained and extended.
There are several other CASE products that we will not discuss in this book. A few additional products worth investigating include Datanamic’s DeZign for Databases, QDesigner by Quest Software, Visible Analyst by Standard, and Embarcadero ER/Studio. The Visual Studio .NET Enterprise Architect edition includes a version of Visio with some database design stencils that can be used to create ER models. The cost and function of these tools vary wildly, from open source products up through enterprise software that costs thousands of dollars per license.
The full development cycle includes an iterative cycle of understanding business requirements; defining product requirements; analysis and design; implementation; testing (component, integration, and system); deployment; administration and optimization; and change management. No single product currently covers that entire scope. Instead, product vendors provide, to varying degrees, suites of products that focus on portions of that cycle. CASE tools for database design largely focus on the analysis and design, and to a lesser degree testing, of the database model and creation as illustrated in Figure 9.2.
CASE tools provide software that simplifies or automates some of the steps described in Figure 9.2. Conceptual design includes steps such as describing the business entities and functional requirements of the database; logical design includes definition of entity relationships and normal forms; physical database design helps transform the logical design into actual database objects, such as tables, indexes, and constraints. The software tools provide significant value to database designers by:
You can find a broader list of available database design tools at the Web site “Database Answers” (http://www.databaseanswers.com/modelling_tools.htm), maintained by David Alex Lamb at Queen’s University in Kingston, Canada.
9.2 Key Capabilities to Watch For
Design tools should be able to help you with both data modeling and logical database design. Both processes are important. A good distinction between these appears on the “Database Answers” Web site, cited above.
For data modeling, the question you are asking is: What does the world being modeled look like? In particular, you are looking for similarities between things. Then you identify a “supertype” of thing which may have subtypes. For example, Corporate Customers and Personal Customers. If, for example, supplier contacts are conceptually different things from customer contacts, then the answer is that they should be modeled separately. On the other hand, if they are merely subsets of the same thing, then treat them as the same thing.
For database design, you are answering a different question: How can I efficiently design a database that will support the functions of a proposed application or Web site? The key task here is to identify similarities between entities so that you can integrate them into the same table, usually with a “Type” indicator. For example, a Customer table, which combines all attributes of both Corporate and Personal Customers. As a result, it is possible to spend a great deal of time breaking things out when creating a Data Model, and then collapsing them back together when designing the corresponding database.
Support for programmable and physical design attributes with a design tool can also expand the value a tool provides. In database terms, aspects to watch for will include support for indexes, uniqueness, triggers, and stored procedures.
The low-end tools (selling for less than US$100 or available as open source) provide the most basic functionality for ER modeling. The higher end products provide the kinds of support needed for serious project design, such as:
• Complete round trip engineering
• UML design
• Schema evolution; change management
• Reverse engineering of existing systems
• Team support, allowing multiple people to work on the same project concurrently
• Integration with Eclipse and .NET and other tooling products
• Component and convention reuse (being able to reuse naming standard, domain, and logical models over multiple design projects)
• Reusable assets (e.g., extensibility, template)
• Reporting
9.3 The Basics
All of the products in question provide strong, easy to use functions for both data modeling and database design. All of these products provide the ability to graphically represent ER relationships. These tools also provide transformation processes to map from an ER model into an SQL design (DDL), using the transformation types described earlier in Chapter 5:
• Transform each entity into a table containing the key and nonkey attributes of the entity
• Transform every many-to-many binary or binary recursive relationship into a relationship table with the keys of the entities and the attributes of the relationship
• Transform every ternary or higher-level n-ary relationship into a relationship table
Similarly these tools produce the transformation table types described in Chapter 5:
• An entity table with the same information content as the original entity
• An entity table with the embedded foreign key of the parent entity
• A relationship table with the foreign keys of all the entities in the relationship
Chapter 5 also described rules for null transformations that must apply, and the CASE tools typically enforce these.
These CASE tools also help with the modeling of normal forms and denormalization to develop a true physical schema for your database, as described in Chapter 5. The tools provide graphical interfaces for physical database design as well as basic modeling of uniqueness, constraints, and indexes. Figure 9.3 shows an example of the IBM Rational Data Architect’s GUI for modeling ERs. Figure 9.4 shows a similar snapshot of the interface for Computer Associate’s AllFusion ERwin Data Modeler.
Figure 9.3 Rational Data Architect ER modeling (courtesy IBM Rational Division)
After creating an ER model, the CASE tools enable easy modification of the model and its attributes through graphical interfaces. An example is shown below in Figure 9.5 with IBM’s Rational Data Architect, illustrating attribute editing. Of these CASE tools, Rational Data Architect has perhaps the most useful UML modeling function for data modeling and design. Its predecessor, Rational Rose Data Modeler, was the industry’s first UML-based data modeler, and IBM has continued its leadership in this area with Rational Data Architect. UML provides a somewhat richer notation than information engineering (IE) entity-relationship diagram (ERD) notation, particularly for conceptual and logical data modeling. However, the IE-ERD notation is older and more commonly used. One of the nice aspects of Rational Data Architect is the ability to work with either UML or IE notation.
Figure 9.6 shows the AllFusion ERwin screen for defining the cardinality of entity relationships. It is worth noting that many relationships do not need to enter this dialog at all.
Figure 9.4 AllFusion ERwin Data Modeler ER modeling (picture from Computer Associates, http://agendas.ca.com/Agendas/Presentations/AFM54PN.pdf)
Figure 9.5 Property editing with IBM Rational Data Architect (courtesy IBM Rational Division)
Table 9.1 ER Model Containing Sales Relationships
9.4 Generating a Database from a Design
To really take your design to the next level (i.e., a practical level) you will need a good design tool that can interact with your specific database product to actually create the Data Definition Language (DDL) and associated scripts or commands to create and modify the basic database for you. For instance, using the example of Chapter 7, we have modeled an ER model containing the sales relationships shown in Table 9.1.
The CASE tools will automatically generate the required scripts, including the DDL specification to create the actual database, and will provide you with an option to apply those changes to an actual database, as follows:
It is worth noting that with all the CASE tools we discuss here, the conversion of the logical design to the physical design is quite rudimentary. These tools help create basic database objects, such as tables and, in some cases, indexes. However, the advanced features of the database server are often not supported—where they are supported, the CASE tool is usually behind by two or three software releases. Developing advanced physical design features, such as multidimensional clustering or materialized views, is far beyond the capabilities of the logical design tools we are discussing. Advanced physical database design is often highly dependant on data density and data access patterns. One feature of Rational Data Architect that stands out is that it provides linkages with the automatic computing (self-managing) capabilities within DB2 to provide semi-automated selection of advanced physical design attributes.
Figure 9.7 shows an example with ERwin schema generation, generating the DB2 DDL directly from the ER model designed within ERwin.
Other very important capabilities shared by these tools include the ability to reverse-engineer existing databases (for which you may not have an existing ER or physical UML model), and the ability to automatically materialize the physical model or incremental changes of a model onto a real database. This capability enables you to synchronize your database design with a real database as you make changes. This capacity is massively useful for incremental application and database development, as well as for incremental maintenance.
Figure 9.7 ERwin schema generation for a DB2 database (picture from IBM: http://www.redbooks.ibm.com/abstracts/redp3714.html?Open)
9.5 Database Support
All of these products support a large array of database types. Certainly, all of the major database vendors are supported by each of these products (i.e., DB2 UDB, DB2 zOS, Informix IDS, Oracle, SQL Server), and a much larger set is supported through ODBC. However, what really matters most to the application developer is whether the database he or she is programming toward is directly supported by the CASE design tool. Database support is not equal between these products. Also, very significantly, each database product will have unique features of its own (such as reverse scan indexes, informational constraints, and so forth) which are not standard. One of the qualitative attributes of a good database design tool is whether it distinguishes and supports the unique extensions of individual database products. Each of the products has a strong history for doing so: in descending order, AllFusion ERwin Data Modeler, Sybase PowerDesigner, and Rational Data Architect. Notably, IBM’s Rational Data Architect has a somewhat smaller range of supported databases than the other products, though it does support all the major database platforms. However, Rational Data Architect can be expected over time to have the tightest integration with the DB2 and Informix families, since all of these products are developed by IBM. Database designers are advised to investigate the level of support provided by a CASE tool for the database being developed toward, to ensure the level of support is adequate. Figure 9.8 shows an example of database server selection with AllFusion ERwin Data Modeler.
Figure 9.8 DBMS selection in AllFusion ERwin Data Modeler (picture from Computer Associates: http://iua.org.Uk/conference/Autumn%202003/Ruth%20Wunderle.ppt#9)
9.6 Collaborative Support
All three of these products are designed for collaborative development, so that multiple developers can work together to design portions of a database design, either supporting different applications or collaborating on the same portions. These collaboration features fall into two domains:
Figure 9.9 Merge process with PowerDesigner (courtesy of Sybase)
Figure 9.9 shows the Sybase PowerDesigner merge GUI, which identifies significant changes between the existing schema and the new schema being merged. In particular, notice how the merge tool has identified a change in Table_l Column_l, which has changed base types. The tool also found that Table_2 and Table_3, which exist in the merging design, were not present in the base design. AllFusion ERwin Data Modeler and Rational Data Architect have similar capabilities for merging design changes.
9.7 Distributed Development
Distributed development has become a fact of life for large enterprise development teams, in which groups of developers collaborate from geographically diverse locations to develop a project. The phenomenon is not only true across floors of a building, or between sites in a city, but now across states, provinces, and even countries. In fact, outsourcing of software development has become a tour de force, with many analysts projecting that the average enterprise will ultimately outsource 60% of application work, shifting aspects of project development to locations with cheaper labor. As the META Group said in its September 16, 2004 Offshore Market Milieu report, “With global resources costing one-third to one-fifth that of American employees—without accounting for hidden costs and having higher process discipline, offshore strategies now pervade North American IT organizations.”
Therefore, developers of database software working in a distributed collaborative environment need to consider the collaborative and distributed qualities of CASE tools for database design. The trend towards collaborative development and distributed development shows no sign of slowing; rather, it is on the increase. In the existing space, IBM’s Rational MultiSite software, shown in Figure 9.10, allows the best administration across geographically diverse locations for replicating project software and data and subsequently merging the results. Rational MultiSite is a technology layered on top of Rational ClearCase and Rational ClearQuest (development and source code control products) to allow local replicas of Rational ClearCase and Rational ClearQuest repositories. Rational MultiSite also handles the automatic synchronization of the replicas. This is particularly useful for companies with distributed development who wish to have fast response times for their developers via access to local information, and such replication is often an absolute requirement for geographically distributed teams.
Figure 9.10 IBM’s Rational MultiSite software for massively distributed software management (courtesy IBM Rational Division)
9.8 Application Life Cycle Tooling Integration
The best CASE tools for database design are integrated with a complete suite of application development tools that cover the software development life cycle. This allows the entire development team to work from an integrated tool platform, rather than the data modelers being off in their own world. Only the largest vendors offer this, and in fact true tooling integration across the development life cycle is somewhat rare. This solution is, in a very real way, the philosopher’s stone of development infrastructure vendors. All the vendors who produce software development platforms have been working to develop this breadth during the past two decades. The challenge is elusive simply because it is hard to do well. The three companies we are discussing here all have broad offerings, and provide some degree of integration with their database design CASE technology.
Figure 9.11 Sybase PowerDesigner plug-in to Sybase PowerBuilder (picture from http://www.pbugg.de/docs/1, Berndt Hambock)
For Computer Associates, the AllFusion brand is a family of development life cycles tools. It is intended to cover designing, building, deploying and managing eBusiness applications. Sybase also has a broad product suite, and their strength is in collaborative technology. From a design perspective, the ability to plug Sybase PowerDesigner into their popular Sybase Power Builder application development tooling is a very nice touch, as seen in Figure 9.11. The IBM tooling is clearly the broadest based, and their new IBM Software Development Platform, which is built heavily but not exclusively from their Rational products, covers everything from requirements building to portfolio management, source code control, architectural design constraints, automated testing, performance analysis, and cross site development. A representation of the IBM Software Development Platform is shown in Figure 9.12.
Figure 9.12 IBM Software Development Platform (courtesy IBM Rational Division)
9.9 Design Compliance Checking
With all complex designs, and particularly when multiple designers are involved, it can be very hard to maintain the integrity of the system design. The best software architects and designers grapple with this by defining design guidelines and rules. These are sometimes called “design patterns” and “anti-patterns.” A design pattern is a design principle that is expected to be generally adhered to within the system design. Conversely, an anti-pattern is precisely the opposite. It represents flaws in the system design that can occur either through violation of the design patterns or through explicit definition of an anti-pattern. The enforcement of design patterns and anti-patterns is an emerging attribute of the best CASE tools for systems design in general, and database design in particular. Figure 9.13 shows an example of the interface used in the Rational Data Architect for compliance checking, which scans the system to enforce design patterns and check for anti-patterns. Some degree of support for design pattern and anti-pattern checking exists in AllFusion ERwin Data Modeler and Sybase PowerDesigner, as well. The compliance checking in IBM’s Rational products is the most mature in general, with the notion of design patterns and anti-patterns being a key philosophical point for the IBM’s Rational family of products. Some examples of things these compliance checkers will scan for include:
Figure 9.13 Modeling and database compliance checking (courtesy IBM Rational Division)
• Complete round trip engineering
• Design and normalization
— Discover 1st, 2nd, and 3rd normalization
• Index and storage
— Check for excessive indexing
• Naming standards
• Security compliance
• Sarbanes-Oxley compliance
— Check for valid data model and rules
• Model syntax checks
9.10 Reporting
Reporting capabilities are a very important augmentation of the design capabilities in CASE tools for database design. These reports allow you to examine your ER and UML modeling and database schema in both graphical and textual formats. The Sybase products have a superb reputation for reporting features; their products enable you to generate reports in common formats like Microsoft Word. Reporting can include both the modeling and the annotations that the designer has added. It can cover physical data models, conceptual data models, object-oriented models (UML), business process models, and semi-structured data using extensible Markup Language (XML). Notice in Figure 9.14 how the fourth page, which contains graphics, can be oriented in landscape mode, while the remaining pages are kept in portrait mode. AllFusion ERwin and Rational Data Architect also provide rich reporting features, though Sybase PowerDesigner has the richest capabilities.
Figure 9.14 Reporting features with Sybase PowerDesigner (courtesy Sybase)
9.11 Modeling a Data Warehouse
In Chapter 8 we discussed the unique design considerations required for data warehousing and decision support. Typically, warehouses are designed to support complex queries that provide analytic analysis of your data. As such, they exploit different schema topology models, such as star schema and horizontal partitioning. They typically exploit data views and materialized data views, data aggregation, and multidimensional modeling far more extensively than other operational and transactional databases.
Traditionally, warehouses have been populated with data that is extracted and transformed from other operational databases. However, more and more companies are moving to consolidate system resources and provide real-time analytics by either feeding warehouses data in near-real-time (i.e., with a few minutes latency) or entirely merging their transactional data stores with their analytic warehouses into a single server or cluster. These trends are known as “active data warehousing,” and pose even more complex design challenges. There is a vast need for CASE tooling in this space.
Sybase offers a CASE tool known as Sybase Industry Warehouse Studio (IWS). Sybase IWS is really a set of industry-specific, prepackaged warehouses that require some limited customization. Sybase IWS tooling provides a set of wizards for designing star schemas, dimensional tables, denormalization, summarization, and partitioning; as usual, the Sybase tools are strong on reporting facilities.
The industry domains covered by ISW are fairly reasonable—they include IWS for Media, IWS for Healthcare, IWS for Banking, IWS for Capital Markets, IWS for Life Insurance, IWS for Telco, IWS for Credit Cards, IWS fro P&C Insurance, and IWS for CRA.
IBM’s DB2 Cube Views (shown in Figure 9.15) provides OLAP and multidimensional modeling. DB2 Cube Views allows you to create metadata objects to dimensionally model OLAP structures and relational data. The graphical interface allows you to create, manipulate, import, or export cube models, cubes, and other metadata objects.
Figure 9.15 DB2 Cube Views interface (courtesy IBM Rational Division)
Sybase IWS uses standard database design constructs that port to many database systems, such as DB2 UDB, Oracle, Microsoft SQL Server, Sybase Adaptive Server Enterprise, and Sybase IQ. In contrast, IBM’s DB2 Cube Views is designed specifically to exploit DB2 UDB. The advantage of DB2 Cube View is that it can exploit product-specific capabilities in the DB2 database that may not be generally available in other databases. Some examples of this include materialized query tables (precomputed aggregates and cubes), multidimensional clustering, triggers, functional dependencies, shared-nothing partitioning, and replicated MQTs. Sybase IWS dependence on the lowest common denominator database feature provides flexibility when selecting the database server but may prove extremely limiting for even moderately sized marts and warehouses (i.e., larger than 100 GB), where advanced access and design features become critical.
To summarize and contrast, Sybase offers portable warehouse designs that require minimal customization and are useful for smaller systems, and DB2 Cube View provides significantly richer and more powerful capabilities, which fit larger systems, require more customization, and necessitate DB2 UDB as the database server.
AllFusion ERwin Data Modeler has basic support to model OLAP and multidimensional databases, but does not have the same richness of tooling and wizards that the other companies offer to actually substantially simplify the design process of these complex systems.
9.12 Semi-Structured Data, XML
XML (extensible Markup Language) is a data model consisting of nodes of several types linked together with ordered parent/child relationships to form a hierarchy. One representation of that data model is textual—there are others that are not text! XML has increasingly become a data format of choice for data sharing between systems. As a result, increasing volumes of XML data are being generated.
While XML data has some structure it is not a fully-structured format, such as the table definitions that come from a fully-structured modeling using ER with IE or UML. XML is known in the industry as a semi-structured format: It lacks the strict adherence of schema that structured data schemas have, yet it has some degree of structure which distinguishes it from completely unstructured data, such as image and video data.
Standards are forming around XML to allow it to be used for database style design and query access. The dominant standards are XML Schema and XML Query (also known as XQuery). Also worth noting is OMG XMI standard, which defines a standard protocol for defining a structured format for XML interchange, based on an object model. Primarily for interfacing reasons, UML tools such as MagicDraw have taken XMI seriously and have therefore become the preferred alternatives in the open source space.
XML data is text-based, and self-describing (meaning that XML described the type of each data point, and defines its own schema). XML has become popular for Internet-based data exchange based on these qualities as well as being “well-formed.” Well-formed is a computer science term, implying XML’s grammar is unambiguous through the use of mandated structure that guarantees terms are explicitly prefixed and closed. Figure 9.16 shows the conceptual design of a semi-structured document type named “recipe.” Figure 9.17 shows an XML document for a hot dog recipe. Notice that the file is completely textual.
Figure 9.16 An XML schema for a recipe
IBM Rational Data Architect and Sybase PowerDesigner have taken the lead in being early adopters of XML data modeling CASE tools. Both products support the modeling of semi-structured data through XML and provide graphical tooling for modeling XML hierarchies.
Figure 9.17 An XML document for a hot dog
9.13 Summary
There are several good CASE tools available for computer-assisted database design. This chapter has touched on some of the features for three of the leading products: IBM Rational Data Architect, Computer Associates AllFusion ERwin Data Modeler, and Sybase PowerDesigner. Each provides powerful capabilities to assist in developing ER models and transforming those models to logical database designs and physical implementations. All of these products support a wide range of database vendors, including DB2 UDB, DB2 zOS, Informix Data Server (IDS), Oracle, SQL Server, and many others through ODBC support. Each product has different advantages and strengths. The drawbacks a product may have now are certain to be improved over time, so discussing the relative merits of each product in a book can be somewhat of an injustice to a product that will deliver improved capabilities in the near future.
At the time of authoring this text, Computer Associate’s AllFusion ERwin Data Modeler had advantages as a mature product with vast database support. The AllFusion products don’t have the advanced complex feature support for XML and warehousing/analytics, but what they do support they do well. Sybase PowerDesigner sets itself apart for superior reporting capabilities. IBM’s Rational Data Architect has the best integration with a broad software application development suite of tooling, and the most mature use of UML. Both the Sybase and IBM tools are blazing new ground in their early support for XML semi structured data and for CASE tools for warehousing and OLAP. The best products provide the highest level of integration into a larger software development environment for large-scale collaborative, and possible geographically diverse, development. These CASE tools can dramatically reduce the time, cost, and complexity of developing, deploying, and maintaining a database design.
9.14 Literature Summary
Current logical database design tools can be found in manufacturer Web sites [Database Answers, IBM Rational Software, Computer Associates, Sybase PowerDesigner, Directroy of Data Modeling Resources, Objects by Design, Understanding relational databases: referential integrity, and Widom].