Data Modeling Tools – Xcase

Xcase – Overview:

Xcase is a powerful and intuitive data modeling tool. It automates database creation and maintenance, making the modeling process simple and visual. The tool provides tight support for the leading DBMS and an impressive set of capabilities.

Using Xcase, data modelers can create data models from scratch or from existing databases using the Reverse Engineering module. Once the model is complete, Xcase Forward Engineering module generates all the DDL scripts necessary to create a new database or update an existing one.

Using Xcase, you can:

⇒ Create Professional Data Models from scratch.

⇒ Reverse Engineer existing database structures.

⇒ View and organize the database structure at different levels of depth.

⇒ Communicate effectively with clients and colleagues.

⇒ Fully control database Metadata within the model and add user defined attributes for each object.

⇒ Manage different versions of your model/database using the advanced Version Control module – compare a model to the actual database, to another model or to a previous version of the same model and generate a detailed report of all discrepancies found.

⇒ Synchronize your model with the database using the 2-way synchronization feature. Xcase provides a detailed report on discrepancies found between the model and the database and allows you to update the model based on the actual database structure or update the database based on your model.

⇒ Automatically generate all the code necessary to create and maintain the database without jeopardizing existing data.

⇒ Validate the data in the database against the business rules defined in the model.

⇒ Access and edit the data relationally using automatic parent/child browsers and lookups.

⇒ Migrate the database metadata and data from one DBMS to another.

Note: For more information, refer the Xcase data modeling web page.

 

Creating a Physical Data Model using Erwin

How to create a Physical Data Model using Erwin 4.1.4 version?

1: Change the view from “Logical to Physical” from the drop down list.

2: Click “Database” from main menu and then click “Choose Database” from the sub menu. Then select your target database server where the database has to be created. Click ok.

3: Place the cursor on the table “Country” and right click it. From the displayed menu, click columns which will take you to the column wizard. Click the “Database Tab”, which is next to “General Tab” and assign datatypes “VARCHAR2(10), VARCHAR2(50) for columns COUNTRY_CODE and COUNTRY_NAME respectively. Change the default NULL to NOT NULL for the column COUNTRY_NAME. Similarly, repeat the above step for the BANK table. Once you have done all of these, you can see the physical version of the logical data model in the current workplace.

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

physical

How to generate DDL(sql) scripts to create a database?

1: Select the view as <b>Physical</b> from the drop down list.

2: Click “Tools” from main menu and then click “Forward Engineer/Schema Generation” from the sub menu which will take you to the “Schema Generation Wizard”. Select the appropriate properties that satisfies your database requirements like schema, table, primary key etc. Click preview to see your scripts. Either you can click to generate the table in a database or you can store the scripts and run against the database later.

The DDL(sql) scripts generated by Erwin by following the above steps looks similar to the following script.

CREATE TABLE Country(Country_Code VARCHAR2(10) NOT NULL,
Country_Name VARCHAR2(50) NOT NULL,
CONSTRAINT PK_Country PRIMARY KEY (Country_Code));
CREATE TABLE Bank(Bank_Code VARCHAR2(10) NOT NULL,
Bank_Name VARCHAR2(50) NOT NULL, Country_Code VARCHAR2(10) NOT NULL,
CONSTRAINT PK_Bank PRIMARY KEY(Bank_Code) );ALTER TABLE Bank ADD( CONSTRAINT FK_Bank
FOREIGN KEY (Country_Code) REFERENCES Country );

Note: This is not a complete tutorial on Erwin. We will add more Tips and Guidelines on Erwin in near future. Please visit us soon to check back. To know more about Erwin, contact its official website www.ca.com.

Creating a Logical Data Model using Erwin

How to create a Logical Data Model using Erwin 4.1.4 version?

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.

1: Open All Fusion Erwin Data Modeler software.

2: Select the view as “Logical” from the drop-down list. By default, logical will be your workplace.

3: Click New from File menu. Select the option “Logical/Physical” from the displayed wizard. Click Ok.

4: To create an Entity, click the icon “Entity” and drop it on the workplace. By default E/1 will be displayed as the entity name. Change it to “Country”.

5: To create an Attribute, Place the cursor on the entity “Country” and right click it. From the displayed menu, click attributes which will take you to the attribute wizard. Click “New” button on the wizard and type attribute name as “Country Code”. Select the data type as “String” and click OK. Select the option Primary Key to identify attribute “Country Code” as the primary key. Follow the same approach and create another attribute “Country Name” without selecting the primary key option. Click ok, and now you will be having 2 attributes Country Code, and Country Name under the entity “Country” in the current logical workplace.

6: Create another entity “Bank” with two attributes namely Bank Code and Bank Name by following steps 4 and 5.

7: 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.

(a) Click the symbol “Non Identifying Relationship”.
(b) Place the cursor on the entity “Country”.
(c) 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.

logical

 

Next⇒ Creating a Physical Data Model using Erwin

 

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.

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.

Left Side BoxRight Side Box
DatabaseDo not select anything
SchemaDo not select anything
StorageDo not select anything
Table1) Check table under 'create' option
Materialized View LogDo not select anything
ColumnDo not select anything
Materialized ViewDo not select anything
ViewDo not select anything
IndexDo not select anything
Referential Integrity1) Check 'create/pk' under 'primary key/pk';
2) Check 'create/fk' under 'foreign key (FK)'
TriggerDo not select anything
Other OptionsClick '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) );

Next⇒ Creating a Logical Data Model using Erwin

 

Data Modeling Tools – What to Learn? Part 2

Dimensional Data Model:

  • Is there any specific notation to identify a Data Warehouse/Data mart data models?

Subject Area:

  • How to create subject area and assign relevant entities to subject area?

Reports:

  • How to generate reports from data model and export to .XLS, .DOC, .XML file formats?

Naming Options:

  • Is there any method to change the entity/table, attribute/column name from
    upper case to lower case or lower case to upper case?

Import & Export:

  • How to create data models from .xls, .txt files etc.?
  • How to import and export meta data into ETL tools?

Abbreviation Document:

  • How to create/attach a standard abbreviation document(for naming tables, columns etc.)?

Print:

  • How to send data models to printer/plotter/Acrobat Reader?

Backup:

  • How to take backup of data model?

Others:

  • How to split a data model to logical and physical data model?
  • How to copy and paste objects within data model and across data models?
  • How to search an object within a data model?
  • How to change the font size and color of entities,attributes,relationship lines?
  • How to create a legend?
  • How to show a data model in different levels like entity level, attribute level, and definition level?

 

Data Modeling Tools: What to Learn?

Data modeling tools are the only way through which we can create powerful data models. Following are the various options that we have to know and learn in data modeling tools before start building data models.

Logical Data Model:

  • How to create entity and add definition, business rule?
  • How to create domains?
  • How to create an attribute and add definition, business rule, validation rules like default values and check constraint?
  • How to create supertypes, subtypes?
  • How to create primary keys, unique constraint, foreign key relationships, and recursive relationships?
  • How to create identifying and non-identifying relationship?
  • How to assign relationship cardinality?
  • How to phrase relationship connecting two tables?
  • How to assign role names?
  • How to create key groups?
  • How to create sequence no’s?

Physical Data Model:

  • How to rename a table?
  • How to rename a column,validation rules like default and check constraints?
  • How to assign NULL and NOT NULL to columns?
  • How to name foreign key constraints?
  • How to connect to databases like MS Access, Oracle, Sibase, Terradata etc?
  • How to generate sql code from data model to run against databases like MS Access, Oracle, Sibase, Terradata etc.?
  • How to create a data model from an existing database like MS Access, Oracle, Sibase, Terradata etc.?
  • How to add database related properties to tables, indexes?
  • How to check different versions of the data model?
  • How many data modelers can concurrently work on the same version of a data model?

Data Modeling Tools – What to learn? Part 2

Data Modeling Tools

There are a number of data modeling tools to transform business requirements into logical data model, and logical data model to physical data model. From physical data model, these tools can be instructed to generate sql code for creating database.

Popular Data Modeling Tools:

Tool NameCompany Name
ErwinComputer Associates
EmbarcaderoEmbarcadero Technologies
Rational RoseIBM Corporation
Power DesignerSybase Corporation
Oracle DesignerOracle Corporation
XcaseRESolution LTD.

 

How to create a physical data model from a logical data model using Embarcadero’s Data Architect?

We are focusing to create this physical data model with oracle as the database. So we have selected oracle 12c as the database.

Open existing logical data model “02_LDM_creating_entity_version2”.

Click menu Model/Generate Physical Model. A small screen will appear. Click ok.

A new screen is opened. There are 5 steps in this process.

We are explaining step 1 and Step 3 only.

Step 1 of 5:

Name the physical data model: pdm_creating_entity_version2

Type of physical model: relational

Target_database_platform: oracle12c.

Click next and again click next.

Step 3 of 5:

Attach the naming template “enterprise_naming_standards.nst”. Click finish.

You can now see the physical data model.

04_physical_data_model_diagram

 

 

How To Create Naming Standards In Embarcadero’s Data Architect?

Create a .csv file named “Naming_Standards.csv” and store the following information in that csv file. The reason for creating this file is to avoid errors and typos in our naming conventions and data modeling naming convention standards can also be followed.  We have eliminated VOWELS in the actual word so that physical object names will not exceed the actual length provided by the database. Later this file will be created in .nst format (embarcadero’s naming standards format) and has to be attached to the physical data model that we will create in the next section. This file (.nst) can be attached to all data models created across the enterprise.

Note: We are not providing naming standards for logical data model.

Step1: Creating .csv file and storing it as “Naming_Standards.csv”

Open your Microsoft Excel and type the following data. Then store it in .nsv format.

DEPARTMENT DPRTMNT
NUMBER NMBR
NAME NM
ADDRESS ADDRSS
IDENTIFIER IDNTFR
EMPLOYEE EMPLY
MANAGER MNGR
SALARY SLRY
DATE DT
OF OF
JOINING JNNG
PHONE PHN
NUMBER NMBR
INCENTIVE INCNTV
INDICATOR INDCTR

Step2: Creating .nst file and storing it as “Enterprise_Naming_Standards.nst”

Open existing logical data model “02_LDM_creating_entity_version2”

Click menu Tools/Naming Standards Template Editor.

A new screen appears.

On top of the screen, there are four different tabs. Name, Logical, Physical, and Mapping.

Under name tab: type the file name as enterprise_naming_standards.

Under Logical tab, you can change max length of entity, attribute, view, key, relationship. Select case as upper for entity.

Under physical tab, you can change you can change max length of entity, attribute, view, key, relationship.  You have to change the length as per the target database standards. Select case as upper for table.

Under mapping tab, click import. Select the file “naming_standards.csv”.

In mapping tab, below the screen there are three tabs abbreviations, order, and general. Click general tab.

Since we will be placing underscore between each word of the column, type “_” in physical word separation under heading “delimiter options”.

Click save. Save the file as “enterprise_naming_standards.nst”

 

03_how_to_convert_.csv_file_to_.nst_file

 

How to add identifying, non-identifying, self-recursive relationship in ER Studio Data Architect?

Now, we are going to make changes to the previously created data model. So create another file LDM_creating_entity_version2. Open the previous data model “LDM_creating_entity_version1” and click menu file/save as “LDM_creating_entity_version2”.

How to add entity and attributes in ER Studio Data Architect:

Add the following attributes to “Employee” Entity. Place the cursor in attribute section of Employee Entity, right click edit entity. Click attributes tab and add the following attributes.

  • “Employee Name” with data type as Varchar(50); NOT NULL;
  • “Incentive Indicator” with datatype as Char(10);  NOT NULL;
  • “Phone Number” with datatype as Char(12); NULL;

You can use UP or DOWN arrow keys to align the attributes.

Create another entity Address with Address Identifier as attribute, datatype as Integer and Address identifier as primary key.

Address ID should be a sequence number.

How to create a sequence number in ERStudio Data Architect:

When you add an attribute, you can see a section “Identity Property”.  Click the check box in Identity column.

Type 1 for seed and 1 for increment. Seed means the starting number of the sequence and increment means how it has to be incremented.

Create another entity Employee-Address with no attributes.  We will explain how to add attributes below.

How to add non-identifying relationship in ER studio data architect?

Department entity to Employee entity:

Many employees can work in one department. One-to-many relationship. Hence we can create this relationship by Non-Identifying Relationship.

Click menu Insert/Relationship/Non-Identifying mandatory:

Click the cursor on Department Entity and then on Employee Entity.

Now you can see the non-identifying relationship between department entity and employee identity.

A new attribute Department Number is added to employee entity.

How to add identifying relationship in ER studio data architect?

Employee entity and Address entity: One employee can stay in many addresses.

One address can contain many employees.

Many to many relationship: Identifying relationship

Create address entity with address id as the primary key.

Click menu Insert/Relationship/Identifying

Click the cursor on employee entity and employee address entity. Again click on address entity and then on employee-address entity.

Now you can see the identifying relationship in employee address entity.

Two new attributes employee number, and address identifier are added as primary keys to employee-address entity. Since two keys are there, it is called as composite primary keys.

How to add self-recursive relationship in ER studio data architect?

To connect relationship between an employee and a manager: There is no attribute which references the employee number in the employee entity.  So we will create a new attribute Manager Number by using self-recursive relationship. When we want to create an attribute in an entity that references the same primary key attribute, we have to create role name, because, two attributes cannot have the same name in the same table.

In this example, the parent entity and child entity are same (employee entity).

Click menu Insert/Relationship/Non-Identifying mandatory

Click the cursor on employee entity twice. You can see a new screen. Type role name as manager number.

You can see a new attribute manager number in employee entity.

If you want to know more about the relationship, click each relationship line and you can see different options.

02_identifying_and_non_identifying relationship

 

1 2