Read (and Visualize) Database Schema

[send.me.all.email]

Hi experts,

which approaches would you suggest for:

- Reading a database schema (tables, fields, relationships) from SQL
Server 2005?
- Visualizing the DB schema?

For developing a DB tool (Eclipse plug-in) the schema should be read
from the server and then be visualized, e.g. like the SQL Server BI
Development Studio does when importing the whole schema to a data
source view.

Any ideas or hints on how to start approaching this (either reading
schema and/or visualizing it) are welcome!


Regards,
Max

 

[Christian]

Hi experts,

which approaches would you suggest for:

- Reading a database schema (tables, fields, relationships) from SQL
Server 2005?
- Visualizing the DB schema?

For developing a DB tool (Eclipse plug-in) the schema should be read
from the server and then be visualized, e.g. like the SQL Server BI
Development Studio does when importing the whole schema to a data
source view.

Any ideas or hints on how to start approaching this (either reading
schema and/or visualizing it) are welcome!


Regards,
Max

Well visualization. How about doing it like an Entity-Relationship model...
thats at least one Visulaization that comes to my mind .. may be you
want more..

Christian

 

[send.me.all.email]

Well visualization. How about doing it like an Entity-Relationship model...
thats at least one Visulaization that comes to my mind .. may be you
want more..

No, in contrast, I want less.

Generating an ER model based on a given database schema would require
some model transformation or reverse engineering approaches (e.g. to
decide if a concrete relation could have been introduced to realize a
weak entity or a multivalued attribute).

The kind of visualization I have in mind is just an easy to understand
graphical 1:1 representation of the schema. Something like: Tables are
depicted as boxes, containing the fields of the table as rows showing
the name and type of the field. Fields acting as primary or foreign
keys are specially marked. Relationships between tables are depicted
as lines connecting the tables. If the name of a relationship and the
cardinality information are given, then these should also be included
as labels to the relationship lines. The visualization of the database
schema should then allow the user to select tables by clicking on them
and perform basic operations on them.

So this should be no rocket sience, but as I never built such a tool
before, I'm quite interested in what is the current state-of-the-art-
approach to do this. Building an Eclipse-PlugIn using EMF? Which
frameworks to use to minimize the development effort?

Any ideas?


Regards,
Max

 

[Martin Gregorie]

No, in contrast, I want less.

Generating an ER model based on a given database schema would require
some model transformation or reverse engineering approaches (e.g. to
decide if a concrete relation could have been introduced to realize a
weak entity or a multivalued attribute).

The kind of visualization I have in mind is just an easy to understand
graphical 1:1 representation of the schema. Something like: Tables are
depicted as boxes, containing the fields of the table as rows showing
the name and type of the field. Fields acting as primary or foreign
keys are specially marked. Relationships between tables are depicted
as lines connecting the tables. If the name of a relationship and the
cardinality information are given, then these should also be included
as labels to the relationship lines. The visualization of the database
schema should then allow the user to select tables by clicking on them
and perform basic operations on them.

So this should be no rocket sience, but as I never built such a tool
before, I'm quite interested in what is the current state-of-the-art-
approach to do this. Building an Eclipse-PlugIn using EMF? Which
frameworks to use to minimize the development effort?

Any ideas?

As you say, this seems quite doable - the JDBC metadata retrieval
functions should be able to return most of what you need, though you may
need to rethink the GUI. It may be enough of a problem to draw a
readable ER diagram (where E=table, R=fk) showing just the table names
from a decent sized schema and disentangling it so not to many
relationship lines cross could be quite difficult. You may want to show
table properties (columns, keys, indices, etc) only by clicking on a
table and the same for a relationship.

Are you familiar with the Embarcadero (http://www.embarcadero.com)
tools? If not you could do a lot worse that download an evaluation copy
or two. Some of their stuff may be what you need and failing that you'll
surely get some ideas by playing with it.

If you may find that automatically producing a schema diagram that isn't
a tangled mess is sufficiently difficult that the way to go is to let
the user drag objects around to make the diagram readable and then save
the result. On start-up it should be easy enough to compare the saved
model with the actual schema. If they are the same there's no problem:
just show the hand-modified version. If they differ you'll need to do
something about it: either replace the diagram entirely or just patch it
to match the schema, again letting the user change any tangled bits.

HTH

 

[Mishagam]

No, in contrast, I want less.

Generating an ER model based on a given database schema would require
some model transformation or reverse engineering approaches (e.g. to
decide if a concrete relation could have been introduced to realize a
weak entity or a multivalued attribute).

The kind of visualization I have in mind is just an easy to understand
graphical 1:1 representation of the schema. Something like: Tables are
depicted as boxes, containing the fields of the table as rows showing
the name and type of the field. Fields acting as primary or foreign
keys are specially marked. Relationships between tables are depicted
as lines connecting the tables. If the name of a relationship and the
cardinality information are given, then these should also be included
as labels to the relationship lines. The visualization of the database
schema should then allow the user to select tables by clicking on them
and perform basic operations on them.

So this should be no rocket sience, but as I never built such a tool
before, I'm quite interested in what is the current state-of-the-art-
approach to do this. Building an Eclipse-PlugIn using EMF? Which
frameworks to use to minimize the development effort?

Any ideas?


Regards,
Max

I believe I have seen Eclipse plugin doing just this - probably
azzurri.clay. I played with it some time ago - you setup parameters like
database connection, user, password, database used - and it produces
picture or some structure with database schema.

 

[send.me.all.email]

[...] showing just the table names from a decent sized schema and
disentangling it so not to many relationship lines cross could be
quite difficult.

Yes, you are addressing a very important point. Finding a framework
for just creating a visualization somehow is one thing. Getting a
neatly layouted schema representation is way more complex, if we
assume this framework doesn't offer ready to use layout algorithms
(layouter) right out of the box.

Are you familiar with the Embarcadero (http://www.embarcadero.com)
tools?

No, not yet, but a graphical representation and a layout like e.g.

http://www.embarcadero.com/news/product_photos/ERStudioOverview_HiRes.jpg

would be quite perfect.

If you may find that automatically producing a schema diagram that isn't
a tangled mess is sufficiently difficult that the way to go is to let
the user drag objects around to make the diagram readable and then save
the result.

This is quite a good solution if no layouter is available or the
effort of writing one, would be overkill. I think the approach you
describe should do it.

Now, the big question is: What framework should I base my tool
experiments on? Which framework is the current "state of the art" for
developing a design environment dealing with database schemas? Using
the Eclipse Modeling Framework (EMF) and start from scratch? Or are
they any sophisticated frameworks available, which could reduce the
development effort?


Regards,
Max

 

[send.me.all.email]

I believe I have seen Eclipse plugin doing just this - probably
azzurri.clay. I played with it some time ago - you setup parameters like
database connection, user, password, database used - and it produces
picture or some structure with database schema.

Yes, thanks for the hint. Azzurri Clay is able to "reverse engineer"
the schema of a concrete database and offers modeling in a visual
manner. Like e.g. depicted in:

http://www.azzurri.jp/en/software/clay/images/clay_eclipse_1.png

So now I'm interested in what frameworks are available to start
building my own visual modeling environment based on a concrete
database schema. Just read the database schema, visualize it and let
the user select some tables and perform some (nontrivial) operations
on it. What's the best framework to start with to minimite the
development effort? Any hints, ideas, approaches?

Regards,
Max

 

[Lew]

Yes, thanks for the hint. Azzurri Clay is able to "reverse engineer"
the schema of a concrete database and offers modeling in a visual
manner. Like e.g. depicted in:

http://www.azzurri.jp/en/software/clay/images/clay_eclipse_1.png

So now I'm interested in what frameworks are available to start
building my own visual modeling environment based on a concrete
database schema. Just read the database schema, visualize it and let
the user select some tables and perform some (nontrivial) operations
on it. What's the best framework to start with to minimite the
development effort? Any hints, ideas, approaches?

Any chance you could set the followup on this thread to one newsgroup? I keep
reading the same posts again and again.

Which newsgroup best represents the issues of your inquiry?

 

[Sualeh Fatehi]

Max,

- Reading a database schema (tables, fields, relationships) from SQL
Server 2005?

SchemaCrawler is an open-source project that produces a rational the
database metadata ojbect model. Take a look at http://www.devx.com/Java/Article/32443
SchemaCrawler can be downloaded from http://schemacrawler.sourceforge.net/

- Visualizing the DB schema?

SchemaCrawler comes with some dead-simple schema visualization
examples, using JUNG. You can build more elaborate visualization using
a good graphing library.

Sualeh.