ERwin Data Modeller tutorial - Our First Model

In the last post I have described how to set up ERwin so you can store all your models into database using AllFusion ERwin Model Manager. This is very useful since it allows you to:

1. share models with other team members – yeap, teamwork!!!
   
2. make different versions of your model - ERwin has its own versioning tool which works fine; whenever you save current version of model old version (one before changes were made) is saved as well, so you can roll back
   
3. data mine Model Mart database and get some interesting info about the model (meta-data) you might need – I will talk about this later since it is very important
   

This post will be about creating your first model. I will show you how to create simple model and explain the difference between logical and physical model, identifying and non-identifying relationships. So let’s get started. Shall we?

1. To create new model, click on File/New and in the dialog select if you want to create Logical, Physical or Logical/Physical model. If creating physical model, choose the database you want to create it for. For those who don’t know the difference between logical and physical: logical is about the business (gather business requirements and transfer them into model), physical is purely about the db. To be honest I usually draw logical model on a piece of paper and then create physical model in Erwin.
   
2. If you have created new model, your screen should look like this:
   The whole screen consists from 3 main areas – menu and toolbars, object explorer on the left and main workplace on the right.
   
3. So how do I create a model? First of all you have to create some tables (in logical model they are called entities). Just click on and create entity in the main workspace area. Give this entity name. After that you have to add some columns (in logical model they are called attributes). If you create more than one table, you can connect them with relationships. You can basically choose (by clicking on ) between two types of relationships:
   
   • Identifying relationship – PRIMARY key from parent table migrates to the child table as a PRIMARY key, which means that the row in the child table cannot exist without row with the same PRIMARY key in parent table. I use identifying relationship only when I want to resolve many-to-many relationships using additional “key ”table.
   • Non-identifying relationship – PRIMARY key from parent table migrates to the child table as a FOREIGN key, which means that the row in child table can exist without row in parent table (FOREIGN key can be NULL, PRIMARY cannot be NULL).
     

Scenario: Let’s create simple model. In CreditCrunch Bank Ltd. they store data about Customers and their bank Accounts. Every customer can have more than one Account and one or more customers can be assigned to one Account (for example company account can be accessed by more people). Every customer can have Personal Banker assigned. Bank is supposed to store Customer’s and Personal Banker’s name and surname. You also have to store currency and type of Account. Do not forget about proper definition of PRIMARY keys!

Solution: You have to create 4 tables: Customer, Account, PersonalBanker and CustomerAccount. The last one just resolves the many-to-many problem between Customer and Account. Others contain info about business entities (Customer, Account, Personal Banker). What about the relationships? You use identifying relationship between Customer and Customer_Account and also between Account and CustomerAccount. Why? Because when there is an Account it must have Customer assigned and also if someone is a Customer he/she must have at least one Account. One cannot exist without the other one and so you need to use identifying relationships. The last relationship between Customer and PersonalBanker is non-identifying because Customer can (but does not have to) have Personal Banker. And here is the picture:

If you have created your model you probably want to save it. You can store it either locally or in a database. To save it locally just click File and save or save as… as with any other application. What is more interesting is that you can store models in a database. Click Services and Connections. Dialog allowing you to connect to Model Mart should appear. This dialog contains connection we have created in the previous post so just select this connection and click Connect. Now you are connected to Model Mart and can save all your models into DB (click Services and Save/Save as…).