Tag: Logical and Physical Data Model using Toad

How to create Data Modeling Objects in Toad?

Here in this section, we will create a physical data model instead of starting from logical data model, since we can cover most of the features of a data modeling tool in the physical data model. Please refer our sections listed under Data Modeling Topic Logical Data Modeling and Data Modeling Tool – Erwin for creating a logical data model.

Table Structures:

Employee

Title

Department

Project

Employee_Project

Employee_No (PK)

Title_Code (PK)

Department_No (PK)

Project_Code (PK)

Employee_No (FPK)

Employee_Name

Title_Description

Department_Name

Software_Used

Project_Code (FPK)

Manager_No (FK)

Title_Code (FK)

Department_No (FK)

Working with Toad:

Open Toad Data Modeler Software

Click Menu File → New → Model

A new window will appear which asks you to select the database on which you will be generating the physical model

Since we are doing it in Oracle, select Oracle 11g release 2. Click OK.

A new screen appears. On the left hands side top, you can see the database name (Oracle 11g release 2). Below that you can see Physical Model Explorer, which contain different objects. Physical Model looks similar to a windows explorer.

On the middle and right hand side, you can see work-space (work area), where we will be creating objects.

On the tool bar, you can see a list box which displays Captions (Logical Data Model, Names (Physical Data Model) and Full Names. Select Names.

How to create table ‘DEPARTMENT’?

Click entity icon displayed on the tool bar and drop it on the work area. A new entity is created. By default some name is provided by the tool.

Highlight the entity, right click and click edit from the sub menu.

A new window, which contains different tabs, is opened.

Under General Tab, change the name and captions to “DEPARTMENT”.

Click Apply and OKAY. Now in the work area, you can see the DEPARTMENT table.

NOTE: Give proper names to TABLES, COLUMNS, PRIMARY KEYS, FOREIGN KEYS, INDEXES etc. and all other database objects as per the standard maintained by the data modeling team and database team.

How to create columns, primary keys, data type to table?

1. DEPARTMENT Table:

Column Name

Constraint Name

DataType

Department_No

Primary Key

Integer

Department_Name

NOT NULL

VARCHAR2 (30)

Highlight table DEPARTMENT, right click edit from the sub menu.

Click Tab ‘Attributes’. Click Add. By default, an attribute is created. Click edit and from the new window

Change name and caption to ‘Department_No’.

Change the data type to Integer.

Department_No is the primary key of the table. So double click the space under the key column and you can see the key symbol. Click apply and OK. Go to the keys tab and change the primary key name to “Department_PK’.

On the work area, you can see table DEPARTMENT with a column “Department_No” as the primary key.

Similarly create column ‘Department_Name” with data type as VARCHAR2 (30). It should not be the primary key and it should be a column in the table. When you create, you can see a check box under NOT NULL. Check that check box so that this column will be NOT NULL. Assign data type as VARCHAR2 (30).

Similarly create tables TITLE and PROJECT and add columns to those tables.

2. TITLE Table:

Column Name

Constraint Name

DataType

Title_Code (PK)

Primary Key

Integer

Title_Description

NOT NULL

VARCHAR2 (30)

3. PROJECT Table:

Column Name

Constraint Name

DataType

Project_Code (PK)

Primary Key

Integer

Software_Used

NOT NULL

VARCHAR2 (30)

Similarly create table EMPLOYEE with Employee_No as the primary key and Employee_Name as the column. Later we will add other columns.