In the last two posts I have described an existing database and created an ODBC connection to it, now it’s time to actually do some reverse-engineering. For me, the primary purpose of reverse-engineering is to document a database — the first step in any kind of refactoring or upgrade.
This should not be too complicated, the database was originally designed using Visual UML. An object modeling tool, Visual UML is not just for databases, it generates or reverse-engineers Java, VB/VBA, C++, etc. It is great for getting handle on projects where developers did not have much time for documentation. I have an older version (2.9.2) which still works fine, a newer (5.3) trial version is available for download here.
To start with a new model, select File —> New Model, enter the model name and click OK.
Now it is time to select a target language for the model, we need the SQL Server.
From the Tools menu, select Reverse Engineer –> Database
From ODBC DSN select the CodeDB from the dropdown list. The list is populated from ODBC data sources and we have added the CodeDB database as an ODBC source in the previous post.
Fill-in the User ID, Password and the Database name – make sure they match what was entered during ODBC source definition.
A list of tables from the database appears. I have selected all tables except the sysdiagram table, this one belongs to the system.
Select yes to create a diagram.
Leave everything default and click OK. A new diagram is created and is quite messy, so I will have to clean it up later.
Let us look at the list of objects created.
- Tables are created as classes;
- Database is an equivalent of a package;
- Associations are created out of foreign key constraints;
- There is one Class Diagram object that was created during the last step.
Time to clean-up the diagram. After some re-arranging and selecting what to display and what to hide, here is the result – not bad. You can compare it with the original from which the database was created. There are some minor differences:
- In the original model, the primary key constraint was hidden (display), so it does not show explicitly in table attributes.
- I did not explicitly create indexes for primary keys in design stage, they were automatically created when the database was created. I could have decided not to process indexes during reverse-engineering, or to simply hide them from the diagram.
- Arrows are reversed. On the reverse-engineered model they follow direction of one-to-many relationships, by default the "many" side has an arrow. On my original model, I have set up arrows to point into primary key, the ‘1’ side of the relationship. This is just my preference, similar to the key side of an SQL server diagram; arrow directions can be reversed by editing relationship properties.
My version of Visual UML did not offer to reverse-engineer stored procedures, however according to the website the latest version does support stored procedures and triggers.
When it comes to documenting databases, I find that understanding tables and relationships is the most important part, stored procedures and triggers can be uploaded using Management Studio or similar tool.
To be continued.