Reverse Engineer a DB [6] – Oracle SQL Developer Data Modeling

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.