Many DBAs find themselves working with someone else's database design. Unless you are involved with building or designing a new application or database from scratch, when else would you have the opportunity to use a CASE tool? One of the points to take away from having read this article is that a CASE tool can also be used after the fact. CASE tools are an invaluable resource for a DBA and knowing how to use one (or more) of them can make your job easier.
Who are some of the major vendors of these products and how do they compare to one another?
Some of the major CASE tool vendors include Microsoft, Oracle, and Computer Associates. However, it should be noted that there are many vendors whose products support database design even though their products are general purpose drawing tools. Additionally, the cost of these tools varies widely. Some are very inexpensive (including free) while others are quite expensive. Which product you would choose depends on factors such as budget, training time, functionality, support, and compatibility. Do you want to be locked into using one type of model, or is flexibility important?
Shown below is a summary of features and costs for Visio, Designer, and Erwin.
Microsoft Visio 2003
Visio is a more powerful version of PowerPoint. In fact, Microsoft's Web site shows a comparison between the two products to help you decide which product you need or should use. Visio is a general-purpose drawing tool with a high degree of technical functionality (it does more than just draw circles, squares and lines). Showing entity relationships via the database design component is but one feature of this product.
The database design component is only available in the professional edition, which costs $499 for a "new user" version.
The screenshot below is from Microsoft's Web site and it shows a comparison between the standard and professional editions (with respect to database modeling). Although you could draw ERDs using basic symbols in the standard edition, you would miss out on the added features contained within the database design component.
Like many other products from Oracle, with Designer there is an interface hurdle (getting used to how Oracle presents windows on Windows, which usually aren't very Windows-like at all). In addition, Oracle has undergone several major version releases in the past few years. You may become comfortable with one toolset, only to have it dramatically change, albeit with ample warning, but still being very painful. Case in point: Oracle's conversion from Oracle Forms being client-server based to becoming Web only.
As far as cost is concerned, you can freely download a full-scale version of Designer for personal use, but you must pay to license it if used in a production environment. At the Oracle Store, Designer is bundled with the Internet Developer Suite, which rings in at a cool $5,000. Aside from keeping up with all of the releases and patches that accompany pretty much all of Oracle's products, there is the licensing guessing game (i.e., decoding what "Named User Plus Perpetual" means). Oracle does explain the licensing terminology, but it could be much clearer up front.
ERwin is one of the more popular design tools on the market today and pretty much specializes in database modeling.
One key feature (among many) is that ERwin will generate the code to create objects within your database. ERwin could be considered top of the line as far as CASE tools are concerned. Serious modeling requires a serious tool, and that is where ERwin comes in. Personally, I find it interesting how much Oracle Corporation has to compete with other vendors for some of the tools Oracle sells. If Oracle made better ancillary products, there probably would not be so many other tools on the market doing (and doing well) what Oracle is trying to do with Designer.
A major downside to ERwin is its price ($3995), so more than likely your professional experience with this tool will be on the job (as opposed to in an academic environment). At least the cost of Visio's professional edition is within range for someone who wants a top tier CASE tool, but there are trade offs involved with respect to functionality.
The product is available as a trial (you have to register with Computer Associates first) and the download includes some documentation.
How do they relate to one another?
Many tools can generate data flow diagrams (DFDs). With DFDs being generic representations of the data flow process, there is not much difference among any of the tools. A $50 version of SmartDraw does the same thing more expensive tools do.
Connecting to major name RDBMSs is a common feature among the more sophisticated tools. Visio, for example, supports connections to three prominent vendors (SQL Server, Oracle, and DB2). Ease of installation, setup and configuration is another area of consideration. The "real" Windows-based tools install with minimal effort. Oracle Designer installation requires a repository that can take up to several hours to create and requires significant human interaction along the way. Some tools only design (on paper), while others execute DDL statements against a database and create the objects in question, and some tools can reverse engineer a database (which totally justifies the cost in that situation).
What are possible drawbacks to using CASE tools?
There are several drawbacks to using CASE tools, but these drawbacks are common to any situation where a specialized tool is used. One drawback is that CASE tools do not necessarily prevent people from making bad designs. The output from the CASE tool may look nice, but a pig in a dress is still a pig. Another drawback has to do with training.
If you already know how to use one tool, learning how to use a new one is not as difficult because the same concepts still apply. If CASE tools are totally new to you, then the time for learning how to use the tool plus someone verifying that you do in fact know how to design in accordance with best practices - must be taken into account. If the CASE tool undergoes a major upgrade/version release, there may be old features lost and new features gained, neither of which may be desirable for your user community.
Another drawback is the cost of using these tools. Microsoft Visio 2003 Professional Edition costs almost $500. The database design may be a one-time effort, so why not use something cheaper. Drawing the tables and relationships in PowerPoint may be a viable option for a budget constrained startup company. In addition to the cost of the tools is overhead of applying patches. The number of users may be small, but nonetheless, applying a patch is one more thing to keep track of.
An "after the fact" use consideration applies to those tools that can reverse engineer a schema, and this feature can counter most drawbacks. Being able to reverse engineer a schema can pay tremendous dividends for you, especially when taking on a new job or set of databases. A pictorial "road map-like" representation of the schema can save you time and effort in your quest to learn and become familiar with a newly inherited or acquired database.
In summary, Visio appears to be one of the best CASE tools available considering its robust functionality, ease of use, simple installation, and price. The computer science curriculum of many colleges and universities typically includes a database architecture or concepts type of class. If you are in school, you can take advantage of Microsoft's academic pricing program (Newegg.com lists Visio 2003 Professional at $132, which is considerably cheaper than the full retail price).
If cost is not a consideration, ERwin would be an excellent choice. Oracle Designer, because of its complex installation process, numerous patches (i.e., bug fixes), and cost may not be your first choice, but if you've already paid for Developer Suite (because of Forms and Reports development), you may as well get your money's worth out of what you paid for.