Devart Blog

How to: Create MySQL Database in One Shot

Posted by on July 19th, 2010

This article gives a step-by-step instruction for visual database creation.
There is a lot of instruments that allow database developers avoid monotonous and error prone manual writing of scripts for tables creation. But to have a complete picture while creating a database it’s necessary not only to speed up the process of database objects creation, but also to visualize relations between them. dbForge Studio for MySQL offers a perfect tool for such purpose – Database Designer.

Let’s create the sample demobase database visually.

Task:

Create a database that would contain information about the salary of employees of different company departments.

Database Sketch
Database Sketch

Solution:

Let’s start with database normalization. In the result we will get three tables:

Database Sketch after Normalization

Database Sketch after Normalization

Now it’s time to use the visual tool for designing databases – Database Designer.
But before let’s set a connection to MySQL server

Creating a new connection

Creating a new connection

and create an empty salary_db database:

Creating a new database

Creating a new database

Now let’s create an empty database diagram file:

Creating a new database diagram file

Creating a new database diagram file

Press the New Table button on the toolbar:

Creating a new table

Creating a new table

Let’s specify the table name and database in the visual table editor that appeared. After this let’s create a column:

Creating a new column

Creating a new column

and fill its parameters:

Entering column properties

Entering column properties

After this let’s create all remaining columns in the same way, and after we’ve finished let’s click OK to save our first table.
Let’s create the remaining EMP and DEPT tables. Tables can be created in any order -the order does not have any influence.
Now let’s ensure that our database has referential integrity. To do this, let’s press the NEW RELATION button on the toolbar.
After performing this action the mouse pointer should change. Let’s click the DeptNo column of the EMP table and drag the relation to the DeptNo column of the DEPT table.
In the Foreign Key Properties that appeared press OK.

Creating a new relation

Creating a new relation

The result of these actions is that there is a relation between the tables now. Let’s add a relation between the SAL and EMP tables.
To do this, let’s drag a relation from the EmpNo column of the SAL table to the column with the same name of the EMP table. Note, that it is not necessary to press the New Relation button once more.
Now let’s arrange the tables of the created database for better perception. To do this, call the popup menu of the database diagram and select the Layout Diagram option.
And here is the result:

Database Diagram

Database Diagram

Now let’s generate and save the script of the created database for further usage.
To do this, select all tables and click Generate Schema Script in the popup menu.

Generate Schema Script

Generate Schema Script

And here is the result:

Database Script

Database Script

Conclusion:
In the Database Designer tool you see the database you are creating in the form of a database diagram. Such approach allows you to create databases of any complexity visually and save the script of the created database for future use.

2 Responses to “How to: Create MySQL Database in One Shot”

  1. Trevor Says:

    This seems crazy to me. As a database developer, I could type out the SQL code above in a few minutes. Using this application though I would be clicking and selecting options for at least half an hour. Even if I did type some errors, it would be seconds to fix compared to the minutes wasted going through dialogs, menus, and drop downs.

    I wouldn’t even choose to use the tools in SQL server’s SSMS or MS Access which are still much faster to use than all these dialogs.

  2. .jp Says:

    Yes, you may be right on the one hand that for a small database it’s more convenient to type scripts manually instead of performing a lot of clicks on visual editors. But on the other hand, when you have to think over the design of a future database or redesign an existing one, from our point of view it’s more convenient to see the created (or modified) database structure as a database diagram with database relations displayed on it and perform the changes in the database structure on this database diagram.

Leave a Reply