How can I generate an Entity-Relationship diagram and SQL DDL from natural language text?

Question

Good morning,

Using LaTeX or another text-macro tool/library, how can I use natural language text to describe relationships, then generate an Entity-Relationship Diagram and generate SQL DDL to create the database schema from it?

Answer 1

There are many ways to do this, but the most important thing to know is that there is a name for what you want to do: Object Role Modeling. It's a process for creating conceptual models using natural-language semantics.

This is a pretty good (if old) overview of the process.

The Wikipedia page for ORM has a pretty complete list of the available tools, many of which are free.

The most up-to-date solution is NORMA, since it is actively maintained by the ORM Foundation, but that's a plug-in that requires Visual Studio.

If you don't have Visual Studio, Infagon looks like it is still pretty actively developed and may be your best bet.

CaseTalk is also worth a look. Free to students and educators, but commercial otherwise.

Answer 2

I did something like this once with a document parser tool I wrote. It would strip out specially formatted comments from a DDL statement and produce a data dictionary. This was renedered with LaTeX. However, I still had to reverse engineer the database into Visio to make E-R diagrams.

For a limited subset of SQL needed to parse create table statements it wasn't too hard to write the parser. I had it working in a couple of weekends.

Answer 3

Hey If it's any help i have used visio to create my ERD and revere engineer it to DB using it's Reverse engineering tool, which provides reverse engineering support to ms access, ms sql server, ...

Answer 4

I think I will write the SQL by hand, and generate a diagram from that using SchemaSpy.

Unless you can think of a quicker; more efficient way?