In this section, you will learn how to create a new database model, create a table, create an EER Diagram of your model, and then forward engineer your model to the live database server.
Start MySQL Workbench. On the Home window, click the
[+] icon next to the
Models section on the bottom of the page,
or select File, New
Model. A model can contain multiple schemata.
Note that when you create a new model, it contains the
mydb schema by default. You can change the
name of this schema to serve your own purposes, or delete it.
On the Physical Schemata toolbar, click the button + to add a new schema. This will create a new schema and display a tabsheet for the schema. In the tabsheet, change the name of the schema to “dvd_collection”, by typing into the field called Name. Ensure that this change is reflected on the Physical Schemata tab. Now you are ready to add a table to your schema.
In the Physical Schemata section, double-click Add Table.
This will automatically load the table editor, with the default table name being table1. In the table editor, change the name of the table from “table1” to “movies”.
Next, add several columns. Double click a cell within the
Column Name column, and the first field
will default to “moviesid” because MySQL Workbench
appends “id” to the table name as the default for
the initial field. Change the name to “movie_id”
and keep the Datatype as
INT. Then, be sure PK
(PRIMARY KEY), NN (NOT NULL), and
AI (AUTO_INCREMENT) are all checked.
Add two additional columns using the same method as described above:
|Column Name||Data Type||Column Properties|
Now you can obtain a visual representation of this schema so far. From the main menu, select Model, Create Diagram from Catalog Objects. The EER Diagram will be created and displayed.
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.
There are several ways to open the table editor. Either
change back to the MySQL Model tab and
right-click on the
movies table, or
movies in the EER diagram
and select an Edit 'movies'
At this point, you can save your model. Click the main toolbar
button Save Model to Current File. You
have not yet saved this file so you will be prompted to enter
a model file name. For this tutorial, enter
“Home_Media”. The Home_Media model may contain
further schemata in addition to
dvd_collection, such as
Save to save the model.
You can synchronize your model with the live database server. First, tell MySQL Workbench how to connect to the live server. From the main menu, select Database, Manage Connections....
Here you can create a new MySQL connection, or use a MySQL
connection that you created previously. For our example, we
will use the MyFirstConnection from the
previous tutorial. To do this, select
MyFirstConnection from the list of MySQL
connections on the left.
Click Test Connection to test your connection parameters. If everything is okay at this point, you can click Close.
You are now ready to forward engineer your model to the live server. From the main menu, select Database, Forward Engineer.... The Forward Engineer to Database wizard will be displayed.
The Connection Options page of the wizard lets you set additional options for your selected MySQL connection. Or, you may decided to choose a different MySQL connection. We do not require any connection changes, so click Next.
Notice that your
connection is selected.
Optionally, you can execute a Catalog Validation. Click Run Validations and it should display "Validation Finished Successfully" without any errors.
The Options page of the wizard shows various advanced options. For this tutorial, you can ignore these and simply click Next.
On the next page, you can select the object you want to export to the live server. In this case, we only have one table, so no other objects need be selected. Click Next.
The Review SQL Script page displays the script that will be run on the live server to create your schema. Review the script to make sure that you understand the operations that will be carried out.
Clicking Next will execute the Forward Engineering process.
The Commit Progress page shows each executed step, and its associated status. It is recommended to view the logs on this page, so click Show Logs.
After ensuring that the script ran without error on the
server, then click Close. As a simple
test that the script worked, launch the MySQL Command Line
Client (mysql). Enter
DATABASES; and identify your schema. Enter
USE dvd_collection; to select your schema.
SHOW TABLES;. Enter
SELECT * FROM movies;, this will return the
empty set as you have not yet entered any data into your
database. Note that it is possible to use MySQL Workbench to carry
out such checks, and you will see how to do this later, but
the MySQL Command Line Client has been used here as you have
probably used it previously.
Ensure that your model is saved. Click Save Model to Current File on the main toolbar.