Re: [gnome-db] Entity-Relation to Relational mapping

[Vivien Malerba <vmalerba gmail com>]

On Sat, 6 Nov 2004 09:31:44 -0700, Neil Zanella <nzanella gmail com> wrote:
> On Sat, 6 Nov 2004 14:10:17 +0100, Vivien Malerba <vmalerba gmail com> wrote:
> 
> 
> > On Fri, 5 Nov 2004 10:14:30 -0700, Neil Zanella <nzanella gmail com> wrote:
> > > Hello,
> > >
> > > Does mergeant deal with Entity-Relation to Relational mapping. Right
> > > now the only Open
> >
> > Right now Mergeant translates the foreign key constraints into a
> > graphical representation to make it easy to see the relations between
> > the tables in the database (have a look at
> > http://malerbavintner.free.fr/mergeant_screenshots/SC1.png) to see
> > what it looks like.
> 
> I think that could be good enough. What you have is similar to what
> Silbershatz's
> textbook on databases calles "schema diagrams". Here are some comments of
> mine on what a schema diagram is and how it differ from an ER model:
> 
> The
> schema diagram is a logical representation of the relational model
> which is in turn derived from the conceptual ER-model design. The main
> difference among the higher level ER-model and the lower level schema
> diagram are that in the schema diagram each box represents a physical
> table which is to be created in the database. Each box contains all
> of the defining column names which make up the table with the primary
> key separated from others via a horizontal line. One feature of the
> schema diagram that the ER-model lacks is the ability to represent
> foreign key constraints by means of arrows (if this were done in
> the ER-model then there would be ambiguity as to what an arrow
> might mean). In the ER-model an arrow points to a relationship
> to indicate that for each participating entity there is exactly
> one entity on the other side of the diamond shaped relationship.
> On the other hand n the schema diagram an arrow points from some
> column a to another column b in another table indicating that each
> time a is not null it must appear as a value of b.
> 

Yes.

> One suggestion for your diagram though: consider:
> 
> FOREIGN KEY (a,b) REFERENCES D
> FOREIGN KEY (a,e) REFERENCES C
> 
> a-------------+-------------------------------+
>                +-------------------------------+----------------->
>           D
>                |                                 |
> b-------------+                                |
>                                                  |
> c                                               |
> 
> +--------------------------------------> C
> d                                               |
>                                                  |
> e-----------------------------------------------|
> 
> The above would be much more explicit. Or maybe you can
> include some notation on top of the lines, like (a:a, b:x) to show
> that a maps to a, b mapsto x, on the other table (maybe this is
> cleaner). I guess my tables have fields that match anyways,
> but it is not always so, and in some cases it is not obvious
> (you could have a -> a and b -> a for example).
> 
> Anyways, it looks good.

I prefer one single arrow because it is more readable if there are
quite a lot of tables. Something not shown on the screenshot is that
if you move the mouse cursor on top of the arrow, then the PK and FK
fields are highlighted.

> 
> > > I wonder whether Mergeant plans do incorporate:
> > >
> > > 1. An ER modeler
> > > 2. Functions to map ER models to Relational ones
> >
> > Yes, both of these. Many pieces of the puzzle are already in place
> > (canvas items for the graphical representations, which can be extended
> > for example).
> 
> Nice. Either email it to me or send me a link to a hidden file on your website.
> 
> > Here is what needs to be done:
> > -> create classes for ER objects (entities, relations, notes, etc)
> > -> create missing classes for graphical representation
> > -> create a ER to relational translation (which is quite easy)
> >
> > Optionnaly, but would be good to have:
> > -> create a relation to ER (which is more complex)
> 
> the only problem here is it often can be done in several ways. I would ask the
> user what to do in a case by case basis when ambiguities can arise.

Yes, that's more difficult and the usefullness of that remains to be proved!

> 
> 
> 
> > -> think about a good way to keep updated the ER model and the real
> > database structure as represented in the data dictionnary (this needs
> > a bit more thoughts).
> >
> > >
> > > ER models can be huge, and the user inteface is crucial here.
> > > Unfortunately I am not able to compile mergeant and the screenshots
> > > in the user manual are broken so I cannot get a feel for what it looks like.
> >
> > There are som screenshots at http://malerbavintner.free.fr/mergeant_screenshots/
> >
> > I can send you a compiled version if you want. Tell me.
> 
> Yes please.

I've uploaded several .tbz archives at http://malerbevintner.free.fr,
have a look at them. For the executable file, I've sent the shared
libraries I use as well, but you'll have to configure the way your
files use the DTD.

> 
> 
> 
> > > Does it have a GUI of some sort with a canvas where the user can draw
> > > just like in Dia?
> >
> > Yes.

Vivien