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

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?

• Oracle SQL Developer Data Modeler Release 4.1 or above

• Access to a database in Oracle Database 12c with the sample schema installed

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

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.

   View Image

   

3. The Data Dictionary Import Wizard opens. Click Add to create a connection to the database.

   View 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>

   View 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.

   View Image

   

6. In the Data Dictionary Import Wizard, select the hr_orclpdb connection you just created and click Next.

   View Image

   

7. You want to import only the HR schema. Select only the HR schema and click Next.

   View Image

   

8. Select the DEPARTMENTS and EMPLOYEES tables and click Next.

   View Image

   

9. Click Finish to generate the design.

   View Image

   

10. Review the log file created and click Close.

    View 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.

    View Image

    

12. The table and relations are repositioned.

    View Image

    

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.

View Image



2. 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.

   View Image

   

3. Review the list. Note the other objects available in the Physical Model.

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..

   View Image

   

2. A Table Properties dialog opens. Enter PROJECTS for Name and click Columns on the left-navigator.

   View Image

   

3. Click the Create Column icon to add a column.

   View 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>

   View 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

   View Image

   

8. The table is created successfully. Reposition the table, if required.

   View Image

   

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.

   View Image

   

2. To create a foreign key in the PROJECTS table, click the DEPARTMENTS table and then click the PROJECTS table.

   View 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.

   View 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.

   View Image

   

5. To make the Foreign Key optional, right-click the line joining the two tables and select Properties.

   View Image

   

6. Deselect both the Mandatory checkbox and click OK.

   View Image

   

7. Click Yes in the dialog box to disable a mandatory Foreign Key.

   View Image

   

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

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

   View Image

   

4. The COST_CENTER column is successfully created in the DEPARTMENTS table.

   View Image

   

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

   View Image

   

2. The Engineer to Logical Model window appears. Expand Tables and click Engineer.

   View 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).

   View Image

   

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.

   View 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.

   View 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.

   View 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.

   View 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.

   View 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.

    View 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.

    View 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.

    View 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.

    View Image

    

14. In the General tab of the Entity Properties window, enter TASKS for Name and click the Attributes property in the left navigation.

    View 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

    View 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.

    View Image

    

21. Click the EMPLOYEES entity and then the TASKS entity.

    View Image

    

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

    View 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.

    View 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.

    View Image

    

28. Verify that the changes to the logical model are complete.

    View Image

    

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

   View Image

   

2. The Engineer to Relational Model window appear. Expand Entities, PROJECTS, and then Attributes. Note the differences and click Engineer.

   View 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.

   View Image

   

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.

   View Image

   

2. The Data Dictionary Connections dialog opens. Select the Redirect Connection from the select list and click OK.

   View 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.

   View Image

   

4. Expand DEPARTMENTS and then Columns. The COST_CENTER column will be added. Click DDL Preview.

   View 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.

   View 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.

   View 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.

   View 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.

• Oracle SQL Developer Data Modeler on OTN

• Oracle Data Modeling and Relational Database Design course

• Oracle Learning Library