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.
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.
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.
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:
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:
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.
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:
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.