13. ER Modeler in ADS

The Enitity Relationship Modeler (ER Modeler) helps in designing and editing database schemas and relationships through graphical representation of abstract database concepts. Tables, views, relationships and constraints are converted to shapes, lines, nodes and vectors so that complex abstract interactions and processes can be viewed in a coherent diagram. The models created in the ER Modeler immediately translate into queries and commands that databases can understand. Any database connection within Aqua Data Studio can be used to generate an ER Model (reverse engineering), and any model built within the Entity Relationship Modeler can be converted into a DDL script (forward engineering) for importing into any database available to Aqua Data Studio. It is possible to migrate database schema from one database server, vendor or version into any other database server through the ER Modeler's "Convert To" tool. For more on how to convert a model from one database to another see the "Converting ER Model DB to DB" section. The Entity Relationship Modeler uses Aqua Data Studio's Docking Framework, allowing users to undock its various panes and panels into separate windows.

The ER Modeler can be used for:

Creating a New Diagram:  New diagrams can be created manually by drawing entities on a sheet and altering their attributes. It is possible to create tables, indexes, constraints, relationships, add notes and regions and more.

Extracting a Diagram From a Database:  (commonly referred to as reverse engineering) A diagram can be extracted from an existing database, its schema objects quickly drawn for modification by the ER Diagram Generator.

Generating DDL: (commonly referred to as forward engineering) The diagram can be converted into DDL for use in SQL scripts. Entire databases can be designed then converted to scripts for importing.

Converting a model for one database into a model for another database (Database Migration): If migration is required, the DDL of one database can be converted to the DDL of another database through the use of the "Convert to" tool in the Tools menu. See the "Converting ER Model DB to DB" section for how this process works.

Saving, Modifying and Sharing Diagrams: Diagrams can be saved, opened, documented, modified and shared with groups. When a diagram is added to the script mount for a database server, the mount can be included in a source control repository. All of the developers in an organization can then access the same model, modify it and track the modifications.

Printing Diagrams: Diagrams can be printed, enlarged and laminated for use as a poster for quick reference. The dimensions of the print out can easily be altered and enlarged. Fit Width and Fit Height buttons allow printing diagrams to fit paper dimensions.

Saving a Diagram As an Image: The diagram can also be saved as a JPG or PNG image for inclusion in word processing documents or presentation slides.

Generating an HTML Report: The modeler can be used to generate an HTML report containing all of the views, indexes, tables and relationships in the model.  The generated report is created as frameset in a zip file which can be expanded into a directory for use in a website.

ER Modeler Workspace and Dockable Framework Overview

There are five major functional areas within the ER Modeler Workspace where most of the creation and editing of models takes place: Model Node Browser, Sheet, Entity Palette, Object Properties Pane, and Overview.

Model Node Browser: Presents all of the objects within a diagram in a tree containing all relationships, tables, views and notes. The tree can be expanded or collapsed to display more detailed information such as Indexes and Constraints.

Sheet: Shows the diagram contents in independent views for manual or automatic layout. This is the drawing area of the ER Diagram. Objects can display on more than one sheet at a time.

Entity Palette: Displays the entities which can be created. Left clicking on one converts the into a tool cursor for dragging out the entity in the sheet.

Object Properties Pane: Displays, in non-editable form, the properties of the selected object or objects.

Overview: A bird’s eye view of the current sheet, allowing fast navigation with a draggable and resizeable zoom rectangle. The rectangle indicates what portion of the diagram is currently being viewed.

Workspace Diagram

A more in depth description of the ER Modeler's menus and panels follows. The numbered sections relate to the screenshot.

Though not shown below, the Properties window, available for editing when an item is double clicked or right clicked, is discussed on the pages on how to create tables, indexes, constraints, relationships, and add notes and regions.

1. Menu Bar: Contains all of the menus available in the ER Modeler. Its menus are context sensitive based on the current selection in the model. All available menus are listed below.

• File
  • New - Creates a new, blank ER Diagram.
  • Open - Opens an exisitng ER Diagram.
  • Open Recent - Displays the 10 most recently opened files. Selecting one opens it.
  • Save - Saves the current diagram.
  • Save As - Saves the current diagram with a different filename.
  • Print - Prints the current sheet view.
  • Close - Closes the current diagram.
• Edit
  • Copy - Copies the selected entity object(s) or content(s).
  • Paste - Pastes the entity object and contents from the clipboard.
  • Rename - Renames the current object
  • Delete - Deletes the currently selected object(s)
  • Select All - Selects all objects
  • Show/Hide - Shows or hides the selected object in the sheet
  • Auto-fit - Selected entities can be automatically resized to fit their contents
  • Properties - Opens the properties dialog for the selected object
  • Find - Allows searching for an object in the diagram, highlighting it in the model when found.
  • New Table - Creates a new table entity object.
  • New View - Creates a new view.
  • New Note - Creates a new note.
  • New Relationship - Creates a new relationship.
  • New Subcategory - Creates a new subcategory.
  • New Region - Creates a new region.
  • New Index - Creates a new index on the currently selected Table
  • New Primary Key Constraint - Creates a new primary key on the currently selected Table
  • New Unique Constraint - Creates a new index on the currently selected Table
  • New Check Constraint - Creates a new check constraint on the currently selected Table
• View
  • Zoom: 1:1 Scale - Zooms current view to actual size.
  • Zoom: In - Magnifies the current view.
  • Zoom: Out - Shrinks the current view.
  • Zoom: Fit to Window - Forces the the entire model to fit in the Sheet.
• Sheet
  • Add Sheet - Adds a sheet to the current diagram.
  • Rename Sheet - Renames the selected sheet.
  • Layout - Allows selection of different automatic layout of entities.
  • Delete Sheet - Deletes the current sheet.
• Tools
  • Generate Script - Generates the SQL required to script the diagram.
  • Generate HTML Report - Generates an HTML report containing information about all of the tables, indexes, views and relationships in the diagram. The generated report is a set of zipped html frameset pages.
  • Save As Image - Saves the current sheet view as PNG or JPG.
  • Compare - Compare the model to another model.
  • Compare Database - Compare the model to an existing database.
  • Import Database - Import a database schemas to create a model.
  • Convert Model - Convert the model for a different database vendor and version
  • Export ot ERX Format - Export the model for Visio or ERWin.
• Display
  • Attribute Level - Displays only the entity object attributes.
  • Primary Key Level - Displays only the Primary Keys.
  • Entity Level - Displays only the entities.
  • Physical Schema Level - Displays the Data Types.
  • Nullable Columns - Displays NULL and NOT NULL for columns.
  • Comments - Displays the comments associated with an entity.
  • Relationship Names - Displays the names or relationships.
  • Printing Grid - Displays the printing grid to indicate where page breaks occur during printing.
  • Schema Folder Grouping - Displays model objects categorized by the schema of the objects.
• Window
  • Hide Model - Hides the Model Node Browser.
  • Hide Object Properties - Hides the Objects Properties pane.
  • Hide Overview - Hides the Overview.
  • Hide Palette - Hides the Entity Palette.
  • Fast Rendering - Displays dashed One-to-Many Relationships as thin, solid grey lines so that 2000 or more objects can be viewed in a single sheet.

2. Tool Bar - Contains button icons for the viewing and display of items in the ER Modeler.

• Save - Saves the current ER Diagram.
• Print - launches print dialog.
• Display Printing Grid - Displays a printing grid to indicate where page breaks occur.
• Schema Folder Grouping - Displays model objects categorized by the schema of the objects.
• Zoom: 1:1 Scale - Zooms current view to actual size.
• Zoom: In - Magnifies the current view.
• Zoom: Out - Shrinks the current view.
• Zoom: Fit to Window - Forces the the entire model to fit in the view.
• Display Attribute Level - Displays entities and their attributes.
• Display Primary Key Level - Displays entities and their primary keys.
• Display Entity Level - Displays on entities by name.
• Display Physical Schema Level - Displays or hides the physical schema level.
• Display Nullable Columns - Displays NULL and NOT NULL for columns.
• Display Comments - Displays or hides the comments.
• Display Relationship Names - Displays or hides the names of relationships.

3. Model Browser - Contains a node view of all entities and objects within a diagram. Objects hidden in the current sheet are gray.

4. Sheet View - Contains all of the sheets of a diagram, each indicated with a tab. This is where the diagram displays. Objects can display on multiple sheets and can be layed out differently on each one without altering the layout of the other sheets.

5. Entity Palette - Contains all of the entities and relationships which may be created within a sheet. Regions can also be created from the Entity Palette.

6. Model Overview - Contains a thumbnail of the entire active sheet. A red rectangle indicates what portion of the thumbnail is being viewed. Moving this rectangle changes the area visible in the sheet.

7. Object Properties - Displays, in a non-editable form, the properties of the object currently selected in the Model Node Browser or Sheet.

8. Sheet "SQL Server 2000 - 54" - The current view of selected tables, relationships, notes and regions given the name "SQL Server 2000 - 54". Any number of sheets can appear as tabs in the modeler, allowing cuttin and pasting of various tables and relationships into different layers for further organization.

9. Sheet "Views" - A sheet created by the user named "Views" containing entity objects.

10. Table Entity Node - Contains a tree of the contents of the table "Orders" selected in the current sheet.

11. Additional Object Nodes - The Model Node Browser, though not displaying it here, allows browsing Views, Notes and Relationships as distinct object nodes. These nodes also appear in expandable trees.

12. Table Entity Object - A table, its attributes, keys, physical schema and comments in a relationship with two tables.

13. Region - A grouping of entity objects and notes. Regions do not exist as schema objects and are strictly for diagram organization.

14. Relationship Entity Object - Represents a relationship between tables.