Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Reverse Engineering CUBRID Database with ERwin


This tutorial is part of CUBRID Data Modeling with ERwin series. To illustrate how to reverse engineer an existing database and get its schema information we will use the demodb database as an example, which you can choose to create when you install CUBRID RDBMS.

Once you have installed ERwin (in this tutorial we use version 8.2), start the program and choose from main menu Actions -> Reverse Engineer....

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

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 Next.

erwin_reverse_engineer_select_template.png

A new empty Data Model document will be created and opened. At this moment Set Options modal window will popup. We will go through some of them now.

erwin_reverse_engineer_set_options.png

Items to Reverse Engineer

On the left side of the window in the Items to Reverse Engineer group you can choose which objects you want to get the schema information of. You can remove check from Stored Procedure and View if you do not use them.

Options

Reverse Engineer

On the left side of the Set Options modal window you can choose what type of objects you want to reverse engineer. In CUBRID you have System Objects (Tables) and User Objects (Tables). If you want to reserve engineer only User Tables which you use in your application, leave the System Objects check box unchecked. Checking System Objects will get schema info for only System Tables. Here we will reverse engineer only User Tables which we can edit later.

For User Tables you can also choose to reverse engineer only those table to which the current or the specified user has access to, or all the tables.

Infer

Here you can choose which attributes of columns should Reverse Engineer infer from you database schema. Since CUBRID is a relational DBMS, it allows to set Primary and Foreign Keys. If the target database which you are reverse engineering already defines these keys, you do not need to set anything in this Infer group. ERwin will automatically detect them for you.

Once you have set the options, click Next to complete the reverse engineering process.

ODBC Connection

In the new modal view 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.

erwin_reverse_engineer_odbc_connection.png

Output

As the output of this process you will see the schema of your database. Below is a partial view of our demodb database.

erwin_reverse_engineer_db_schema.png

Relations

As you notice, in ERwin Data Modeler each table can be visually divided into two by a horizontal line. The above section list all keys (primary and foreign). The lower section list the rest member columns.

In the above example, game table has host_year column in the above section which is a part of three-column Composite Primary Key (a PK defined by multiple columns). In addition, it has a foreign key relationship with event and athlete tables through event_code and athlete_code FK. These relationships are illustrated with a grey line connecting two tables where the round circle point to a referencing table.

The important thing to learn here is that these event_code and athlete_code Foreign Keys represent Identifying Relationship which is different from usual Non-identifying Relationship. Identifying Relationship are always part of a Composite Primary Key. You can read more about in ERwin documentation.

To get detailed information about a specific relationship between table a and table b, double click on the relationship between those tables. You will see the following modal window. If you click on RI Actions tab (Referential Integrity Actions), you can check what values are set for ON DELETE and ON UPDATE. In the below example, both triggers are set to RESTRICT. You can choose any RI action allowed in CUBRID.

To confirm the correctness of this reverse engineering you can open CUBRID Manager and compare the actual database schema with this model.

erwin_reverse_engineer_foreign_keys.png

In addition, you can see the host_year column in the above section which is a part of three-column Composite Primary Key (a PK defined by multiple columns). To confirm the correctness of this reverse engineering you can open CUBRID Manager and compare the actual database schema with this model.

Column Attributes

When you double click on any column of a table in the model (alternatively right click on the table and choose Column Properties...), you can see a window with the columns attributes.

erwin_reverse_engineer_column_attributes.png

To check whether or not all the attributes are correct, we can compare them with the actual table schema.

CREATE TABLE "game"(
    "host_year" integer NOT NULL,
     "event_code" integer NOT NULL,
     "athlete_code" integer NOT NULL,
     "stadium_code" integer NOT NULL,
     "nation_code" character(3),
     "medal" character(1),
     "game_date" date,
    CONSTRAINT pk_game_host_year_event_code_athlete_code PRIMARY KEY("host_year","event_code","athlete_code"),
     FOREIGN KEY ("event_code") REFERENCES "event"("code") ON DELETE RESTRICT ON UPDATE RESTRICT,
     FOREIGN KEY ("athlete_code") REFERENCES "athlete"("code") ON DELETE RESTRICT ON UPDATE RESTRICT
);

You can see that everything is exactly as in the actual database. The NOT NULL constrains can be checked in the ODBC tab.

erwin_reverse_engineer_column_not_null.png

To find you the exact name of foreign keys used in the actual database, double click on the model table (not on its columns) or choose Table Properties... from the context menu. In the Where Used you can see the foreign key names used in the selected table.

erwin_reverse_engineer_table_properties.png

You can play with other options and investigate the rest functionality of ERwin Reverse Engineering tool.

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