Creating an Entity Relationship Diagram from Existing Databases

Post date: Mar 14, 2013 6:18:03 PM

At the graduation we learn that every system must have proper documentation and this documentation must be provided to the product support teams after the project is closed, so people can properly support the application.

Bad news. Even at big companies, big technology companies, documentation is still very poor.

So, to easy your pain, whenever you need to generate reports or understand the data layer, Oracle has made freely available a great feature in SQL Developer to reverse engineer an Oracle database and output modeling information.

First, download Oracle SQL Developer:

Follow installation instructions over there. Then, follow these steps I've got from here:

File → Data Modeller → Import → Data Dictionary → select DB connection (add if none) → Next → last few steps intuitive.

Remark: It's very important to select the correct schema.

It will take some time analyzing your database. If the database lacks proper constraints, PKs and FKs it may not be able to fully represent the relationships. Also, the layout it outputs from the data dictionary is somewhat messy and you may want to organize it according to the affinities between tables.

Note #1: It can also be done with Oracle SQL Developer Data Modeler standalone.

Note #2: You can also import several other formats like DDL files. I'm not sure if it would be useful with MySQL data dictionaries, for example, but might worth a try.

Note #3: in the "select DB connection" step you'll realize it doesn't share connections with SQL Developer main module. You have the option to 'import' them, though. Go figure...