Forward and Reverse Engineering Models and Working with Logical Model Diagrams, Displays and Subviews (2024)

Open Oracle SQL Developer Data Modeler from the icon on your desktop. 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: <your_sid>_system
Username: system
Password: <your password>
Select Save Password check box
Hostname: <your_hostname>
SID:<your_sid>

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 the Select All icon.

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.

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.

Right-click the background of the diagram. There are 3 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.

Right-click the white space on the subview 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.

There may be some lines you want to manipulate manually instead of them being redrawn automatically. To manually adjust the lines, you need to turn Auto Route off. Right-click the white space in the diagram and select Auto Route.

If you want to move a line to another edge of the entity, select the line. Select the square in the middle of the line. This is called an Elbow.

Drag the Elbow to the desired location and release

In this example, you also want to drag the other elbow to another location.

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.

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 the SALES subtype entity sits inside the the super type EMPLOYEES entity box. The default is the Box-in-Box Presentation.

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.

A new relation will be created between the EMPLOYEES Supertype and Sales Subtype. Click OK.

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 preference setting first. Click Cancel.

Select Tools > Preferences.

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 can resize and move the subtype so that they align correctly in the EMPLOYEES subtype box. In the next section, you forward engineer to a new relational model.

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.

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

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.

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.

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. Make sure the check box in front of Address is not checked.

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. Make sure the check box in front of Address is not checked.

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.