|
Data
Architect
Professional database modeling |
Chapter 12
Tutorial
About this chapter
This chapter describes how to use the principle features of Data Architect.
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. Data Architect will not run 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 Data
Architect.
See Installation for more details.
Create ODBC DSN
Create one or more ODBC DSN's using your systems ODBC Administrator.
Data Architect contains a toolbar button
which can be used to quickly invoke the ODBC Administrator. This tutorial
assume you have created a DSN called 'WEBDB'.
Create Project
Data Architect will always start
with one empty Workspace. The empty 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.
|
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
item listed in the browser.
|
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
|
Look at the Output window to see any possible problems while connecting.
Focus upon the General tab and the ODBC tab.
|
Output General Tab
|
|
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.
|
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
from the toolbar and then dragging the mouse over the canvas while holding
down the left button.
|
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
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.
|
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.
|
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
. The results of the validation are sent to the Output window under the
validation tab.
|
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 Data Architect 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 Data Architect 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
toolbar button.
|
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
|
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
or the run-selected toolbar button
.
|
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 Data Architect support.
|
Reverse Engineer
Data Architect 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
button. This will invoke a dialog which will allow you to select the database
object to bring into the PM.
|
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
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.
|