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

[Neil Zanella <nzanella gmail com>]

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.

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 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.

> -> 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.

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