The model allows for tagging of property (observation) types, similar to labelling messages in Gmail, including tag hierarchies. For example height, weight and width would be tagged with: all, dimensions, physical; some other tags would be: accounting_interest, tracking_specific, and so on. This way a user can subscribe to a set of tags she may be interested in.

• One observation type (height, weight, color) can have many tags, one tag may be applied to many observation types.
• Each tag may have a parent tag forming a hierarchy.
• A user stores preferences for a set of tags that she usually monitors.

To summarize relationships:

• One report can list many observations, an observation can appear in many reports.
• One subject (under observation) can undergo many observations, an observation relates to one subject only.
• An observation is of a specific type, there can be many observations of the same type.
• Measurement and trait are types of observations. Measurement is a numeric observation, like height. Trait is a descriptive observation, like color.

This is a simplified model based on Fowler’s observation pattern, for more details see Analysis Patterns by Martin Fowler.

Simple three-table Kimball star to allow for easy reporting. You can consider dimDate and dimEmployee as lookup tables for date and employee attributes.

To summarize relationships:

• Customer, vendor and distributor are types of organizations.
• One organization can have many contacts, a contact belongs to only one organization.

A few notes on tables:

• The Organization table has columns common to all organizations.
• The Customer, Vendor, and Distributor tables contain only specific columns for each one.
• The primary key in the Customer table also serves as a foreign key to Organization ID; same for Vendor and Distributor.

To summarize relationships:

• Book, article and magazine are (sub)types of publication.
• One publication can have many notes, a note belongs to only one publication.
• One publication can have many authors, one author can write many publications.

Few notes on tables:

• Publication table has columns common to all publication types.
• Book, Article, and Magazine tables contain only specific columns for each one.
• Primary key in the Book table also serves as a foreign key to Publication ID; same for Article and Magazine.
• P_mm_A is a helper table for many to many relationship between Author and Publication tables.

As in all previous examples of the series, I am using the database described in the first post.

 

Step 1

Start the program and click on File –> Reverse Engineering.

Step 2

The Reverse Engineering Wizard opens, note wizard steps on the left pane. The Alias is a name given to a collection of all setup parameters for a specific database; including connection, which objects to reverse, options to use, etc. At this point we have no previously defined aliases, so simply click Next to continue and create a new one.

Step 3

For Data Source, select MS SQL 2005.  As you can see from the list, all mayor databases are covered. Note the DDL Script RE branch, Toad allows reverse engineering of a database from a DDL script file too.

Step 4

For the data provider I had only one option available – ADO.

Step 5

The connection details; Toad actually displays the connection string here. To create a new one, click on Data Link Properties.

Step 6

We can type a connection string here, but it is simpler to click on Build and let Toad take care of the syntax.

Step 7

For the provider, choose SQL Native Client. On the Connection tab, enter the name of the server or an IP address; fill-in the username and the password and select the database name. If the list is empty, try clicking on Test Connection first.

Step 8

Now we have the connection string, click OK to continue.

Step 9

Back to the main wizard form, simply click Next to continue.

Step 10

Data Migrator selection — a new concept, did not see this one in previous examples. However, the wording is self explanatory, so simply select Reverse Engineering from Database MS SQL 2005.

Step 11

A list of objects to reverse engineer, followed by a list of options. Select all objects and accept default setting from the list of options.

Step 12

Time to save all this settings as an alias, which can be quickly selected next time. Once saved, select the alias and click Next.

Step 13

First select the Schema and than tables to reverse engineer; click on Execute to start the process. The bottom part of the form shows log messages.

Step 14

Diagram on the left was automatically generated, obviously it needed some rearranging. After some tinkering with shapes, I got the one on the right.

This is enough steps for one post. In the next part we’ll look into the procedures editor, converting the relational to a logical model and reporting. To be continued.

The biggest problem I encountered was a driver for MS SQL Server; took me a while to get it working. To save you some time, here are the steps to take:

1. Download the jtds driver;
2. Install it as described in Help –> Data Modeling Options –> Third Party JDBC Drivers;
3. Reboot.

Step 1

Once you have the driver installed; after reboot, start the program and click on File –> Import –> Data Dictionary.

Step 2

The import wizard opens. Note the four wizard steps: connect to database, select schema, select objects to import and generate design. The right-side pane contains already defined database connections, in this case it is empty. To add a new connection, click on the Add button; the New Connection Editor opens.

Step 3

First step is to select a database type; form the Type list select MS SQL Server 2005. At this version we have an option of Oracle, MS SQL Server, IBM DB2/UDB and JDBC/ODBC bridge for generic ODBC access. Keep in mind that the program uses Java drivers natively.

Step 4

Fill in all other connection parameters.

Step 5

The database is now present in the connection list. Select the database from the list and click on Test Connection, it should work.

Step 6

The connection for this example is actually to the MS SQL Server, which contains several databases. Select the database to reverse-engineer; all schemata below the database name are automatically selected.

Step 7

Which objects to import? Oracle allows for fine grain selection of what to reverse-engineer, objects are grouped into categories, each category on a tab. On the Tables tab I have selected all except the sysdiagrams table, and on the Procedures Tab all stored procedures starting with "p_"; as in most of previous examples.

Step 8

Review the summary and click Finish to start the reverse-engineering process.

Step 9

The diagram one on the left was automatically generated, I have rearranged shapes to get the one on the right. When moving shapes around, make sure to disable the Line Auto Route option, found under Tools –> General Options –> Diagram. The graphical part is still a bit rough and slow, but will probably be improved by the time the program reaches commercial release.

Step 10

To move to the Logical level (model) select Design – > Engineer to Logical Model.

Step 11

Again Oracle offers a detailed list of options — what to engineer. The logical model is on the right. For this level Oracle offers Barker and Bachman notations, the example is in Barker (crow feet) notation. It is easy to switch, right-click on the diagram and select a different notation;

Step 12

The navigation pane shows object for both, logical and relational levels. Explore the objects to see mapping; table – entity, column – attribute, foreign keys – relations.

Step 13

Still have to find my stored procedures somewhere; they are located in the physical model. To view the physical model — with database objects — select Physical –> Open Physical Model from the menu, and then select MS SQL Server from the list.

Step 14

Expand the Physical Models branch in the navigator and double-click on a procedure name to open the Stored Procedures Editor.

Step 1

Start ERwin and click on Tools –> Reverse Engineer.

Step 2

Select Logical/Physical for the New Model Type, and SQL Server for the Target Database.

Step 3

Under the Owners option, type in dbo; leave all other options as per default. Find some time to return to this dialog and explore all the options for reverse-engineering.

Step 4

The SQL Server Connection dialog opens, type in (or select) all the connection parameters and click Connect. The process starts as soon as the connection is established, you will see a progress bar and staus display during the reverse engineering process.

Step 5

Here is the first diagram, pretty good. Time to rearrange shapes and change few display options.

Step 6

The diagram on the left is on the Physical level and the one on the right is on the Logical level. You may have noticed that ERwin did not offer a list of tables, but has included all the tables owned by dbo, including the sysdiagrams table — in previous examples I have excluded the table. This is actually a correct behaviour, because the sysdiagrams is a part of the database and ERwin is a bi-directional tool, so it can be also used for database maintenance and refactoring.

Step 7

Expand Stored Procedures branch on the Model Explorer; make sure you are on the Physical level. Right-click on a procedure as select Properties.

Step 8

The SQL Stored Procedure Editor opens. All procedures staring with "p_" are mine, while those starting with "sp_" are part of the SQL Server diagram package, together with the sysdiagrams table. It is interesting to compare the Code and the Expanded tabs; with reverse-engineering, stored procedures are moved to model level — hence all the placeholders on the Code tab.

By poking around the Model Explorer, we can see all the reverse-engineered objects, including:

• Tables
• Relationships (foreign & primary keys)
• Triggers
• Stored procedures
• Functions
• Views
• Logins
• User IDs.