In this article, we will go through all the mapping constraints in dbms. We will see different mapping cardinalities (one-to-one, one-to-many, many-to-one, many-to-many) and existing dependencies in ER relationships.

An E-R enterprise schema defines certain constraints to which the contents of a database must conform. These constraints are determined from the miniworld situation that the relationships represent. In this section, we will study about two most important types of mapping constraints in dbms i.e. (i) Mapping Cardinalities and (ii) Existence Dependencies.

1. Mapping Cardinalities in DBMS

The mapping cardinalities or the cardinality ratio represents the number of relationship instances that an entity can participate in, i.e. the number of entities to which another entity can be associated via a relationship set. In other words, number of entities in one entity set which can be associated with the number of entities of other set via relationship set. Mapping cardinalities are most useful in describing binary relationship sets.

For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following –

• One to One (1:1)

  An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A.

  For example: If Employee and Department are 2 entity sets, then we can define a one to one relationship ‘Manages’ between an Employee entity and a Department entity i.e. one employee manages at most one department and one department is managed by at most one employee.

  The 1:1 relationship ‘Manages’ that shows total participation of Department entity set and partial participation of Employee entity set.

• One to Many (1: M)

  An entity in A is associated with many entities of B, and an entity in B can be associated with atmost one entity in A.

  For example: A 1:M relationship ‘Works-In’ can be defined between entity sets, Department and Employee i.e. one department can be associated with any number of employees, but an employee works in only one department.

  A 1:M relationship between Department and Employee entity sets.

• Many to One (M: 1)

  An entity in A is associated with atmost one entity in B. and an entity in B can be associated with any number of entities in A.

  For example: A M:1 relationship studying-in between two entity sets, student entity set and a class entity set i.e. A student can belongs to atmost one class but one class can have many students.

  A M:1 relationship between Student and Class entity sets.

• Many to Many (M: M)

  An entity in A is associated with any number of entities in B and an entity in B is associated with any number of entities in A.

  For example: Consider 2 entity sets Employee and Project. We can define a many to many relationship as ‘Works-In’, which states that an employee can work in many projects and a project can have many employees working in it.

  An M: M relationship

  The cardinality ratio of a relationship affects the placement of relationship attributes. Attributes of one to one or one to many relationship sets can be associated with the participating entity sets, rather than with the relationship set. The choice of attribute placement is clearer for many-to-many relationship sets.

  Considering the above example, if we want to have the No-of-days an employee works on a project, to be stored in our E-R schema, then, this attribute No-of-days will be either part of relationship works-in or part of Employee or part of Project.

  So, if No-of-days were an attribute of Employee, then, we can’t determine as to which Employee worked for the No-of-days on the project since a project is associated to many employees.

  Hence, if we are to express the number of days (No-of-days) an employee worked on a project, then, this attribute has to be placed along with the relationship ‘Works-In’, rather than with any of the participating entity sets.

Now, let’s see another mapping constraints in dbms – Existence Dependencies.

2. Existence Dependencies

Existence dependencies are another important class of mapping constraints in dbms.

If the existence of entity X depends on the existence of entity Y, the X is said to be existence dependent as Y. So, operationally if Y is deleted, so is X.

Entity Y is said to be a Dominant Entity and X is said to be a Subordinate entity.

For example: In case of 2 entity sets Invoice and Invoice-Payment, an entity in Invoice-Payment is existence dependent on an entity in Invoice, since an Invoice-payment can’t be there without an invoice already existing in Invoice Entity Set.

If an invoice entity is deleted, all its corresponding payment entities from the Invoice-Payment entity set is deleted, but vice-versa is not true.

Total Participation

The participation of an Entity Set E in relationship set R is said to be total if every entity in E participates in at least one relationship in R.

Partial Participation

If only some entities of E participate in R, then the participation of entity set E in R is said to be Partial. Total participation is closely related to existence dependency.

For example: In case of Invoice-Payment entity set, since all the entities are related to some or the other invoice, the participation of Invoice-Payment Entity Set is total in ‘Payment’ relationship, whereas the participation of Invoice entity set is Partial, since an invoice can exist without having any Invoice-Payments against it. Hence, the Invoice Entity Set has partial participation in the relationship ‘Payment’.

That’s end of tutorial on mapping constraints in dbms.