You perform system design at the end of analysis and the beginning of design. The tools you use bring the analysis logical model of entities and relationships into the physical model of tables and foreign keys. They bring the logical model of functions
into the physical model of reports, screens, and menus. The tools in system design include the following:
Some of the tools discussed in this chapterthe Data Diagrammer, the Module Structure Diagrammer, and the Preferences Navigatorhave very similar screen formats. At the top of the screen is a pull-down menu, as well as a toolbar of icons. All
the functions that these icons perform also appear on the menu bar under Edit, View, Utilities, and Tools. Whether the user wants to use the icons or the menu is simply a style choice. For the most part, the descriptions in this chapter refer to the icons
rather than the menu bar.
Initially, you can create a new diagram by either choosing New from the File menu or clicking the first icon on the upper-left corner of the page. When choosing this icon or any icon from the Designer 2000 toolset, you always have help available. The
help message appears in a balloon to the right of the icon when you move your cursor to it without clicking the mouse. You can also get help by selecting anything on your diagram (icon or element) and clicking the Question Mark icon. Finally, you can
search for help on any topic from the help menu pick.
You use the Database Design Wizard for generating table definitions from entity definitions. Before running the tool, make sure that your entities are properly defined. To check them, you can run the Entities With No[el] reports off the Repository
Reports. The Matrix Diagrammer is also helpful in ensuring that all of the manipulation of entitiesCreate, Retrieve, Update, Delete (CRUD)is covered under at least one function. For more information on running these reports, see Chapter 24,
You can invoke the Database Design Wizard from either the Repository Object Navigator (RON) or Entity Relationship Diagrammer. Select Utilities | Database Design Wizard.
Initially, the tool loads all the entity and table information available in your application.
The first screen that appears is the Table Mappings tab (see Figure 27.1). It contains all the entity names, whether they have been mapped to a table, and the table name. If you want to generate or regenerate an entire entity into a table, select the In
If the Design Wizard has not yet generated a table name, leave the table name blank. It is generated when the table definition is generated.
The rest of the tabs refer to information specific to each table.
On the Column Mappings tab (see Figure 27.2), you can choose columns to generate initially or columns to regenerate. You might want to regenerate a table because it relates to a new entity or because you did more analysis and added new attributes to the
entity. On the Column Mappings tab, you can remap attributes to existing columns, add new attributes, or add new columns. If an entity is in the set, there is no reason to add the attributes; they are included. You can select a single attribute without
selecting the table. If the table does not exist, only these attributes selected generate columns in the table.
On the Primary Keys tab, you can choose primary keys to generate initially or primary keys to regenerate. On the Primary Keys tab, you can remap attributes to existing unique keys or add new unique keys to the table. If an entity is in the set, there is
no reason to add the keys; they are included. You can select a single key without selecting the table. If the table does not exist, only these unique IDs selected will generate unique keys in the table. The screen looks the same as the screen for the
Column Mappings tab but shows unique IDs and primary keys instead of attributes and columns.
On the Foreign Keys tab, you can choose foreign keys to generate initially or foreign keys to regenerate. Here, you can remap attributes to existing foreign keys or add new foreign keys to the table. If an entity is in the set, there is no reason to add
the keys; they are included. You can select a single key however without selecting the table. If the table does not exist, only the foreign keys selected will generate foreign keys in the table. The screen looks the same as the screen for the Column
Mappings tab but shows relationships and foreign keys instead of attributes and columns.
On the Indexes tab, you can choose indexes to generate initially or indexes to regenerate. Here, you can remap relationships and unique IDs to existing indexes or add new indexes to the table. If an entity is in the set, there is no reason to add the
indexes; they are included. You can select a single index without selecting the table. If the table does not exist, only the indexes selected will generate indexes in the table. The screen looks the same as the screen for the Column Mappings tab but shows
foreign keys and indexes instead of attributes and columns.
For each table definition generated, you can set the options for what types of items are created or modified (see Figure 27.3).
For each table definition generated, you can set the options for what data storage structures store the table. If you provide no information, the Database Design Wizard uses the defaults. If you give no table prefix, the Wizard uses the short name of
the entity. If you choose the Don't Commit option, the Wizard validates your choices but does not generate the tables. For the first time through, choosing this option is a good idea to ensure that your selections generate properly.
Once you are satisfied with all your choices, you can click the Start button. The Design Wizard Output window prompts you as it generates the definitions, letting you know what steps are taken. Any warnings (such as a table is missing for a foreign key)
or errors (such as a table name is too long) display on-screen. Once the generation is complete, you are returned to the Table Options tab so you can continue additional generation or leave the tool.
You use the Data Diagrammer to create a physical model of the database. This physical model is then used by the DDL Generator to generate the Data Definition Language (DLL) that is run to create tables, views, keys, and snapshots.
Start the Data Diagrammer by clicking its icon and entering your user ID, password, and application. Across the top of the screen, you see a set of icons used for designing a diagram (see Figure 27.4).
To create a diagram, you need to create summary information, add tables, link tables together with foreign keys, add views and snapshots if necessary, and save the diagram.
You can enter any summary information you want to display on the top of the diagram. To edit the summary information, choose File | Summary Information (see Figure 27.5).
If you used the Database Design Wizard, tables exist in RON, the Repository Object Navigator. You could add tables into RON directly or through another database diagram. To select an existing table or view, select Edit | Include. Select the table or
view from the list available.
When you add a table to the diagram, any existing relationships to tables already on the diagram are also added.
You might want to create a table because you forgot an entity in your analysis so the table you need was not generated, or you might want to simply create your system starting at the design level. To create a new table, click the Table icon (refer to
Figure 27.4). Click where you want the table to appear on the diagram. The Data Diagrammer prompts you for a name, alias, and display title. To create a view, select the View icon. To create a snapshot, select the Snapshot icon.
To edit a table, double-click the table on the diagram. You see a set of tabs. The Table tab lists the information needed for generating the table (see Figure 27.6).
The Column Defn tab lists the definition information for columns. Tables generated from entities have columns named with the table prefix joined to the attribute name. Any spaces are replaced with underlines. Here you can edit column names, sequence,
data type, length, and so on.
The Column Display tab lists the display information about a column. The information is used when table definitions are used in creating screen and report modules in the Application Generator. Whether or not an item should be displayed, the display
type, the display length, and the display sequence are modifiable here.
The Constraints tabs enable you to define keys and check constraints. You can define the names, columns, and rules on these tabs (see Figure 27.7).
The Validation Tab enables you to enter a valid list of values for a column as well as a failure message if the column is not filled with a valid item (see Figure 27.8).
To edit a view, double-click the view on the diagram. You see a set of tabs. The tabs are very similar to the table tabs, except for the Base Relations tab and the Base Cols tab. The Base Relations tab is where you add all the tables included in the
view. You define their sequences and aliases here. The Base Cols tab is where you list the columns within the included tables. You define their aliases and sequences here.
When you add a new table to the diagram, it can relate to an existing table. You also might want to add a relationship between two existing tables. To create a foreign key, you need to click one of the foreign key iconsthe Mandatory Foreign Key
icon or the Optional Foreign Key icon (refer to Figure 27.4). After selecting the icon, select the From table and then the To table on the diagram. You are then prompted for the constraint name.
Sometimes, you might want to show an arc across multiple foreign keys. First, select each foreign key in the arc by selecting one and then holding down the Ctrl key while selecting the others. Then, select the table that they arc to. The Create Arc icon
becomes active (refer to Figure 27.4). Click the Create Arc icon, and the arc appears. To remove a foreign key from an arc, click the arc and then hold down the Ctrl key while selecting the foreign keys to remove. The Remove from Arc icon becomes active.
Click the Remove from Arc icon, and the foreign keys are removed from the arc. If you want to add a foreign key to the arc, click the foreign key to add. The Add to Arc icon becomes active. Click the Add to Arc icon, and the foreign key is added to the
You can create a primary key on the diagram rather than through the Constraint tab on a table or view. Select the first item in the primary key. Hold down the Ctrl key and select any other items in the key. Click the Primary Key (refer to Figure 27.4).
To save the diagram, click the Save icon (refer to Figure 27.4). You can name your diagram anything you like; it probably makes sense to name it something related to the top menu level.
Often, you could have multiple diagrams for one application in Designer 2000. For example, a business might have an accounting department and a payroll department. Both departments could have separate diagrams, and a third diagram might depict all the
departments in the business.
You can make changes to a table or its foreign keys in either the Data Diagrammer or RON. The changes are not reflected on other diagrams that use the same tables. The user might have a reason to keep the changes offfor example, to reflect an
earlier point in time or the business opinions of another department. It is possible to take into account the changes from other users and consolidate them into the current diagram. To do so, choose Edit | Consolidate from the menu bar. At this point, you
can choose whether to consolidate a specific table, foreign key, or the entire diagram.
If foreign keys have been added to tables that are on your diagram, you can add them to your diagram by choosing Edit | Include | Relationship. All the relationships missing from your diagram appear on a list, and you can select the ones you want to add
to your diagram.
There are several ways you can make the diagram easier for the customer to follow. You can change the layout to make the table layout easier to follow, and you can change the colors and line widths.
When you add tables to a diagram, you place them where they seem to fit, and as you add more tables, the layout might become crowded. When the diagram starts to look ugly, it is time to use the Autolayout icon (refer to Figure 27.4). When you click the
icon, the tables and foreign keys shift around for readability. If you don't like the way the computer reset the layout, you can click the Autolayout icon again. You can continue to click the icon until you are happy with the outcome. You can go to the
last autolayout using the Revert Layout button.
Sometimes, you add a number of new tables to a specific area, and the layout of these tables is difficult to follow. To reposition them, you need to select just those tables and click the Autolayout button. To reposition the tables to another area on
the diagram, select the tables and choose Utilities | Autolayout to New Area. Select where you want to reposition the tables and drag an area large enough to fit them. When you release the mouse, the tables are repositioned in the new area.
It is possible to change the colors of tables and foreign keys, the fonts of words, and the line width of table outlines and foreign keys. By changing these items, you can make the diagram more readable and add intelligence. For example, you can fill in
all payroll tables with red.
You can change all of the diagram or just one specific table, foreign key, or combination. To change the entire diagram, choose Edit | Select All. To select one or more tables or foreign keys, select one and hold down the Ctrl key while you select all
To change the line width, select the Line Width icon (refer to Figure 27.4). Choose a line width from the items listed. To change the line color, choose the Line Color icon. Choose a line color from the colors listed. To change the fill color of tables,
choose the Fill Color icon. Choose a color from the items listed. To change the font of any selected text, choose the Text icon. You can change the font, the font style, and the text size.
You can also change the preferences for specific types of items. By choosing Edit | Preferences, you can set all tables to be one color, all views to be another color, and all snapshots to be yet another color.
When you work with large diagrams, it can become difficult to keep track of where you are on the diagram. The diagram might take up many pages when printing. There are simple ways to deal with these irritations.
Select Edit | Navigate and choose either a table, view, snapshot, or foreign key. The cursor selects the requested item, and the focus of the screen changes to include the item.
When a diagram is filled with many tables, it often becomes very large, sprawling horizontally or vertically. As a result, many blank pages might fill the borders of the diagram. You can reposition the diagram to use the least number of pages by using
the Minimize Number of Pages icon. Clicking this icon repositions the entire diagram so that it uses the least number of pages.
You can zoom in and out of your picture to get a better idea of how an area of the picture looks or how the entire picture looks. The magnifying glass icons enable you to perform this function. Use the Normal Size icon to return to normal size (refer to
Figure 27.4). Use the Enlarge icon to enlarge. Use the Shrink icon to shrink.
If you are not satisfied with the generation of any tables or there are new entities you want to generate as tables, you can do so from the diagrammer. Click the Database Design Wizard icon to regenerate or generate any tables from entities (refer to
Figure 27.4). For more information on the Database Design Wizard, see the "Database Design Wizard" section earlier in this chapter.
If you have made changes to a table that you want to see reflected in the entity the table was generated from, click the Table Entity Retrofit icon (refer to Figure 27.4). You can choose what tables to retrofit, what entity they return to, and what
columns and constraints should be passed back.
Once you are satisfied with what you have created in the Data Diagrammer, you can generate the DDL for creating the tables, views, snapshots, and constraints on your database. Make sure you save the diagram before generating. Click the Generate DDL icon
(refer to Figure 27.4). Select what tables, views, snapshots, and constraints you want to generate. The scripts to create the items are created at this point. The scripts receive the following names:
The XXX is defined as the short name for your table. You can examine the scripts and run them right from the generator. The DDL Generator also generates DBA information such as triggers, tablespaces, clusters, and so on.
You use the Application Design Wizard to generate modules and menus from functions. Before running the tool, you should make sure that your functions are properly defined. To check them, run the Matrix Diagrammer to ensure the functions are covering all
the CRUD for the entities to be generated. For more information on running these reports, see Chapter 24, "Repository Administration." Finally, the Database Design Wizard should have already generated the table definitions so that the table
usages are recognized by the Application Design Wizard.
You can invoke the Application Design Wizard from either the Repository Object Navigator, the Function Hierarchy Diagrammer, or the Dataflow Diagrammer. Select Utilities | Database Design Wizard.
The Application Design Wizard selection screen appears (see Figure 27.9). You can select the start function level, the module prefix, the language options, the merge granularity, and whether to generate modules or menus.
Merge granularity refers to the level of information coming from the functions. The transfer can be at the attribute level or only the entity level.
Once you click the Generate button, the system generates the modules from the candidate functions. You are prompted when the generation is complete. Next, you can click the Show Results button to see a Notepad file showing what functions were generated
The module names take on the following format: the prefix (if any was given) 0010 (incrementing by tens from the initial elementary function through the last function). You can edit the name in RON.
Functions that are elementary generate as screen, report, or manual operation modules (if there is no entity usage). Functions that are not elementary generate as menu modules.
To regenerate a module, you must first delete it.
You use the Module Structure Diagrammer to set up a hierarchy of modules that are generated into a menu.
Start the Module Structure Diagrammer by clicking its icon and entering your user ID, password, and application. Across the top of the screen, you see a set of icons used for designing a diagram (see Figure 27.10).
A menu consists of different menu levels and actual screens, reports, procedures, and utilities. You can start your menu at the top level and work down, or you can select groups of modules that belong together and then link them together under menu
You can enter any summary information you want to display on the top of the diagram. To edit the summary information, choose File | Summary Information (see Figure 27.11).
You can either use existing modules in your diagram or create new modules. If you forgot a module or realize when you are creating your Module Structure Diagram that you should include a new module, you can add one. Otherwise, you are better off
creating your module in the Module Data Diagrammer. For more information on using the Module Data Diagrammer, see Chapter 28, "Client Application Generation."
Sometimes, a module is similar to an existing module. Rather than create a new module, you can copy an old one and give the copy a new name. To copy a module, click the Copy Module icon, which is the fifth icon from the right on the bottom bar. Select
the module to copy and then drag the cursor to the spot for the copied module. You are prompted to give the name and description information.
To include an existing module on the diagram, choose Edit | Include Network. A list of modules appears. You can pare down the list by selecting a type or by entering a string contained in the module's short name. Once you choose an item, it is added to
You can also add a new module to the diagram. Decide what kind of item you want to put on the diagram and select its icon: Menu, Form, Report, PL/SQL, or Utility (refer to Figure 27.10). Once you place the cursor on the diagram, you are prompted for a
short name, a description, the module type, and the language the module will be generated in.
If you want to edit information about any of the modules, double-click the module on the diagram. You then have a set of tabs for defining module information. The first tab, Module, defines general module information (see Figure 27.12).
The Implements tab refers to what functions or business units are implemented by the module. If the module is new or the module implements additional functions, you can add these functions here.
The Parameters tab lists the parameters needed to run this module, their data types, and whether the parameters are input, output, or both.
The Access tab lists the users or groups that can access the menu. Groups are set up in business units to determine what roles can run certain functions. When the functions create modules, these groups get passed along to the modules. You can change or
add these groups here.
Certain tabs are only available to client modulesor screens, reports, and menus (see Figure 27.13). The Client Details tab contains information specific to generating a client tool.
If you are generating a utility or PL/SQL, you have a Server Details tab (see Figure 27.14). The server details list items specific for generating a function or proceduretype, name, database, return type, and so on.
The Program Data tab lists any defined variables that the SQL uses, such as character fields.
The Data Structure tab lists any cursors or virtual tables that the SQL creates and uses. You can copy a structure from a table structure.
Once you have added a number of modules to your diagram, you can start to place them under a menu level. To do so, click the Make Slave icon (refer to Figure 27.10). Select the module to be at the slave level. Drag the cursor to the parent module. The
slave is placed under the parent.
To save the diagram, click the Save icon (refer to Figure 27.10). You can name your diagram anything you want; it probably makes sense to name it something related to the top menu level.
Once you've saved the initial diagram, you might want to move items around to make more sense structurally.
Once you have created your diagram, you might want to resequence certain modules under the parent. To do so, click the Reposition icon (refer to Figure 27.10). Select the module you want to resequence. Drag it into the module you want to follow it and
then release the button.
Sometimes, it makes sense to move a module from one area of the diagram to another. To reparent a module, click the Relocate icon (refer to Figure 27.10). Select the module you want to move. Drag the cursor to the new parent and release the button or
drag it to empty space to make it an orphan.
If you delete a parent module from the diagram, the slave modules hook up to the parent above their original parent. To delete the entire chain, use the Delete Association icon (refer to Figure 27.10). Click the icon and select the parent module you
want to delete. The module and all its slave modules are deleted.
You can make changes to a module's definition or its point in the structure in either the Module Structure Diagrammer or RON. The changes are not reflected on other diagrams that use the same modules. You might have a reason to keep the changes
offfor example, to reflect an earlier point in time or the business opinions of another department. It is possible to take into account the changes from other users and consolidate them into the current diagram. To do so, choose Edit | Consolidate.
At this point, you can choose whether to consolidate a specific module or the entire diagram.
When you work with large diagrams, it might get difficult to keep track of where you are on the diagram. The diagram can take up many pages when printing. There are simple ways to deal with these irritations.
As you add slaves to parent modules, they make the diagram larger and larger. Sometimes, you want to look at the diagram at a higher level and ignore the lower level. If a parent has a minus sign to the right of it, you can recompose it. If a parent has
a plus sign to the right of it, you can decompose it in order to see more modules. To decompose an item, click the Collapse icon (refer to Figure 27.10). To decompose an item to its lowest level, click the Collapse All icon. To recompose a decomposed item,
click the Expand icon. To recompose an item and all the items below it, click the Expand All icon.
There are three types of diagram layout. (See Figures 27.15, 27.16, and 27.17.)
To choose a specific layout type, select the icon: Horizontal, Vertical, or Hybrid (refer to Figure 27.10).
You can also choose a structure for an area of the diagram from a parent down. Select the parent of the area you want to restructure. Then, select the Horizontal icon or the Vertical icon.
You can change the colors of module, the fonts of words, and the line width of module outlines and breakdown lines. By changing these items, you make the diagram more readable and add intelligence. For example, you can fill in all screen modules with
You can change all of the diagram or just a specific module or connection line combination. To change the entire diagram, choose Edit | Select All. To select one or more modules or connection lines, select one and hold down the Ctrl key while you select
all the items.
To change the line width, select the Line Width icon (refer to Figure 27.10). Choose a line width from the items listed. To change the line color, choose the Line Color icon. Choose a line color from the colors listed. To change the fill color of
modules, choose the Fill Color icon. Choose a color from the items listed. To change the font of any selected text, choose the Text icon. You can change the font, the font style, and the text size.
You can change the preferences of your diagram so that screens are always one color and menus always another color and so on. To make these global changes, select Edit | Preferences (see Figure 27.18).
When you work with large diagrams, it can be difficult to keep track of where you are on the diagram. The diagram can take up many pages when printing. There are simple ways to deal with these irritations.
When you fill a diagram with many modules, it often becomes very large, sprawling horizontally or vertically. As a result, many blank pages might fill the borders of the diagram. You can reposition the diagram to use the least number of pages by
clicking the Minimize Number of Pages icon. Clicking this icon repositions the entire diagram so that it uses the least number of pages.
You can zoom in and out of your diagram to get a better idea of how an area of the diagram looks or how the entire diagram looks. The magnifying glass icons enable you to perform this function. Use the Normal Size icon to return to normal size (refer to
Figure 27.10). Use the Enlarge icon to enlarge. Use the Shrink icon to shrink.
Once you are satisfied with your diagram, you are ready to generate. Click the Generate icon (refer to Figure 27.10). You see the generate screen. For more information on using the generator, see Chapter 28, "Client Application Generation."
You use the Preference Navigator to set up the standards for generating screens and reports for an application. You can start it from its own icon or from the Repository Object Navigator or the Module Data Diagrammer via the Tools | Preference Navigator
option. Initially, you start from RON or the icon itself to set up the global preferences for the entire application. When you are generating the modules, it is good to start from the Module Data Diagrammer so that you can set specific preferences for a
specific module as you generate. That way, you can perform what-if scenarios with the preferences and see the results as you generate.
After you decide where to start the Preference Navigator, start it up. If you are starting from the icon, you need to enter your user ID, password, and application. If you are starting from another tool, the tool passes this information. The first thing
you must do is enter whether you are setting preferences for Oracle Forms, Oracle Reports, or Visual Basic.
The screen pops up a list of decomposable items (see Figure 27.19):
You know these items are decomposable because they each have a plus sign to the left of its name. You can set preferences at any one of these levels.
Preferences at the application level include things such as commenting, coding style, layout, and so on. For a screen, layout includes things such as buttons, canvases, or check boxes. For a report, layout includes things such as frame style, box style,
or field format. Many of the preferences for both screens and reports are the same. If you click the plus sign to the left of Application Preferences, it decomposes again.
Once you pick a preference summary level such as button layout, it decomposes again into the specific preferences. These preferences have minus signs to the left of them because they are at the lowest composition level.
When you select a preference from the list, the meaning of the preference appears on the bottom of the screen. For example, BUTMAX is the maximum number of buttons within a radio group. The properties window displays the current value for the
preference. You can change it to whatever you choose.
By setting the preferences at the application level, all your reports can have the same look and feel throughout the entire application.
You can also set preferences at levels below the application level for any domain, table, or module. For example, assume you set the BUTMAX preference at the application level to 6, but you want it to be 3 on a specific screen. Select that module from
the module list, go to the button layout preferences, and change the BUTMAX preference to 3. When that module generates, the maximum number of buttons within a radio group will be 3. If you later decide to switch the preference back to the application
level, just click the Remove Properties icon, the fifth from the left on the icon bar.
When you are satisfied that you set your preferences to a point where you want to generate, click the Save icon, the first icon from the left.
If you accessed the Preference Navigator from the Module Data Diagrammer, you can generate the module by clicking the Generate icon, (refer to Figure 27.19). To learn more about generating a module, see Chapter 28, "Client Application
With so many preferences under so many decomposable items, it can be difficult to sift through all the preferences. There are some methods make using Preference Navigator less difficult.
To decompose a decomposable item, click the Collapse icon (refer to Figure 27.19). To decompose an item to its lowest level, click the Collapse All icon. To recompose a decomposed item, click the Expand icon. To recompose an item and all the items below
it, click the Expand All icon.
Suppose you are looking at a preference in application preferences. Then, you jump to a preference in table preferences, and you want to go back to the preference in application preferences. You either have to do a lot of scrolling back and forth on
your screen, or you have to do a lot of decomposing and recomposing. To go back to a certain point fairly quickly, you can set a mark on it. To set a mark, click the Set Mark icon (refer to Figure 27.19). When you are ready to go back to that point, click
the Go To Mark icon. You can only mark one item at a time.
If you know the name of your preference or block of preferences, but you are not sure where they are on the preference hierarchy, you can find the preference or preference block with a search. Fill in the blank box to the right of the first icon on the
icon bar. Then, click the Search Forward icon to look down the hierarchy for the search item, or click the Search Backward icon to look up the hierarchy for the search item (refer to Figure 27.19).
Once you have completed the analysis of your application, you are ready to move on to design work. You use the Database Design Wizard initially to set up default tables from the entities created during analysis. Next, you use the Data Diagrammer for fine-tuning the table definitions created and adding new table definitions where necessary. You use the Applications Design Wizard to set up default modules from the function definitions created during analysis. You use the Module Structure Diagrammer to link the module definitions together into a proper hierarchy for an application menu. Finally, you use the Preferences Navigator to set up the preferences so that you can move on to application generation.