Read (and Visualize) Database Schema - Java

send.me.all.email@googlemail.com schrieb:
> 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@googlemail.com schrieb:
> 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

Words to the wise, Christian <fakemail@xyz.de> wrote:

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

If you are up to an interesting read for visualization databases and
more importantly, user queries on a database, try to locate:

"VisDB: Database Exploration Using Multidimensional Visualization"
(Daniel A. Keim & Hans-Peter Kriegel, University of Munich)
contained in
"Readings in Information Visualization" (Stuart K. Card, Jock D.
Mackinlay, Ben Shneidemann)
--
Claus Kick
Computers are stupid, but they create work

On 17 Mai, 15:19, Christian <fakem...@xyz.de> wrote:

> 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

On 17 Mai, 15:19, Christian <fakem...@xyz.de> wrote:

> 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

On 17 Mai, 22:49, CK <a...@c.d> wrote:

> "VisDB: Database Exploration Using Multidimensional Visualization"
> (Daniel A. Keim & Hans-Peter Kriegel, University of Munich)
> contained in
> "Readings in Information Visualization" (Stuart K. Card, Jock D.
> Mackinlay, Ben Shneidemann)

Thanks for the hint. Keim is really a name when it comes to the
visualization of data. I liked the "triangle approach" for visualizing
stock prices he presented on "****ytics Day" at Fraunhofer IGD this
year.

But in my case I just want to show a database schema to the user to
enable him/her to perform selections and basic operations on the
tables. I'm just asking myself what is the current state-of-the-art-
approach to do so. Which frameworks can be used to minimize the
development effort and get a neatly looking GUI?

Regards,
Max

send.me.all.email@googlemail.com wrote:
> On 17 Mai, 15:19, Christian <fakem...@xyz.de> wrote:
>
>> 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?
>
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


--
martin@ | Martin Gregorie
gregorie. | Es**** UK
org |

send.me.all.email@googlemail.com wrote:
> On 17 Mai, 15:19, Christian <fakem...@xyz.de> wrote:
>
>> 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?
>
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


--
martin@ | Martin Gregorie
gregorie. | Es**** UK
org |

send.me.all.email@googlemail.com wrote:
> On 17 Mai, 15:19, Christian <fakem...@xyz.de> wrote:
>
>> 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
>
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.