MySQL Workbench
This tutorial describes how to create a new database model and how to forward-engineer a model to a live MySQL server.
Alternatively, you can create a model from a database by using the reverse engineering wizard. For additional information, see Section 9.4.2.2, “Reverse Engineering a Live Database”.
Start MySQL Workbench. On the home screen, click the models view from the sidebar and then click (+) next to Models. Alternatively, you can click and then from the menu (shown in the figure that follows).
A model can contain multiple schemas. Note that when you create
a new model, it contains the mydb
schema by
default. You can change the name of this default schema as
needed or you can delete it.
Click the Physical Schemas toolbar to add a new
schema. The default schema name is
new_schema1
, which you can now change to
dvd_collection
by modifying its
Name field. Confirm this change in the
Physical Schemas panel shown in the next
figure. Now you are ready to add a table.
Double-click Add Table in the Physical Schemas section.
This automatically loads the table editor with the default table
name table1
. Edit the Table
Name field to change the table name from
table1
to movies
.
Next, add columns to your table. Double-click a Column
Name cell and the first field defaults to
moviesid
because (by default) MySQL Workbench
appends id
to the table name for the initial
field. Change moviesid
to
movie_id
and keep the
Datatype as INT
, and
also select the PK (PRIMARY KEY),
NN (NOT NULL), and AI
(AUTO_INCREMENT) check boxes.
Add the two additional columns described in the following table.
The figure that appears after the table shows all three columns
in the movies
table.
Column Name | Data Type | Column Properties |
---|---|---|
movie_title |
VARCHAR(45) | NN |
release_date |
DATE (YYYY-MM-DD) | None |
For a visual representation (EER diagram) of this schema, select EER Diagram, which displays diagram representation of the movies table and columns.
and then to create the EER Diagram for the model. The next figure shows the a new tab titled
In the table editor, change the name of the column
movie_title
to title
.
Note that the EER Diagram is automatically updated to reflect
this change.
To open the table editor, either change back to the
MySQL Model tab and right-click on the
movies
table, or right-click on
movies
in the EER diagram and select an
option.
Save the model by choosing Home_Media
and then click
.
Before synchronizing your new model with the live MySQL server, confirm that you already created a MySQL connection. This tutorial assumes you have created a connection already. If not, see Section 5.2, “Creating A New MySQL Connection (Tutorial)” and use that tutorial to create a MySQL connection named MyFirstConnection, although an alternative connection can also work.
Now forward-engineer your model to the live MySQL server as follows:
Select
and then from the menu to open the Forward Engineer to Database wizard.The Connection Options step selects the MySQL connection and optionally sets additional options for the selected MySQL connection. Make any necessary connection changes and then click
.You may decided to choose a different MySQL connection here, but this tutorial uses MyFirstConnection.
The Options step lists optional advanced options (as shown in the figure that follows). For this tutorial, you can ignore these options and click Next.
Select an object to export to the live MySQL server. In this
case, there is only one table
(dvd_collection.movie
). Select the
Export MySQL Table Objects
check box (as the
figure that shows) and then click .
The Review SQL Script step displays the SQL script that will be executed on the live server to create your schema. Review the script to make sure that you understand the operations that will be carried out.
Click
to execute the forward-engineering process.The Commit Progress step confirms that each task was executed. Click
to view the logs. If no errors are present, click to close the wizard.
The new dvd_collection
database is now
present on the MySQL server. Confirm this by opening the MySQL
connection and viewing the schema list, or by executing
SHOW DATABASES
from the MySQL Command Line
Client (mysql).
Click the Save Model to Current File icon on the menu toolbar to save the model.
For additional information about data modeling, see Chapter 9, Database Design and Modeling.