Creating Models › Creating a Physical Model › Views

Views

In a physical model, you can create views, which are SQL queries that are permanently stored in the database under assigned names. The result of a view query is a virtual table. To the database user, a view appears just like a real table with a set of named columns and rows of data. However, a view is not a permanently stored set of data values. The rows and columns of data visible through the view are the result of the database query that defines the view.

You can use views in SQL to:

• Create a customized presentation of the data stored in a database.
• Restrict access to data, allowing different users access to only certain rows or columns of a table.
• Simplify database access by creating customized structures tailored to the needs of individual users. A user can write a simple query on a view rather than a complex query on the actual database tables.

Views are supported in the following features:

• Forward Engineering. When you forward-engineer your data model to generate a database, the SQL code to define the view is generated to the database.
• Reverse Engineering. When you reverse-engineer an existing database that includes one or more views, each view is imported, the view syntax is parsed, and where possible the relationships to the tables referenced by the view are created.
• Complete Compare. When you use Complete Compare and update a view either in the model or in the database, you can keep the model view specification in sync with the database view specification.

When you add a view to a model, it appears as a box with a dotted outline and round corners, and a view relationship as a dotted line with circle at the view end of the line. A view relationship between a table or view and a view indicates that the view references one or more of the columns from that table or view.

A view column can be a reference to a table column, a reference to a column in different view, or a user-defined expression.

After you draw a view relationship between a table and a view, you must use on-diagram editing or the View Editor to migrate columns in the table to the view.

You can edit views directly in the diagram window using on-diagram editing. When you drag a column from a table into a view, the view column and the view relationship are created. You can drag a view column out of a view to remove it from the view.

If you delete a table column that is referenced by a view, the corresponding view column is deleted. If you delete a table that is referenced by a view, the corresponding view columns are deleted.

You can add a view using any of the following methods:

• Click in the ERwin Toolbox, and click in the diagram window.
• Reverse engineer an existing database.
• Copy a view from one model and paste it into another model.
• Right-click the Views folder in the Model Explorer and select New.

Note: You can add consecutive multiple views by pressing the Ctrl key before you click the view tool. When you click again in the diagram window, another view is created.

More information:

Views in the Model Explorer

Editors for Managing Views

View Editor

View Column Editor

View Relationships Dialog