Nip DATA Logo  
 

Contents



ByteDesigner icon Byte Designer

Professional database modeling


Chapter 12

Tutorial

About this chapter

This chapter describes how to use the principle features of Byte Designer. You may want to read the Overview before getting started here.

Assumptions

At this point you should have a complete ODBC sub-system installed on your system. Byte Designer will not be able to connect to any database without the presence of an ODBC sub-system on the machine.

You will probably want to have an ODBC driver installed for each type of database you want to work with. These drivers are not strictly required but without them you will not be able to use important features of Byte Designer.

See Installation for more details.

Create ODBC DSN

Create one or more ODBC DSN's using your systems ODBC Administrator. Byte Designer contains a toolbar button odbc which can be used to quickly invoke the ODBC Administrator. This tutorial assume you have created a DSN called 'mari'.

Create Project

Byte Designer will always start with one empty Workspace. The empty Workspace workspace is shown in the Browser. Lets create a Project within the Workspace. <right-click> on the Workspace item in the Browser - this will invoke the context sensitive menu for the Workspace. Select 'New' from this menu to get a list of things you can create in a Workspace.

Dialog Workspace New
Workspace -> New

Select 'Project' and then <click> Ok. The Project properties dialog will automatically be invoked for a new project. Select the desired DSN, enter the default User ID and <click> Ok. At this point you will have a unnamed Project project item listed in the browser.

project properties
Project Properties
<right-click> on the project item in the browser to invoke the context sensitive menu. The projects context sensitive menu provides menu options to allow you to come back to the properties dialog and to connect/disconnect to the data source.

Connect/Disconnect

Invoke the context sensitive menu for the project. You will see a menu item to toggle the connect status of the project. Select Connect from the menu to connect to the data source - the default project properties will cause you to be prompted for; DSN, User ID, and Password.

login
Login

Look at the Output window to see any possible problems while connecting. Focus upon the General tab and the ODBC tab.

output general login
Output General Tab

output odbc login
Output ODBC Tab

Create Physical Model

At this point you should be connected to your data source. Now lets create a Physical Model (PM) inside our project. We could create the PM such that it resides under the Workspace but that would limit the features of the PM because the PM would lack any kind of data connection. It almost always makes the most sense to create Physical Models within a project.

<right-click> the project item in the browser and select the 'New' menu option. You will shown a list of things you can create within this project. Select one of the Physical Models - preferrably one which matchs the type database you are using. If your database is not listed then select the Generic PM.

The PM properties dialog will automatically be invoked. The tabs availible in a PM properties dialog will vary depending upon the type of database in question. Tabs will be availible for; Sequences, Domains and Procedures if those features are supported by the database. The Validation tab will always be availible and is used to customize model Validation.

Enter a name for the PM and <click> Ok. The PM editor is invoked leaving a canvas area for drawing on. The editor may be invoked in the future by a <double-click> on the PM item in the browser.

dialog pm
Physical Model Properties

Create Table

Database tables are represented in a PM by a rectangle containing some details about the table. These can be drawn on the canvas by selecting the table tool table from the toolbar and then dragging the mouse over the canvas while holding down the left button.

table properties
Table Properties
<double-click> on the table to invoke the table properties dialog. The table properties dialog contains tabs for such key elements of a PM as; table columns, and indexes. Fill, Line and Font tabs are standard and other tabs may exist depending upon the database type.

Enter 'tbOrders' for the name and create a few columns. Have at least one column marked as a Primary Key. This will be our parent table.

Now create another table and name it 'tbOrderItems'. Add a few columns with one of the columns marked as a Foreign Key. This will be our child table.

Create Reference

References are represented in a PM by a line with an arrow pointing to the parent table. These can be drawn by selecting the reference tool reference from the toolbar and then dragging the mouse from the child table to the parent table while holding down the left button.

Create a reference which starts at tbOrderItems and ends at tbOrders. Each end should be glued to a table - you can tell when this is the case because the end handle will be red instead of green.

<double-click> on the reference to invoke the properties dialog.

dialog reference
Reference Properties

Enter 'ref01' for the name. Go to the Expressions tab and add a new row to the grid. <click> in each cell of the grid to select the columns which are used to join these two tables. In this case 'fkOrder = pkOrder'. So fkOrder is selected under tbOrderItems and pkOrder is selected under tbOrders. Your column names may be different.

star
In a reference expression - only columns marked as primary key are shown under the parent table and only columns marked as a foreign key are shown under the child table.

Validation

A model can be validated for a wide variety of things. Validate this model by clicking on the validate toolbar button model validate . The results of the validation are sent to the Output window under the validation tab.

output validation
Output Validation
The messages in the validation output contain an icon indicating severity. The severity can be changed by invoking the model properties dialog and going to the Validation tab. In this case we have a number of warning and informational messages.

A <single-click> on a message will tell Byte Designer that you want to bring the source of the message into view. This may, or may not be relevant depending upon the source. For example; if the source is the model itself then nothing will happen but if the source is a table column then the table will be selected and brought into view.

A <double-click> on a message will tell Byte Designer to bring the source into focus and invoke an editor, usually a properties dialog. For example; if the source is the model then the focus will not change and the model properties dialog will be invoked and if the source is a table column then the table will be brought into focus and the column properties dialog will be invoked.

Generate Create Script

Now lets assume we have corrected all of our errors in the model. Now lets create a SQL script which we can use to create the database. <click> on the database create toolbar button.

create database
Create Database

The number of tabs and options on the create database dialog vary depending upon the database type being used. In this case we choose to create all tables and then <click> Ok. This creates a new, unnamed, SQL file in out project and loads it with SQL which we can use as a start to a creation script.

create script
Create Script

At this point the SQL script should be thoroughly reviewed and edited before submitting to the database. Once the SQL appears satisfactory you can submit it to the database using the run toolbar button run or the run-selected toolbar button run selected .

note
Some databases do not like SQL comments in their statements or do not like to have more than one statement submitted to it at a time. In such cases you should select each statement and use the run-selected toolbar button. If you experience problems such as this when submitting statements to the database then you should notify Byte Designer support.

Reverse Engineer

Byte Designer has the ability to reverse engineer an existing database - in other words - to create a Physical Model by querying an existing database. To do this you need to create a project with a connection to your data source. Then create a PM within the project ensuring to select a PM type which matchs the database type being reverse engineered. Then <click> the toolbar reverse engineer button. This will invoke a dialog which will allow you to select the database object to bring into the PM.

reverse engineer
Reverse Engineer

When we are reverse engineering an existing database we have the option to select the objects we want. The tabs available on the Reverse Engineer dialog vary depending upon the type of database. The result is a PM with the selected objects.

reverse engineer
Reverse Engineer
note
Reverse engineering a database using the Generic PM is limited by the capabilities of ODBC and the ODBC driver.

Reverse engineering a database using the other PM types is always a better idea as long as the PM type matchs the database type being reverse engineered.