Overview

In this chapter, I will give a very superficial overview of three CASE tools. The purpose of this chapter is merely to introduce you to these tools so that you know that they exist and to tell you something about what they can do for you.

In particular, this chapter focuses on the following products:

• The Data Module Designer from Paradox 7.0: This tool can help you design Paradox databases. It gives you a visual overview of your database and gives you a handy visual method for establishing referential integrity.

  　

• SDesigner: This full-featured, powerful, and very complex tool is meant for professional database designers.

  　

• ER1: Embarcadero makes one of the hottest data modeling tools on the market. Like SDesigner, this full-featured database tool is meant for professionals. ER1 is known for its advanced technology and easy-to-use tools.

  　

• Cadet: This shareware tool is developed in Delphi. It provides an inexpensive yet very powerful means of working with Paradox, dBASE, and InterBase tables.

Before going any further, I want to stress that this subject is starting to wander fairly far afield from the main topics of this book. In particular, my goal is to describe BCB, not third-party tools such as SDesigner or Cadet.

The main purpose of this chapter is to alert you to the existence of these tools and of others like them. My intention is not to recommend these tools above competing products, nor am I interested in describing these tools in any depth.

I have gone into considerable depth about database programming in this book. I feel that I would be remiss at this point if I did not at least point you toward these kinds of tools. If you earn your living creating databases, then you already know how important data modeling tools can be. In that case, your only interest in this chapter will be simply to see highly abstracted database tools working with BCB. On the other hand, if you're interested in entering the database field, then this is a subject you should master, and hopefully this chapter will inspire you to explore this field in more depth.

Once again, I want to stress that this subject is too wide of the main interests of this book to merit an in-depth examination. Instead, I want to give a quick overview of the subject to alert you to its importance and to the availability of some valuable tools. Unlike all the other chapters in this book, this one is designed to be completed in only a few minutes.

What Are CASE and Data Modeling Tools?

The primary problem solved by CASE and data modeling tools is managing complexity. Most of the databases in this book are fairly simple. Even the more complex databases I've presented, such as the ones for kdAdds or Music, are fairly easy to grasp.

Problems arise, however, when you have huge databases consisting of 30 or more tables that are related in a maze of complex links. If you're wrestling with that kind of complexity, or if you're considering taking on a project of that size, then you will find some kind of data modeling tool to be a necessity.

Good tools of this kind will reverse-engineer a database and show you a diagram depicting the relationship between tables. An example of this kind of diagram, as drawn by SDesigner, is shown in Figure 18.1. While you're studying this screen shot, understand that sometimes pictures shown in books look best when shot in 640x480 screen mode but that tools such as SDesigner were made to be run in much higher resolutions. If you increase the resolution to 1024x768, for example, then you can get a clear view of a larger number of tables.

FIGURE 18.1. A portion of a physical data model displayed in SDesigner.

Powerful data modeling tools will also let you add tables, fields, indexes, and referential integrity to your database. The goal is to allow you to use the mouse to drag lines between the tables to designate how they interrelate. At the appropriate times, dialogs should pop up automatically asking you to specify the details of your links. After you're done, you can save the data model, at which time SQL or some other code that will automatically update the database is generated.

A number of sophisticated CASE tools will also automatically generate code for you. For example, Delphi has CASE tools available from third parties that will automatically generate forms and code for projects after you're through designing the tables and their relationships. At the time of this writing, I do not know of any CASE tools that perform this task for BCB, but I assume that they will appear over the next few months.

The Paradox Data Model Designer

If you have Paradox 7.0, you have a data modeling tool that you can use with BCB. To get started, launch Paradox and select the CUNLEASHED alias. This alias points to most of the Paradox tables that ship with this book. A description of the way to create this alias is provided or referenced in the readme file on the CD that ships with this book.

After you set up the alias, choose Tools | Data Model Designer from the Paradox menu. A window like the one shown in Figure 18.2 appears.

FIGURE 18.2. The Data Model Designer as it appears when first selected from the Tools menu of Paradox 7.0.

The goal of this exercise will be to get an overview of the tables in the kdAdds program from Chapter 14, "Sessions and Relational Real-World Databases." To get started, add all the tables from the database to the Data Model Designer, as shown in Figure 18.3.

FIGURE 18.3. The Data Model Designer after you have added all the tables used in the kdAdds program.

Now that the tables are in place, the next step is to show the relationships between these tables. Click once on the kdNames table, drag the mouse cursor on top of the kdAdds table, and let go. Afterward, you will see the image shown in Figure 18.4.

FIGURE 18.4. The relationship between the kdAdds and kdNames tables as shown in the Paradox Data Model Designer.

At this stage, you might try connecting the kdNames table to the other tables in the database, as shown in Figure 18.5.

FIGURE 18.5. The kdNames table is the controlling table in the relationship between it and kdAdds, kdPhone, kdEmail, and kdMemo.

When you're defining these relationships, be sure you drag from the kdNames table to the tables that are subordinate to it. If you define the relationship in the opposite direction, you end up with a different depiction of the relationship between the tables, as shown in Figure 18.6. This view of the table is not incorrect, but it gives you a different perspective.

If you try to define a relationship between two tables that have no links, a dialog pops up explaining what has happened, as shown in Figure 18.7.

If you drag the mouse cursor between two indexed tables, a dialog that lets you define the relationship between the tables will pop up. You can use this tool to actually design the elements of your database, as shown in Figure 18.8.

That's all I'm going to say about the Paradox Data Model Designer. As I said in the beginning of this chapter, dwelling on Paradox in a book about BCB would make no sense. However, I wanted to give you a brief introduction to this subject so that you can understand something about the tools available on the market.

FIGURE 18.6. The relationship between the kdMemo and kdNames table from the point of view of the kdMemo table.

FIGURE 18.7. The dialog you get when you try to relate two tables that have no links on which a relationship can be defined.

FIGURE 18.8. Establishing a relationship between two tables in the Paradox Data Model Designer.

SDesigner

SDesigner is a much more powerful and much more complex tool than Paradox. To get started with it, you should have ODBC drivers installed on your system. In Chapter 8, "Database Basics and Database Tools," I described how to set up an ODBC connection to an InterBase table.

Assuming you have SDesigner, InterBase, and ODBC installed on your system, you can get started by launching SDesigner. Close all the windows and open the File menu, as shown in Figure 18.9.

FIGURE 18.9. The File menu in SDesigner gives you the option of reverse-engineering a database.

If you select Reverse Engineering from the menu, you can choose the name of the database you want to use from the list of available drivers, as shown in Figure 18.10. If you don't see InterBase in this list, then you probably don't have the ODBC drivers for InterBase installed. In this case, you still have a course of action open to you since SDesigner can reverse-engineer the schematic SQL code produced when you choose Extract | SQL Metadata for Database from the WISQL menu. In particular, you should choose the menu item in WISQL and then save the output to a text file. Then point SDesigner at the text file, and it will reverse-engineer it. The Music.ddl files in the Data directory on the CD that accompanies this book are examples of the type of file that SDesigner can reverse-engineer.

After clicking OK, you are presented with a dialog that lets you select the InterBase database to which you want to connect. I described how to set up these database links in Chapter 8. In this case, I signed into the link to the Music database, filling in my username of SYSDBA and the default password of masterkey. The password might be different on your system.

You can then select which tables you want to examine, as well as which parts of the tables, such as indexes, primary keys, and foreign keys. The selections I made are shown in Figure 18.11.

FIGURE 18.10. Selecting a database name.

FIGURE 18.11. Selecting the tables that I want to import from the Music database. Notice that you can choose to view the primary keys, foreign keys, and other pieces of metadata.

In Figure 18.12, you can see what the diagram of the database looks like after SDesigner has finished the reverse-engineering process. Notice that the links between the tables are visible.

FIGURE 18.12. The Music database after it has been imported into SDesigner.

From the view shown in Figure 18.12, you can perform almost any action you want on the databases. You can physically move around the relative positions of the tables, and you can examine or redefine the metadata in any manner you choose. For example, if you look at Figure 18.13, you can see part of a list of all the indexes in the music database. The columns on the far right designate whether a particular index is primary, foreign, or unique, as designated by the columns headed by P, F, and U.

FIGURE 18.13. Examining the indexes from the Music database.

If you click the Define button at the bottom left of the dialog, you can edit an index. If you click the New button, you can create a New index, and--well, I'll leave it up to you to figure out what the Delete button does!

That's all I'm going to say about SDesigner. I'm sure you can tell from this brief introduction that this tool is very powerful. I have not touched on even one-tenth of the capabilities of this tool. For example, if you drop down the Dictionary menu, as in Figure 18.14, you can see some of the many features of this product.

FIGURE 18.14. The Dictionary menu gives some hint to the capabilities to be found in SDesigner.

Of all the traits of a tool like SDesigner, perhaps the most important is the capability to give a visual overview of your database. You can often spot flaws in your database design after just a glance at one of these views.

ER1

ER1, a tool from Embarcadero Technologies Inc. (www.embarcadero.com, 415-834-3131), is comparable to SDesigner in terms of its scope and capabilities. It does not, however, have as large a presence in the market.

You can reverse-engineer a database in ER1 exactly as you did in SDesigner. Once again, the key to the process is having the ODBC drivers in place. The result of importing the Music database is shown in Figure 18.15. Notice the fancy zoom window in the bottom right of the screen; it shows the currently selected database in a mode that is easier to read. Both SDesigner and ER1 let you zoom in as much as you want on the view of a table, but this handy zoom window is particularly easy to use.

FIGURE 18.15. ER1 can reverse-engineer a database using the same type of technology as SDesigner.

If you click any one table, you can pop up an Entity Editor window that lets you examine the attributes, columns, indexes, relationships, and other aspects of the table. Sample views of the Entity Editor are shown in Figure 18.16 and Figure 18.17.

FIGURE 18.16. The Columns page in the Entity Editor from ER1.

FIGURE 18.17. The Relationships page in the Entity Editor from ER1.

Some of the pages in the Entity Editor are designed to let you enter notes so that you can add comments about a particular table. Your comments, of course, will be attached specifically to a particular table, and they can be viewed by others who want to be apprised of your work or design.

SDesigner also gives you at least this much functionality, if not more. However, I do find that ER1 has a particularly clear view of the tables in a database, as well as a very cleverly designed interface. Remember that this description is not meant to be a comparative review of the two products, and you should definitely examine other sources of information before spending money on any of these tools.

These products all enable you to print out the diagrams they make of a database. You can then hang this printout on a communal bulletin board where it can be referenced or reviewed by others working on your team. In general, both SDesigner and ER1 have numerous features designed to aid a team of programmers in database design.

Cadet

I don't think the talented creator of Cadet would mind if I preface my overview of his product by stating that it is not as powerful as either SDesigner or ER1. It is a much cheaper product to buy, and you can get trial shareware versions for free from the Net. I should add, however, that Cadet has more to offer than the Data Model Designer from Paradox.

　

---

NOTE: I should perhaps add here that price is a big issue on these products. The big professional database tools like ER1 and SDesigner can be very pricey. Finding a relatively affordable product like Cadet can be a boon to programmers. In fact, you should check the CD that accompanies this book to see whether it includes a trial version of Cadet.

---

Cadet can reverse-engineer an InterBase table via ODBC. Note, however, that you do not have as much detailed information available after you import. For example, no lines designate the links between the tables.

Cadet does, however, allow you to draw the relationships yourself using a few easy-to-master visual tools. If you take a few minutes to do this work, you can get a neat, easy-to-read diagram showing the relationship between the tables.

Finally, you can use Cadet to edit the metadata of your database. For example, you can create tables, edit the fields of the tables, or set up indexes.

That's all I'm going to say about Cadet in this book. Before closing this section, however, I want to point out what a great bargain Cadet can be for small programming houses or independent programmers. It does not pack the power of a tool such as ER1 or SDesigner, but it is still a very powerful tool that can dramatically improve your ability to manipulate and maintain a database. For example, take a look at a view from WISQL, as shown in Figure 18.18. When you get down to it, wouldn't you rather use Cadet than a tool like WISQL?

FIGURE 18.18. WISQL provides a very primitive view of a database compared to what you see in a product like Cadet.

Summary

In this brief chapter, I have given you an overview of some of the CASE tools you can use with BCB. I have made no attempt to cover all the products in the field. I have also made no attempt to explore these products in depth, in large part because this book is about BCB, not about any third-party CASE tool, regardless of its merits.

I want to stress that the information shown in this chapter is not a form of review, nor have I tried to compare or rate these tools. On the contrary, my goal has been merely to make sure you are aware of the presence of these tools and of the fact that you can use them with BCB.

CASE and data modeling tools can provide an enormous boost to difficult database projects. If you work with databases all day long, then you probably ought to be using one of these tools. Certainly, they are worth careful consideration for anyone who works full-time in the field. As a rule, after you have spent a day or two with any one of them, you will find it difficult to consider programming without it.

Another great benefit of data modeling tools is that they can teach you a good deal about database design, and they can help steer you in the right direction when creating your data model. Just wandering through the menus of a product like SDesigner is an education in itself, and no one who has mastered the tool could ever be said to be less than a solid intermediate-level database programmer. Becoming an expert at using these tools is likely to improve the skills of most programmers and to add considerably to your knowledge of database design.