Explain an SQL query using UML or another modeling language?

Question

Is there a recommended / established way to explain the operation of the SQL query?

We have quite a few complex queries in our project, and we need to document it using UML or any other modeling language.

Please note that I am not asking about data modeling. This is more about documenting SQL logic, since tables are related, how conditions affect the result, are combined, etc.

So finally:

• What are the recommended ways?
• Any example?

Answer 1

Good question.

I don’t have one answer, it depends on the context - how complicated the actual request is, who the audience is (how experienced they are with different designations), if I can present this meeting face to face, or is it for offline documents, etc. Some suggestions:

• For a different audience, use the source-target mapping in the form of a spreadsheet table:

  Source Column | Target Column | Conversion logic (rules + SQL)

  Please note that this also covers part of the data model, it does not have to be very formal (but it can be), and you can decide on the appropriate level of detail (display only the inputs and outputs of the entire data processing or document each step, i.e. each subquery).

• For other developers (who own SQL), well-written and documented SQL code, with extensive indentation, to indicate different levels of nesting of sub-subqueries.

• For other developers or database administrators, many mature RDBMSs have the functionality of generating an explanation plan (or even a visual explanation plan). Such a conclusion is often very useful and contains additional information (the actual implementation plan with the estimated cost of each step).

• For academics ;-) - use relational algebra notation and draw a tree for the image query logic (example: https://people.ok.ubc.ca/rlawrenc/teaching/304/Labs/Lab1/ , check Wikipedia for the characters used for representations of specific operations).

• Sometimes a specific tool is used to create / define data processing (for example, Informatica or MS SSIS, used to assemble parts of the ETL logic or SAS Enterprise Guide, which has a graphical interface for presenting the code) and such a stream is visualized in the tool itself, however I'm not sure that it is as expressive as SQL, and can display some really complex queries well.

• QBE - Query-By-Example, invented by Moshé M. Zloof (available, for example, in MS Access), is a graphical tool / language used to define a query. But it also has its limitations (not every request can be presented in this way).

Answer 2

This link talks about using UML to visually represent SQL queries. http://forumone.com/blogs/post/visualization-modeling-sql-queries-uml