0

I am in the process of mapping my country's open data, and the relationships between them, and I need to do it in both human and machine readable formats.

I am looking at diagramming tools for the human readable part, but I would need the tool to be able to export the results to a machine readable format other than SQL, such as json or xml, so as to be able to pass it to a program of my own writing for later processing.

Since I would not need to be dependent on SQL, I would like as many of the UML relationship types to be supported. However, I would be able to make do with association, reflective association (self reference) and multiplicity.

Is there any free tool that can achieve this?

P.S.: If not, are there any Python or C# libraries that can read SQL DDL statements into a class structure?

  • 1
    What kind of expressivity do you need? For example directional relationships, multiplicity, etc. Maybe even inheritance? (which most SQL modeling tools can not do) – Nicolas Raoul Feb 22 '17 at 6:15
  • 1
    What program(s) would you be passing the data to? And do you have a particular operating system / SQL dialect in mind? – Mawg Feb 22 '17 at 10:41
  • @NicolasRaoul I've updated my question – Kenji Kina Feb 22 '17 at 13:58
  • @Mawg The program would probably be a script of my own writing. Operating system would be either Windows or Mac OS. I had no particular SQL dialect in mind, but maybe those with parser libraries available would be best. Still have not checked which those are. – Kenji Kina Feb 22 '17 at 14:00
  • What do you call "reflective association"? bi-directional association? – Nicolas Raoul Feb 23 '17 at 3:20
1

SQL Power Architect stores its definition as an XML file. However, I find that format quite hard to use from e.g. XSLT.

But it also supports the Liquibase XML format for "forward engineering" which is easier to process then the format Power Architect uses (at least in my opinion).

1

I'm still a little unclear. Couldn't you use Python to query the database and use that result to generate XML?

Ah, " my country's open data, and the relationships between them". So, not necessarily every piece of data, but the relationships between tables, for instance?

If you have access to the meta data (schema). It you do, this shouldn't be difficult. If you don’t, can you sue SQL queries, or are you limited to just getting data in a format that they (some government website) want to offer you?

To clarify: to determine relationships among data, your best approach is to look for foreign keys.

You can get this rom the database schema. Or, for instance, by executing the MySql SHOW CREATE TABLE command.

This excellent answer shows

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Of course, it is probably the case that you don’t have such direct access to the actual database, and just an API which does the querying for you.

In that case, I see two possibilities:

1) Contact whoever supplies the data. If they are making it freely available then they are probably going to be not only willing, but actually pleased, to help you out. If you are really lucky, you will get full read-only access and can use tools like MySql Workbench which can help you reverse engineer an existing database and visualize it like this

http://download.nust.na/pub6/mysql/tech-resources/articles/workbench-screenshot.png (sorry, my browser is playing up & won't insert the image)

2) Dump every table. It won’t be presented that way, but you are probably getting the result of `SELECT * From ’ and look, by hand or by code, for identically named columns.

Good database design will see meaningfully named columns with the same column name used in several columns. E.g. customer_id, order_id, etc

And, with good database design/good luck, they will be of the form <table_name¬_Id, or <table_name>_index, or similar.

From which you can surmise that customer_id is the PRIMARY KEY in the customers table, and uis used as a FOREIGN KEY in the orders table, thus estcablishing your relationship.

  • I will try your suggestion to contact the providers, but knowing a bit of the behind-the-scenes, I doubt we'll get much out of it. Furthermore, there are more than a thousand data sources, including local and federal government institutions. Our country's open data initiative is in its early infancy, and it is in the process of getting everything standardized. Our intention is to participate in this. Additionally, we want to create relationships between different sources of data, so in this case a direct connection to the databases is not going to be very helpful, if we even get to that. – Kenji Kina Feb 22 '17 at 15:29
  • 1
    There are some keys to relate data from different sources, but they are not enforced by a single relational database with foreign key constraints, but instead by a ruling standard. As it stands, I believe the task to map the data and the relationships will have to be manual. – Kenji Kina Feb 22 '17 at 15:31
  • Then I think that you are unlikely to get an answer to your solution here. Can you ask another question to help you get tools to help you with your task? I am quite interested in the idea. Will you be documenting what you do anywhere? – Mawg Feb 22 '17 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.