Re-engineering Your Database Using Oracle SQL Developer Data Modeler 4.1


Options



Before You Begin

Purpose

This tutorial covers how to make changes to your database using the relational model in Oracle SQL Developer Data Modeler Release 4.1.

Time to Complete

Approximately 60 minutes

Background

Oracle SQL Developer Data Modeler is a tool that enables you to diagrammatically display your database objects, make changes, and update the schema based on the changes quickly and easily. It is a standalone, independent product available for download and free use.

You can import from and export to a variety of sources and targets. You also have a variety of formatting options and can validate the models through a predefined set of Design Rules. By using this tool, you can connect to any Oracle supported database.

Scenario

In this tutorial, you use Oracle SQL Developer Data Modeler Release 4.1 to perform the following:

  • Import two tables (EMPLOYEES and DEPARTMENTS) from the HR schema into SQL Developer Data Modeler.

  • Add a new table (PROJECTS), join it to the DEPARTMENTS table and create a new column (COST_CENTER) in the DEPARTMENTS table.

  • Reverse engineer the relational model to a logical model.

  • Modify the model by creating a new Entity with Attributes and relations.

  • Synchronize the logical with the Relational Model.

  • Generate the DLL with the changes you made.

What Do You Need?

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

Importing Tables from HR Schema

In this topic, you create a connection to the HR schema. You then use this connection to import the EMPLOYEES and DEPARTMENTS tables into SQL Developer Data Modeler.

  1. Open SQL Developer Data Modeler.

  2. You now import the tables from the data dictionary. From the File menu, choose Data Modeler, Import, and then Data Dictionary.

    Oracle SQL Developer Data Modeler - Import menu
    Description of this image
  3. The Data Dictionary Import Wizard opens. Click Add to create a connection to the database.

    Data Dictionary Import Wizard
    Description of this image
  4. A New / Select Database Connection dialog opens. Enter the connection details and click Test.

    • Connection Name: hr_orclpdb

    • Username: hr

    • Password: <your_password>

    • Hostname: localhost

    • SID / Service name: <your_own_SID> / <your_own_servicename>

    New / Select Database Connection dialog
    Description of this image

    Note: In this tutorial, the Service Name is specified instead of SID.

  5. If all the details are entered correctly, the status is Success. Click Connect.

    New / Select Database Connection dialog - Status: Success
    Description of this image
  6. In the Data Dictionary Import Wizard, select the hr_orclpdb connection you just created and click Next.

    Data Dictionary Import Wizard - Step 1: Connect to Database
    Description of this image
  7. You want to import only the HR schema. Select only the HR schema and click Next.

    Data Dictionary Import Wizard - Step 2: Select Schema/Database
    Description of this image
  8. Select the DEPARTMENTS and EMPLOYEES tables and click Next.

    Data Dictionary Import Wizard - Step 3: Select Objects to Import
    Description of this image
  9. Click Finish to generate the design.

    Data Dictionary Import Wizard - Step 4: Generate Design
    Description of this image
  10. Review the log file created and click Close.

    View Log window
    Description of this image
  11. The EMPLOYEES and DEPARTMENTS tables are successfully imported into a relational model within SQL Developer Data Modeler. If the tables do not line up correctly, drag the object and realign accordingly.

    hr_orclpdb relational model with HR.EMPLOYEES and HR.DEPARTMENTS tables (objects misaligned)
    Description of this image
  12. The table and relations are repositioned.

    hr_orclpdb relational model with HR.EMPLOYEES and HR.DEPARTMENTS tables (objects repositioned)
    Description of this image

Reviewing the Physical Model

In this topic, you review the physical model that is automatically created when you import the objects from the data dictionary.

  1. From the View menu, choose Data Modeler and then Browser.

  2. SQL Developer Data Miner View menu
    Description of this image
  3. The Browser window gets docked to SQL Developer under Reports. In the Browser, expand Relational Models followed by hr_orclpdb, Physical Models, Oracle Database 12c, Tables, HR.DEPARTMENTS, and Columns in the order specified here.

    Browser
    Description of this image
  4. Review the list. Note the other objects available in the Physical Model.

Creating a New Table

In this topic, you create a PROJECTS table. The PROJECTS table has four columns: PROJECT_ID, PROJECT_NAME, PROJECT_START_DATE, and PROJECT_END_DATE.

  1. Click the New Table icon from the Oracle SQL Developer Data Modeler toolbar and click the white space of the diagram..

    New Table icon in SQL Developer Data Modeler toolbar
    Description of this image
  2. A Table Properties dialog opens. Enter PROJECTS for Name and click Columns on the left-navigator.

    Table Properties dialog - General tab
    Description of this image
  3. Click the Create Column icon to add a column.

    Table Properties dialog - Create Column icon
    Description of this image
  4. In the Column Properties section, enter the following values and click Create Column.

    • Name: PROJECT_ID

    • Datatype: Logical

    • Source Type: NUMERIC

    • Precision: 6

    • PK: <enabled>

    Table Properties dialog - Create Column: PROJECT_ID
    Description of this image
  5. Add another column. In the Column Properties section, enter the following values and click Create Column.

    • Name: PROJECT_NAME

    • Datatype: Logical

    • Source Type: VARCHAR

    • Size: 100

  6. Add another column. In the Column Properties section, enter the following values and click Create Column.

    • Name: PROJECT_START_DATE

    • Datatype: Logical

    • Source Type: Date

  7. Add the last column. In the Column Properties section, enter the following values and click OK to create the table.

    • Name: PROJECT_END_DATE

    • Datatype: Logical

    • Source Type: Date

    Table Properties dialog - Create Column: PROJECT_END_DATE
    Description of this image
  8. The table is created successfully. Reposition the table, if required.

    hr_orclpdb relational model with HR.EMPLOYEES, HR.DEPARTMENTS, and PROJECTS tables
    Description of this image

Joining the New Table to the Existing Table

In this topic, you create a foreign key between the PROJECTS table and the DEPARTMENTS tables.

  1. Click the New Foreign Key icon from the Oracle SQL Developer Data Modeler toolbar.

    New Foreign Key icon in Oracle SQL Developer Data Modeler toolbar
    Description of this image
  2. To create a foreign key in the PROJECTS table, click the DEPARTMENTS table and then click the PROJECTS table.

    Creating a foreign key between DEPARTMENTS and PROJECTS table
    Description of this image
  3. A Foreign Key Properties dialog opens. In the Associated Columns section, confirm that DEPARTMENTS_DEPARTMENT_ID is selected for Column and click OK.

    Foreign Key Properties - PROJECTS_DEPARTMENTS_FK: General tab
    Description of this image
  4. The Foreign Key Relation is created. The 'F' against DEPARTMENTS_DEPARTMENT_ID in the PROJECTS table identifies the column as a foreign key.

    Foreign Key Relation between DEPARTMENTS and PROJECTS table
    Description of this image
  5. To make the Foreign Key optional, right-click the line joining the two tables and select Properties.

    Right-clicking the line joining DEPARTMENTS and PROJECTS table
    Description of this image
  6. Deselect both the Mandatory checkbox and click OK.

    Foreign Key Properties - PROJECTS_DEPARTMENTS_FK
    Description of this image
  7. Click Yes in the dialog box to disable a mandatory Foreign Key.

    Mandatory FK columns confirmation dialog
    Description of this image

    The Foreign Key is now optional, which means that you can now have a Project without a Department.

Modifying an Existing Table

In this topic, you add the COST_CENTER column to the DEPARTMENTS table.

  1. In the diagram, double-click the DEPARTMENTS table.

  2. Select the Columns property from the left navigator and click the Create Column icon.

  3. In the Column Properties section, enter the following values and click OK to create the column.

    • Name: COST_CENTER

    • Datatype: Logical

    • Source Type: VARCHAR

    • Size: 25

    Table Properties - DEPARTMENTS: Columns tab
    Description of this image
  4. The COST_CENTER column is successfully created in the DEPARTMENTS table.

    The DEPARTMENTS table shows the COST_CENTER column
    Description of this image

Reverse Engineering the Relational Model to Create a Logical Model

  1. With the relational model diagram window open, select the Engineer to Logical Model icon in the toolbar.

    Engineer to Logical Model icon in the Oracle SQL Developer Data Modeler toolbar
    Description of this image
  2. The Engineer to Logical Model window appears. Expand Tables and click Engineer.

    Engineer to Logical Model window
    Description of this image
  3. The Logical Model is displayed. Review the entities and attributes. The diagram for the Logical Model is called the Entity Relationship Diagram (ERD).

    Entity Relationship Diagram of EMPLOYEES, DEPARTMENTS, and PROJECTS table
    Description of this image

Modifying the Logical Model

  1. You now add some attributes to the PROJECTS entity. Double-click the PROJECTS entity in the ERD.

  2. Select the Attributes property in the left navigator.

  3. Click the Add Attributes icon.

    Add Attributes icon in Entity Properties window
    Description of this image
  4. In the Attribute Properties section, enter the following values and select the DEPARTMENT_DEPARTMENTS_ID attribute from the list.

    • Name: STATUS

    • Datatype: Logical

    • Source Type: VARCHAR

    • Size: 30

  5. The DEPARTMENT_DEPARTMENTS_ID attribute is shown as a result of the relation between the DEPARTMENTS and PROJECTS entities. Notice that you can not change the name of the attribute. You do want to change the name so you change one of the preference settings and click OK.

    Entity Properties - PROJECTS: Attributes tab
    Description of this image
  6. Notice that the STATUS attribute was added to the PROJECTS entity and that the DEPARTMENT relation is not displayed on the diagram. Currently you are viewing the Barker notation which does not show the relation attributes. Later in this section you change the notation to the Bachman notation which does display the relation attributes in the diagram.

    Updated Entity Relationship Diagram
    Description of this image
  7. You want to change the default so that you can change the name of the relation attribute. Select Tools from the toolbar and choose Preferences.

    Tools menu in SQL Developer Data Modeler
    Description of this image
  8. In the Preferences window, expand Data Modeler, Model and choose Logical. Deselect the checkbox for Name - Keep as the name of the Originating attribute and click OK.

    Preferences window - Logical tab
    Description of this image
  9. Double-click the PROJECTS entity again and ensure that the Attributes property is selected in the Entity Properties window.

  10. Select the DEPARTMENT_DEPARTMENT_ID attribute from the list.

    Notice that the Name field is now enabled so you can change its name. Change the name to PROJECT_DEPT and click OK.

    Entity Properties - PROJECTS: Attributes tab
    Description of this image
  11. Change the notation to the Bachman notation. Right-click the white space in the diagram to show the context window and select Bachman Notation.

    ERD diagram: Changing to Bachman Notation
    Description of this image
  12. Notice the notation has changed. You now see the relation attributes (noted with the 'F') as well as the Primary Key attributes. The '*' before each attribute indicates that it is a mandatory attribute.

    ERD diagram: Bachman Notation
    Description of this image
  13. Now you add another entity to the Logical Model. Select the New Entity icon in the toolbar and click the white space in the Logical diagram.

    New Entity icon in the SQL Developer Data Modeler toolbar
    Description of this image
  14. In the General tab of the Entity Properties window, enter TASKS for Name and click the Attributes property in the left navigation.

    Entity Properties window: General tab
    Description of this image
  15. Click the Add Attribute icon.

  16. In the Attribute Properties section, enter the following values and click the Add Attribute icon to add another attribute.

    • Name: TASK_ID

    • Datatype: Logical

    • Source Type: NUMERIC

    • Precision: 6

    • Primary UID: <enabled>

  17. Again, in the Attribute Properties section, enter the following values and click the Add Attribute icon to add another attribute.

    • Name: TASK_NAME

    • Datatype: Logical

    • Source Type: VARCHAR

    • Size: 255

  18. Again, in the Attribute Properties section, enter the following values and click the Add Attribute icon to add another attribute.

    • Name: COST

    • Datatype: Logical

    • Source Type: NUMERIC

    • Precision: 6

  19. Add the last attribute. In the Attribute Properties section, enter the following values and click OK to create the entity and its attributes.

    • Name: BUDGET

    • Datatype: Logical

    • Source Type: NUMERIC

    • Precision: 6

    Entity Properties window: Add Attributes
    Description of this image
  20. Reposition the table in the Logical Diagram, if required. You now create relations between the EMPLOYEES and TASKS, and the PROJECTS and TASKS entities. Select the New 1:N Relation icon in the toolbar.

    New 1:N Relation icon is the SQL Developer Data Modeler toolbar
    Description of this image
  21. Click the EMPLOYEES entity and then the TASKS entity.

    Creating relation between the EMPLOYEES and TASKS entities
    Description of this image
  22. The Relation Properties window opens. Click OK to create the relation.

    Relation Properties window
    Description of this image
  23. Now you create the second relation. Click the PROJECTS entity and then the TASKS entity.

  24. The Relation Properties window opens. Click OK to create the relation.

  25. The relation is displayed. You now change the name of the relation in the TASKS entity. Double-click the TASKS entity.

    ERD Diagram showing EMPLOYEES, DEPARTMENTS, TASKS, and PROJECTS table along with their relation
    Description of this image
  26. In the Entity Properties window, select the Attributes property from the left navigation. Then, choose the EMPLOYEES_EMPLOYEE_ID attribute from the list.

    In the Attribute Properties, change the Name of the EMPLOYEES_EMPLOYEE_ID attribute to ASSIGNED_TO. Then, click the PROJECTS_PROJECT_ID attribute from the list.

  27. Change the name of the PROJECTS_PROJECT_ID to PROJECT_ID and click OK.

    Entity Properties - Tasks window
    Description of this image
  28. Verify that the changes to the logical model are complete.

    Logical Model diagram with EMPLOYEES, DEPARTMENTS, TASKS, and PROJECTS tables
    Description of this image

Synchronizing the Logical Model with the Relational Model

  1. With the logical model diagram window open, select the Engineer to Relational Model icon in the toolbar.

    Engineer to Relational Model icon in the SQL Developer Data Modeler toolbar
    Description of this image
  2. The Engineer to Relational Model window appear. Expand Entities, PROJECTS, and then Attributes. Note the differences and click Engineer.

    Engineer to Relational Model window
    Description of this image
  3. In the Relational Model, note that the new STATUS column, the name change PROJECT_DEPT for the foreign key, and the new TASKS table.

    Updated Relational Model diagram
    Description of this image

Generating the DDL to Update the Database

Now that your models are complete, you synchronize the data dictionary with the model and generate the Data Definition Language (DDL) statements.

  1. Select the Synchronize Data Dictionary with Model icon in the toolbar.

    Synchronize Data Dictionary with Model icon in the SQL Developer Data Modeler toolbar
    Description of this image
  2. The Data Dictionary Connections dialog opens. Select the Redirect Connection from the select list and click OK.

    Data Dictionary Connections dialog
    Description of this image
  3. The design is generated and the Compare Models dialog opens. Nodes where changes have been made are marked with a triangle icon. In this case, the tables node is marked. Expand the Tables node to see the new tables for PROJECTS and TASKS. Notice that the DEPARTMENTS table has changed.

    Compare Models dialog
    Description of this image
  4. Expand DEPARTMENTS and then Columns. The COST_CENTER column will be added. Click DDL Preview.

    Compare Models dialog
    Description of this image
  5. The code generated is displayed. Review the CREATE and ALTER statements. Note that the PROJECTS and TASKS tables will be created and altered to create the Primary Keys. Scroll down to the bottom of the page.

    DDL statements generated by Oracle SQL Developer Data Modeler
    Description of this image
  6. On this page, you see the new column, foreign key constraints. Click Save.

    Note you can execute this script in SQL Developer to update the database.

    DDL statements generated by Oracle SQL Developer Data Modeler
    Description of this image
  7. In the Save DDL Locally window, specify the location where you want to save the DDL statements file, enter the File Name, and click Save.

  8. Click Close to close the Compare Models window.

  9. To save the model, click File, Data Modeler, and choose Save.

    File menu in SQL Developer Data Modeler
    Description of this image
  10. In the Save Design window, specify the location where you want to save the design file, enter the File Name, and click Save.

  11. Close Oracle SQL Developer Data Modeler.

Want to Learn More?