Mandatory and Optional Participation

Every entity in a relationship has either

1. a mandatory or
2. an optional

type of participation with respect to the relationship it is in. The participation type an entity enjoys in a relationship has a direct bearing on the rule that describes the relationship itself, as you will see in the next lesson. The rule that describes the relationship between entities, determines whether the participation is mandatory or optional. To distinguish between

1. mandatory and
2. optional relationships,

it is better to use the language of tables rather than the language of entities.

A mandatory relationship exists for a table if at least one record in this table must exist before any records can be added to its associated table. Consider the following example of a 1:N relationship, which is based on a “common sense” business rule: A customer may place many orders;

every order is placed by a customer.

According to the italicized portion of the rule, every order in a database must be placed by a customer. Put simply, no order can exist in a database unless it has first been placed by a customer. Therefore, at least one record must exist in the Customers table before any records can be added to the Orders table, as illustrated below:

Reverting back to the language of entities, the type of participation for the CUSTOMER entity in the CUSTOMER:ORDER relationship is designated as mandatory.

In our discussion of types of data relationships, we have defined those relationships by starting each with zero, indicating that the participation by a given instance of an entity in a relationship is optional. For example, DistributedNetworks can store data about a customer in its database before the customer places an order. Therefore, an instance of the customer entity does not have to be related to any instances of the order entity. However, the reverse is not true in this database because an order must be related to a customer. Without a customer, an order cannot exist. An order is therefore an example of a weak entity, one that cannot exist in the database unless a related instance (a customer) of another entity is present and related to it. An instance of the customer entity can be related to zero, one, or more orders. However, an instance of the order entity must be related to one and only one customer. The 'zero' option is not available to a weak entity. The relationship between an instance of the order entity and an instance of the customer entity is therefore a mandatory relationship.

• Mandatory Relationship

  An order cannot exist without a customer, therefore
  The relationship between an 1)instance of the order entity and 2)an instance of the customer entity is a mandatory relationship.

Identifying weak entities and their associated mandatory relationships can be very important for maintaining the consistency and integrity of the database. Consider the effect, for example, of storing an order without knowing the customer to which it belongs. There would be no way to ship the item to the customer, and the company would lose business. By the same token, we typically specify the relationship between an order and the order lines (the specific items on the order) as mandatory because we do not want to allow an order line to exist in the database without it being related to an order. (An order line is meaningless without knowing the order to which it belongs.) In contrast, we can allow a merchandise item to exist in a database without indicating the supplier from which is comes (assuming that there is only one source per item). This lets us store data about new items before we have decided on a supplier. In this case, the relationship between a supplier and an order item is not mandatory (often described as zero-to-many rather than one-to-many).
The next lesson describes optional participation in relationships.