RelationalDBDesignRelationalDBDesign





ER Diagrams   «Prev  Next»
Lesson 13Mandatory and optional participation continued
ObjectiveDescribe optional participation in relationships.

Optional Participation in a database

Continuing with the language of tables to distinguish between mandatory and optional relationships, an optional relationship exists for a table when there is no requirement for any records to exist in that table before any records can be added to the associated table. Consider the following example, with a focus on the italicized portion:
A customer may place many orders; every order is placed by a customer.
According to this rule, a customer may place many orders, or place just one order, or never place an order at all (potential customers are often found in databases, as are potential suppliers).
All three possibilities are options for actual customers who exist in the Customers table. Put simply, it is not necessary for any records to exist in the Orders table before customers can be entered into the Customers table.
Reverting again to the language of entities, the type of participation for the ORDER entity in the CUSTOMER:ORDER relationship is designated as optional.
The optional entity is termed a weak entity.
To understand how important it is to know that CUSTOMER is a mandatory entity[1] and ORDER is a weak entity, consider once more the general rule for 1:N relationships:
A one-to-many (1:N) relationship exists when, for one instance of entity A, there exists zero, one, or many instances of entity B; but for one instance of entity B, there exists zero or one instance of entity A.
Now plug in the entities from the CUSTOMER:ORDER relationship, again focusing on the italicized portion:
A 1:N relationship exists when, for one instance of CUSTOMER, there exists zero, one, or many instances of ORDER; but for one instance of ORDER, there exists zero or one instance of CUSTOMER.
To accommodate the weak entity ORDER, the “zero” must be removed from the italicized portion; it is mandatory that one instance of CUSTOMER exist. That is why CUSTOMER has mandatory participation.[2]


Mandatory Participation in Database

Mandatory participation describes a relationship in which at least one record in a table must exist before any records can be added to its associated table. Entity Patterns
Mandatory and optional participation[3] are important considerations when establishing relationship constraints (discussed in the next module).
In a 1:1 relationship, if one entity has optional participation and the other has mandatory participation, the weak entity (the entity with optional participation) receives the key attribute from the mandatory entity to establish the link.
The following graphic illustrates the relationship between weak and mandatory entities with respect to the key attribute.

In a 1:1 relationship, if one entity has optional participation and the other has mandatory participation, the weak entity receives the key attribute from the mandatory entity to establish the link.
In a 1:1 relationship, if one entity has optional participation and the other has mandatory participation, the weak entity receives the key attribute from the mandatory entity to establish the link.

Mandatory Optional Participation - Exercise

Before moving on to the next lesson, click the Exercise link below to check your understanding of mandatory and optional relationships.
Mandatory Optional Participation - Exercise

Terms

[1]mandatory entity: In the language of relational database design, mandatory entity is another name for the entity that has mandatory participation in an entity relationship.
[2]mandatory participation: In the language of database design, participation describes the nature of the relationship between two tables.
[3]optional participation: In the language of database design, participation refers to the nature of the relationship between tables. Optional participation describes a relationship in which no records need to exist in a table before records are added to its associated table.