Author Topic: Reverse Engineer DB to ER Diagram  (Read 5898 times)

frederickb

  • EA Novice
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Reverse Engineer DB to ER Diagram
« on: May 20, 2015, 03:16:27 am »
I imported a database schema using ODBC with all the types of data selected.  The tables and constraints are all visible in the Project Browser.  However, when I create an ERD and drag tables into the diagram, the existing foreign key relationships are not displayed.

I initially tried "Drop as" "Link".  The connectors were not show.  Then I tried "Instance" and selecting "Copy connectors".  Still no joy.  I've used tools like Enterprise Architect before.  This is a wonderful way of creating diagrams and visualizing an existing schema.  How do you get the connectors to automatically appear when you add a table to the diagram that has a relationship to a table already in the diagram.  Thank you.

Fred

qwerty

  • EA Guru
  • *****
  • Posts: 9368
  • Karma: +144/-137
  • I'm no guru at all
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #1 on: May 20, 2015, 03:59:50 am »
I just tried to import an EAP and it created a diagram showing all tables and relations with no issue.

q.

frederickb

  • EA Novice
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #2 on: May 20, 2015, 08:27:36 am »
It is not clear to me what an EAP is.  An Enterprise Architect Project?  If so, why would you import an existing project?  I am attempting to import a database schema from a Microsoft SQL Server, into a new project, using the ODBC connector.  I tried following the directions in the article:

http://www.sparxsystems.com/enterprise_architect_user_guide/8.0/software_development/importdatabaseschemafromod.html

I am still not seeing connectors between tables clearly having primary and foreign key relationships.  While I have used these types of tools dating back to Paradigm Plus and Together Control Center, I am new to EA, so please, it is probably something basic that I am not selecting in the UI.  Thank you.

swc

  • EA User
  • **
  • Posts: 56
  • Karma: +1/-0
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #3 on: May 20, 2015, 10:12:39 am »
see these links that should help you with database functions in Ver 12:
https://www.youtube.com/watch?v=LLtp49TU1H8
https://www.youtube.com/watch?v=TAAabflPff4
« Last Edit: May 20, 2015, 10:13:08 am by scpag »

qwerty

  • EA Guru
  • *****
  • Posts: 9368
  • Karma: +144/-137
  • I'm no guru at all
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #4 on: May 20, 2015, 07:29:10 pm »
Quote
It is not clear to me what an EAP is.
The file where your repository is stored (the files ending with .eap). Actually they are MS Access databases. So I RE one via an ODBC connection.

q.
« Last Edit: May 20, 2015, 07:29:27 pm by qwerty »

Sunshine

  • EA User
  • **
  • Posts: 578
  • Karma: +40/-1
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #5 on: May 21, 2015, 12:57:00 pm »
Its possible that Sparx EA has some kind of bug. I remember a few years back it would reverse engineer MS Access databases fine with all the tables, columns and relationships but when I tried it with Oracle or SQL Server it would only import the tables and columns.

Here are some thoughts.
Check if any relationships to a particular table are being imported by opening the table element  (Class) and checking links in the properties dialog. Select element right click> properties - links.

Another think to check your account has proper access rights to read the meta data of the database. Its possible Sparx EA is reading the tables but not the relationships due to some access rights.

If that all proves unsuccessful report to Sparx EA as bug.

Dermot

  • EA Administrator
  • EA User
  • *****
  • Posts: 587
  • Karma: +7/-0
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #6 on: May 22, 2015, 02:51:19 pm »
When Reverse Engineering a DBMS you do need to create a diagram in the package that you are importing this too. Then it will generate the diagram by default. If a diagram is not available it will only import to the Project Browser. In this case you can create a Database diagram and can drag the Tables onto this as 'Links' - not as 'Instance'.

Nick B

  • EA Novice
  • *
  • Posts: 12
  • Karma: +0/-0
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #7 on: May 22, 2015, 08:30:18 pm »
Don't think it should make any difference, but just for the record:
- what DBMS are you importing from?
- what version of EA are you using?

One obvious thing (sorry) to check: In your Diagram->Properties (F5), under Connectors, is "Show Relationships" checked?

When I import from SQLServer 2012 using EA-12, underneath each imported table in the browser, I get: columns, PK, FKs, and indexes - it this what you mean by "The tables and constraints are all visible in the Project Browser."?

And just to reinforce what Sunshine says above, when I double-click in the browser on the table so that the properties dialog opens, what I see is entries under "Related"->"Links" that list the associated tables with Connection type "Association", and Stereotype "FK". Its these that show up in my diagrams as the lines between tables, which I think is what you are looking for.

If you cant see any, can you create an FK Association between your tables (i.e. just to prove that it does work in your setup)?

frederickb

  • EA Novice
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #8 on: May 23, 2015, 10:16:55 am »
All of this advice is helping.  The links are empty in the Related->Links dialog.  Yet Microsoft Dynamics says there is a foreign key relationship.  The database is Microsoft SQL Server 2008.  There are lots of constraints in the tables, but they don't seem to have made there way into EA, so perhaps Sunshine's comment about a bug is also a factor.  I will try to create the foreign key relationship this weekend and see if it shows up in the diagram.  Thank you all.

smendonc

  • EA User
  • **
  • Posts: 142
  • Karma: +4/-0
  • I love YaBB 1 Gold!
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #9 on: May 25, 2015, 04:27:46 am »
Just a thought, if you are reverse engineering a Dynamics db, are the relationships actually being created within the database or are these held in a Dynamics meta data table that makes it easier for the object layer to use?  Using any tools supplied with Dynamics will probably show these as PK/FK relationships when in fact they are not at the database level.

Do the relationships show up when using SQL Management Studio attached directly to the Dynamics db?

benjoy

  • EA Novice
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: Reverse Engineer DB to ER Diagram
« Reply #10 on: March 01, 2018, 02:22:44 am »
Hi everyone,

I come again to this old post because I imported the database schema from an SQL Server using ODBC and I have all the tables and constraints except the foreign keys. During the importation I see that there are FK but when it's finished there isn't any in tables. So on the schema I have all the tables without the relations between them. Is there other people who are still experiencing this bug with SQL Server? Has someone found a fix to this problem?
I precise that I'm using EA version 12 Corporate Edition.

Thanks for your help