Forward Engineering
Last week, you created a relational model from the Soccer League EER using MySQL Workbench. In today's exercise, you will practice using "forward engineering" in MySQL Workbench to create a database from a relational model.
Workbench's EER Diagram and Model tool is much more than a drawing tool. As you draw the model, you are explicitly defining the structure of the database. This information can then be used to generate the SQL DDL (data definition language) statements needed to build the physical database in MySQL.
Setup¶
Create a connection to the MySQL server¶
Each of you has a user account and private database on the MySQL server, which is running on a host named data.cs.jmu.edu.
Your account name and database (schema) name are both your JMU eID.
Your password is your student ID number.
On the main screen of Workbench, you can create database connections using the plus sign.
Create a new connection using the method Standard TCP/IP over SSH.
You are going to connect to the server from stu.
This setup will allow you to connect from off campus.
Fill out the fields as you see in the figure below, replacing elkadima with your own eID.
Then use the Test Connection button to test the connection.
You will first be asked to enter your password for stu.
If you select to save the password, you won't have to enter it again.
You will then be asked to enter your password for the MySQL server. It's your student ID number. If you select to save the password, you won't have to enter it again.
Load a model¶
Open your Soccer League model or the reference solution.
In the MySQL Model tab, set or change the schema name to your schema name (your eID).
Double-click the database icon above the list of tables to open the schema editor.
Forward Engineering¶
Select Forward Engineer... from the Database menu.
This will open a screen for you to connect to the database server; is is almost identical to the one you used to define a database connection, and should be set up correctly.
Click Next to move on to the Options screen, where you can set up what you want to be generated.
Click Next to start generating the scripts.
If you haven't saved your passwords, you may have to enter them again at this step.
The screen will show the progress of the forward engineering.
When you click Next again, you will be shown the generated script.
You can edit it directly, copy it to the clipboard, or save it to a file for editing.
Even if you are not making any changes, you should save it to a file (with the extension .sql).
It is important that the schema name used in the script matches your schema name.
Click Next to run the script on the database server.
If you haven't saved your passwords, you may have to enter them multiple times at this step.
This is the end of the forwarding engineering process, and your database has been built. You can close the screen.
The Soccer League database¶
To view and use the Soccer League tables, so back to Workbench's home screen and double-click the connection to open a SQL editor tab which is connected to the MySQL server.
You should see your schema on the left side of the screen, and if you expand the Tables, you should see all the Soccer League tables, as shown in the figure below:
Now you can insert data into the tables, using SQL or a data entry tool. You can modify the structure of the tables or query them. If you are connected to your own server, you can start and stop the server and do administrative tasks, like adding users and giving them privileges. Workbench has lots of features, as well as lots of idiosyncrasies and issues. Experiment with Workbench!
