Data Modeling

2008-04-12T00:24:00.000-07:00

How to create a Logical Data Model:

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.

2008-04-12T00:06:00.000-07:00

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.

Software:

» How to install the data modeling tool on server/client?

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?

2008-03-05T03:51:00.000-08:00

How to create a Logical Data Model:

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.

2008-03-05T03:40:00.001-08:00

Erwin Tutorial

All Fusion Erwin Data Modeler commonly known as Erwin, is a powerful and leading data modeling tool from Computer Associates. Computer Associates delivers several softwares 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.

2008-02-08T09:26:00.002-08:00

>>Previous>>

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?

>>>Next>>>

2008-02-08T09:23:00.000-08:00

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.

Software:
» How to install the data modeling tool on server/client?

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?

2008-02-08T08:51:00.000-08:00

>>Previous>>

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 Name	Company Name
Erwin	Computer Associates
Embarcadero	Embarcadero Technologies
Rational Rose	IBM Corporation
Power Designer	Sybase Corporation
Oracle Designer	Oracle Corporation
Xcase	RESolution LTD.

>>>Privious>>>

Data Modeling Overview

2007-12-30T13:22:00.000-08:00

Data Modeling Overview

A Data model is a conceptual representation of data structures(tables) required for a database and is very powerful in expressing and communicating the business requirements.

A data model visually represents the nature of data, business rules governing the data, and how it will be organized in the database. A data model is comprised of two parts logical design and physical design.

Data model helps functional and technical team in designing the database. Functional team normally refers to one or more Business Analysts, Business Managers, Smart Management Experts, End Users etc., and Technical teams refers to one or more programmers, DBAs etc. Data modelers are responsible for designing the data model and they communicate with functional team to get the business requirements and technical teams to implement the database.

The concept of data modeling can be better understood if we compare the development cycle of a data model to the construction of a house.

For example Company ABC is planning to build a guest house(database) and it calls the building architect(data modeler) and projects its building requirements (business requirements). Building architect(data modeler) develops the plan (data model) and gives it to company ABC. Finally company ABC calls civil engineers(DBA) to construct the guest house(database).

Need for developing a Data Model:

A new application for OLTP(Online Transaction Processing), ODS(Operational Data Store), data warehouse and data marts.

Rewriting data models from existing systems that may need to change reports.

Incorrect data modeling in the existing systems.

A data base that has no data models.

Advantages and Importance of Data Model

The goal of a data model is to make sure that all data objects provided by the functional team are completely and accurately represented.

Data model is detailed enough to be used by the technical team for building the physical database.

The information contained in the data model will be used to define the significance of business, relational tables, primary and foreign keys, stored procedures, and triggers.

Data Model can be used to communicate the business within and across businesses.