Reverse Database Enhancements
Visual Paradigm supports reverse engineering database into Entity Relationship Diagram (ERD). In the past versions, if you generate database from ERD, make changes to the database such as removing columns, and then reverse the database back into ERD, there may be columns that are removed from the database but keep existing in the ERD. This lead to inconsistency between data design and the database schema. In the new version, the reverse database feature is enhanced such that the ERD is conformed to the database by reversing database into ERD.
Reverse Database to ERD
The steps below shows how to reverse engineer a MySQL database into ERD.
Select
Tools > Object Relational Mapping (ORM) > Reverse Database... from the main menu.
Select the Programming language for the project and press
Next to continue.
Enter the database configuration and press
Next to continue.
Select the tables in the database, and press
Finish to proceed with the reverse process.
An ERD is formed from the entity models reversed from database schema.
Modify Database
The steps below will remove a column, add a table and add a foreign key in MySQL database through the use of the MySQL Table Editor.
Remove columns
Let’s remove the other_customer_details column in the Customers table.
Select the
Customers table, and press the
Edit Table button.
This shows the
MySQL Table Editor.
Right-click on the
other_customer_details column and select
Delete Table Columns from the pop-up menu.
The column is deleted.
Press the
Apply Change button. This shows the
Confirm Table Edit dialog.
Press the Execute button to perform the delete column action and close the dialog box.
Press the Close button to exit the MySQL Table Editor.
Add table
Let’s create a table by using MySQL administrator. This is the schema:
movie_info(
id int PRIMARY KEY,
description varchar(255),
cast varchar
);
Press the Create Table button in the MySQL Administrator.
Enter the
Table name in the
MySQL Table Editor
To add a columns to the table:
Double-click on the
Column Name in the last row, enter the name and press
Enter.
This creates the row is and cause the cursor to move to the
Datatype column.
Enter the
Datatype and press
Enter.
The
id column is created and the firstly created column will become the primary key column by default. Now the cursor is moved to the next row. Repeat these steps to complete the table.
Press the
Apply Changes button. This shows the
Confirm Table Edit dialog box.
Press the
Execute button to confirm and execute the generated
SQL.
Add Foreign Key
Now the table movie_info is created. This table is use to store information about movies, so the id is referenced to the movie table. Let’s add a foreign key to the movie_info table.
Switch to
Foreign Keys tab in the
MySQL Table Editor.
Press the
+ button under the foreign key list.
Enter the
Foreign Key Name in the
Add Foreign Key dialog box, and press the
OK button.
Select the reference table for the foreign key.
Select the column to become a foreign key and map with the reference column.
Press the
Apply Changes button to apply add a foreign key. This shows the
Confirm Table Edit dialog box.
Press the
Execute button to confirm and execute the generated
SQL.
Update the ERD by reverse Database
After modified the database, switch back to VP-UML and reverse the database to ERD.
Select Tools > Object Relational Mapping (ORM) > Reverse Database from the main menu.
Use the same database configuration. The table we created in the database is available to be reversed.
Press the
Finish button to reverse the tables.
The ERD is updated by showing the
movie_info entity.
Resource
