Relationship Constraints in DBMS-(ER Model Part-4)

 Relationship Constraints in DBMS – There are three Types of Relationship Constraints-

1. Structural Constraints
   1.  Participation Constraints
   2. Cardinality Ratio
2. Overlap Constraints
3. Covering Constraints

Structural Constraints are applicable for binary relationships and Overlap and Covering Constraints are applicable for EERD(Extended ER Diagrams).

Participation (or) Optionality Constraints-

Participation concerns with the involvement of entities in a relationship. It specifies whether the existence of an entity depends on another entity. There are two types of Participation Constraints –

1. Total/Mandatory Participation
2. Partial/Optional Participation

Notations of Different Types of Participation In ER Diagram –

Total/Mandatory Participation or Existence Dependency-

Participation is said to be total if every entity in E participates in atleast one relationship in R (or) every entity in entity set must depend on another entity. For example, every department will have a startup date as a department was created on that startup date (SINCE ATTRIBUTE of MANAGE relationship). and that department is being handled from that date through a Manager. So, the participation of DEPARTMENT entity in the “MANAGE” relationship type is total. Total Participation is also known as Existence Dependency. In ER Diagram, it is represented as a Double Line, connecting the participating entity to the relationship.

Partial/Optional Participation-

Participation is said to be partial if only some entities in E participate in relationships in R (or) some entities in entity set are depend on some another entities in entity set. For example, It is not necessary that all employees manage some department Because all employees may not be the Manager . So the participation of “EMPLOYEE” entity in the “MANAGES” relationship type is partial.

                            (Figure-1)

Cardinality/Mapping Cardinality-

Cardinality expresses the number of entities to which another entity can be associated via a relationship set (or) It specifies the number of relationship instances that an entity can participate in a relation set. There are 4 types of Cardinality Ratios :

• One-to-One Cardinality (1:1)
• One-to-Many Cardinality (1:m)
• Many-to-One Cardinality (m:1)
• Many-to-Many Cardinality (m:n)

Notations of Different Types of Cardinality In ER Diagram –

One-to-One Cardinality (1:1) –

An entity in set A is associated with atmost one entity in B, and an entity in B is associated with atmost one entity in A. This type of cardinality is referred as one to one Cardinality. For example, an Employee as a  Manager manage only one Department  and the vice versa is also true as a department have only one Manager  

Create Table MANAGE
( SSN varchar(10),
  DNO varchar(15),
  Primary key(SSN),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "MANAGE" relationship - SSN,DNO

One to Many Cardinality (1:M) –

An entity in A is associated with any number (0 or more) with an entity B, but a entity in B, however can be associated with atmost one entity in A. For example, An employee as a Manager can manage more than one Department.

Create Table MANAGE
( SSN varchar(10),
  DNO varchar(15),
  SINCE date,
  Primary key(DNO),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "MANAGE" relationship - DNO

Many to One Cardinality (M:1) –

An entity in A is associated with atmost one entity in B. An entity in B, however, can be associated with any number (0 or more) of entities in A. For example, An Employee can work only for one Department, But each Department can have 0 or more employees.

Create Table WORKS_ON
( SSN varchar(10),
  DNO varchar(15),
  SINCE date,
  Primary key(SSN),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "WORKS_ON" relationship - SSN

Many to Many Cardinality (M:N) –

An entity in A is associated with any number (0 or more) of entities in B, and an entity in B is associated with any number( 0 or more) of entities in A. For example, An Employee can works on several Projects and a Project may have several Employees.

Create Table WORKS_ON
( SSN varchar(10),
  DNO varchar(15),
  SINCE date,
  Primary key(SSN,DNO),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "WORKS_ON" relationship - SSN PID

Some Points regarding Cardinality and Participation –

1. Minimum Cardinality = 0, for partial participation and equal to 1 for
   total participation.
2. Maximum Cardinality = n, if one entity occurrence relates to multiple
   entity occurrences.
3. Minimum Cardinality give partial or total participation.
4. Maximum Cardinality give the maximum number of entities that is related
   to.

---

Previous	Home	Next
Types of Relationships in ER Diagram		Entity and its Types

---

]]>