There are many ways of creating database tables and relationships in MySQL:

• You can use data description language (DDL) statements directly at MySQL Command Prompt from the Terminal
• You can use MySQL Workbench and create tables directly
• You can create an entity-relationship diagram in MySQL Workbench, export it to create a DDL script, and then run this script to create tables and relationships

We will use the third option. If you just want to get the script to create tables and want to skip creating the ER diagram, then jump to the Script to create tables and relationships section of this chapter.

If you have not already installed MySQL and MySQL Workbench, then refer to Chapter 1, Introducing JEE and Eclipse, for instructions:

1. Open MySQL Workbench. Select the File | New Model menu. A blank model will be created with the option to create ER diagrams:

2. Double-click the Add Diagram icon; a blank ER diagram will be opened:

3. By default, the new schema is named mydb. Double-click on it to open properties of the schema. Rename the schemacourse_management:

4. Hover over the toolbar buttons on the left-hand side of the page, and you will see tool tips about their functions. Click on the button for a new table and then click on the blank page. This will insert a new table with the name table1. Double-click the table icon to open the Properties page of the table. In the Properties page, change the name of the table to Course:

5. We will now create columns of the table. Double-click on the first column and name it id. Check the PK (primary key), NN (not null), and AI (auto increment) checkboxes. Add other columns as shown in the following screenshot:

6. Create other tables, namely Student and Teacher, as shown in the following screenshot:

Note that if you want to edit column properties of any table, then double-click the table in the ER diagram. Just selecting a table by a single click would not change the table selection in the Properties page. All columns in all tables are required (not null), except the last_name column in Student and Teacher tables.

We will now create relationships between the tables. One course can have many students, and students can take many courses. So, there is a many-to-many relationship between Course and Student.

We will assume that one course is taught by only one teacher. However, a teacher can teach more than one course. Therefore, there is a many-to-one relationship between Course and Teacher.

Let's now model these relationships in the ER diagram:

1. First, we will create a non-identifying relationship between Course and Teacher.
2. Click on the non-identifying one-to-many button in the toolbar (dotted lines and 1:n).
3. Then, click on the Course table first and then on the Teacher table. It will create a relationship as shown in Figure 4.7. Note that a foreign key Teacher_id is created in the Course table. We don't want to make a Teacher_id field required in Course. A course can exist without a teacher in our application. Therefore, double-click on the link joining Course and Teacher tables.
4. Then, click on the Foreign Key tab.

5. On the Referenced Table side, uncheck the Mandatory checkbox:

Creation of a many-to-many relationship requires a link table to be created. To create a many-to-many relationship between Course and Student, click on the icon for many-to-many (n:m) and then click on the Course table and Student table. This will create a third table (link table) called Course_has_Student. We will rename this table Course_Student. The final diagram is as shown in the following screenshot:

Follow these steps to create DDL scripts from the ER diagram:

1. Select the File | Export | Forward Engineer SQL Create Script... menu.
2. On the SQL Export Options page, select checkboxes for two options:
   • Generate DROP Statements Before Each CREATE Statement
   • Generate DROP SCHEMA
3. Specify the Output SQL Script File path if you want to save the script.
4. On the last page of the Export wizard, you will see the script generated by MySQL Workbench. Copy this script by clicking the Copy to Clipboard button.