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) Employee_Name Manager_No (FK) Title_Code (FK) Department_No (FK) | Title_Code (PK) Title_Description | Department_No (PK) Department_Name | Project_Code (PK) Software_Used | Employee_No (FPK) Project_Code (FPK) |
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 | Data Type |
|---|---|---|
| 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 | Data Type |
|---|---|---|
| Title_Code (PK) | Primary Key | Integer |
| Title_Description | NOT NULL | VARCHAR2 (30) |
3. PROJECT Table:
| Column Name | Constraint Name | Data Type |
|---|---|---|
| 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.
4. EMPLOYEE Table:
| Column Name | Constraint Name | Data Type |
|---|---|---|
| Employee_No (PK) | Primary Key | Integer |
| Employee_Name | NOT NULL | VARCHAR2 (30) |