Cardinality in DBMS: Definition, Types [GATE Notes]

Q: What is cardinality in DBMS?

Cardinality in DBMS usually represents the relationship between the data of two different tables by highlighting how many times a specific relation entity occurs compared to another. For example, the database of the student may show that the student enrolled on multiple courses.

What is Cardinality in DBMS?

The cardinality in DBMS is most useful in describing binary relation sets, but it can also help to describe relation sets with more than two entity sets. Cardinality in databases describes the connections between the data in two database tables.

Define Cardinality in DBMS

The cardinality in DBMS refers to the number of entities to which another entity can be linked via a given relation set. It shows the number of times instances of one thing are connected to another.

Let us see the various cardinalities that exist in DBMS. We will only consider binary relation sets here, which means we will look for the relationship between entity sets E₁ and E₂ for the set R.

Download Formulas for GATE Computer Science Engineering - Discrete Mathematics

Types of Cardinality in DBMS

4 different relations exist in DBMS. The cardinality in DBMS that is possible for binary relationships is as follows:

One to One(1: 1)
One to Many(1: M)
Many to One(M: 1)
Many to Many(M: M)

One to One(1: 1)

A one-to-one relationship exists when only one instance of an individual is connected with, at most, one entity of another (same) entity set via relationship instance. An entity(tuple) in E₁ is associated with at most one entity(tuple) in E_2, and an entity in E₂ is associated with at most one entity in A.
For example, in the following ER diagram, entity E₁ and entity E₂ have one-to-one mapping on the relation R. The candidate key for the entity set E₁ is A, and for the entity set E₂, B is the candidate key.

The candidate keys of relation R(A, B) = A, B

One to Many (1: M)

A one-to-many relationship exists when only one instance of an entity is connected with many entities of another entity set via relationship instances. An entity(tuple) in E₁ is associated with zero or more entities in E_2, but an entity in E₂ can be associated with at most one entity in E₁.

For example, in the following ER diagram, entity E₁ and entity E₂ have the one-to-many mapping on the relation R. The candidate key for the entity set E₁ is A, and for the entity set E₂, B is the candidate key.

Candidate key of R (A, B) = B

Download Formulas for GATE Computer Science Engineering - Algorithms

Many to One (M: 1)

A many-to-one relationship exists when more than one instance of the entity set is on the left, and only one instance of the entity set on the right is associated with the relationship. An entity(tuple) in Ę₂ is associated with zero or more entities in E₁ but an entity in
E₁ can be associated with at most one entity in E₂.

For example, in the following ER diagram, the entities E₁ and E₂ have the many-to-one mapping on the relation R. The candidate key for the entity set E₁ is A, and for the entity set E₂, B is the candidate key.

Candidate key of R (A, B) = A

Many to Many (M: M)

A many-to-many relationship exists when more than one instance of the entity set is on the left and more than one entity of another entity set is on the right associated with the relationship. An entity(tuple) in E₂ is associated with zero or more entities in E_1, and an entity(tuple) in E₁ is associated with zero or more entities in E₂.

For example, in the following ER diagram, entity E₁ and entity E₂ have many-to-many mapping on the relation R. The candidate key for the entity set E₁ is A, and for the entity set E₂, B is the candidate key.

Candidate key of R(A, B) = AB

Important GATE Topics
Work Done By A Force	Motion Under Gravity
Dynamic Resistance	Static Resistance
Ideal Diode	Bettis Theorem
Work Done By A Constant Force	Application Layer Protocols
Castigliano's Theorem	Portal Frames

FAQs on Cardinality in DBMS

What is cardinality in DBMS?

Cardinality in DBMS usually represents the relationship between the data of two different tables by highlighting how many times a specific relation entity occurs compared to another.

For example, the database of the student may show that the student enrolled on multiple courses.

What are the possible cardinalities in DBMS?

The cardinality ratios that are possible for binary relationships are:

One to One(1 : 1)
One to Many(1 : M)
Many to One(M : 1)
Many to Many(M : M)

Is cardinality in DBMS important?

Cardinality is important because it creates relations from one table or entity to another in the structured manner. As a result, cardinality significantly impacts the query execution plan. A query execution plan is a sequence of steps users can take to access data and search for the data stored in a database system. Therefore having a well-structured query execution plan can make it easier for users to quickly locate the data they need.

What is meant by index cardinality in DBMS?

The index cardinality in DBMS is the total number of distinct key values for each index in the relation.

Are many to one and one to many cardinality in DBMS is same?

The many to one and one to many cardinality in DBMS is not the same as one-to-many relationships exist when only one instance of an entity is connected with many entities of another entity set via relationship instances. On the other hand, a many-to-one relationship exists when more than one instance of the entity set is on the left. Only one instance of the entity set on the right is associated with the relationship.

Cardinality in DBMS