Data Modeling with ERWin

Exclusive offer: get 50% off this eBook here
Oracle SOA Suite Developer's Guide

Oracle SOA Suite Developer's Guide — Save 50%

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

$47.99    $24.00
by Djoni Darmawikarta | February 2009 | Content Management Oracle

"ERWin from Computer Associates (CA) is one of the most popular data modeling software in the market. I’ve been using this data modeling tool since its release 3; it’s now at release 7. In this article I’ll show how to create a data model from scratch, starting with logical and physical models, and then forward engineer the physical model to Oracle database". In this article by Djoni Darmawikarta we’ll create a data model for Sales Order, with customer and product data.

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

Oracle SOA Suite Developer's Guide Design and build Service-Oriented Architecture Solutions with the Oracle SOA Suite 10gR3
Published: March 2009
eBook Price: $47.99
Book Price: $79.99
See more
Select your format and quantity:

Relationship

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.

Summary

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.

Oracle Modernization Solutions A practical book and eBook guide to planning and implementing SOA Integration and Re-architecting to an Oracle platform
Published: September 2008
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Djoni Darmawikarta built his career in IBM Asia Pacific and Canada as a software engineer, international consultant, instructor and project manager, for a total of 17 years. He's currently a technical specialist in the Data Warehousing and Business Intelligence team of a Toronto-based insurance company. Outside of his office works, Djoni writes IT articles and books.

Books From Packt

Oracle SOA Suite Developer's Guide
Oracle SOA Suite Developer's Guide

Service Oriented Architecture with Java
Service Oriented Architecture with Java

Object-Oriented JavaScript
Object-Oriented JavaScript

SOA Governance
SOA Governance

Oracle Modernization Solutions
OpenCms 7 Development

Zenoss Core Network and System Monitoring
Zenoss Core Network and System Monitoring

Building Websites with Joomla! 1.5
Building Websites with Joomla! 1.5

Building Powerful and Robust Websites with Drupal 6
Building Powerful and Robust Websites with Drupal 6

Your rating: None Average: 5 (6 votes)
Thank U by
detailed info for the novice user
Very Useful.... by
Thanks alot for presenting this document.....Very helpful...
Thank you so much. Very by
Thank you so much. Very useful and neat explanation
thanks Djoni by
thanks for the article.
Reverse Engineer by
Very useful description. Request you to please provide the same kind of desc for Reverse Engineer .

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
v
G
W
Z
w
e
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software