Data Modeling with ERWin

Design and build Service-Oriented Architecture Solutions with the Oracle SOA Suite 10gR3

Depending on your data modeling need and what you already have, there are two other ways to create a data model: Derive from an existing model and Reverse Engineer an existing database.

Let’s start with creating a new model by clicking the Create model button.

Data Modeling with ERWin

We’d like to create both logical and physical models, so select Logical/Physical.

Data Modeling with ERWin

You can see in the Model Explorer that our new model gets Model_1 name, ERWin’s default name.

Data Modeling with ERWin

Let’s rename our model to Packt Model.

Data Modeling with ERWin

Data Modeling with ERWin

Confirm by looking at the Model Explorer that our model is renamed correctly.

Data Modeling with ERWin

Next, we need to choose the ER notation. ERWin offers two notations: IDEF1X and IE. We’ll use IE for our logical and physical models.

Data Modeling with ERWin

Data Modeling with ERWin

It’s a good practice during model development to save our work from time to time.

Data Modeling with ERWin

Data Modeling with ERWin

Our model is still empty, so let’s next create a logical model in it.

Logical Model

Logical model in ERWin is basically ER model. An ER model consists of entities and their attributes, and their relationships. Let’s start by creating our first entity: CUSTOMER and its attributes.

To add an entity, load (click) the Entity button on the toolbar, and drop it on the diagramming canvas by clicking your mouse on the canvas.

Data Modeling with ERWin

Data Modeling with ERWin

Rename the entity’s default E/1 name to CUSTOMER by clicking on the name (E/1) and typing its new name over it.

Data Modeling with ERWin

To add an attribute to an entity, right-click the entity and select Attributes.

Data Modeling with ERWin

Click the New button.

Data Modeling with ERWin

Type in our first attribute name (CUSTOMER_NO) over its name, and select Number as its data type, and then click OK.

Data Modeling with ERWin

Data Modeling with ERWin

We want this attribute as the entity’s primary key, so check the Primary Key box.

Data Modeling with ERWin

In the same way, add an attribute: CUSTOMER_NAME with a String data type. Our CUSTOMER entity now has two attributes as seen in its ER diagram. Notice that the CUSTOMER_NO attribute is at the key area, the upper part of the entity box; while the CUSTOMER_NAME is in the common attribute area, the bottom part.

Data Modeling with ERWin

Similarly, add the rest of the entities and their attributes in our model. When you’re done, we’ll have six entities in our ER diagram. Our entities are not related yet.

To rearrange the entities in the diagram, you can move around the entities by clicking and dragging them.

Data Modeling with ERWin


In ERWin you can set Identifying, Non-identifying, and Many-to-many relationships. In our model example, all relationships are Identifying, meaning the parent entity’s primary key is migrated to the child entity as its primary key (or part of if it has its own key already).

In Non-identifying relationship the migrated key as common attribute.

To add an Identifying relationship between two entities, click the Identifying relationship button on the toolbar, then click the parent entity first then the child. For example, after clicking the button, click CUSTOMER then click SALES_ORDER.

The two entities are related, CUSTOMER_NO attribute is the primary key in the SALES_ORDER entity.

Do the same with PRODUCT and SALES_ORDER. Note that PRODUCT_CODE attribute is added as the primary key of SALES_ORDER entity.

Now, HARDWARE, SOFTWARE, and SERVICE are sub-type entities of PRODUCT (in ERWin sub-type is a.k.a. sub-category).

You can move around the relationship lines by clicking and dragging them.

We create sub-type relationship using the Exclusive sub-category button.

For the first relationship, after you load your mouse with the button, click the parent entity (PRODUCT in our example), and then click the child (either one of the three sub-type entities).

For the next sub-type relationships to the same parent (PRODUCT), after you click the Exclusive sub-category button, click the sub-type notation on the diagram, and then on the sub-type entity.

We haven’t quite finished with our sub-types; we now need to set our sub-type discriminator; so, right-click the sub-type notation and select Subtype Properties.

We use PRODUCT_TYPE attribute to designate (determine) which of the three sub-products the data is about.

Note that our discriminator (PRODUCT_TYPE) is shown on the diagram next to the sub-type notation.

Validation Constraint and Default Value

Next, I’d like to show two related features of ERWin: Validation constraint and Default value. We’ll apply these features to the PRODUCT_TYPE attribute.

Right-click the PRODUCT to open up the Attributes window.

Select PRODUCT_TYPE attribute, and then click the ellipsis button to open the Validation constraint window.

Click New to add a new Validation Rule.

On the Logical field, type in the name of your new rule; for our example: PRODUCT_TYPE.

Our PRODUCT_TYPE can only have three valid values: HW for Hardware product type, SW for Software, and SV for Services. Back on the Validation Rules window, select (check) the Valid Values List, and enter the three values of the PRODUCT_TYPE validation rule.

Next, on the Attributes window, click the ellipsis button of the Default field.

When prompted, confirm Yes.

On the Default Values window, click New, to create a new Default Values.

In our example, we have much more Hardware sales orders than the other two types, so we’d like to set HW as the default value or PRODUCT_TYPE.

We’re done with our Validation constraint and Default value, so close all windows. Let’s take a look at the physical model by clicking the dropdown arrow of the model selection.

You can see that the only change on diagram is the sub-type notation; but, there’s a whole lot more (physical) object types available on the Model Explorer to enrich your physical data model. (In this article, we only deal with tables)

Forward Engineer

Finally, you can forward engineer the physical model into a database. The following screenshots showing the generation of our model to Oracle should give you an idea how to forward engineer in ERWin.

If you haven’t done so (such as during the model creation), select your target database.

You then start by selecting Forward Engineer > Schema Generation.

You have plenty to choose, depending on which physical objects you want to generate from your physical model. When you click Generate, the Connection dialog window shows up.

Enter the connection information (or choose if you already have the connection you need): user id and its password, and the connection string (database name), and then click Connect. You’ll see generation messages. When your forward-engineering is successful, close all windows. You can then check on your Oracle database that the Sales Order tables are created.


By way of the Sales Order example I showed you a typical cycle of data modeling in ERWin. ERWin has much more functions and features that I don’t use in the modeling example above; also, it supports many other databases to forward engineer your model to, including MySQL and Progress open source databases.

Books to Consider

comments powered by Disqus