Background Image

DOCUMENTATION

?

Shortcut

PrevPrev Article

NextNext Article

Larger Font Smaller Font Up Down Go comment Print Attachment

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 2020 R2 14677), 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.

 

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

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.

Please refer to the tutorial for CUBRID ODBC driver connection.

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.

demodb_erd.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 RelationshipIdentifying 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 Admin and compare the actual database schema with this model.

fk_erwin.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 Admin 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.

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

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.

table_properties.png

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


List of Articles
No. Category Subject Date
8 Tools eXERD Which Deals with Logical/Physical Models, Supports CUBRID file 2020.07.23
7 Tools Utilizing the CUBRID Migration Tool (CMT) file 2020.07.23
6 Tools Running CUBRID Container Services in Docker, Kubernetes Environment file 2020.07.23
» Tools Reverse Engineering CUBRID Database with ERwin file 2020.07.14
4 Tools Let’s Take a Look at SQLGate for CUBRID! file 2020.07.23
3 Tools Forward Engineering a Data Model with ERwin file 2020.07.14
2 Tools CUBRID Manager Import Wizard Useful Tips! file 2020.07.23
1 Tools CSQL - Command Line SQL Interpreter 2020.05.12
Board Pagination Prev 1 Next
/ 1

Join the CUBRID Project on