Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Forward Engineering a Data Model with ERwin


This tutorial is part of CUBRID Data Modeling with ERwin series. We will illustrate how to forward engineer a data model and apply it to the actual database.

In ERwin terms Forward Engineering is the process of transforming a Logical Data Model (PDM) from ERwin Data Modeler to a Physical Data Model (LDM) on the actual database.

Once you have installed ERwin (in this tutorial we use version 8.2), start the program and choose from the main menu File -> New.... In the opened modal window choose Logical/Physical as a Model Type, and in Target Database select ODBC/Generic Version 3.0 (CUBRID ODBC is based on 3.0 spec). Then click OK.

forward_engineering_create_model_select_template.png

Create Entity

In ERwin language, Entity is a table object. To create a table representation, choose Entity tool from the main toolbar at the top.

forward_engineering_toolbar_entity.png

Then click on any place in your workspace. It will automatically place a new table object. Start typing its name in the text field. Once finished, press Tab key on your keyboard to jump to Keys section to add Primary Key. Then again press the Tab key to jump to the next section to enter other column. To enter more columns of the same type, hit Enter key on your keyboard. Once finished entering all columns, hit Esc key.

forward_engineering_create_entity.png

For this tutorial we will create two tables a and b and will forward engineer it to our existing demodb database.

forward_engineering_create_entity_tables.png

Setting Column Attributes

Once we have created our tables and the necessary columns, we need to set the data types and other attributes to each column. For this double click on any column to get started. Choose the parent domain, data type, check if a column is a primary or foreign key. Similarly, repeat for all new your tables.

Entity Relationships

In ERwin there are several types of Relationships. For our example we are interested in Identifying Relationship and Non-identifying Relationship.

Identifying Relationship

The main difference between these two types of relationships is that a Foreign Key of type Identifying Relationship is automatically added as a part of Composed Primary Key (a PK defined by multiple columns) as you can see below or in the output of this demodb example. In other words:

An identifying relationship is a relationship between two entities in which an instance of a child entity is identified through its association with a parent entity, which means the child entity is dependent on the parent entity for its identify and cannot exist without it. In an identifying relationship, one instance of the parent entity is related to multiple instances of the child. (ERwin documentation)

So, if you want to create a Foreign Key which is also a part of Composite Primary Key, choose Identifying Relationship. For this choose Identifying Relationship tool from the main toolbar.

forward_engineering_identifying_relationships.png

Since we need to reference table_a in table_b (FK will be created in table_b), once Identifying Relationships tool has been selected, first click on table_a then on table_b or simply click on table_a and drag your mouse to table_b. If the referencing table already contains a column with the same name as the primary key of referenced table (like in our case the column id exists in both tables), the program will popup a Key Migration Conflict modal window.

forward_engineering_key_migration_conflict.png

Since we want both tables have their own id columns and do not want to replace the child attribute, we will choose Rolename migrating attribute and enter our custom Foreign Key column name. Then click OK. You will notice that a new FK column has been added and become a part of Composed Primary Key.

forward_engineering_foreign_key_relationship.png

Non-identifying Relationship

Now let's perform the same steps but choose Non-identifying Relationship for our FK and see what model will be created. For this choose Non-identifying Relationship tool from the main toolbar. You can notice here that Non-identifying Relationship tool is represented like a dashed line with a blue square while Identifying Relationship tool is a solid line with a blue square.

forward_engineering_nonidentifying_relationships.png

Like before, click on table_a and drag your mouse to table_b. Also as before to resolve id column name conflicts choose Rolename migrating attribute and enter a custom Foreign Key column name like table_a_id. Then click OK. Now notice that the model is different from the previous one in the way that our FK is no more part of Composed PK.

forward_engineering_foreign_key_relationship_non_identifying.png

To finalize your changes you may double click on a relationship line between these two tables to change FK attributes like values for ON UPDATE or ON DELETE Referential Integrity actions, or the name of this foreign key index. In our case we will set the constraint name as fk_table_a_id and set RI Actions as displayed below.

The important fact to know here is that index names in CUBRID are unique per table meaning that two random tables can have same index names. Therefore in CUBRID you can give simple and clear names to indexes like fk_col_name instead of cluttering them with table names or with other identifiers. For more, refer to Important Facts to Know about CUBRID.

forward_engineering_fk_attributes.png

Forward Engineering Data Model

Finally, lets proceed to forward engineering our newly created data model to the existing demodb database.

Set Database Connection

Choose from the main menu Actions -> Database Connections.... If this menu item is not enabled, it is likely you are still viewing the Logical Model. To switch to Physical Model choose View -> Physical Model. Now the menu items should be enabled.

Once you open Database Connections window, enter the database connection information such as User Name and Password. If "CUBRID' is not automatically selected as a value for ODBC Data Source parameter, click on it and choose "CUBRID". Then click on Connect button. This will establish a connection with our database on the background.

erwin_reverse_engineer_odbc_connection.png

Schema Generation Preview

Now choose from the main menu Actions -> Forward Engineer... -> Schema....

forward_engineering_schema_generation.png

To preview the script which will be executed on your database server, press on Preview... button located at the bottom of the new opened window. If necessary you can manually modify the SQL in this editor.

forward_engineering_schema_preview.png

Quoting Reserved Words

In this example we can see that table_b contains a column which has a name called date. In CUBRID, date is a reserved word to represent DATE data type. So if we try to directly execute the given script above, we will get an error saying:

CUBRIDODBC CUBRID Driver-493 Syntax: syntax error, unexpected Date, expecting CHECK or FOREIGN or PRIMARY or UNIQUE 
Execution Failed!

To resolve similar errors when we need to have a column which has the same name as one of the reserved words, we simply need to quote such column names either in backticks (``), or double quotes (""), or square brackets ([]) . For more info about reserved words, see the Reserved Words section in CUBRID Wiki.

Instead of manually quoting each column name, we can rely on ERwin which provides an option to automatically Quote Names. For this to work, before pressing on Preview... button, click on the Summary tab, scroll down and check the Quote Names box (default: unchecked).

forward_engineering_quote_names.png

Now again press on the Preview... button to see the changes. Notice that all identifiers, including tables names and key names, are now escaped with double quotes which is now ready to be executed on CUBRID Server.

forward_engineering_schema_preview_quoted.png

Once ready, click on the Generate... button. If everything is correct, a success message will be displayed.

Important thing to notice here is that all statements are executed one by one, not in one batch. This means that if one of the statements fail somewhere in the middle, the previous success executions will not be rolled back.

forward_engineering_schema_generation_success.png

To confirm whether or not everything was correctly executed on the server side, we can open CUBRID Manager, a GUI database management tool for CUBRID, and check the tables and their attributes. We can see that both columns are now in our localhost, all columns are in-place, the data types are correct, PK and FK constraints have been successfully created and their names are exactly as we have set in our Data Model.

forward_engineering_cubrid_manager.png

Other Options

What you can also do before you generate the schema is check the DROP TABLE box. It is useful when the target database already contains the tables you are going to push from your model. If checked, in the Preview window you can notice a separate DROP TABLE statement for each table you are going to create.

You should remember that if no table exists which the script tries to drop, an error message will be displayed.

forward_engineering_drop_table_option.png

Remaining Questions

There are a few more questions which require an answer to better master ERwin Data Modeler. If you know answers, please leave your comment below.

  1. How to set custom escape symbol such as square brackets like in SQL Server instead of double quotes set by ERwin?
  2. What option is required to be set so that Forward Engineering rolls back in case some statements fail during schema generation process?
  3. Or how to make ERwin execute all queries in one transaction so that if failed, everything could rollback automatically?
  4. How to enable not just DROP TABLE option but DROP TABLE IF EXISTS syntax? Need to use pre-scripts?

comments powered by Disqus
Page info
viewed 24494 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated 2 years ago by
View revisions
Share this article