Erwin Tutorial

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.

Parallax Capital Partners acquired Erwin and incorporated Erwin Inc, located in CA.

Academic Edition, Standard Edition, Work-group Edition, Navigator edition are the different types of editions available in Erwin.

Standard Edition: Most of the data modelers work with Standard Edition which provides all data modeling features.

Work-group Edition:  In this edition all data modeling features provided plus the data model versioning and multi-user modeling. Many data modeler can work on the same data model concurrently.

Navigator Edition:  Erwin Data Modeler Navigator Edition provide read-only access to models built with Erwin Data Modeler for viewing and analysis.

Academic Edition: The Academic Edition is a time-limited edition and includes a limited number of features. from the main product. It is designed to help students to learn creating simple data models.

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.

• Open CA Erwin Data Modeler Version 9 software.
• Go to menu ‘file’ and click ‘new’. A new box will be displayed. Click the button logical/physical from type. From the drop down list box, select ‘oracle’ as the database and version as ’10g/11g’. Click OK button. A new workspace is created. To select notation ‘IDEF1X’ or ‘IE’ Go to MODEL/MODEL PROPERTIES/GENERAL TAB to change.
• Above the workspace, you can see menus File, Edit, View, Diagram, Model, Actions, Tools, Windows, Help. Below that you can see the following tool bars.
  Standard, Toolbox, Formatting, Layout, Zoom, Display, Mart, Database, Alignment, Drawing, Transformations, Customize. On the display toolbar, you can see ‘Logical’ selected by default i.e. you will be working on creation of a logical data model.
• To create an Entity, click the icon “Entity” from ‘Toolbox’ Toolbar and drop it on the workplace. By default E/1 will be displayed as the entity name and type it as ‘Country’.
• To create an Attribute, Place the cursor on the entity ‘Country’ and right click it. From the displayed menu, click attribute properties which will take you to the attribute wizard. You can see a button New. Click ‘New’ on the wizard and type attribute name as ‘Country Code’. You can see different headers like ‘Parent Domain’, ‘Logical Datatype’, ‘Primary Key’, ‘Foreign Key, ‘Logical Only’.
  Select the parent domain as ‘string’ and by default logical datatype becomes ‘varchar2(20)’. Change the datatype to ‘varchar2 (2); Click the check box near the primary key.Similarly create another column Country Name with parent domain as ‘string’ and datatype as ‘varchar2 (50)’. Select NULL OPTION as ‘NOT NULL’ from the NULL OPTION list box. THERE ARE DIFFERENT TABS LIKE ‘General’, ‘Constraint’, ‘Link’, ‘Key Groups’, ‘Style’, ‘Definition’, ‘Where Used’, ‘UDP’, ‘Notes’ etc. To create constraints, you can use ‘constraint’ tab. To create indexes, you can use ‘key groups’ tab. For typing attribute definition ‘ you can use ‘definition tab’. For changing font size, font name, font color, you can use ‘style tab’. For seeing the properties like event, date created, created by, data modified, modified by, you can use ‘Notes’ tab. Click ok, and now you will be having 2 attributes Country Code, and Country Name under the entity ‘Country’ in the current logical workplace.
• Create another entity ‘Bank’ with two attributes namely Bank Code and Bank Name.
• In order to relate these two tables country, bank, a Foreign Key relationship must be created. To create a Foreign Key relationship, follow these steps.
  • Click the symbol ‘Non Identifying Relationship’ from ‘Toolbox’ Toolbar.
  • Place the cursor on the entity ‘Country’.
  • Place the cursor on the entity ‘Bank’. Now you can see the relationship (a line drawn from Bank to Country) between ‘Country’ and ‘Bank’. Double click on that relationship line to open ‘Relationships wizard’ and change the option from ‘Nulls Allowed’ to ‘No Nulls’ since bank should have a country code.

  The Logical Data Model created by following the above steps looks similar to the following diagram.

• SAVE: To save the file, click save button in the ‘standard’ tool bar and save it as .’CA_Erwin_Data_Modeler_v9_example’ and file type will be .erwin.

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.

Explanation:

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.

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) );

Next⇒ Creating a Logical Data Model using Erwin

We are providing Online Data Modeling Training on:

OLTP, Data Warehouse, Datamart, Dimensional and Snow Flake Data Modeling and Normalization, end to end process with ERWIN Tool.

To know more about the Syllabus, please visit the page Online Data Modeling Training Syllabus.