| In the following part, you will create a table called colors in MySQL database. The colors table contains an enumeration typed column called name, which contains three values, red, green and blue. We will use the colors table to demonstrate how DB-VA handle user defined type when reversing database to an ER Diagram. |
|
| 1. |
Create table colors with the following column and values in the MySQL Database. |
|
| Column name |
Data type |
| name |
enum('red', 'green', 'blue') |
|
|
| 2. |
Type the following SQL statement to create the "colors" table in the MySQL database. |
|
|
CREATE DATABASE test;
| |
USE test;
| |
CREATE TABLE colors(name ENUM('red', 'green', 'blue'));
|
|
|
| 3. |
Start DB-VA |
|
| 4. |
Open a new Project called Test User Type in DB-VA. |
|
 |
|
| 5. |
Select Tools > Object-Relational Mapping (ORM) > Reverse database... from the main menu. |
|
 |
|
| 6. |
Select Java as the Language and click Next. |
|
 |
|
| 7. |
Select MySQL (Connector/J Driver) as driver, enter the connection information, check Set as Default. This setting is important to let DB-VA 'knows' how to handle a user defined type during reversing a database. |
|
 |
|
| 8. |
Click Next. |
|
| 9. |
Keep the colors table checked. Click Finish. |
|
 |
|
| 10. |
A ERD is formed with a colors table with a name column in it. |
|
 |
|
| 11. |
Let's examine the name column. Right-click on it and select Open Specification... from the popup menu. |
|
 |
|
| 12. |
You should see the User Type field is filled in with the enumeration values. |
|
 |
| In the following part, you will create a table in DB-VA called sports. The sports table contains a column called name, typed as set. You will export the ERD to your MySQL database. |
|
| 1. |
Start DB-VA |
|
| 2. |
Open a new Project called Designing User Type in DB-VA. |
|
 |
|
| 3. |
Select Tools > Object-Relational Mapping (ORM) > Database Configuration... from the main menu. |
|
 |
|
| 4. |
Right-click MySQL and select Set as Default. Enter the database and connection setting on the right hand side of the dialog box. Click OK to confirm the changes. |
|
 |
|
| 5. |
Create an Entity Relationship Diagram by selecting File > New Diagram > Others > Entity Relationship Diagram from the main menu. |
|
 |
|
| 6. |
Create a table, name it as sports. |
|
 |
|
| 7. |
Create a column in the sports table. |
|
 |
|
| 8. |
Right-click on the column and select Open Specification... from the popup menu. |
|
 |
|
| 9. |
From the specification dialog box, set the column name as name the column type as varchar. Enter SET('Basketball','Tennis','Badminton') in the user type field. Click OK to confirm the changes. |
|
 |
|
| 10. |
Select Tools > Object-Relational Mapping (ORM) > Generate database... from the main menu. This shows the Database Code Generation dialog box. |
|
 |
|
| 11. |
Keep the Generate DDL field checked. In addition, check Export to database, so that we will be able to examine the changes that will occur in the database. Specify the output path and click OK to start exporting the ERD to the database. |
|
 |
|
| 12. |
Open the generated DDL file, you will see the following SQL statement, which is responsible create a sports table with a SET of sport name. The segment SET('Basketball','Tennis','Badminton') is what you have entered as the user type of the name column. |
|
|
CREATE TABLE sports (name SET('Basketball','Tennis','Badminton'));
|
|
|
| 13. |
In MySQL, type the following SQL statement to describe the name field. |
|
|
Comments (0)