The Database Builder

The Database Builder is a tailored interface for the data modeler, so that all database-related modeling tasks can be performed in a single location. The interface and its related screens include only the information relevant to data modeling, thereby streamlining and simplifying the modeling process.

Access Tools | Database Builder


Database Builder

This collection of images shows the pages of the Database Builder and the typical information that they allow the user to modify.


Overview

The interface of the Database Builder consists of:

  • A Tree of data models, listing all defined data models in the current repository
  • A 'Columns' tab through which you directly manage the Table columns
  • A 'Constraints' tab for the direct management of Table constraints such as Primary Keys, Foreign Keys and Indexes
  • An SQL Scratch Pad that you can use to run ad-hoc SQL queries against a live database
  • A 'Database Compare' tab that displays the results of comparisons between the data model and a live database
  • An 'Execute DDL' tab on which you can execute generated DDL against a live database, instantly

You can use the Database Builder to:

  • Create, edit and delete database objects (Tables, Views, Procedures, Sequences and Functions)
  • Create, edit and delete Table constraints (Unique Constraints, Primary Keys, Indexes, Check Constraints and Triggers)
  • Create, edit and delete Table Foreign Keys
  • Reverse engineer database schema information
  • Generate DDL from a modeled database
  • Compare a live database schema with a modeled database
  • Execute generated DDL against a live database
  • Execute adhoc SQL statements against a live database

Opening the Database Builder

When you first open the Database Builder, it searches the entire project for all Packages that have the stereotype <<Data Model>> and loads the corresponding data models as root nodes into the tree. A grayed-out icon indicates that the details of the data model are not loaded.  

If a data model is selected in the Project Browser when the Database Builder is opened, that model's details will be automatically loaded.

Using the Database Builder

You can start working in the Database Builder in one of these two ways:

Task

Action

See also

Create a new data model

Once you have opened the Database Builder view, right-click in the empty space of the tree and select 'New Data Model' to invoke the Model Wizard.

Create a Data Model from a Model Pattern

Load an existing Data Model

Once the Database Builder view is opened, load any of the defined data models by either:

  • Right-clicking on the name and selecting 'Connect', or
  • Double clicking on the name

When a data model is loaded the Database Builder creates a series of logical folders (one for each object type supported by the current DBMS) each populated with all objects of that type found in the data model's hierarchy of Packages.

Available Actions in the Database Builder

The majority of the Database Builder functions are accessible via context menus. Each object in the Tree has its own set of unique menu items based on its type and status. This table describes the available context menu items and identifies which object they apply to.

From this point, you can begin to model the database data structures in their corresponding Packages, starting with Tables. Alternatively, you can import database schema to edit, either directly or using the model/database synchronization facility.

Menu Option

Applies to / Description

New data model

Applies To: Blank Space

Description: Open the Model Wizard screen.

Refresh All

Applies To: Blank Space

Description: Reload the complete list of data models.

Connect

Applies To: Root Node

Description: Load the full details of the data model.

Disconnect

Applies To:  Root Node

Description: Unload the full details of the data model.

Import DB Schema from ODBC

Applies To:  Loaded Root Node

Description: Open the 'Import DB schema from ODBC' dialog using the current active connection as the ODBC source.

Generate DDL

Applies To:  Loaded Root Node, Folder, Table, View, Procedure, Function, Sequence, Package

Description: Open the 'Generate DDL' dialog with the current object(s) selected.

Show Differences

Applies To:  Loaded Root Node, Folder, Table, View, Procedure, Function, Sequence

Description: Compare the selected objects to the current active connection.

View Record Count

Applies To:  Table, View

Description: Build and run a SELECT query (formatted to suit the element's DBMS) to show the number of records in the selected Table or View.

If there is no active connection, you are prompted to select one.

View Top 100 Rows

Applies To:  Table, View

Description: Build and run a SELECT query (formatted to suit the element's DBMS) to show the top 100 rows of the selected Table or View.

If there is no active connection, you are prompted to select one.

View Top 1000 Rows

Applies To:  Table, View

Description: Build and run a SELECT query (formatted to suit the element's DBMS) to show the top 1000 rows of the selected Table or View.

If there is no active connection, you are prompted to select one.

View All Rows

Applies To:  Table, View

Description: Build and run a SELECT query (formatted to suit the element's DBMS) to show all rows of the selected Table or View.

If there is no active connection, you are prompted to select one.

Properties

Applies To:  Loaded Root Node, Folder, Table, View, Procedure, Function, Sequence, Package, Connection

Description: Open the standard 'Properties' dialog for the selected object.

Find in Project Browser

Applies To:  Loaded Root Node, Folder, Table, View, Procedure, Function, Sequence, Package, SQL Query, Connection

Description: Find the selected object in the Project Browser.

Refresh

Applies To: Loaded Root Node

Description: Reloads the details of the current loaded data model.  This is necessary when objects are added, changed or deleted by other users or when the changes are performed outside of the Database Builder.

Add new <type>

Applies To:  Folder, Table, View, Procedure, Function, Sequence, Package, SQL Query, Connection

Description: Create a new object of the specified type.

Clone <name>

Applies to: Folder, Table, View, Procedure, Function, Sequence, Package, SQL Query, Connection

Description: Make a new copy of the selected object. When you select this option, a prompt screen displays on which you set the name and owner of the new object. For Table objects, you can choose which existing constraints should be copied (and set a name for each one) along with which Foreign Keys. For SQL-based objects, you can make any necessary changes to the SQL for the new element.

Delete <name>

Applies To:  Table, View, Procedure, Function, Sequence, Package, SQL Query, Connection

Description: Permanently delete the selected object from the repository.

Add new Foreign Key on <name>

Applies To:  Table

Description: Create a new relationship between the selected Table and another one, then show the 'Foreign key Constraint' screen for the new relationship.

SQL Object Properties

Applies To:  View, Procedure, Function, Sequence

Description: Open the 'SQL Object Editor' screen.

Edit

Applies To:  SQL Query

Description: Load the SQL (as defined in the selected element) into the SQL Scratch Pad.

Run

Applies To:  SQL Query

Description: Load the SQL in the SQL Scratch Pad and run it.

If there is no active connection, you are prompted to select one.

Set as active DB Connection

Applies To:  Connection

Description: Flag the selected Database Connection as the active one for the current session.

Set as Default DB Connection

Applies To:  Connection

Description: Flag the selected Database Connection as the active one each time the data model is loaded.

DB Connection Properties

Applies To:  Connection

Description: Opens the 'Database Connection Properties' screen, to manage the connection settings.

Database Connections in the Database Builder

When performing certain tasks such as 'Compare' or 'Execute DDL', the Database Builder requires an active database connection. Only one database connection can be made active (indicated by a colored 'Database Connection' icon, while the others are gray) at a given time. If a database connection is not currently active and you try to perform a task that requires one, the Database Builder performs one of these actions based on how many connections are defined:

  • 0 Connections – prompts you to create a connection and, if successful, continues
  • 1 Connection – sets it as active and continues
  • 2 (or more) Connections – prompts you to select one and, if successful, continues

Notes

  • The Database Builder is available in the Corporate, Systems Engineering, Business and Software Engineering, and Ultimate editions of Enterprise Architect

Learn more