Participation

Every binary relationship consists of two separate constraints known as connectivity and participation. Connectivity is always one-to-one, one-to-many or many-to-many and describes the maximum number of possible relationship occurrences for an entity participating in a given relationship.

A participation constraint represents whether all entity occurrences are involved in a particular relationship (known as a mandatory participation), or only some (known as optional participation).

In the diagram above, the two constraints can be understood. Notice that the participation of Staff to Branch has a maximum value of N indicating that a member of staff can manage many branches. Likewise, Branch has a maximum participation of 1 indicating that a branch is managed by a maximum of 1 Staff member.

By setting all of the Participation options in the ERD Tools | Preferences | ERD property sheet, you can have a complete visualization of the Participation constraint.

To interpret this and predict the Foreign Key Rules for UPDATE and DELETE, you must conceptualize the combined relationship constraint of Connectivity and Participation. The Connectivity stipulates that the foreign key placement is on the Many side (Branch). Therefore the Foreign Key Rules must be understood from the perspective of the Many end of the relationship. Let's apply the logic and predict the behavior of the application.

1.

Foreign key placed in Branch

2.

Participation is one mandatory and one optional

3.

The optional side is Staff, the mandatory side is Branch

4.

Therefore, the applicable participation rule is "one mandatory, one optional, with the key on the mandatory side"

5.

The foreign key column should allow Nulls (as a branch may not have a manager).

6.

Delete should Set NULL or Restrict

7.

Update should Cascade

If you refer to the chart, you will find that this is the default behavior of the relationship as implemented by the application.

1.	Foreign key placed in Branch
2.	Participation is one mandatory and one optional
3.	The optional side is Staff, the mandatory side is Branch
4.	Therefore, the applicable participation rule is "one mandatory, one optional, with the key on the mandatory side"
5.	The foreign key column should allow Nulls (as a branch may not have a manager).
6.	Delete should Set NULL or Restrict
7.	Update should Cascade