Cardinality in DBMS
Cardinality in DBMS is a vital piece of information about a relation between two entities. Cardinality of a relationship can be defined as the number of times an entity of an entity set participates in a relationship set. Cardinality shows how the entities are connected to each other. Types of cardinalities between tables are:
What is Cardinality?
Cardinality helps us identify the uniqueness of the data in database columns. Here Low cardinality denotes that there are a lot of redundancies(repeated values) in columns. High cardinality denotes that there are a lot of unique values in columns.
Why is Cardinality Important in Databases?
In databases, Cardinality is very important because it helps us to create links between entities. Cardinality has a significant impact on query execution. Query execution is a sequence of steps that users take to search and access the data stored in a database. Cardinality is applied to databases for a variety of reasons, however many businesses use the cardinality model to analyze the data in the database.
For example, consider an online grocery store where one table consists of all of its customer details. Consider another table that contains the shopping history of every customer. We know that every customer might have purchased multiple items, so the database administrator likely uses the One-to-Many cardinality relationship which would link each customer in the first table to all the purchases they made in the second table.
Types of Cardinality Ratios
There exists a few cardinality ratios which determine the cardinality in DBMS.
One-to-One cardinality (1:1)
In One-to-One cardinality, an entity in a set A relates to exactly one occurrence of another entity in set B.
In other words, one entity is related to only one another entity.
For example, let us consider two tables STUDENT and STUDENT_INFO. Let us consider the STUDENT table has a name column and STUDENT_INFO has a id_no column. Here, we could find the id_no of every for every student using these two tables. The relation between these two tables would be One-to-One.
One-to-Many cardinality (1
In One-to-Many cardinality, an entity in set A relates to many occurrences of another entity in set B. In other words, an entity in set A can be linked with any number of entities in set B while an entity in set B can be linked with only one entity in set A.
For example, let us consider two tables department table and an employee table. Here one department can contain many employees but one employee can be in one department only
Many-to-One cardinality (m:1)
In Many-to-One Cardinality, multiple occurrences of an entity in set A can relate to one occurrence of another entity in set B. In other words, an entity in set B can be linked with at most one entity in set A while an entity in set A can be linked with any number of entities in set B.
For example, let us consider two tables employee table and a department table. Here many employees can work for one department.
Many-to-Many cardinality (m
In Many-to-Many cardinality, multiple occurrences in an entity set can relate to multiple occurrences of another entity set. In other words, an entity in set A can link to any number of entities in set B while an entity in set B can link to any number of entities in set A.
For example, let us consider two tables, a student table, and the course table. Here a student can enroll in multiple courses and at the same time a course can contain multiple students
By this time, you might have got a good idea about Cardinality in DBMS, to quickly recollect what we discussed, let’s see a few points
- Cardinality portrays the relationship among entities
- Cardinality is used to link entities which portray meaningful relationships among entities
- In the context of Query optimization cardinality refers to the uniqueness of a column in a table
- As discussed above, mainly we have four types of cardinalities in DBMS.