Week 3

Day 20

Using Oracle Database
Designer and Oracle
Designer/2000 in
Application
Development

Although this lesson comes near the end of the book, you may want to think of this lesson as the starting point for developing an Oracle database application. In this lesson, you examine the use of Database Designer and Designer/2000.

Database Designer was announced by Oracle Corporation in 1997. It is an entity-relationship (ER) design tool, designed to compete with Logic Works' ERwin and Sybase's S-Designor. All three tools provide a graphical interface for creating an entity-relationship diagram which can generate the DDL statements that create a working database. Database Designer, and its competitors, are

appropriate for projects where it is practical for one person to create and maintain the data model. Also, these tools focus only on the development of a logical data model and its implementation as a physical database; they do not help the application designer with process modeling, dataflow modeling, and other facets of large application design.

In contrast, Designer/2000 is a comprehensive suite of tools that supports the analysis, design, and implementation phases of a large project. You can think of Designer/2000 as a "cradle-to-grave" development methodology. These tools can help an entire team to gather, document, and verify the requirements for an application on many levels—data model, process model, and data flow, to name a few. These tools work together such that the outputs produced by one tool become the inputs used by another tool. At the heart of Designer/2000 is an Oracle-based repository that contains a description of the elements of the application to be built.

You can order Database Designer and Designer/2000 directly from Oracle Corporation or an Oracle reseller. You can also download a trial version of Database Designer from the Oracle Web site at www.oracle.com. To get the most from this lesson, you'll want to have a copy of Database Designer so you can follow the steps in each exercise.

NEW TERM
A repository is a set of database objects that contains metadata—that is, data about data. For example, the Designer/2000 repository contains information about the data model, the process model, and many other design aspects of an application.

A natural question is: Which tool is better—a tool like Database Designer (including ERwin or S-Designor) or Designer/2000? The answer is: It depends on "where you are going." To use an analogy, if you are planning a long weekend in your mountain cabin, it might suffice to pack a small overnight bag. However, if you are planning a six-month expedition to a remote region in Central Asia, packing an overnight bag would be a grave mistake; instead, it would be prudent to pack a dozen large crates with gear that might be needed to deal with unforeseen circumstances.

Similarly, Database Designer, ERwin, or S-Designor are good choices for a project that is

• Intended for a small organization
• Not too ambitious in terms of the features to be provided by the application
• Staffed by a handful of developers who have access to the end users

However, by themselves, Database Designer, ERwin, or S-Designor would be inadequate for a project that is

• To be implemented for a large enterprise, spanning many departments and divisions
• Seen as a threat by influential political players in various parts of the organization
• Very ambitious in terms of the features to be provided by the application and the technologies to be employed
• Staffed by many individuals—analysts, designers, developers, testers, technical writers, and trainers

If you haven't worked with Designer/2000, don't expect to receive the software on Monday and begin building finished applications on Tuesday. This is a complex suite of tools that requires significant training to exploit. Also, don't expect a single person to +become intimately familiar with every feature in every component; that just isn't realistic. Instead, it makes much more sense for each member of the development team to focus on the components that are associated with his or her role in the development process. In this lesson, you get only a glimpse of the Designer/2000 components; a thorough tour of Designer/2000 would require a book in itself.

Creating an Entity-Relationship Diagram
with Database Designer

There are many advantages to using a tool such as Database Designer instead of maintaining a script containing SQL statements. You are far less likely to make typographical errors with Database Designer; for example, it validates that the column referenced by a foreign key is itself a primary key. You can print the Entity Relationship Diagram and present it in a meeting; it is much more difficult to visualize the data model represented by a script.

Let's look at the steps that are required to create a new ER diagram. To invoke Database Designer in Windows 95, press Start | Programs | Database Designer | Database Designer. When the program is first started, you will see a menu and toolbar at the top of the screen. To create a new diagram, select File | New from the menu. A window will appear in which you have two choices: you can either create a new diagram or reverse engineer an existing database. The default option is to create a new diagram (see Figure 20.1). Click OK. You should see a window labeled ODD1—the default name for the first diagram you create.

Figure 20.1.
Creating a new
diagram.

Adding a Table to the Diagram

To begin with, let's add the Student table to the diagram. In the bottom row of the toolbar, there are five buttons that correspond to these elements in an ER diagram:

• Table
• View
• Mandatory foreign key
• Optional foreign key
• Primary key

For example, to create a new table, you would click the Table button on the toolbar, position the pointer on the diagram where you want the table to be placed, and left-click (see Figure 20.2).

To specify details about the new table, move the pointer over the table and double-click. A window, labeled Edit Table, will appear, and it contains six tab folders:

• Table
• Column Definition
• Column Display (optionally used with Designer/2000 Application Generators to specify the display characteristics of the table's columns)