1. Home
  2. Docs
  3. Chapter 14. Database Design & Engineering
  4. 2. Database Designer’s Guide
  5. Modeling database view

Modeling database view

Download PDF

A database view is the result of a query on the data stored in a database. You can select columns, specify where and join statements to a view and present the data as if the data were coming from one single table. In Visual Paradigm, you can edit database view in a visual editor.

Creating database view

A database view is represented visually with a View shape. You can create a database view from the diagram toolbar or from the entities involved in the database view.

From diagram toolbar

You can create a database view and then edit it by specifying the entities involved. To create a database view:

  1. Select View from the diagram toolbar.
    Select View from diagram toolbar
  2. Click on the diagram to create a view.
  3. Enter its name and confirm.
    Database view created
    You can now specify the entities involved in the view via the View Editor. To open the View Editor, right click on the background of ERD and select Show Table Record Editor or View Editor from the popup menu.
  4. On the right hand side of the View Editor. Click on the add button. Then, select the entities that are involved in the view and click Apply.
    Add some entities to a view
    Now, you can specify the view. For details, read the following sections.
    Entity added to view

From entities

Instead of creating a blank view and adding entities into the view, you can create a view directly from entities that are involved in the view. To create a database view from entities involved:

  1. Select the entities in ERD.
    Select entities to create view
  2. Click Create View in the View Editor. The View Editor is placed under the ERD. If you do not see it, right click on the background of ERD and select Show Table Record Editor or View Editor from the popup menu.
    To create view
    Now, you can specify the view. For details, read the following sections.
    View formed from multiple entities

Column selection

A database view contains rows (i.e. the results) and columns, just like a database table. To select columns, simply click on the checkbox of the desired column. Alternatively, select the column in the Column column in the View Editor.

Selecting column

Joining columns

Joining of columns between entities can be done by the resource-centric interface. To do this:

  1. Click on the source column in the entitiy in View Editor.
  2. Press on the desired resource and drag it out.
    To join columns
  3. Drag to the target column and release the mouse button.

Editing database view in View Editor table

There is a table under the visual view editor where you can configure a view’s column alias, sort order, grouping, function, filter, etc.

Editing the alias of column

Property Description
Column Correspond to the SELECT clause of a view creation statement.
Alias The displayed name of column.
Entity The entity where the column come from.
Output Check it to include the column into the creation statement of view.
Sort Specify whether to sort the column ascendingly or descendingly
Sort Order Specify the sort column. Records are sort following the order, with smaller number sort first.
Group By Check it if you want the results be grouped by the column.
Function Apply function to the column.
Filter Add filter for the column.

Supported fitlers

The following table lists out the filters you can enter in the Filter field.

Filter Description Sample
= Equals = 10
<> Does not equal <> 250
< Less than < 80
<= Less than or equal to <= 200
> Greater than > 0
>= Greater than or equal to >= 18
LIKE Search for a pattern LIKE ‘%PRIORITY%’
IN To specify multiple possible values IN (‘Administrator’, ‘Manager’)
BETWEEN Between an inclusive range BETWEEN 10 AND 20
IS NULL / IS NOT NULL IS NULL: To select records with null
IS NOT NULL: To select records with filled values
IS NULL / IS NOT NULL
NOT Only when no row is returned from the sub query. NOT EXISTS (SELECT NAME FROM USERS)
EXISTS Only when at least one row is returned from the sub query. EXISTS (SELECT NAME FROM USERS)
ALL Compares a scalar value with a single-column set of values. >= ALL (SELECT STOCK FROM PRODUCTS)
ANY Compares a scalar value with a single-column set of values. >= ANY (SELECT STOCK FROM PRODUCTS)