erwin data modeller tutorial - forward engineering tool

In last post we have designed simple model of a bank and save it either locally or to data mart. But usually what you want to do with model is to export it to the database so it can be used by you and other developers. By export I actually mean creating all tables, relationships etc. in the database. ERwin’s export dialog is really sophisticated and allows you to properly set what you want to export. If you have not chosen target db engine do it now clicking on Database/Choose Database. If you have chosen your target db engine you are ready to export your model. Click on Tools select Forward Engineer and then Scheme generation. This dialog should pop up:

This dialog allows you to fully set up export. Here is description of main fields:

• Schema Generation file – any configuration you set up can be saved into XML file and used later. This is extremely useful when you have one file for creating tables, relationships etc. and one for dropping them. Thing is when you make changes to the model you have to recreate tables in the DB and in order to do so you have to drop them first (when you use PK and FK constraint you have to drop them in special order) and create them again. So just create two config export files – one for dropping and one for creating.
• In Option tab there are two windows: DB Schema Generation and Schema where you actually set up what is ERwin going to do. I recommend you focus on Table and Referential integrity in left window where you can set whether and what to create or drop. I also recommend to uncheck creation of ERwin triggers.
• On the bottom there are few buttons that might be interesting for you:
  • Filter allows you to filter out tables you do not want to be generated
    
  • Preview actually shows you the SQL query that will be sent to db
  • Generate will connect (ask to select connection) to the database. If you have already selected connection ERwin will NOT ask you again so if you want to export your model to different db you have to change in main window in Database/Database Connection…
    

One important thing is that you do not make changes to design directly in the database but you do them first in ERwin and then recreate tables changed. Developers tends to skip ERwin and make some little changes directly to the DB.
Well you know what is forward engineering tool. But what if you already have some tables in database and you want to create model according to them? ERwin allows you to reverse engineer your database and export all metadata from DB and get a model. Just click Tools/Reverse engineering tool… and short wizard will guide you through the process. In this post you have learnt how to use forward engineering tool to export your model into DB and reverse engineering tool to create model according to existing DB.

Read more...

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…).

Read more...