Friday, 16 September 2011

Generating Entity Relationship diagrams from a database schema

Consider the situation where you have been given a database schema to work with but there is no E-R diagram documentation available and you need to re-design the database schema. The first step would be to try and understand the existing schema by generating an Entity-Relationship (ER) diagram from the existing database schema.To get an E-R diagram where none is provided, use SchemaSpy.

SchemaSpy produces a rich set of diagrams and tables which gives the users and developers a detailed description of the schema tables, constraints, anomalies ( to investigate what might be wrong with the schema) and most importantly, it displays the relationships between the entities as an E-R diagram. The following steps should help you get started with SchemaSpy. 
  • The tool is Java based open source software tool that it has been developed using Java, runs on Windows as well as UNIX and requires the Java Runtime Environment (JRE) so install the JRE if you don't have it,
  • For generating Graphical views of the schema, the tool is dependent upon the Graphviz library, so download and install it before installing Schema Spy. You'll have to make sure that your Windows PATH Enviroment variable points to the bin directory of the Graphviz install. If it doesn't, you can still run SchemaSpy but you'll need to specify an additional parameter along with the start up parameters of SchemaSpy.
  • Download the jdbc driver for the database SchemaSpy will connect to. For example, for PostGresql, you can download the jdbc driver from this page. For MySql, you'll find the driver here and for Oracle, here.
  • Download and install SchemaSpy. Have a read of the start up instructions and see which parameters are mandatory and which are optional.  Open a DOS command prompt and run SchemaSpy using the following command : 
  • java -jar schemaSpy_5.0.0.jar -dp    -t databaseType  -host -db -s  -u  -p   -o
  • You may also need the -gv pathToGraphviz parameter 
Depending upon the size of the database schema, SchemaSpy will take a few minutes to generate the ER diagram which can be viewed by clicking on the index.htm in the 

No comments: