[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Data Modeling with ERWin, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
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.
We’d like to create both logical and physical models, so select Logical/Physical.
You can see in the Model Explorer that our new model gets Model_1 name, ERWin’s default name.
Let’s rename our model to Packt Model.
Confirm by looking at the Model Explorer that our model is renamed correctly.
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.
It’s a good practice during model development to save our work from time to time.
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.
Rename the entity’s default E/1 name to CUSTOMER by clicking on the name (E/1) and typing its new name over it.
To add an attribute to an entity, right-click the entity and select Attributes.
Click the New button.
Type in our first attribute name (CUSTOMER_NO) over its
We want this attribute as the entity’s primary key, so check the Primary Key box.
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.
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.
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.
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.
Post new comment