Data modelling

[Jayson]

Hi there

I am trying to accomodate our data modeller's desire to model at the conceptual, logical and physical levels.
However, when I look at the languages proposed by Sparx for achieving this, I run into problems.

At the physical level:
=============

Sparx recommends tables which has primary keys, foreign keys, constraints, etc. and so all is well (no problem here).

At the conceptual level:
===============

Sparx recommends either class diagrams or ERD diagrams.

The ERD diagram is adequate although it does allow for specification of primary keys which I would normally not expect to occur at a conceptual level.
Rather, I would expect to see "natural keys" being modelled which in most cases would be modelled as unique constraints.

The class diagram is the preferred method because it captures sub-typing, generalisation, etc.
However, there seems to be no way whatsoever to specify natural keys (using unique constraints or any other method).

So, I can use ERD's at the conceptual level, but seem to be prevented from using Class diagrams which is the preferred method.

At the logical level:
============

Here is where I come unstuck, because at this level I MUST be able to specify natural keys (presumably using unique constraints), but cannot find any way to do this using classes.

I could try to use tables for logical modelling, but this has its own problems.
If I use tables, I am locked into specifying which DBMS I am modelling for and must therefore also specify DBMS specific attribute types rather than generic ones.

So, the question is: What am I missing?
It makes no sense to me to not to be able to specify basic constraints in the logical or conceptual layers, and yet I don't seem to be able to.

Any help gratefully received.

Jays :-)

Btw, I am using version 9.3.

[qwerty]

Many ways lead to Rome. One would be to use a naming convention. Another way is to attach constraint notes. Only in simple cases you can map directly from logical models to data models. Usually this step needs a lot of side aspects where performance is the key word. So your model needs to show how logics and physics are connected. There is no standard way to my knowledge. But you are free to be intuitive when expressing your steps.

q.

[Sunshine]

Here is one way
Conceptual - use packages for subject areas
Logical - use classes
Physical - use tables with primary and foreign keys

Can use DDL transformations to go from logical to physical.

Simple aye?

[Jayson]

Hey Sunshine

How can I specify unique constraints in a class diagram?
I have looked and looked, but buggered if I can find a way.

Cheers  

[Helmut Ortmann]

Hi,

if you want to define things that aren't available in class diagrams you always may define your own subtypes for specials things. In UML we call this profiles and this is supported by EA.

There are a lot of possibilities like:
- Use the existing constraints in class/attribute
- Use pre- and post processing for operations
- Define you own class/attribute/operation types with tagged value(s) as constraint or what else

You may integrate these additional features to transformations.

To understand the possibilities it's useful to understand UML Profiles. To make things easy and convenient you may develop an MDG for your own types.

Helmut

[Jayson]

Hey Helmut

Yup, I am creating my own MDG at the moment.
However, I want to avoid creating new stereotypes where I can avoid it.

In this case, I am completely perplexed as to how Sparx can implement a set of modelling languages that allow primary keys at the conceptual level but not at the logical level.
This is totally counter-intuitive to how conceptual and logical models are supposed to work.

Aside from that, if I was to create my own stereotype to address these issues, I would now strike the problem of how to implement unique constraints in a stereotype such that:

* The user can select from the set of currently defined attributes,
* If an attribute is deleted, then it is also removed from the constrain.

Cheers  

[Sunshine]

IMHO you shouldn't be putting primary keys or foreign keys in to the logical model. That's what the physical model is for isn't it? The simple reason is that a logical model can be transformed to many types of physical models such as database or EJB or C#, XML etc. But I guess this is where the Object Oriented World vs the Data World differ.
If you need constraints on attributes or classes then one possible way forward is to abstract it out to be a stereo type and apply the constraint when the logical model is transformed to the physical based on the stereotype. That way you can apply the constraint in a platform specific way. It also means if you need to change it you can change it in the transform script and apply it globally to the physical models.
Hope that makes sense but I know from past experience a lot of people just simply don't get the concept.

[Jayson]

Hi Sunshine

Yes, I agree totally that you shouldn't have those things in a logical model. However, what I want to model are the natural keys so that when I perform the conversion to a physical model, the transformation:

* Creates an artificial primary key, and
* Creates a unique constraint based on the natural key.

The thing that dumbfounds me the most however is that the ERD model which Sparx proposes be used for CONCEPTUAL modelling will allow you to specify primary keys but the Class model does NOT.

Cheers  

[Helmut Ortmann]

Hi,

the definition of CLASS Diagram is defined in UML by the OMG.

ERD is also defined somewhere.

In essence: SPARX implemented the UML definition. There is nothing to blame SPARX about.

You may file a feature request to get additional features not contained in the UML definition.

Helmut  

[Tim Hosking]

I'm with you Jayson. Sparx is not a great tool for data modelling. they always come up with the defence that it's a UML tool. Unfortunately, that's just not good enough. I suspect that no Sparx EA developer has ever done data modelling at an enterprise level. Wait until you get into trying to make a primary key automatically flow from one entity to its related entities! You have to manually add it to all entities "downstream" and then add the foreign keys. If the keys have multiple columns and the names of the columns in the foreign keys are different from those in the primary key, then EA will guess and often get it wrong. Then it gives an error saying your datatypes don't match. It's a very poor implementation.    

[qwerty]

Tim,
That is the reason that there are those rather expensive data modeling tools. I for myself would not be willing to pay for that with a normal license. Maybe Sparx could implement something on a DB modeler license base. OR even better some plugin with that special support.

As a side note: if you look into the database model of EA itself it should be clear that this has been designed by someone doing is manually after (maybe) too many beers.

q.

[Graham_Moir]

I don't have any answers, but the following may help:

     www.dbmode.com

And the white papers

http://community.sparxsystems.com/white-papers/669-data-modeling-from-conceptual-model-to-dbms

http://community.sparxsystems.com/white-papers/670-data-modeling-with-the-uml

http://community.sparxsystems.com/community-resources/512-84data-modeling-logical-and-conceptual-mda-transforms