Relationship Constraints in DBMS –

There are three Types of Relationship Constraints-

  1. Structural Constraints
    •  Participation Constraints
    • 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 –

Participation Notations-Relationship Constraints in DBMS

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.
Participation Example-Relationship Constraints in DBMS

                            (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 –

Cardinality Notations-Relationship Constraints in DBMS

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
One to One Cardinality Example-Relationship Constraints in DBMS

One to one Cardinality-Relationship Constraints in DBMS

 

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.

one to many Cardinality Example -Relationship Constraints in DBMS

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.

Many to One Cardinality Example-Relationship Constraints in DBMS

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.

Many to Many Cardinality Example-Relationship Constraints in DBMS

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.


Constraints and cardinality - Relationship Constraints in DBMS

Relation Between Cardinality and Joins

Cardinality means it is a relation between tables using joins which tells that how many rows of one table will match with rows in other tables when these tables are joined.
In other words Relationships between the tables define Cardinality while explaining how each table links to the other.

 

Example :

Consider the two tables Consumer(custID) and Bill(BID, FK_custID). Each consumer pays the electricity bill every month. So, a customer will be associated with 12 bills after one year. It means there is one to many relationship between consumer and the bills he paid.  In this case, the cardinality is defined from primary key of the Customer table(PK_Custid) to the foreign key (FK_Custid) of the Bill table.

The number of same rows in both the tables depends upon which join is used.


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

     

Incoming search terms:

  • structural constraints in dbms
  • mapping constraints
  • structural constraints in database
  • mapping constraints in dbms
  • structural constraints definition in dbms
  • roles and structural constraints in dbms
  • mapping constraint in dbms
  • structural constraints
  • mapping in dbms with example
  • structural constraint in dbms

Leave a Reply