Chapter 18: Rose Data Modeler

7.

On the Action Body tab, enter the SQL for the stored procedure.

Adding Relationships

A relationship in the data model is similar to a relationship in the object model. Where a relationship in the object model joins two classes, a relationship in the data model joins two tables. There are two primary types of relationships supported by Rose: identifying relationships and non−identifying relationships.

In either case, a foreign key is added to the child table to support the relationship. With an identifying relationship, the foreign key becomes part of the primary key of the child table. In this situation, a record cannot exist in the child table without being linked to a record in the parent table. An identifying relationship is modeled as a composite aggregation:

A non−identifying relationship will still create a foreign key in the child table, but the foreign key field will not become part of the primary key of the child table. In a non−identifying relationship, the relationship cardinality (multiplicity) controls whether a record in the child table can exist without a link to a record in the

639

Chapter 18: Rose Data Modeler

parent table. If the cardinality is 1, a parent record must exist. If the cardinality is 0..1, the parent record does not need to exist. A non−identifying relationship is modeled as an association:

To add an identifying relationship:

1.

Select the Identifying Relationship toolbar button.

2.

Drag and drop from the parent table to the child table.

3.

Rose will automatically add a primary key constraint and a foreign key constraint to the child table.

To add a non−identifying relationship:

1.

Select the Non−identifying Relationship toolbar button.

2.

Drag and drop from the parent table to the child table.

3.

Rose will automatically add a foreign key constraint to the child table.

Cardinality defines the number of rows in one table related to a single row in another table. Cardinality in the data model has much the same meaning as cardinality in the object model. You set cardinality options on both ends of the relationship. The cardinality at the end of the relationship nearest the parent can be set to 1 if the relationship is mandatory, or set to 0..1 if the relationship is optional. The cardinality nearest the child table controls how many records in the child table can be created for each record in the parent table.

A many−to−many relationship is modeled through the use of a join table:

640

Chapter 18: Rose Data Modeler

To set cardinality and other relationship specifications:

1.

Right−click the relationship and select Open Specification.

2.

Select the General tab.

3.

Enter the name of the relationship if desired.

4.

641

Chapter 18: Rose Data Modeler

Select the type of relationship (Identifying or Non−identifying).

5.

Assign roles to the parent and child tables (optional).

6.

Set the cardinality for both sides of the relationship.

7.

Select the Enforce with Trigger check box to automatically generate a trigger that will enforce the cardinality rules you just established.

8.

Select the Migrated Keys tab.

9.

The names of the fields in the child and parent table that participate in the relationship are listed. You can change the field names here if needed.

Adding Referential Integrity Rules

Referential integrity establishes a set of rules that help keep the data consistent. For John Doe, for example, you may have an employee record in the employee table and two address records (one for his home address and one for his work address) in the address table. If the John Doe record in the employee table is deleted, the address records will be "orphaned" (that is, they will no longer have an employee to refer to).

642

Chapter 18: Rose Data Modeler

Referential integrity helps avoid these situations by specifying what should happen when the parent record is updated or deleted. You have several options. One option is for the child record(s) to be automatically updated or deleted. Or, you can prevent the parent from being updated or deleted at all. Or, you can run a trigger when updating or deleting the parent record. Once you choose your option, you enter this information on the relationship specification in Rose.

There are two primary types of referential integrity: trigger or declarative. Trigger−enforced referential integrity will run a trigger when the parent is updated or deleted. Declarative referential integrity includes the constraint as part of the foreign key clause. The following options are available when setting referential integrity:

∙

Cascade determines that when the parent is updated or deleted, all child records are updated or deleted.

∙

Restrict prevents the parent from being updated or deleted.

∙

Set Null sets the foreign keys in the child record to Null if the parent record is updated or deleted.

∙

No Action does not enforce referential integrity at all.

∙

Set Default sets the foreign keys in the child record to a default value if the parent record is updated or deleted.

To set referential integrity for a relationship:

1.

Right−click the relationship and select Open Specification.

2.

Select the RI (referential integrity) tab.

3.

Select either the Triggers or the Declarative Referential Integrity radio button to control whether a trigger−enforced or declarative referential integrity rule will be used.

4.

If you selected a trigger−enforced 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.

♦

643