Revision: 1.0

Millenia Software
200-1168 Hamilton St
Vancouver, BC V6B 2S2
604-729-6397
604-669-7004
http://millsoft.ca
info@millsoft.ca

Visual Database Designer is the ultimate schema manipulation tool for the database administrator. It allows complete visual editing of A Pervasive.SQL 2000 database as well as detailed or summarized printing of the database schema. The majority of editing operations can be performed completely with the mouse.

A full data diagram is presented including all foreign key relationships. Tables can be organized on the display to produce the most meaningful view of the database. As tables are moved all relationship lines to the table move with it.

Establish a new foreign key relationship is as simple as dragging a line from one table to another. Deleting an existing foreign key is as easy as clicking on the foreign key line and pressing delete. All changes are reflected on the display immediately.

This program eliminates the requirements for any knowledge of SQL unless custom triggers or stored procedures are being created.

In detailed view a table's column, indexes, and triggers are displayed.

Attribute View takes the detailed view one step further and displays the attributes of each table object. This includes the datatypes and sizes for columns as well as the index flags for indexes.

This view displays simple boxes for each table, procedure, and view containing their names.

New tables can be created with this function. Specify the name and column information here. When the table is initially created it will have no indexes associated with it. Use Create Index to add indexes as required.

Dropping a table is as simple as clicking on it and pressing delete. A confirmation dialog is displayed to confirm the operation. Tables can not be deleted while they are involved in a foreign key relationship.

This operation allows one or more columns to be added to the end of an existing table.

Select one or more columns and press delete to drop those columns. If a column which is used in an index is dropped the corresponding index will also be dropped. A dialog is displayed to allow a final confirmation of the deletion.

Select a table and right-click to display the index dialog. This operation will create one index containing the specified segments. The sort column indicates whether the segment is to be sorted in ascending or descending order. The default is to sort in ascending order. Enter the index name and indicate whether the index will be unique, modifiable, or not null.

A segment's collating sequence is specified during column creation. All columns in the index, which use an alternate collating sequence, must use the same collating sequence. It is possible to mix columns that are case sensitive, case insensitive, and one alternate collating sequence in the same index.

To drop an index, select the one you want to delete from a table and press Delete or right click and select Delete from the context menu. You will be prompted to confirm the deletion. When an index is selected its name and all it's segments are highlighted.

To define a foreign key, select one or more columns in the referencing (foreign) table. Then click on the foreign table and drag the cursor to a unique key in the referenced (primary table). The foreign key dialog will be displayed indicating the key segments in each table. These can be adjusted prior to actual creation of the relationship.

Only unique indexes in the primary table can be referenced and this index must already exist. All segments of the primary key must match. The foreign key may have excess segments in its index and the index does not need to be unique. If an index in the foreign table does not exist for the specified segments then one will automatically be created.

Any indexes created by the new relationship will be displayed when the operation has been successfully completed.

If Delete Cascade is specified any foreign key rows referencing a primary will be automatically deleted when the primary key is deleted.

If Delete Update is specified an attempt to delete a primary key which has foreign rows referencing it will fail.

Only restrict mode is supported for Updates. This means that any attempt to update the key field in primary table rows that are referenced by a foreign table will fail.

Click on the line that represents the foreign key you wish to delete. It will be highlighted. Press Delete and you will be asked to confirm the operation. Once the foreign key operation is complete the line will no longer be visible on the diagram.

This operation allows the creation of a table. It requires familiarity with SQL.

Specify a trigger name of up to 20 characters. Trigger names must be unique to the entire database.

Specify whether the trigger is to apply to Insert, Update, or Delete operations and whether it is to fire before or after the insert, update, or delete is applied to the database.

Multiple triggers may be assigned to the same Insert, Update, or Delete operation. To control the order in which they fire specify a number in the Firing Order item.

The Where box allows the rows which are affected by the trigger to be further restricted. If no where clause is specified then all rows being inserted, updated, or deleted will cause the trigger to be fired.

The Trigger Body contains the SQL statements which are to be executed when the trigger Is fired. Semi-colons should separate these.

To drop a trigger, select it in the table, and press delete.

Db Designer stores the visual database information including table window positioning and datasource information in a configuration file that has a default extension of .dbd.

The File New operation displays the ODBC connection dialog to allow a datasource to be specified.

Once a connection has been established the database tables are displayed in a tiled pattern. They can then be organized in any pattern that makes sense. The default starting view is Schema View.

The demodata sample appears as follows when first displayed:

Opens a saved configuration (.dbd) file, reconnects to the datasource, and displays the saved view.

Closes an open view and displays a save confirmation prompt if any changes to the configuration have been made.

Saves the current configuration replacing the previous configuration if one existed. If this is a new configuration the Save As dialog is displayed.

Saves the current configuration to the file specified in the Save As dialog.

Prints the current view. The full diagram is split across pages both horizontally and vertically if it does not fit on one page. The display can be zoomed prior to printing.

Allows the printout to be previewed prior to printing.

Allows a printer to be selected as well as paper characteristics and orientation.

Here is a view of Visual Db Designer after it has been launched by PCC.