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.

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.

learndmdwbi

Next Create Data Modeling Objects in Toad »

Previous « Creating a Logical Data Model using Erwin

View Comments

Sateesh says:

August 20, 2017 at 8:08 pm

Hi,

From a data modelling perspective, If I have to pull out columns specific to a table customer (say from involved party entity in banking data warehousing) where do I choose to edit the columns that are required for the customer table? The involved party entity has columns for all the tables like suppliers, employees, customers etc !