Database Modelling with EA - Howto?

[JulianH]

Hi there.

I have a big question.
(Please correct me whenever I talk bulls#it).

It's about the overall database modelling cycle. How can I do this in EA?

I mean, there is the logical model view (this must be the "conceptual model", right?)
and there is the data model view (the way the db is implemented, the physical view, right?).


==
FIRST of all: LOGICAL MODEL
==
When I start modelling a db in the logical view, I concentrate on

-the important, essential Tables (Entities),
-relations and
-the keys (primary / foreign key) as well as on
-the other attributes (that are not pk or fk or both).

After that I have a model, "everybody" can understand.

It will tell me, how many rows of the one table are related with how many rows of the other table... (1:n, n:m,......) and I will see any important tables (entities). There will be more (especially when I create the association tables for m:n relationships...), but with the logical view I will be able to get "the big picture" of the db.

Another question would be: Is EA able to import foreign keys into another tables?

-> If I have a table
   COUNTRY
  -------------
   CName (PK)

and a table

   CITY
   ------
   CityName (PK)

and when I try to connect them via association (I want the CName of Country as part of the PK and as FK for table City)... how can I realize this? (I don't mean via the foreign keys... feature in the menu of the connection, but I think of a way like in ERWin [you connect two tables an ERWin imports the needed Keys in the relevant table])

==
SECONDLY: THE DATA Model
===

But now I want to create the particular implementation, the physical view, where I also have a special db system in mind (oracle, for example).

How can I move all my data from the logical model designing process to the data model? Is there a chance without losing information from the logic design process?

===
THIRDLY: Generating DDL for use in DBMS
===
And after all I can generate DLL-Code for use in my DBMS.



Maybe you can help me and try to explain how I can realize this "workflow"

Thanks

Julian

[Bruno.Cossi]

Hi Julian,

first of all, Enterprise Architect, great as it is, is not a database modeling tool to start with. UML itself is not really geared towards modeling of the databases and while the notation can certainly be used for it, the UML's support of database modeling is still somewhat iffy. This is by no means only EA's problem though.
EA can certainly be used for database modeling, but if you are used to using specialized database modeling tools like ERWin on PowerDesigner, you are likely to miss some of the functionality. Some of the things that those tools do for you automatically, you will have to do manually within EA.
On the other hand, EA has great documentation capabilities, and having the database model within the same repository as the model of the application presents great possibilities :-)

Anyways, to start off with your question. EA does not have the concept of Conceptual and Physical model per se, when you design tables in EA, you are working in what would be an equivalent of the Physical model. That is why for each table you specify the DBMS, that is why you have to create relationships yourself etc.
Recent versions of EA have introduced model transformation capability (lookup "model transformation" in the help) which could likely be used for creation of custom functionality for transforming conceptual models into physical ones and vice versa. In that case you would probably used Class elements for entities.
I say likely becase the transformation between Conceptual and Physical model is by no means trivial, it is possible one would be able to implement most of it, but some manual tweaking may still be required.
As far as the relationships go, since in EA you are working on the physical level, you have to create the Primary key yourself - simply check the Primary Key checkbox for each of the columns you wish to include in the PK.
When creating a foreign key, use the Association line. Then go to the properties of the association and have a look at the Source Role and Target Role tabs - that is where you specify which of the columns are part of the foreign key and which key do they refer to.

Hope this helps!
Bruno

[JulianH]

{snip}

Hope this helps!
Bruno

Hi Bruno.

Thanks for the help. I always kept in mind, that EA is an UML Tool and not an "Database Design" Tool.
I realized that it could be easy to model the physical view of data in EA, but I've also seen that it can be difficult to "hold" 2 layers (logical and physical) of one database. Nevertheless, I will use EA parallel to my other DB Tools and sometimes, who knows, I will only use EA at all

Julian
PS: Do you model databases in EA as well?

[Bruno.Cossi]

Hi Julian,

we use EA for database modeling as well. We also deal a lot with legacy systems, so we use the reverse engineering functionality quite heavily - for documentation purposes, the Physical model is quite enough.
Just out of curiousity, what other DB modeling tools do you use?

Bruno

Hi Bruno.

Thanks for the help. I always kept in mind, that EA is an UML Tool and not an "Database Design" Tool.
I realized that it could be easy to model the physical view of data in EA, but I've also seen that it can be difficult to "hold" 2 layers (logical and physical) of one database. Nevertheless, I will use EA parallel to my other DB Tools and sometimes, who knows, I will only use EA at all

Julian
PS: Do you model databases in EA as well?

[JulianH]

Just out of curiousity, what other DB modeling tools do you use?

Bruno

Hi Bruno.

Normally I use ERWin, because I like this "logical and physical model in one" - concept. It's quite a good tool and I use it for serious DB work .

Sometimes - to type in some ideas or to present db models in an user-friendlier way - I also use MS Visio. But that's only for "presentation use", because I don't like the "forward/reverse engineer" feature of it.

Julian