Oracle Technology Network
 
 
   

Modifying a Logical Model Using Inheritance and Object Types

< Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial shows you how to modify a logical model using inheritance and object types with Oracle SQL Developer Data Modeler.

Time to Complete

Approximately 30 minutes

Overview

Oracle SQL Developer Data Modeler offers a full spectrum of data and database modeling tools and utilities, including Entity Relationship modeling, Relational (Schema), Data Types or Object Types modeling, and Multidimensional modeling and DDL generation. It includes importing from and exporting to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of Design Rules.

Oracle SQL Developer Data Modeler will be released as an extension to SQL Developer and as a standalone product, for those developers who only want to work with visual data modeling.

In this tutorial, you import a schema from the DB catalog, reverse engineer the relational model to create a logical model, create a subview and display, create two subtypes for an entity, forward engineer to a new relational, create a type and associate the new type with a table and then reverse engineer the modified table to synchronize the relational and logical models.

Prerequisites

Before starting this tutorial, you should:

  • Install Oracle SQL Developer Data Modeler from OTN here.
  • Install the Oracle Database 10g and later.
  • Unlock the HR user. Login to SQL*Plus as the SYS user and execute the following command:
    alter user hr identified by hr account unlock;

Note: This tutorial was created in a Linux environment.  If you are using a Windows environment, the screenshots may be slightly different.

Importing Schema From the Data Dictionary

In this section, you import the HR schema from the Data Dictionary to create a relational model. Perform the following steps:

.

Open Oracle SQL Developer Data Modeler. If you performed the previous tutorial, and did not exit Oracle SQL Developer Data Modeling, select File > Close to close the model you previously worked on.

 

.

Select File > Import > Data Dictionary.

 

.

You need to create a database connection. Click Add.

 

.

Enter the following and click Test Connection.

Connection Name: orcl_system
Username: system
Password: <your password>
Select Save Password check box
Hostname: localhost
SID: orcl

 

.

Your connection was successful. Click OK.

 

.

Click OK again.

 

.

Select your connection from the list and click Next.

 

.

Select the check box in front of the HR schema and click Next.

 

.

Under the Tables tab, click Select All.

 

.

Click Next.

Click Finish.

 

.

Review the log. Click Close when done.

 

.

The relational model is created successfully. In the next section, you reverse engineer to create the logical model.

 

Reverse Engineering a Relational Model to Logical Model

In this section, you reverse engineer the relational model to create a logical model. Perform the following steps:

.

Select the Engineer to Logical Model icon. An engineering window opens.

 

.

The warning icons indicate that objects are different between the relational and logical model. Expand the Tables object.


 

.

The plus sign icon indicates that the tables will be added to the logical model. Click Engineer.

 

.

The logical model is created successfully. In the next section, you create a subview that contains only the entities related to the employees table.

 

Creating a SubView

In this section, you create a subview for the logical model based on the entities related to the employees table. Perform the following steps:

.

Right-click the background of the diagram. There are 2 different notations you can use. In this tutorial, you select Bachman Notation. Note that the Barker notation is used by default.

 

.

The Bachman notation is now used.

 

.

Right-click the employees entity and select Select Neighbors.

 

.

Accept the default of 1 zone and click OK.

 

.

Notice that the entities related to the employees table are selected. Right-click the EMPLOYEES entity again and select Create SubView from selected.

 

.

A subview of just the entities selected is created.

 

Manipulating Different Displays

In this section, you create 2 different displays of the subview you just created. A display allows you to change the way the diagram looks based on the same set of Logical or Relational Model objects. Perform the following steps:

.

Right-click the Logical SubView_1 tab and select Create Display.

 

.

You can minimize the amount of detail in the diagram. Right-click the white space in the diagram (make sure your cursor is not on an object) and select View Details > Names Only.

 

.

Only the Entity Names are displayed.

 

.

Reduce the size of one of the entities by selecting the entity and dragging the corner so that the entity is a smaller size.

 

.

Shift-select each entity on the diagram. Select Edit > Equal Width.

 

.

Notice that all the entities have now the same width on the diagram. The width you obtain is based on the width of the first object you selected. Select Edit > Equal Height.

 

.

All the entities are now of equal width and height. Again, the height you obtain is based on the height of the first object you selected. You can also redraw the lines manually. Right-click the white space of the diagram and select Auto Route to deselect it.

 

.

Move the entities to maximize space and redraw the lines.

 

.

Notice that some lines are still not correctly drawn. To redraw a line, right click the line and select Straighten Line.

 

.

After you straighten your lines, you can also move them over or to another side of the entity by dragging the end you want to move .

 

.

If you want to move a line to another edge of the entity (shape) select the elbow and move this to the new edge.  To add or remove elbows, right click and select Add Elbow or Remove Elbow.

 

.

After the elbow is created, you drag the elbow to its desired location. You may need to create multiple elbows on one line.

 

.

The screenshot below is the result of moving entities and lines around to maximize space.

 

.

You can create another display that looks different. Right-click the Logical - SubView 1 and select Create Display.

 

.

Right-click the white space of the diagram and select Barker Notation.

 

.

Right-click the white space and select View Details > All Details.

 

.

The details are displayed for each entity using the Barker Notation. Make the entities bigger so you can see all the attributes and redraw the lines so that they are straight and minimize intersection.

 

.

In the Browser tree, expand Logical > SubViews > Logical - SubView_1 > Displays. Double-click Display_1.

 

.

Change the name of the display to Bachman - Names only and click OK.

 

.

Double-click Display_2. Change the name of the display to Barker - All Details and click OK.

 

.

Your display names have changed. In the next section, you create 2 subtypes for the EMPLOYEES entity.

 

Adding Two Subtypes to an Entity

In this section, you add 2 subtypes to the EMPLOYEES (supertype entity). Perform the following steps:

.

From the Barker - All Details Display, select the Create Entity icon.

 

.

Select anywhere in the white space of the diagram. Enter Sales for the Name and select EMPLOYEES for Super Type. Then select Attributes from the left navigator.

 

.

Select the Add Attribute icon.

 

.

Enter Commission Percent for the Name and click Logical for Datatype. Select NUMERIC from the Type drop list, enter 2 for Precision and 2 for Scale. Click OK to create the subtype.

 

.

Notice that there is a red line between the super type EMPLOYEES and the subtype you just created for Sales. You can represent this relationship as a Box in Box. Right-click the white space and select Box-in-Box Presentation.

 

.

Notice that the subtype Sales appears inside the supertype EMPLOYEES. You may need to enlarge the EMPLOYEES supertype entity and reduce the size of the SALES subtype entity.

 

.

You want to create a relation between the supertype EMPLOYEES and the subtype Sales to store the Account Manager. Select the 1:N relation icon.

 

.

First select the EMPLOYEES entity.

 

.

Then select the Sales subtype.

 

.

You can move the subtype and relation so that it is presented optimally. Now that you have created the relation between EMPLOYEES and Sales, you want to rename the relation to Account Manager. Double-click the Sales subtype.

 

.

Select Attributes in the left navigator.

 

.

Select the EMPLOYEE_ID attribute from the list. Notice that EMPLOYEE_ID is listed as a foreign key attribute. You can't change the name without changing a General Option setting first. Click Cancel.

 

.

Select Tools > General Options.

 

.

Expand Model and select Logical. Deselect the Keep as the name of the Originating attribute option and click OK.

 

.

Double-click the Sales subtype again.

 

.

Select Attributes from the left navigator.

 

.

Select the EMPLOYEE_ID attribute from the list. Change EMPLOYEE_ID to Account Manager and click OK.

 

.

You want to create one more subtype called Non-Sales. Select the New Entity icon and click in the white space on the diagram.

 

.

Enter Non-Sales for the Name, select EMPLOYEES for the Supertype and click Attributes in the left navigator.

 

.

Select the Add Attribute icon.

 

.

Enter Bonus Amount for the Name and select the Logical Datatype. Select NUMERIC from the Logical Type drop list and enter 10 for Precision and 2 for Scale and click OK.

 

.

Your Non-Sales subtype was created successfully. You can align both subtypes so that they appear optimally. Ctrl-select both subtypes: Sales and Non-Sales. Then select Edit > Align Left.

 

.

Your subtypes have been aligned correctly. In the next section, you forward engineer to a new relational model.

 

Forward Engineering to a New Relational Model

In this section, you forward engineer your logical SubView to a new relational model. Perform the following steps:

.

You first need to create a new relational model to forward engineer your subview into. In the left navigator, right-click Relational Models and select New Relational Model.

 

.

The relational model Relational_2 was created successfully.

 

.

To view the forward engineering strategy for the EMPLOYEES Supertype and its subtypes, select Logical-SubView_1 tab and double-click the EMPLOYEES super type entity.

 

.

Review the list under FWD Engineer Strategy. Notice that this field is currently set to Single Table which means that all the attributes from supertype and both subtypes will engineer to one table in the relational model. Leave this setting.

 

.

Click Attributes in the left navigator.

 

.

Select the COMMISSION_PCT attribute and click the Remove icon.

 

.

Click OK.

 

.

Now you are ready to create the relational model. Select the Engineer to Relational Model icon.

 

.

On the left drop down, select Logical - SubView_1. On the right drop down, select Relational_2. You can expand the tree on either side to view how the model will engineer and what objects will be impacted. When done, click Engineer.

 

.

Your SubView of the Logical model was engineered and the results are displayed. Notice that the attributes from the subtype entities appear in the EMPLOYEES table in the relational model. You want to change the names of the columns that were added. Double-click the EMPLOYEES table.

 

.

Click Columns in the left navigator.

 

.

Change the names as follows and click OK.

Attribute Column
Commission Percent COMMISSION_PCT
Account Manager ACCOUNT_MGR
Bonus Amount BONUS_AMT

 

.

Your changes were made successfully. In the next section, you create a Type for Employee Address.

 

Adding a New Type

In this section, you create a new Type for emp_address_typ. Perform the following steps:

.

You want to show the data type model. In the left navigator, right-click DataTypes and select Show.

 

.

Select the New Structured Type icon and click the white space on the diagram.

 

.

Enter emp_address_typ for the Name and click Attributes in the left navigator.

 

.

Click the Add icon.

 

.

Enter street for Name and select Logical for Datatype. Select VARCHAR for Type, enter 40 for Size. You want to create another attribute. Click the Add icon.

 

.

Enter city for Name and select Logical for Datatype. Select VARCHAR for Type, enter 30 for Size. You want to create 2 more attributes. Click the Add icon.

 

.

Create 2 more attributes using the previous steps, then click OK.

Attribute Datatype
state CHAR(2)
postal_code VARCHAR(10)

 

.

Your datatype is displayed. You want to assign this datatype to a new column in the EMPLOYEES table. Click the Relational_2 tab.

 

.

Double-click the EMPLOYEES table.

 

.

Select Columns in the left navigator.

 

.

Click the Add column icon.

 

.

Enter Address for the Name. Click the Structured datatype select emp_addtress_typ from the drop-down list.

 

.

Click OK.

 

.

Your table has been updated with a new column that uses the emp_address_typ. In the next section, you reverse engineer (engineer to logical model) the Address column to the super type entity EMPLOYEES.

 

Synchronizing Relational and Logical Models

In this section, you engineer the column you added in the EMPLOYEES table to the super type entity in the Logical Model. Perform the following steps:

.

With the Relational_2 tab selected, click the Engineer to Logical Model icon.

 

.

Under Relational_2, expand Tables mapped to Hierarchies. Expand the second EMPLOYEES node and its columns in the list. This node corresponds to the Sales subtype in the Logical Model. You do not want the Address column added to the Sales subtype. Deselect the check box in front of Address.

 

.

Expand the EMPLOYEES node that corresponds with the Non-Sales entity and its columns in the Logical model. You do not want Address to be added to the Non-Sales subtype. Deselect the check box in from of Address.

 

.

Scroll up the list and expand the first EMPLOYEES node to see that the Address column will be added to the super type. Make sure the the check box in front of Address is checked. Then click Engineer.

 

.

The Logical Model is displayed. Click the Logical - SubView_1 tab.

 

.

Notice that the Address column was added to the EMPLOYEES super type. It was not added to the Sales or Non-Sales subtypes.

 

Summary

In this tutorial, you have learned how to:

  • Import a schema from the DB catalog
  • Reverse engineer a relational model to a logical model
  • Create a SubView
  • Add two subtypes to an entity
  • Forward engineer to a new relational model
  • Add a new (structures) data type
  • Synchronize your relational and logical models

 
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights
 
Left Curve
Popular Downloads
Right Curve