Damir Systems Inc.

The post relates to a stackoverflow question on DB design and my answer there.

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.

Again, related to a stackoverflow question on DB design and my answer there.

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

This is related to a stackoverflow question on DB design and my answer there.

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.

This is related to a stackoverflow question on DB design and my answer there.

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.

Toad Data Modeler is quite popular design tool from Quest Software. The installation went smooth, and I finished this whole exercise within 15 minutes from downloading Toad — impressive.

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.

Oracle SQL Developer Data Modeling is still in the early adopter release, so it is free to download. However, it will turn into for-cost option of the Oracle SQL Developer. It is developed in Java, so you will need the latest Java SDK. My version is 1.5.1 build 525; it is obvious that some things will change/improve in future versions, but the product is stable — I did not have a single crash. As in all previous posts of the series, I will use the same database, described in the first post.

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.

ERwin Data Modeler is a full-blown data modeling tool, allows for modeling on logical and physical levels, forward and reverse engineering of databases and data warehouses.

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.

The professional edition of MS Visio has a database reverse-engineering feature; see the edition comparison chart. If new to the series, you may want to scroll down to the first two posts of the series, where I describe the sample database and the ODBC connection setup. What follows is a step-by-step reverse engineering procedure.

Step 1

Start a new file by selecting File –> NEW –> Software and Database –> Database Model Diagram.

Step 2

Once a new file opens, give it a name and save the file. After that, select Database –> Reverse Engineer from the top menu bar.

Step 3

The Reverse Engineering Wizard opens. First we have to select a driver to connect to the database — select Microsoft SQL Server from the list.

Step 4

When the driver is selected, the Data Source list is populated with entries from the ODBC  list which use the driver. I have added the database to the ODBC list in a previous post — select CodeDB from the list.

Step 5

Enter the password and click OK to continue.

Step 6

Select which objects to reverse-engineer. Visio offers tables, views, indexes, constraints and code (stored procedures and functions). Select all and click Next.

Step 7

A list of tables to include; as in the previous example (post) I have skipped the sysdiagrams table.

Step 8

A list of stored procedures to include. There is a small trick here: procedures starting with ‘sp_’ are internal to SQL Server. It is a bad practice to name your procedures using the ‘sp_’ prefix, so mine all start with  ‘p_’.

Step 9

Select Yes to add object shapes to the current page.

Step 10

Review the summary and click Finish.

Step 11

The initially generated diagram (left) looks quite good. By rearranging shapes and tinkering with the display options, I have created the one on the right. When moving shapes around, make sure not to accidentally disconnect a connector line from a shape: the connector represents a relationship object between tables, and disconnecting a line may remove a foreign key from a table.

Step 12

To inspect table columns, keys, triggers etc., click on a table in the diagram or in the table list.

Step 13

To examine stored procedures, switch to the code tab and double-click on a procedure name; the code editor opens. Among other features, the editor allows for specification of a mirror file in which to store the code outside Visio.