Constraints on a Relationship Type Cardinality Ratio and Participation || Lesson 14 || DBMS ||

Here, we will try to understand the constraints on the relationship type Cardinality Ratio and Participation.

The discussion on Relationship Type

There are two types of constraints on a relationship type Cardinality Ratio and Participation.
Cardinality Ratio and Participation:
Cardinality Ratio:

The cardinality ratio of a relationship specifies the maximum number of relationship instances that an entity can participate in.

Who will provide those constraints?

Those constraints may be provided in the requirements or documentation.

If not provided in the documentation, we have to question the company employees to determine the constraints

Considering our controls relationship type between the Department and the project entity the constraints were not provided in the documentation.

Finally, after a thorough discussion with the company employees, the following constraints were obtained.


A department can control several projects and a project is controlled by only one department.


D1, D2, D3, D4, D5 are the entities of the department entity type, P1, P2, P3, P4, P5, P6, P7, P8 are the entities of the project entity type, R1, R2, R3, R4, R5, R6, R7, R8, R9, R10 are the relationship instances of the controls relationship type on which the department and the project entity type will participate in.

From the definition, The cardinality ratio of a relationship specifies the maximum number of relationship instances that an entity can participate in.

From the constraints gathered from the company employees, it states that A department can control several projects and a project is controlled by only one department.

Now, consider the constraint A department can control several projects and D3 entity. D3 participates in two relationship instances R3 and R4.

Therefore, the participation of D3 in R3 and R4 relationship instances is allowed.

Because The constraint states that a department can control several projects, so the maximum number of instances a department entity can participate in is N.

Similarly, consider the entity P6 of the project entity type.

The constraint about the project entity states that a project is controlled by only one department. But p6 is controlled by D2 and D3. The maximum number of instances a project entity has to participate in is 1, but P6 is participating in 2 relationship instances which is not allowed.

The cardinality ratio gives the maximum participation of an entity type on the relationship instances.

From the above constraints, the cardinality ratio of the Department: Project is 1: N. This is (one to many 1: N) relationship.

Three different types of cardinality ratios possible. One to One 1: 1, One: Many 1: N, and Many to Many M: N.

How the cardinality ratios are represented in the ER Diagram?
The participation of The Department entity type is shown near to the project and the participation of the projects is shown on the Department side.
Participation:

Similar to the Cardinality ratio, Participation specifies the minimum number of relationship instances that each entity can participate in.

The constraints related to minimum participation of the Department and the Project Entity type were provided below.

Each project must be under the control of a department, and some departments may control no projects.
From the definition, The participation of a relationship specifies the minimum number of relationship instances that an entity can participate in.

The constraints gathered from the company employees states that Each project must be under the control of a department, and some departments may control no projects.

Now, consider the constraint Each project must be under the control of a department and P1 entity.

The constraint states that Each project must be under the control of a department, but P1 is not under the control of any department. It is not allowed.

Similarly, Consider the constraint some departments may control no projects and D5 entity.

The constraint states that some departments may control no projects. D5 is not controlling any project. It is allowed.

How the participation is represented in the ER Diagram?

If all the entities in the entity type should be participated in at least one relationship instance, Similar to the case of the project entity type then it is called total participation.

It is represented by double lines in the ER Diagram.

If some of the entities may not participate in any of the relationship instances, Similar to the case of the Department entity type then it is called partial participation.

It is represented by a single line in the ER Diagram.

Cardinality ratio,one to one relationship,one to many relationshp,many to many relationshp,participation,total participation,partial pariticipation,dbms,leaning monkey,gate cse,gate dbms,placements preparation,