ER Diagrams   «Prev  Next»

Lesson 13Mandatory and optional participation continued
ObjectiveDescribe optional participation in table relationships

Optional Participation in 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.
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 (one-to-many) 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. 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. It is mandatory that one instance of CUSTOMER exist. That is why CUSTOMER has mandatory participation.[2]

Weak Entity Types

Entity types that do not have key attributes of their own are called weak entity types. In contrast, regular entity types that do have a key attribute are called strong entity types. Entities belonging to a weak entity type are identified by being related to specific entities from another entity type in combination with one of their attribute values. We call this other entity type the identifying or owner entity type, [3] and we call the relationship type that relates a weak entity type to its owner the identifying relationship of the weak entity type.[4] A weak entity type always has a total participation constraint (existence dependency) with respect to its identifying relationship because a weak entity cannot be identified without an owner entity. However, not every existence dependency results in a weak entity type. For example, a DRIVER_LICENSE entity cannot exist unless it is related to a PERSON entity, even though it has its own key (License_number) and hence is not a weak entity.

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. Mandatory and optional participation[5] 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.

Entity Patterns

As you gain experience in designing databases, you will start to notice little patterns that emerge. For example, the "many" side of a one-to-many relationship tends to be an optional entity much of the time. But much does NOT mean always, so do not get carried away and just ASSUME it is optional all the time. The interesting aspect about noticing patterns is that, when you find examples that break the pattern, they catch your eye. Always look into things that seem a bit atypical. A lot of mistakes are discovered in just this way.
Think of the Entity as an object.
  1. The Entity set represents a collection of similar entities.
  2. The Entity is similar to a class in object-oriented languages.
  3. The attribute represents a property of (the entities of) an entity set.
  4. Attributes are simple values, such as integers or character strings. Attributes are not structs or sets.

A beer has a 1) name and 2) manufacturer.
  1. Entity set Beers has two attributes, name and manf (manufacturer).
  2. Each Beers entity has values for these two attributes, for example (Bud, Anheuser-Busch)

ERD Relationship

A relationship connects two or more entity sets. It is represented by a diamond, with lines to each of the entity sets involved
beers-bars-relationship
  1. Bars sell some beers.
  2. Drinkers like some beers.
  3. Drinkers frequent some bars.

Relationship Set

  1. The current value of an entity set is the set of entities that belong to it.
    Example: The set of all bars in our database.
  2. The value of a relationship is a relationship set, a set of tuples[6] with one component for each related entity set.

Entity Abstraction

Entity Abstraction is a design pattern, applied within the design paradigm which provides guidelines for designing reusable services whose functional contexts are based on business entities.
The automation of a business process involves the analysis of the business domain and then designing solution logic that represents the different steps within the business process. Some of these steps relate just to that specific business process while others may be of use to other business processes as well. Part of this reusable logic pertains to the business entities that usually remains the same when compared to the rules and processing steps that may change in future. If services are designed that contain both
  1. process-specific logic and
  2. entity-specific logic,
the chances of reusing the same entity-specific logic, from another business process, become somewhat negligible.
If this kind of logic is split up into a separate container, then any new business processes, which make use of the same business entity, can reuse this logic. Apart from the reusability problem, in order to address the change in the behavior of a business entity, updating the entrenched entity related logic across multiple business processes requires extra efforts and makes the maintenance of such services a complex task. The Entity Abstraction pattern advocates that logic that relates to the processing of business entities be separated from the process-specific single purpose logic and designed as independent logic, which has no knowledge of the overall business process in which such logic is being utilized.

Entity Abstraction Pattern

In the context of an Entity-Relationship Diagram (ERD), the "Entity Abstraction Pattern" refers to a design strategy where common attributes, relationships, or behaviors are extracted from multiple entities and consolidated into a single, more abstract entity. This abstract entity then serves as a parent or superclass for the more specific entities, which inherit the properties and relationships defined in the abstract entity.
The purpose of the Entity Abstraction Pattern is to reduce redundancy, improve maintainability, and promote reusability within the data model. By abstracting shared characteristics into a separate entity, changes to these characteristics can be made in a single location, rather than having to update each individual entity that shares the same attributes.
For example, consider a university ERD with separate entities for students, faculty, and staff. These entities may share common attributes such as name, date of birth, and contact information. Using the Entity Abstraction Pattern, a new abstract entity called "Person" could be created, containing these shared attributes. The student, faculty, and staff entities would then inherit from the Person entity, reducing redundancy and simplifying the overall data model.

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
[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] owner entity type: The identifying entity type is also sometimes called the parent entity type or the dominant entity type.
[4] weak entity type: The weak entity type is also sometimes called the child entity type or the subordinate entity type.
[5] 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.
[1]tuples: In the lexicon of relational database design another word for rows or records.

Ad Relational Database Design, Implementation