Diagram Relationships

[stephen]

I am in the process of documenting a system and have run into a jam with my diagrams.  The system runs in parallel on both an HP Mainframe and SQL Server.  For the time being I have to remain completely compatible with both environments.

This introduces a problem for me.  Maintaining compatibility with the Mainframe prevents me from formally defining foreign keys in the SQL tables.  Many of my relationships are complex (i.e. 5+ keys).  

How do I create the associations in EA manually with multiple keys?

(if the answer seems too obvious to post please post it anyway.  I am an EA newbie and it may not be as much of a problem as I think).

[CJ]

G'day Stephen,

Not quite sure I understand what it is you want to do...

Are you saying you want to create associations between the tables without setting up foreign keys?

Or are you saying you don't know how to setup a table with composite key?

What do you mean by "have to remain completely compatible with both environments"?

[Stephen]

I'll try to respond to each question in turn...

Q. Are you saying you want to create associations between the tables without setting up foreign keys?

A. Yes.  the tables are already in a SQL server database.  I have plenty of fields that function as foreign keys but they are not true (defined) as foreign keys.  So when I import them to EA, no foreign keys are found and as a result there are no relationships.  I want to manually create the associations in EA so that my diagrams have proper relationships.

Or are you saying you don't know how to setup a table with composite key?

The tables are already created so I dont need to set any up.  It seems to me that the beauty of EA is that it will recognize foreign keys and figure out the relationships automatically.  But in my case I want to manually define each relationship, most of which are composite keys (5+ fields) and I do not know how to do that.

What do you mean by "have to remain completely compatible with both environments"?

Meaning the mainframe DB cannot handle foreign keys (a limit of the technology) so as long as I am replicating from the Mainframe to the SQL Server I have to accept that the limits of the mainframe will be imposed on my SQL Server environment.

[Fintan]

Hi Stephen,

the mainframe DB cannot handle foreign keys (a limit of the technology) so as long as I am replicating from the Mainframe to the SQL Server I have to accept that the limits of the mainframe will be imposed on my SQL Server environment.

Just a thought - this may or may not work for you.

Could you have a conversion process between the mainframe and the SQL server final tables.  This would know the mappings between main frame tables and the relationships (foreign keys, etc.) needed in SQL server.  You could try to replicate from the mainframe DB to this conversion process.  Run the conversion process to set up the relationships between the SQL tables and then replicate (copy?) to the SQL server database.

If this does not suit - please feel free not to read the above paragraph

Regards,

Fintan

[CJ]

G'day g'day,

Just tested this with a DDL import of a couple of Oracle tables.

I modified the association between two tables by deleting the source role and target role combo box values (leaving them blank) on the source role tab and the target role tab.  When I generated the DDL, there were no ALTER TABLE statements for foreign key creation.

So, from what I gather, you can create an association between two tables without defining foreign keys and without defining the role.

Does that do the trick?

[Stephen]

From the responses I think I may not have been clear about what I am trying to do.  I'll try again...

We have an existing SQL database running on MSSQL 7. We have large financial database with multiple logical foreign key relationships which are not explicitly declared to the SQL server. The foreign keys contain multiple columns (often 5+ columns). It is not feasible to add explicit foreign key declarations to the actual database at the present.

We have a project to document the tables in this database using EA. We have used the reverse engineering features of EA to document the existing database schema. Now we would like to add the logical foreign keys to the EA diagram without changing the database. We have no need to forward engineer (that is to generate an SQL schema that matches the database) at present.

From what people have said, it seems that I should be able to add the logical foreign keys to the EA description of my database. However, I do not understand how to go about this with the complex key structure of the database. Considering I am a neophyte with EA, could someone please explain exaclty how to add a multi-column foreign key to an existing (reverse engineered) database diagram?

[CJ]

G'day Stephen,

Lookup "Foreign Keys" in EA's Help Index to figure out how to create a foreign key operation for a table.

Then, lookup "Link Tables" in EA's Help Index to figure out how to link tables ... create an association then set the source and target roles to the PK "operation" and the FK "operation".

Hope that helps.

[Robert]

Hi Stephen,

I am not sure i can get your meaning exactly. Do you want to setup milti-columns foreign in EA schema diagram? If so, I can help.

Just right clike the mouse with the cursor on the target table, select operations, then input the FK name and select the stereotye "FK", save it. Then you can add multi columns of the table to the definition of the FK(clike 'columns' sub pannel).

If it is not your meaning, pls let me know.

Robert

[Andrew_Webster]

I know exactly what you mean.  It can be represented, but don't expect to be able to create useful DDL.

Create the Primary Key by simply defining each attribute (column) as Primary.  Now create a foreign key (operation stereotype FK) in the other table for each column - 5 keys, 5 FKs.

Now drag an association from the FK table to the primary table.  Open up its properties, and set the Target Role to the primary key, the Source Role to the first Foreign Key.  Change it's name if you wish.  Repeat for all the other keys.

It now looks like a multiple column relationship, although if you try to create DDL based on this you'll be in for a disappointment.

All the best

Andrew Webster