Transform SQL Script into ER Diagram, Modify, and Generate Back to SQL

Help|Tutorials|ERD|Relationships|Diagramming|Video Tutorials| 19 March 2024 | Dusan Rodina - softwareideas.net
Transforming SQL scripts into Entity-Relationship (ER) diagrams and vice versa is a critical task for database designers and developers. This guide provides a step-by-step approach to converting SQL DDL scripts into ER diagrams, modifying these diagrams, and then generating updated SQL scripts using Software Ideas Modeler. By the end of this tutorial, you'll be well-equipped to visualize, adjust, and apply database designs with ease and precision.

Step 1: Creating an Empty ER Diagram

To create a new ER diagram, click on the 'Plus' button in the tab bar and select 'Entity-Relationship Diagram' from the dropdown list. This action sets the stage for your diagram creation.

Step 2: Parsing the SQL DDL Script

With an empty ER diagram ready, it's time to transform your SQL DDL script into diagram elements:

  • Open the Parser sidebar within the software (ribbon/View tab/Window group/Sidebars drop down/Project Tree)
  • Select SQL DDL as the language from the dropdown menu.
  • Paste your SQL script into the large text area using CTRL+V.
  • The parser will automatically analyze your script and convert it into diagram components, displayed in the 'Results' list below the text editor.

You can try the action with this script: 

 -- Create the 'Customer' table
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL,
    PhoneNumber VARCHAR(15)
);
 -- Create the 'Address' table
CREATE TABLE Address (
    AddressID INT PRIMARY KEY,
    CustomerID INT,
    StreetAddress VARCHAR(255) NOT NULL,
    City VARCHAR(100) NOT NULL,
    State VARCHAR(100),
    PostalCode VARCHAR(20),
    Country VARCHAR(100) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Step 3: Inserting Diagram Elements

You can choose to insert some elements manually by clicking the 'Insert' button next to each element or insert all elements at once with the 'Insert All' button. Upon doing so, your diagram will visually represent the entities and relationships defined in your SQL script.

Step 4: Modifying the Diagram

To add or modify entities and relationships:

  • To add a new entity, such as 'Store', simply use the diagram tools to create it and define its attributes.
  • Modify relationships by using the 1 to N relationship tool. For example, to correctly represent that each 'Store' has one 'Address', drag from the 'AddressID' primary key in the 'Address' entity to the 'Store's 'AddressID' foreign key.
  • This visual approach helps clarify the relationships between entities, ensuring the accuracy of your database design.

Step 5: Exporting the Updated Model to SQL DDL Script

After adjusting your ER diagram, it's time to convert it back into a SQL script:

  • Navigate to the 'Generation' sidebar (ribbon/View tab/Window group/Sidebars drop down/Generation)
  • Choose 'SQL DDL' from the language options and select 'SQL DDL - Universal' from the 'Template' dropdown.
  • Click the 'Generate' button.
  • Your modified ER diagram is now transformed back into a SQL DDL script, ready for implementation.

Customizing the Schema Name

In cases where a specific schema name is required (other than the default by the diagram folder name), follow these steps:

  • Right-click on the diagram canvas and select 'Diagram Properties' from the context menu.

In the 'Properties' dialog, enable the 'Custom Namespace' option.

  • Enter your preferred schema name (e.g., 'dbo') into the textbox.
  • Confirm with 'OK' and generate the script again.

This adjustment ensures that your generated script aligns with your database schema requirements.

Video Tutorial

New Comment

Comment