DBMS, October 1996

Oracle's Impressive Designer/2000

Oracle's Analysis and Design toolkit takes a big step in usability.


I recently changed jobs, leaving a company that develops application software packages to become a member of a consulting firm. One of my first projects was to assemble our standard developers workbench. I wanted to work with fairly high-level modeling tools that we could use to capture business requirements and convert them into design forms. We wanted to map these directly to a runtime environment that would let us control how we deploy applications. Such a toolset would minimize the amount of code we had to write. It didn't take long before we reallocated my time to several revenue-generating projects, so I failed to complete the workbench, but I do have some interim findings.

In the decade since I started developing business applications and searching for good tools to support this work, the development product model has changed somewhat. In the mid to late 1980s, I worked with PC-based products that bundled a data file manager, programming language, form design tool, and report designer into a single package. Nowadays the database engine is separate from the application development product that includes those other capabilities, but in most cases a single-user version of a database engine is bundled with the development tool. The Internet puts a great deal of stress on this development tool model; that stress is impacting the communities that are using products such as Delphi and PowerBuilder, whose historically rigid two-tier approach to deployment is out of synch with the requirements of a distributed world.

More fundamentally, this product model fails to capture the upfront analysis and design work that needs to occur in the development of larger systems. The addition of third-party modeling tools has produced mixed results. As data modeling tools have matured, their vendors have tightly integrated the modeling environments with existing database and application development products. Such integration makes the external tool an effective member of the development workbench. Other tools that support analysis and design methodologies, such as Popkin's System Architect and Embarcadero's ER/1, have not matured to the point at which they integrate with popular development tools in a way that significantly increases developer productivity.

I have to admit that between the shifting paradigms of information engineering, object-oriented development, and rapid application development on the one hand and the evolution of host-based, LAN-based, client/server, and Internet architectures on the other, it's been pretty difficult for tool sets to keep pace. Development platforms evolve quickly, and third-party developers of analysis and design products have difficulty supporting more than one platform. They must pick a target platform into which they can get sufficient penetration to recoup their investment. On the other hand, development product vendors have completely filled their production schedules, improving the integration of their languages with their data and form definition tools, and enhancing application deployment options and performance. In the past I have not looked to them to improve the richness of their upstream tools.

Enter Oracle

Against this backdrop, a friend convinced me to evaluate version 1.3 of Oracle's Designer/2000 suite. He got my interest by pointing out that this new 32-bit version of Designer/2000 generates Visual Basic (VB) version 4.0 application components, C++ header files, and Web-based applications as well as applications for deployment with Oracle's own Developer/2000 product. I decided to check it out when I realized that the price was no higher than the combination of tools I was already considering. What convinced me to write about it was the comprehensiveness of the toolkit, its support for multiple deployment environments, and the effectiveness with which all of the components of the suite work together to support a variety of development models. Oracle products have come a long way in usability since I started using the database server a couple of years ago. Still, the installation process is agonizing. Although I have done it successfully on several occasions, I still find installing Oracle's SQL*Net middleware (which is necessary to connect a client workstation to a server) to be an adventure. Unless you have the services of an experienced Oracle administrator, get a large hard disk and run Designer/2000 together with Personal Oracle on your PC!

Designer/2000 provides a collection of business and system modeling applications in which you can record business processes, application requirements, and business standards and conventions, convert these into specifications, and automate the generation of applications. (These are summarized in Table 1.) The Designer/2000 toolset is organized into four groups. The first, a group of just one application, consists of Process Modeler, which provides tools for diagramming complex business processes. The second group, the Systems Modeler, provides Entity Relationship, Function Hierarchy, and Data Flow diagramming tools, as well as a general-purpose cross-referencing tool called the Matrix Diagrammer. The third group, the Systems Designer, contains five diagrammers that target the design and implementation phases of development. These include the Data Diagrammer, the Module Data Diagrammer, the Module Structure Diagrammer, the Module Logic Diagrammer, and the Preference Navigator. Finally, Designer/2000 provides a set of generators for converting the design information into Oracle Forms screens and logic, Oracle Reports, DBMS Server objects, Visual Basic application components, Web applications, and C++ class definitions. The shared repository is the foundation for the tight integration between the components, and it supports a system-wide repository navigator that offers an integrated view of all of the entities, functions, and processes in the system. A collection of wizards further integrate the tools by automating the conversion of analysis models into design models.

The inclusion of a business process modeling tool appears to be driven by the increasing acceptance of structured management techniques that promise to improve business operations. The idea of drawing business process models looks pretty obvious from a systems engineering standpoint. Just as the engineer in me wants to model software applications before I build them, I also like the prospect of drawing models of businesses processes and trying out changes on the model before applying them to the organization. Oracle's Process Modeler presents a framework for considering business processes as the interaction of six elements: organization units, which represent individuals, workgroups, or departments that can take independent action process steps, or tasks, which may involve data entry, decision making, or generating outputs flows, which present the movement of information or material or the passage of time stores, which represent collections of information or materials triggers, events that cause process steps to occur outcomes, the results of a process or process steps Process Modeler can present a property sheet for each of these diagram elements, allowing an analyst to capture time and cost information for each one.

Most businesses are relatively complex, so a model is likely to consist of a number of diagrams, each showing part of a process, with perhaps one diagram showing an overview of the whole process at the top level. A static diagram can illustrate a business process very well, but a diagram is much more effective if you can actually see information and materials moving through the process. Diagram animation is one of the major features provided by Process Modeler. You can create a richly animated process model by using Object Linking and Embedding to associate relevant audio and video clips with process steps. Running the animation highlights each of the flows and processes in turn, illustrating graphically how each works. For each process step, store, or flow, the icon is animated and plays the associated sound (you must start embedded video clips manually). Each item's animation continues for a time proportional to the time value specified in its property sheet. During animation, a process step flashes in a different color if it is waiting for previous processes or flows to complete before it can start. This feature is extremely useful in highlighting bottlenecks in a process.

System Modeler

Although the Process Modeler is innovative and engaging, process modeling is often outside of the scope of a development project. It's more traditional to start by modeling entities, data flows, and functions, and Designer/2000 works perfectly well if you start here. (See Figure 1.) As a bonus, if you did choose to use the Process Modeler, it stores diagram elements in the shared repository; so the Entity Relationship, Data Flow, and Function Hierarchy Diagrammer will use the definitions of those elements if they are there. Designer/2000 presents entity-relationship modeling as a process for identifying the important things in an organization (entities), the properties of those things (attributes), and how they are related to one another (relationships) in a way that is independent of any data storage or access method. I've always identified entities, attributes, and relationships in the context of data modeling tasks, so it was a struggle for me to dissociate the analysis task from the design task of mapping the analysis into data models.

The Function Hierarchy Diagrammer provides an organizational chart-style interface that lets you create hierarchies of all the functions that are performed by a business. You can start at a very high level, by defining very general functions or by reusing the analysis performed with the Process Modeler. From there you can drill down, decompose business functions, define elementary business functions, identify which can be automated, and indicate how they interact with data. Elementary business functions ultimately map to individual screens, forms, reports, and utilities in the application system. For each of these, the Function Hierarchy Diagrammer presents a property sheet that captures a full, detailed description of the function, the frequency of execution, the speed of response required, the event that acts as a trigger for the function, any events that are the outcome of the function, and the business rules and logic associated with the function.

With the entity-relationship model in hand, you can drill down on each function to specify the information that each function will retrieve, create, or change. Performing this analysis in conjunction with the ultimate users of the system may uncover shortcomings in the entity-relationship model that should be addressed before the system moves from analysis to design. Designer/2000 provides a series of matrices for entry of relationship information, including "CRUD" (create, retrieve, update, delete) rules. (See Figure 2.) The analyst can access the matrix through the Function Hierarchy Diagrammer or the Repository Object Manager. With CRUD matrices, an analyst can specify when a function will create, retrieve, update, delete, or archive an instance of an entity and can drill down to define CRUD rules for specific attributes. Reviewing these matrices provides a good check for entities that never get created or deleted and for functions that lack operations. Once complete, this information will drive the Application Design Wizard to determine how application modules use tables.

The final piece of the Systems Modeler is the Data Flow Diagrammer. It's not likely that you would use both the Data Flow Diagrammer and the Function Hierarchy Diagrammer heavily, because they both seek to define how the target organization's business processes interact with the entities that are the object of those processes. Data flow diagramming tools provide somewhat richer semantics, directly addressing the interaction of functions with the flow and storage of information. Most data flow diagramming tools suffer from a lack of integration with other development tools; Oracle's diagramming tool is unique in that it becomes an effective practitioner (and makes your investment worthwhile) by reusing the specifications you enter throughout the successive steps of the process.

From Analysis to Design

Once you are satisfied that the analysis performed with Systems Modeler tools accurately depicts the business for which you are developing an application, you run the application design and database design wizards that populate the design tools that you will work with in Systems Designer. The Database Design Wizard creates and maintains database designs based on entity models captured in the Entity Relationship Modeler and recorded in the repository. It creates tables to record instances of each entity, columns to store the attributes, and constraints to implement the relationships between entities. It also creates constraints to enforce any unique identifiers that you have defined, and indexes to support foreign keys. Other tools will subsequently use the information that's been added to the repository by the database design wizard to generate the SQL statements required to create the database itself.

The Wizard uses a tabbed dialog with matrices for each type of mapping (entities to tables, IDs to keys, relationships to constraints, and so on) to give the designer substantial control over the scope of the conversion and the mappings employed during the conversion process. Most interesting is the flexibility with which you can map subtypes; the Wizard lets you resolve subtype scenarios into a single table with a discriminator column, child tables with only columns specific to the subtype (the Wizard calls this an "arc"), and separate tables with a parent table that retains common information about the subtypes.

To move effectively into the System Designer, you need to understand Designer/2000's concept of a module, which is actually a fairly common systems view in which a module represents a computer program that implements a discrete business function. A module network specifies the order in which the modules are called. System Designer classifies modules as Menu, Screen, Report, PL/SQL, or Utility modules and supports definition of several specific types of screen and report modules. Menu modules provide top-level application partitioning and control functions. Screen modules represent the user interface for entity maintenance and transaction entry procedures. PL/SQL and utility modules perform processing tasks in the database, which may or may not be synchronized with the work of a screen or report module.

The Application Design Wizard deduces which program modules are needed to support the functions captured in the function hierarchy, data flow, and process models, and what data these modules should access. Based on its analysis, the Application Design Wizard creates first-cut application designs in the formats used by the System Designer's Module Structure Diagrammer and Module Data Diagrammer. It converts functions into candidate modules, which can then be implemented as screens, reports, manual procedures, or utilities; it employs a set of configurable rules to determine how to represent a specific function. The Wizard also converts function data usages to module data usages, which define how a module uses the tables and columns in the database. The Application Design Wizard then uses the module-to-business-unit association to create candidate menu modules and menu structures. The resulting modules and their usages can be used for generating applications via the generators.

Systems Designer

If you've been working along the path from analysis to design to generation, it's a simple matter of opening the Systems Designer tools to view and adjust the design output from the wizards. Again, you have a choice: You can choose not to use the Systems Modeler tools and start your project in the design phase. If you follow this course, you should start by using the Database Designer to define data structures and the Module Structure Diagrammer or Module Data Diagrammer to define processes that act on the data. The Database Designer will look familiar if you've worked with database schema design tools. You get boxes that describe tables (with a primary-key region on top and non-key attributes below) and connecting lines with varying numbers of crow's-feet to denote different types of cardinality and dependency relationships. You can design views and snapshots using the diagramming tools, working in dialogs to specify the columns to include and the query conditions that should qualify the result set; views and snapshots created this way can themselves become the subject of forms and reports.

To distinguish itself from the Systems Modeler's Entity Relationship Diagrammer, the Data Designer provides entry forms for specifying physical attributes of the table and defaults for use of a table or view by a module. Physical attributes include the database and tablespace in which the table should be defined and how much space to reserve for it, as well as whether a journal table should be created. If it should, the Forms Generator creates triggers in each of the forms that uses the table to record every insert, update, or delete operation in the journal table. Drilling down to column definitions, you can identify the default type of user interface (UI) control that should represent the column on forms, a default display size, and display sequence, as well as the usual data type and amount of storage to allocate. The Data Designer also permits specification of behavior that the Forms Generator should implement to enforce constraints, starting with whether to enforce them at the client, the server, or both, and the message to display when violations occur.

It's easy to get confused about the three tools whose names start with "Module." The Module Logic Navigator is a PL/SQL coding assistant that provides syntax checking and user-defined code formatting, and the ability to drag and drop valid PL/SQL constructs from a selection tree, import, and export. The Module Structure Diagrammer manages the hierarchy of functions in an application, whereas the Module Data Designer manages the detailed information required to generate a specific module. This is where the rubber really meets the road: Here the data structure and information about physical implementation recorded by the Data Designer merge with the functional hierarchy of the application.

There will be one Module Data Diagram per module. A Module Data Diagram looks like a portion of the data designer diagram, with boxes drawn around entities that will share windows or blocks within windows in the generated application. Using a tabbed dialog interface to collect these specifications, the Module Data Diagram describes how the module uses tables, views, snapshots, and columns. By specifying the mode in which tables or views should be linked, you can create master-detail, matrix, and LOV (List of Values) style entry forms and master-detail, matrix, control break, and label-style reports. Although the nomenclature of this application is arcane (it refers to detailed table usages, blocks, and placement items), the objective is to complete a series of property sheets that specify how the generated application will treat each data element.

After defining the data structures and mappings of data to functions through the other System Designer diagrammers, it's almost time to generate your application. One final, critical step requires entering the Preferences Navigator and setting preferences. Preferences serve as defaults for the values assigned to variable settings during the generation process; there are dozens of them, covering everything from coding style to the type of code to use for validation to the justification of prompts regarding items that themselves have specific styles. All of the preferences have default values, which Designer/2000 refers to as the "factory" settings. You can override the factory settings at any level so that your custom preferences will apply throughout the application, only to usage of specific data elements, or only to certain forms. Preference Navigator presents a Windows explorer-style interface, which exposes a property sheet for the currently selected preference item. Custom settings cascade down the preference hierarchy, and an icon next to the preference value indicates whether it represents a factory or custom setting.

It's Generation Time

Having made it this far, I was extremely eager to try to generate an application. Designer/2000 provides a collection of generators. Of course there is a server generator that generates data definition language (DDL) statements to create database objects from definitions recorded in the Designer/2000 Repository. These statements are written to command files and can be executed on the target database. Of great interest to most shops that use the Oracle design and development workbench is the Forms generator, which takes the specifications and preferences entered in System Designer and then creates entry forms, menus, and modules to make those specifications and preferences into a live application. Oracle developers using the Forms generator will likely use the Reports generator as well to create reports that can be used with the Oracle reporting engine.

Looking at Designer/2000 as a potential general-purpose application design suite, I'm more interested in its ability to generate Visual Basic forms, Windows Help, Oracle's WebServer applications, and (to a somewhat lesser extent) C++ class definitions. For instance, the VB generator creates a VB form for each module defined in the design system. It creates a separate region on the form, which Oracle calls a zone, for each "base" table usage in the module. It then creates a UI control for each of its column usages, plus the column usages of "lookup" tables; the choice of control depends on preference settings. Much of the generated interface is driven from the properties of these usages - for example, captions, types of control, and zone styles. Templates determine the look and feel of the forms, zones, dialogs, frames, and controls that make up the generated Visual Basic application. They are defined as a Visual Basic project and hence are created or modified using the Visual Basic design environment. Oracle includes a set of standard templates with the product; the designer can extend or replace these templates.

Of course, using Designer/2000 in conjunction with Visual Basic compromises one of the objectives I stated at the outset: the integration of implementation and application deployment into the toolset. Once Designer/2000 generates the VB forms and menu system, optimization and deployment is up to you. You would achieve much tighter analysis to deployment with Developer 2000, but issues beyond the scope of this column make VB developers unlikely to switch, even if they use Oracle tools to design their applications and manage the data. Even so, Designer/2000 brings a great deal of value to the design side of the process. Although the user interface for some of the diagrammers is clumsy, and the repetitive use of a grid for capturing details is two-dimensional and arguably not a particularly creative way to capture this information, sharing a comprehensive design repository makes these very effective tools. Overall, Designer/2000 comes the closest to converting a non-procedural specification into an application of any tools that I have investigated. I'm glad I checked it out; it will raise the standard I set for additional products of this nature.


Tom Spitzer is managing consultant for application solutions in the San Francisco office of AmeriData Consulting. You can email Tom at
tspitzer@ameridata.com.


* Oracle Corp., 500 Oracle Pkwy., Redwood Shores, CA 95065; 800-672-2537, 415-506-7000, or fax 415-506-7200;
http://www.oracle.com.



TABLE 1
Designer/2000 Products and Functions

FIGURE 1

--The Entity Relationship Diagrammer makes you focus on defining the relationships between entities in your business environment and
their attributes.

FIGURE 2

--The Matrix Diagrammer lets you define a variety of matrices by specifying logical row and column pairs. Here, the analyst has listed
Business Functions by row and Entities by column to create an Entity to Business Function CRUD matrix.

Copyright © 1996 Miller Freeman, Inc. ALL RIGHTS RESERVED
Redistribution without permission is prohibited.

Please send questions or comments to mfrank@mfi.com
Updated Wednesday, September 25, 1996