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
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
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.
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.
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.
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.
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
As the output of this process you will see the schema of your database. Below is a partial view of our demodb database.
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.
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.
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.
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.
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.
You can play with other options and investigate the rest functionality of ERwin Reverse Engineering tool.