Entity Relationship Diagram Generation from SQL Using Large Language Models
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing software tools for constructing Entity Relationship Diagrams (ERDs) for databases are limited by the lack of current metadata and schema information, requiring significant manual effort from domain experts and are not updated as databases evolve over time.
Innovation Solution
Utilizing a Large Language Model (LLM) to identify entities, attributes, and relationships from Structured Query Language (SQL) commands, translate them into visual markup language code, and merge these into a single ERD, automating the process of generating and updating ERDs based on real-time SQL logs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Extent of automation
If software tools are used to construct ERDs based on metadata and schema information, then the initial ERD creation is automated, but the ERD becomes outdated and inaccurate as databases evolve over time
Solution Approach 1:
The system enables self-updating of ERDs by automatically detecting database schema changes through stored procedures and triggers. The database system itself generates change notifications that automatically update the ERD without requiring external intervention, making the system self-maintaining and continuously accurate.
Solution Approach 2:
The invention implements a feedback mechanism where the system continuously monitors the database for schema changes and automatically feeds this information back to update the ERD. Stored procedures and triggers detect changes and trigger automatic ERD regeneration, creating a closed-loop system that maintains accuracy over time.
2Reliability
If manual effort is applied to create and maintain ERDs with domain experts, then the ERD accuracy is maintained, but significant time and resources are consumed
Solution Approach 1:
The system eliminates the need for manual ERD maintenance by implementing automatic detection and updating mechanisms. The database automatically monitors its own schema changes and triggers ERD regeneration without requiring domain experts to intervene, completely removing the time investment previously needed for manual updates.
Solution Approach 2:
The system performs preliminary actions by pre-configuring stored procedures and triggers that automatically detect schema changes before they propagate through the system. This proactive monitoring ensures ERDs are updated immediately upon any database modification, preventing the need for subsequent manual review and correction.
3Ease of manufacture
If metadata and schema information is documented by database creators, then initial ERD construction is possible, but the information is often omitted or not updated as databases change
Solution Approach 1:
The system implements continuous feedback loops where stored procedures and triggers monitor database schema changes in real-time. This automatic feedback mechanism ensures that metadata information is continuously captured and updated without relying on creators to manually document changes, preventing information loss over time.
Solution Approach 2:
The database system performs self-documentation by automatically generating and updating metadata through triggered procedures. Instead of relying on external creators to maintain metadata accuracy, the system autonomously tracks and records all schema changes, ensuring complete and current metadata availability for ERD construction.
Data Source
AI summary
A database system includes at least one data storage device storing at least one database and one or more processors configured to identify, from Structured Query Language (SQL) commands received for the at least one database, entities of the SQL commands, attributes of the entities, and relationships between the entities. The identified entities, attributes, and relationships are translated into a visual markup language code using a Large Language Model (LLM). In some aspects, the LLM or another LLM may be provided with the SQL commands to identify the entities, attributes, and relationships. An Entity Relationship Diagram (ERD) is generated or updated for the at least one database based on the translated visual markup language code. In other aspects, at least two of the identified entities, attributes, or relationships are merged for representation in the ERD.