Data Modeler / ERD

Dev4Snow

Go back to Contents – Go back to previous page

Data Modeler / ERD Tab

Uses of the Data Modeler / ERD tab:

Use the Data Modeler to reverse-engineer an existing schema into an Entity-Relationship Diagram like the one below. You can drag and drop a schema or table from the Tree into the diagram. If a schema is dropped into the diagram all of its tables will be imported into the diagram, and the existing DB relationships will be used to build the diagram relationships. If a table is dropped into the diagram, the tables that are referenced by it –using DB constraints- will be imported into the diagram as well.
Create a new model without coding DDL. Create tables and their relationships graphically.
Modify existing objects. Import the objects into the diagram and modify them without coding DDL. Add new columns, change data type lengths for existing columns, rename existing columns, rename tables, change Primary Key and or Foreign Keys. Add comments to columns.

Full reverse-engineering of a database schema:

The Data Modeler does not affect the database objects directly, but rather it produces a script that you can run to persist the changes in the database. Until that script runs the objects are only modified at diagram-level –you will notice that new or modified objects are shown in blue, while objects in black are not modified from the original.

The diagram can have objects that exist at database level but that suffered some modification on the diagram. For example this one:

RENAMED_COLUMN: It was renamed in the diagram –it still has the original name at database level.

NEW_COLUMN: It’s a new column in the diagram.

None of these changes were applied to the database. But can click on the DDL icon to get the DDL to produce these changes:

ALTER TABLE SAMPLE_DB.SAMPLE_SCHEMA.LOC6
ADD COLUMN NEW_COLUMN VARCHAR(100);

ALTER TABLE SAMPLE_DB.SAMPLE_SCHEMA.LOC6
RENAME COLUMN LOC_NAME TO RENAMED_COLUMN;

Icons & actions:

Clear / Create a new model / ERD: Clears the diagram to start a new data model / ERD.

Open an existing model / ERD: Opens a data model / ERD previously saved by the Data Modeler / ERD.

Save the ERD: Saves the diagram in a proprietary format.

Generate PDF for Diagram: Generates a PDF document for the diagram.

Copy diagram to Clipboard: Copies the diagram to Clipboard so it can be pasted in another application –like Word.

Show generated DDL script: Shows the DDL script generated from the diagram.

Move DDL script to a SQL Code Window: Creates a new SQL Code Window and moves the DDL script generated from the diagram to it.

(Re)Apply Spring layout: Applies a Spring Layout to the diagram. You can click it multiple times to randomize the Spring Layout until you get the best visualization.

Apply Orthogonal layout: Applies an Orthogonal Layout to the diagram.

Toggle Link descriptions: Turns on and off the link descriptions for entity relationships.

Create table: Opens a new window where you can define the details of a new table. Use the <TAB> key to move from one field to the other for faster data input. Use the arrows on the side to change the order of table columns. You can also open this window by right-clicking on an empty spot of the diagram > Create table:

Scan Data Model: Opens a new window where you can scan a Data Model. Please refer to Scan Data Model for further details.

Remove ERD objects: When importing a full schema you may have found that there are objects that are not connected to other objects so you want to remove them from the diagram. Use this option for an easy removal of those objects. When using this option a new window will open showing all objects with the number of references to others objects. You can use the “Toggle objects with no references” button to select/deselect all of those objects with no references, or alternatively select/deselect them manually and click “Done”. You will be asked to confirm if you want to remove the selected objects –removal is ONLY from the diagram. The Data Modeler will not perform any direct action to the database objects.

Zoom-In & Zoom-Out for Diagram: Zooms in and zooms out the diagram.

Refresh definition for Diagram objects: Refreshes the definition –columns and data types- for objects in the diagram. Particularly useful if you saved the diagram in a previous day and you want to modify the model again, in this case it is convenient to refresh the definition in case some objects changed. The Data Modeler will refresh the objects while keeping your diagram changes –unless some column is no longer available, which is automatically resolved by removing the modification for the column.

Search for object in Diagram: Use this button to search for an object in the diagram.

Show or hide Quick help: Shows or hides the quick help window.

Context Menus:

Context menus activate when right-clicking on a diagram object –to modify a table- , or on an empty spot of the diagram –to create a table:

Context Menu for table > Set Primary key: Sets a new Primary Key or modifies the existing Primary Key.

Context Menu for table > Edit table: Opens a form to edit the table. For existing database tables you will be able to rename tables, rename columns, drop columns, modify data type lengths, add comments, set Primary key, set not null for columns.

For new tables you can fully modify all of the details, including changing the order of columns. The changes will be applied only to the diagram, so to perform any changes you will have to generate the DDL script –using the DDL or Move to Script icons and execute it.

Context Menu for table > Edit DDL: This option displays when the table already exists at database level. Edit DDL will bring the existing DDL for the table into a new SQL Code Window. NOTE: Changes in the diagram will not show up in the DDL.

Context Menu for table > Expand table height / Reset table height: If the table has more than 15 columns only the first 15 columns will display in the diagram. Use the Expand table height option to show all the available columns, or the Reset table height option to go back to displaying 15 columns.

Context Menu on empty diagram spot > Create table: Opens the form to create a new table.

Creating a relationship between two entities:

In the image below two tables were created in the diagram: Orders and Customer.

The table Orders needs to reference the customer that generated the order, but before creating the relationship (foreign key in Orders that references Customer, make sure the destination table (Customer) has the Primary Key defined for CUSTOMER_ID.

A Foreign Key in one table can only point to a Primary Key in the referenced table. If no PK is defined in the destination, define it before attempting to create the relationship.

To create the relationship simply drag an arrow from the Orders table to the Customer table, by clicking the source table and dragging the link into the destination table. When releasing the mouse left key you will be asked to confirm the relationship. In the case below the relationship was automatically filled since both tables had the same column name:

You can also match the origin columns manually –the referenced destination columns are always the Primary Key for the destination table, so only the origin needs to be confirmed.

Managing object deletions:

To remove a relationship click the link and hit the <DEL> button. If the relationship exists at database level the link will still persist but in blue and with the word “(deleted)” written in the link. If you want to reverse the deletion simply delete the link again and it will go back to black color.

To remove a table click the table and hit the <DEL> button. If the table exists at database level you will be asked if you want to delete it from the diagram or to keep it but script a DROP TABLE for the object:

If you selected <NO> the table will show up like this in the diagram:

If you want to restore the table –to avoid scripting a DROP TABLE- simply hit <DEL> again and it will go back to the black color:

When editing columns from a table, to script a drop for a column select the row by clicking the row selector (marked in red below) and hit <DEL>:

The deleted column will show up like in the image below:

To restore the column simply repeat the step and hit <DEL> again:

If you renamed a column it will show up like this:

To restore it to the original value simply delete it twice, the first deletion will bring the original name to the screen:

And deleting it again will fully restore it: