Chapter 18: Rose Data Modeler

Child Restrict, if checked, will prevent orphan child records from being created.

5.

If you selected a declarative rule, enter the following:

♦

Parent Update sets the option (Cascade, Restrict, Set Null, No Action, Set Default) to use when the parent is updated. Note that not all options are supported by each DBMS.

♦

Parent Delete sets the option (Cascade, Restrict, Set Null, No Action, Set Default) to use when the parent is deleted. Note that not all options are supported by each DBMS.

Working with Views

A view is a way of looking at the data a little differently than it is structured in the database. You can create a "virtual" table using a view that will contain data from one or more tables in the database. Views help secure the database; you can give a group of users read−only access to a view in order to prevent accidental modifications of the underlying data.

In Rose, a view is modeled using the following symbol:

644

Chapter 18: Rose Data Modeler

On the Data Model diagram, a dependency is drawn between the view and the table or tables that are the source of its data, as shown in Figure 18.1.

Figure 18.1: Modeling a view

To create a view:

1.

Right−click the schema in the browser.

2.

Select Data Modeler → New → View.

3.

Name the new view.

4.

Right−click the new view and select Open Specification.

5.

On the General tab, enter the following:

♦

Name is used to name or rename the view. Each view within a schema must have a unique name.

♦

Schema displays the name of the schema that contains the view. This field cannot be changed.

♦

645

Chapter 18: Rose Data Modeler

Updateable controls whether a user can modify data using the view. This field can be set only when using Oracle, DB2, or SQL 92.

♦

Distinct determines that, if this is set, only unique rows will be included in the view.

♦

Materialized (Oracle) means that, if this option is set, the view will be populated when it is forward engineered from Rose. If this option is not set, the view will still be created, but will not be populated with data.

♦

Check Option controls what constraints will be applied to the view. The None option will prevent constraints from being enforced on the view. The Local option will enforce any constraints you have set up for the view or for any views dependent on this view. The Cascade option will enforce any constraints you have set up for the view, constraints for dependent views, and constraints for the tables that contain the source data.

6.

On the From tab, enter the following:

♦

Select the table(s) and/or view(s) from the Available Members list box that you would like to use in the view. Press Add to move the selected tables or views to the View Members list box.

♦

The Correlation Name field sets the alias that will be used for the table or view in the current view's SQL statement.

♦

In the Where Clause field, enter a SQL where clause, order by, or group by statement that will be included in the view. Be sure to include the phrase "WHERE," "ORDER BY," or "GROUP BY."

646

Chapter 18: Rose Data Modeler

7.

On the Columns tab, enter the following:

♦

View Columns lists the columns that will be included in the view. To add a new column, select the Import Columns button. A list of all available columns will be displayed. To remove a column, select it and press the Delete toolbar button.

♦

Alias shows the alias name of the column.

8.

On the SQL tab, you can see the SQL statement that was built as you selected tables and columns on the other tabs. You can also enter a SQL statement directly into this window, or change the SQL statement Rose has generated for you. As you change the SQL statement, Rose will update the table

647