All Fusion Erwin Data Modeler:
All Fusion Erwin Data Modeler commonly known as Erwin, is a powerful and leading data modeling tool from Computer Associates. Computer Associates delivers several software for enterprise management, storage management solutions, security solutions, application life cycle management, data management and business intelligence.
Erwin makes database creation very simple by generating the DDL(sql) scripts from a data model by using its Forward Engineering technique or Erwin can be used to create data models from the existing database by using its Reverse Engineering technique.
Erwin workplace consists of the following main areas:
- Logical: In this view, data model represents business requirements like entities, attributes etc.
- Physical: In this view, data model represents physical structures like tables, columns, datatypes etc.
- Modelmart: Many users can work with a same data model concurrently.
What can be done with Erwin?
- Logical, Physical and dimensional data models can be created.
- Data Models can be created from existing systems(rdbms, dbms, files etc.).
- Different versions of a data model can be compared.
- Data model and database can be compared.
- SQl scripts can be generated to create databases from data model.
- Reports can be generated in different file formats like .html, .rtf, and .txt.
- Data models can be opened and saved in several different file types like .er1, .ert, .bpx, .xml, .ers, .sql, .cmt, .df, .dbf, and .mdb files.
- By using ModelMart, concurrent users can work on the same data model.
In order to create data models in Erwin, you need to have this All Fusion Erwin Data Modeler installed in your system. If you have installed Modelmart, then more than one user can work on the same model.
How to create a Logical Data Model using CA Erwin Data Modeler Version 9?
In the following section, a simple example with a step by step procedure to create a logical data model with two entities and their relationship are explained in detail.
How to create a Physical Data Model using CA Erwin Data Modeler Version 9?
- Change the view from ‘Logical to Physical’ from the drop down list.
- If you want to add some physical properties, you can add it here.
In the logical view: When you right click and view entity properties for entity ‘Country’, you can see tabs like volumetrics, definition, style, icon, where used, UDP, NOTES etc. The information that you fill will explain about entity properties.
In the physical view: when you right click and view table properties for table ‘country’, you can see tabs like table options, general, partitions, supplemental logging, validation, materialized view log, style, icon, synonym comment, object creation order, where used, UDP, Notes etc.
If you are an expert, then you can fill relevant details in the above mentioned tabs so that it will be useful while creating scripts. If you are not an expert, you can discuss with DBAs, get relevant information and fill details.
How to generate DDL(sql) scripts to create a database using CA Erwin Data Modeler Version 9?
- Click menu ‘edit’. Navigate to ‘selection’ and click ‘select all’. Objects bank and country will be highlighted.
- Click the ‘forward engineer schema generation’ icon in the ‘database’ tool bar. A new Wizard appears.
- There are two boxes. On the left side box, you can several database objects. On the right side box, you can see different options for each object.
|Left Side Box||Right Side Box|
|Database||Do not select anything|
|Schema||Do not select anything|
|Storage||Do not select anything|
|Table||1) Check table under 'create' option|
|Materialized View Log||Do not select anything|
|Column||Do not select anything|
|Materialized View||Do not select anything|
|View||Do not select anything|
|Index||Do not select anything|
|Referential Integrity||1) Check 'create/pk' under 'primary key/pk';|
2) Check 'create/fk' under 'foreign key (FK)'
|Trigger||Do not select anything|
|Other Options||Click 'constraint name'|
Click preview. You can see the sql code generated by Erwin data modeler tool.
CREATE TABLE Country ( Country_Code VARCHAR2(2) NOT NULL ,
Country_Name VARCHAR2(50) NOT NULL ,
CONSTRAINT XPKCountry PRIMARY KEY (Country_Code) );
CREATE TABLE Bank ( Bank_Code VARCHAR2(2) NOT NULL ,
Bank_Name VARCHAR2(50) NOT NULL ,
Country_Code VARCHAR2(2) NOT NULL ,
CONSTRAINT XPKBank PRIMARY KEY (Bank_Code),
CONSTRAINT Country_to_Bank FOREIGN KEY (Country_Code) REFERENCES Country (Country_Code) );