is optionality (mandatory, optional) and participation (total, partial) are same?

As i know optionality means the minimum cardinality of a relationship which is denoted as optional to optional, mandatory to optional, mandatory to mandatory..

And Participation denoted as bold line and a normal line.

In the Internet some refer participation as the dependency of the entity to the relationship which is also looks like identifying and non identifying relationship.

and some refer it as the minimum cardinality

What is the correct definitions of those relationships and what is the difference..


Let's start with definitions and examples of each of the concepts:

Total and partial participation:

Total participation (indicated by a double or thick association line) means that all the entities in an entity set must participate in the relationship. Partial participation (indicated by a single thin line) means that there can be entities in the entity set that don't participate in the relationship.

Total and partial relationship

Medicine participates totally in the Produce relationship, meaning that Medicine can't exist unless Produced by a Laboratory. In contrast, a Laboratory can exist without Producing Medicine - Laboratory participates partially in the Produce relationsip.

Mandatory and optional roles:

In a relationship, roles can be optional or mandatory. This affects whether a relationship instance can exist without an entity in a given role. Mandatory roles are indicated with a solid association line, optional roles are indicated with a dotted line.

Mandatory and optional entities

Roles aren't often talked about in database tutorials, but they're an important concept. Consider a marriage - a relationship with two mandatory roles filled by the same entity set. In most relationships, the entity sets also define the roles, but when an entity set appears multiple times in a single relationship, we distinguish them in different roles.

In the example above, a Patient can Purchase Medicine with or without a Prescription. A Purchase can't exist without a Patient and Medicine, but a Prescription is optional (overall, though it may be required in specific cases).

Identifying relationship / weak entity:

A weak entity is an entity that can't be identified by its own attributes and therefore has another entity's key as part of its own. An identifying relationship is the relationship between a weak entity and its parent entity. Both the identifying relationship and the weak entity are indicated with double borders. Weak entity sets must necessarily participate totally in their identifying relationship.

Identifying relationship / weak entities

In this example, a Prescription contains LineItems which are identified by the Prescription's key and a line number. In other words, the LineItems table will have a composite key (Prescription_ID, Line_Number).

For examples of non-identifying relationships, see the previous examples. While Medicine participates totally in the Produce relationship, it has its own identity (e.g. a surrogate key, though I didn't indicate it). Note that surrogate keys always imply regular entities.

Mandatory/optional vs total/partial participation

Mandatory or optional roles indicate whether a certain role (with its associated entity set) is required for the relationship to exist. Total or partial participation indicate whether a certain relationship is required for an entity to exist.

Mandatory partial participation: See above: A Laboratory can exist without producing any medicine, but Medicine can't be Produced without a Laboratory.

Mandatory total participation: See above: Medicine can't exist without being Produced, and a Laboratory can't Produce something unspecified.

Optional partial participation: See above: A Prescription can exist without being Purchased, and a Purchase can exist without a Prescription.

That leaves optional total participation, which I had to think about a bit to find an example:

Optional total participation

Some Patients Die of an unknown Cause, but a Cause of death can't exist without a Patient Dying of it.

Total/partial participation vs identifying/non-identifying relationships

As I said before, weak entity sets always participate totally in their identifying relationship. See above: a LineItem must be Contained in a Prescription, it's identity and existence depends on that. Partial participation in an identifying relationship isn't possible.

Total participation doesn't imply an identifying relationship - Medicine can't exist without being Produced by a Laboratory but Medicine is identified by its own attributes.

Partial participation in a non-identifying relationship is very common. For example, Medicine can exist without being Purchased, and Medicine is identified by its own attributes.

Mandatory/optional vs identifying/non-identifying relationships

It's unusual for a relationship to have less than two mandatory roles. Identifying relationships are binary relationships, so the parent and child roles will be mandatory - the Contain relationship between Prescription and LineItem can't exist without both entities.

Optional roles are usually only found on ternary and higher relationships (though see the example of patients dying of causes), and aren't involved in identification. An alternative to an optional role is a relationship on a relationship:

Associative entity

By turning Purchase into an associative entity, we can have it participate in a Fill relationship with Prescription. To maintain the same semantics as above I specified that a Purchase can only Fill one Prescription.

Physical modeling

If we translate from conceptual to physical model (skipping logical modeling / further normalization), making separate tables for each entity and relationship, things look pretty similar, though you have to know how to read the cardinality indicators on the foreign key lines to recover the ER semantics.

Physical model examples

However, it's common to denormalize tables with the same primary keys, meaning one-to-many relationships are combined with the entity table on the many side:

Denormalized died relationship

A relationship is physically represented as two or more entity keys in a table. In this case, the entity keys - patient_id and cause_of_death_id are both found in the Patient table. Many people think the foreign key line represents the relationship, but this comes from confusing the entity-relationship model with the old network data model.

This is a crucial point - in order to understand different kinds of relationships and constraints on relationships, it's essential to understand what relationships are first. Relationships in ER are associations between keys, not between tables. A relationship can have any number of roles of different entity sets, while foreign key constraints enforce a subset constraint between two columns of one entity set. Now, armed with this knowledge, read my whole answer again. ;)

I hope this helps. Feel free to ask questions.