DB Visual ARCHITECT (DB-VA) provides the Generate SQL function on the Entity Relationship Diagram (ERD). The Generate SQL function generates different kinds of SQL statements for entities and relationships. It not only generates SQL statement, but also allows you to modify the generated SQL statement and directly execute the statements on the connected database at once. The MySQL 5.0 database server is used for demonstration.
Generating SQL Statements
Create Relational Model
Create a new Entity Relationship Diagram.
Create a new Entity called Student
Create a new Entity called College
Create a one-to-many relationship from College
Open the Generate SQL dialog box and configure the database connection
There are three ways to open the Generate SQL dialog box:
entity, right-click and select Generate SQL...
from the pop-up menu.
Right-click the relationship between entities, and select Generate SQL...
from the pop-up menu.
Right-click the Entity Relationship Diagram (without selecting any models), select Generate SQL...
from the pop-up menu. The Generate SQL
dialog box will show all SQL statements related to the selected models.
If the database connection was not configured before generating the SQL, you will be prompted to configure the databases for SQL generation.
to open the Database Configuration
Select the MySQL
as the default database, and you can also select any other database servers as the supported databases. Please make sure the correct information for database connection is entered so that you can generate and/or execute the Alter
statement to manipulate the database.
Click OK on Database Configuration dialog box to confirm the database configuration settings. The Generate SQL dialog box shows accordingly which shows the generated SQL statements with the default database (MySQL) in two categories:
The Data definition language
) that can be used either interactively or within programming language source code to define databases and their components. The DDL
The Data manipulation language
) that can be used either interactively or within programming language source code to access and retrieve data stored in a database management system. The DML
For each SQL statement block has a triangle button on the left hand side, it allows you to expand or collapse the statement block. When the statement block is collapsed, only the first statement will be shown on the editor. If you click on the editor, the statement block expends once accordingly.
You can modify the database settings by clicking on the Database Configuration
button on the Generate SQL
You can select other database from the Database
SQL statements based on the syntax of the selected database.
Now you can click the Execute
button for the Create
statments to create tables, Student and College to the connected MySQL database.
Generating Alter Statements
The ALTER statement is used to change the table’s definition such as adding or removing an column or a constraint. The Generate SQL feature supports comparing the differences between the database and ERD and generating the alter statements according to the differences.
Let’s add a new column called address
in the Student
Open the Generate SQL and expand the Alter statement block.
Click the Generate
button, the Warning
dialog box shows alerting you for connecting the database to gather information from the existing database so as to generate the alter statements.
, the Alter statement is created and shown on the editor.
Manipulating Generated SQL Statements
As a set of SQL statements are generated from the Generate SQL dialog box, you can edit, save, copy, revert and execute the SQL statements. The following section introduces how you can manipulate the generated SQL statements.
The SQL query supports a SQL syntax highlight (the reserved words are highlighted in red) to enhance the readability and ease the maintenance of SQL statements.
The Select statement with specified condition has a standard format:
SELECT TABLE <columns> FROM <table> WHERE <condition>
The reserved words are highlighted in red on the editor.
You can save SQL statement as a file with a .sql extension. The sql file can act as a backup file and be executed for the database server.
Enter an output path and file name for the file.
Let’s open the saved file, the Select statements are exported.
You can copy the SQL statement to clipboard such that you can modify and use the SQL statements on the database directly.
button to copy the selected SQL statement.
You can paste the SQL statement anywhere you want.
After you modified the SQL statement, you can click the Revert button to restore the SQL statement to the original generated SQL statement.
button to revert the modified SQL statement.
The SQL statement is reverted to the original generated SQL statement.
You can execute the generated SQL statement in real-time and test the modified statements. By clicking the execute button, the Generate SQL executes the statements immediately and the result is shown on the bottom of the dialog box.