Logical Data Modelling in SPARX

[DavidM]

Hi I have just joined an organisation that uses Sparx for all modelling including Data.

From what I can see the suggestion (not sure why) seems to be UML class diagrams for logical, and something resembling true data modelling called uml extended->Data modelling for physical diagrams.

I would much prefer to use the Data modelling option for Logical data model, but it is very technology specific , and the whole point of a logical model is that it is abstract from any technology.

Is there
a) Any way you can use Data modelling without a specific dbms type or
b) Using clunky uml class notations can you identify which are primary and foreign keys in the entity, and create relationships between multiple attributes?

I am sure it is obvious for such simple a task, but I cannot seem to figure it out.

Manky thanks,
David

[Glassboy]

I have no problem creating conceptual and logical models in UML.  I suspect you want the Link to Element feature of associations for creating relationships between attributes.  But it isn't clunky so maybe not.

[Geert Bellekens]

David,

Using Class Diagram for Logical Data Model is pretty much an industry standard these days.

Class diagrams allow for modelling on a higher conceptual level, including things like inheritance, many to many relations, etc..
From this logical model you can transform to a number of different technical model such as XML schema models, relational database model, code (java, C#,...) models etc..

So the transformation to a relation database model is just one of the possible transformations. Also not that this specific transformation probably can't be done completely automatically. Often design decisions have to be made to decide how to transform inheritance (at which level will you collapse into a table) and how to deal with many to many relations.

Typically in a LDM you would use associations for relations between entities (that will be translated into tables) and attributes for relations with datatype.
There is a property IsID that can be used to identify the unique identifiers for each class. This information could be used to create the primary key on the table, but more often than not tables get a technical PK that has not business value. In that case the "isID" information could still be used to create Unique constraints though.

Geert

[MaXyM]

Why don't you want to use specific RDBMS for modeling? If you do modeling after existing system, then technology is given.
If indeed model is technology independent, then choose RDBMS which can cover most functionality. In fact I'm not even sure there is a real need for it, since EA data modeling (at least physical) is very limited. Probably RDBMS engine selection affects reverse engineering and generating DDL only.

BTW in LDM some things can be modeled in simpler way. For example you can express n-to-m relationship by single association, while physical model requires additional table, a binding one.

[DavidM]

Hi Guys,
Thanks for all the responses.

To answer your questions - so I don't want to develop a technology as we have a ground of about 8 Data Architects charged with the  task or creating an Enterprise wide Data model, abstract from systems, much of the data entities are spread over 10-15 systems  each, with different technologies (ranging from Oracle to mySQL to a flattened Parquet files system in HDFS). All we want to map at this point in time are the data assets, not their technology associations.

Class diagram industry standard - I'm not convinced with that statement, maybe users in Sparx, but certainly not in more data centric applications like ERWIN etc.

I have no issue using Class diagrams, but I need to have a primary key of 2-3 fields, and I need another entity to have these 2-3 fields that are logically the primary and foreign keys, and therefore need associations between all fields, I am struggling how to do this in UML.

Also how do you define what is a primary key and foreign key in a methodology put together for application collections with a single key?

There is a lot of pressure to disregard Enterprise Architect as a data modelling tool and evaluate others, but I am keep to keep it going as I want to tie it with systems and business capabilities (I've defined a MDG to accommodate all this), the easy part, I thought, would be the data modelling.

Thanks guys,
David

[Sunshine]

I guess you are struggling as the object oriented world and data worlds have similar concepts they are actually different. Fundamentally Sparx EA is built upon UML.

In OO world you have
CIM->PIM->PDM

In the data world you have
CM->LM->PM

Generally in the OO world we don't worry about primary keys and foreign keys until mapping to a physical relational database. i.e. PDM. Why is this you ask? Well its because we could map the PIM to different PDM like Java/C#/XML/ RDMS etc and some of those don't have the concept of primary and foreign keys.

You'll notice this in the DDL transformation when the PIM is translated to a PDM for a RBMS the Key's are added.

Must admit I never like to see more than one field used as primary or foreign key as it usually means the fields are being used for more than one purpose and kind of goes against normal form.

I guess you need to read up a bit more on UML. Hope that helps.

[DavidM]

Hi Sunshine - -exactly there is a difference between data architecture and OOA.

It looks like then that  Sparx doesen't support logical modelling outside of the OO arena where a table can have a combination of logical elements as a primary key e.g. FlightNo, Date, and the other tables would need to reference  these with foreign keys e.g. FlightNo,Date.

In the physical/OO world we can connect with a single GUID etc. but in the logical world, that is abstract from systems and technology, we have to go with business attributes, which of course is the whole point of logical modelling.

It's a shame we will have  to take our Logical Data repository out of the enterprise repository because something so basic in logical modelling is not supported, but at least I know it can't be done instead of struggling.

I don't suppose there are any MDGs out there that provide this is there?

Thanks everyone for your contributions.

[qwerty]

I only heard of DBMode MDG but have no idea how well it works.

q.

[Richard Freggi]

Just a few 2c

A logical data model is implementation-independent (as-in: RDBMS independent) by definition.  If there is a RDBMS-specific item, then it's a physical data model.  The idea of logical data model is high reusability and easy extension to whatever technology is chosen for implementation (+robust against mid-project IT architecture changes)

A UML class diagram is a superset of data model (i.e. a data model + other stuff that you can simply ignore if you want to do ERD).  Any leading data modeling tool like ERWin or Sparx (professional and higher if I remember right) lets you choose what notation to use (IE/Crow's Foot or UML).  Yes UML handles PK/FK and FK migration just fine as stereotyped attributes.  You can also show triggers and indexes a stereotyped operations.  You can easily handle relationships with composite foreign keys (if that's what you mean by relationships between multiple attributes).

You can make conceptual, logical and physical data models easily in Crow's Foot.  Just click the notation you like by clicking on the Diagram options in Sparx.   There really is no significant difference in what you can do with UML or Crow's Foot / IE notation (google for details).

You can reverse/forward engineer from most leading DBMS with Sparx.  It's not as easy and fully featured as ERWin , Embarcadero etc; also relationship management is a little clunky, but anyway Sparx gets the job done at lower cost than dedicated data modeling tools.

There is a documentation PDF in the Sparx reference section that explains how to do all above.

Good luck!!

[DavidM]

Richard - how do you represent composite keys in relationships with UML? That is the basis for what I am struggling with.
Also I know you can define a primary key in UML, but cannot figure it out in Sparx.

I have looked through a lot of the documentation, but examples are always with a single attribute/key

Spent all day on this researching, lots of hints that it exists, but no hard and fast explanation as to how it is done.

In  the end I gave up an decided to go with the technology specific physical model of data model under extenstions, but I wanted to add attributes to it with MDG, and they don't seem to support it, so am back to square one

[Richard Freggi]

I don't know how to upload an image in this forum, tried and failed using the 'image' button

If someone teaches me how to upload images I'll upload some examples

[DavidM]

Yes I've had that problem many times :-)
Richard sorry I don't want you to go to a lot of trouble, if you can point me what I should use to linke multiple attributes I'm sure I can work it out.

I just cannot find anything in google.
If I know it is done in the UML class element somehow, I'll go through everything,

But  many thanks for your help, I much appreciate it.

[qwerty]

@Richard: You need to place images on a public server (e.g. imgurl) and use the link with the 1st icon 2nd line. Welcome to the 21st century.

q.

[DavidM]

Hi does anyone know how to link UML classes in sparx with composite keys?

I'm in the process of  evaluating SPARX to see if it can provide logical DATA modelling, and before we go out to market, it would be good to know if we need to put this on the list of possible solutions or not.

The Data modelling in the extended UML sort of does the job, if I accept the technology constraints, but we also have lots of data categorisation (in the airline industry) which are currently in tags, and these don't seem to work well with entity subtypes in Shape scripts.

e.g. Class flight with a relationship to flight leg, on FlightNumber,FlightDate

Thanks, any help or pointers appreciated

[Richard Freggi]

Here's quick and dirty from my own notes.  Sorry no image attach b/c I's not really incluied to find out how to use imgr or whatever.  Text based!

MDG: select core extensions, data modeling

Diagram / Features menu:
- Show Qualifiers and visibility indicators
- Elements: show attributes, operations (it want to list FKs in separate box)
- Features: show stereotypes, full attribute and parameter detail (shows PK and FK indicator
- Connectors: show relationships. Notation = IE

Create a data model Package (must have data modeling MDG enabled): right click on a view package (not model or root package) / Add a package / Select and apply model pattern / Select Database type
- The correct stereotype packages and diagrams are created and data modeling tools appear in the toolbox

Draw tables and columns, diagram shows PK/FK/datatype by default (can be hidden by double click on diagram / properties for conceptual and logical level; also select IE notation if you like Crow's Foot). 

Make sure that all tables having relationships have at least 1 PK, then draw association (the relationship), if 'edit association on creation' option is not enabled then double click on relationship, the FK editor opens, select cardinality including 1 or 0 (non-identifying).  FKs are automatically migrated to child entities (including composite keys)

There's much more including data dictionary, forward/reverse engineering etc.  Search data modeling in Sparx documentation. 

Goodspeed!