This tutorial covers how to make changes to your database using the relational model in Oracle SQL developer Data Modeler Release 3.1.
Time to Complete
Approximately 60 minutes
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 from OTN.
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. Using this tool, you can connect to any Oracle supported database.
In this tutorial, you use Oracle SQL Developer Data Modeler Release 3.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 (MOBILE_NUMBER) in the EMPLOYEES 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
Before starting this tutorial, you should:
- Have installed Oracle SQL Developer Data Modeler Release 3.1
- Have access to an Oracle Database 11g database that has the sample schema installed.
Importing Tables from HR Schema
In this topic, you will create a connection to the HR schema. Using this connection, you import the EMPLOYEES and DEPARTMENTS tables into SQL Developer Data Modeler:
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.
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.
Joining the New Table to the Existing Table
In this topic, you create a foreign key between the PROJECTS table and the DEPARTMENTS tables.
Modifying an Existing Table
In this topic, you add the COST_CENTER column to the DEPARTMENTS table.
Reverse Engineering the Relational Model to Create a Logical Model
In this topic, you reverse engineer the relational model to create a logical model.
Modifying the Logical Model
In this topic, you modify the logical model.
Synchronizing the Logical Model with the Relational Model
In this topic, you synchronize the logical model with the relational model.
Generating the DDL to Update the Database
Now that your models are complete you want to synchronize the data dictionary with the model and generate the DDL.
In this tutorial, you have learned how to:
- Import tables from data dictionary
- Review the physical model
- Create new table and join to an existing table
- Create new column in an existing table
- Reverse engineer to create the logical model
- Modify the logical model
- Synchronize the logical model with the relational model
- Generate DDL to update database
To help navigate this Oracle by Example, note the following:
- Hiding Header Buttons:
- Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
- Topic List Button:
- A list of all the topics. Click one of the topics to navigate to that section.
- Expand/Collapse All Topics:
- To show/hide all the detail for all the sections. By default, all topics are collapsed
- Show/Hide All Images:
- To show/hide all the screenshots. By default, all images are displayed.
- To print the content. The content currently displayed or hidden will be printed.
To navigate to a particular section in this tutorial, select the topic from the list.