Creating Database Views

In a DB diagram, you create a new database view using the context menu. 'From' clauses, which reference column sets (table or view) used in the view, are created using the carousel on a view. If you want to reference existing column sets using a From clause, add these to the diagram from other tool windows using drag-and-drop. Modeling union views and sub selects is supported by specific stereotypes in the element type view.

Automatic Creation of View Columns

In the steps described below, View columns are created automatically when a column set is referenced by a From clause for the first time in the View. In the process, the View column is linked with the source column in the Features Used property and the Expression property is set appropriately.

Creating a New Database View

How to proceed

Position the mouse pointer at your chosen position in the diagram and click on the view template in the mini toolbar of the context menu.

A new view is created in the diagram. The name of the view is automatically selected to be edited.
Enter the name for the table and confirm with the [Enter] key.

Adding an Existing Column Set

How to proceed

Select the column set in another tool window and drag-and-drop it to your chosen position in the diagram.

A node that shows the existing column set is created in the diagram.

Creating a New From Clause (Complete)

How to proceed

Select a view in the diagram.

The carousel appears next to the view.
Select the From Clause icon in the carousel and drag it onto the column set to be referenced.

The From clause is created and represented in the diagram as an edge with an arrow to the referencing column set. A corresponding column is created and linked in the view for each column in the referencing column set.
Select the columns that are not required in the view and delete them.

Creating a New From Clause (for Selected Columns)

How to proceed

Select one or more columns in a column set and drag-and-drop these onto a view.

The From clause is created and represented in the diagram as an edge with an arrow to the referencing column set. A corresponding column is created and linked in the view for each column selected.

Creating a New From Clause (Join)

How to proceed

Drag-and-drop a foreign key from the section of a table onto a view.

Two From clauses are created and shown in the diagram. The first of these references the table with the referencing key, and the second the table with the foreign key. The second From clause receives the Join condition in the Expression property (comparison of the key columns).

Formulating View Clauses (WHERE, GROUP BY, ORDER BY, HAVING)

How to proceed

Select a view.

The property dialog for the element includes the Expression property.
Specify all View clauses in the Expression property.

The Concrete Expression property displays the expression using replaced $ wildcards.

Modeling a Union View

Context

Specific view stereotypes are configured for modeling union views: Union view for the view itself, 'Select as View' and 'Select as Union View' for the sub selects of the union view.

Create a 'Select as View' in the same way as for a normal view and a 'Select as Union View' as for a union view. Two or more such sub selects are referenced from the union view using a From clause.

In contrast to the normal view, the View columns are not linked to the source columns by 'Features Used'; instead, the order is evaluated. The user is responsible for the appropriate order of the columns in the sub selects and in the union view.

How to proceed

Model the sub-selects.

There are two or more sub selects in the diagram.
Position the mouse pointer at your chosen position in the diagram and click on the union view template in the mini toolbar of the context menu.

A new union view is created in the diagram. The name of the view is automatically selected to be edited.
Enter the name for the table and confirm with the [Enter] key.
Select the union view in the diagram.

The carousel appears next to the view.
Select the From Clause icon in the carousel and drag it onto the first sub-select.

The From clause is created and represented in the diagram as an edge with an arrow to the sub select. Unlinked columns of the same name are created in the union view for each column of the sub select.
Repeat steps 4 and 5 for the other sub-selects.

In the case of additional From clauses, no further columns are created in the union view. If appropriate, check in advance whether the additional sub selects have appropriate columns in the appropriate order, or sort the columns subsequently.

Using a Sub Select

Context

Graphic models are not suitable for the complete mapping of SQL grammar. Sub Selects are included in database views by using placeholders in expressions. They can be used in expressions for all view elements (Expression of the View, Expression of a View Column, Expression of a From Clause). The placeholder is created by using the key word SELECT followed by the name of the sub select in parentheses: SELECT(<name>).

Views of all stereotypes can be used as sub select (View, Union-View, Select as View, Select as Union View). Create a 'Select as View' in the same way as for a normal view and a 'Select as Union View' as for a union view.

When generating the view, the DDL generator replaces the placeholder with the code fragment of the sub select.

How to proceed

Model a sub-select.

The model owns a sub select with a unique name.
Select the view element that the sub-select should use.

The Properties tool window contains the 'Expression' property for the element.
Use the placeholder 'SELECT(<Name of the Sub Select>)' in the expression.
To test, select the view and run the DDL Generator from Extras>Export>DDL Export.

The View DDL is shown in the information window.