Tag: Erwin

Erwin Tutorial

2 comments

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.

[ultimatetables 25 /]

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.

 

Reverse Engineering a Data Model

Leave a comment

What is Reverse Engineering a Data Model?

Reverse Engineering is a process or function to create a logical and physical data model by extracting information from an existing data source. You may either create a physical data model or logical data model or combination of both. Data Source may be oracle data base or MS SQL Server or IBM DB2 or .sql files etc.

Example: Oracle database may contain objects likes tables, views, indexes, sequences, triggers, rules, data type, owner, constraints like primary keys, foreign keys, comments etc. You can specify the objects that you are interested to create a model. If you are not interested in triggers, then there is no need for you to check that option.

All you have to know is how to connect to the data source and whether necessary privileges are allocated to you.

If you do have any problems, while connecting to the data source, please ask system administrator or database administrator to resolve the issues.

Reasons for Reverse Engineering:

  • Data base may be present but there may be no data models and hence no logical data model and physical data model print outs for which project team may not be sure about the objects(tables, index, constraints etc) and relationships between them in data base.
  • Data Models may be present but it may not be in sync with data base.
  • When you integrate different sources(some sources may not have data models) into one database to create a data warehouse, then you have to represent these sources as data models (original AS IS). During discussions within the project team, it will give more clarity about the original structure (AS IS) and future (data warehouse) data models.
  • You can give logical data models to the business community (SME, Business Analysts etc.) and physical data models to development community (developers and dbas)

Note:

You can modify/update the reverse engineered model as per your needs. Alignment of tables and relationship on the work place may not be proper and printing out the models will not be in a presentable format. So please align it.

Sources from which data model can be reverse engineered: Oracle, MS SQL, PostGreSql, DB2 UDB, MYSQL, MS ACCESS, Sybase, xml files, sql files etc.

The Reverse Engineering wizard for each data base modeling software may be different. Learn how to connect to the databases, how to check different database objects that you want to reverse engineer and see whether you can save these settings.

How to create a data model from a Database?

For example, if you would like to reverse engineer database object from Oracle Production Server. To connect to oracle, you will need user name, password, and connection string. Then you have to check/click all different database objects(tables, columns, indexes, views, synonyms etc) that your are interested to reverse. Try to find out whether you can save these settings with a “NAME” (PRODUCTION_ORACLE_SERVER). The reason is when you connect to the same database next time, then there is no need for you to check all those options once again and you can use the saved information with the name “PRODUCTION_ORACLE_SERVER”.

How to create a data model by reverse engineering from scripts?

In the section, we are using the DDL scripts which have been created earlier in the page DDL Scripts from Data Model. Generated.SQL is the file name that contains DDL scripts which is stored in your hard drive. Now we will explain how to reverse engineer and create data model from the above script.

Reverse Engineering – Creating a data model from DDL scripts using Toad Data Modeler:

  • Click File→Reverse Engineering.Click Next button on the bottom of the page.
  • Select Data Source: Under “Select Data Source”, you can see the options “Database RE” and “DDL Script RE”.
  • Since we have created this .sql script from “oracle 11g release 2” database, highlight “oracle 11g release 2” and click next.
  • Select Data Provider: By default, “DDL script file data provider” is chosen. Click next.
  • Connecting:Browse file from your folder and select the file “C:\Users\Data Modeling\Documents\Toad Data Modeler\GeneratedScripts\Generated.SQL”. Click next.
  • Select Data Migrator: By default, it chooses “Reverse Engineering from oracle 11g release 2 DDL script of database”. Click next.
  • What to reverse: Check the boxes against different objects like tables, sequences, triggers etc. Click next
  • Options: Check different options like check constraints, indexes, unique constraints etc. Click next
  • Alias: If you want to use the same kind of settings in future, then save your settings, click save and enter some alias name. Click next.
  • Tables: You can check the tables that are required. Click Execute.

Now you can see the created data model with all database objects, relationships, and their properties in the work-space.