What is Cardinality in DBMS?
Modern Relational Database Systems contain data objects in the form of Entities and some Relationships among those Entities. Relationships among the Entities are of different types depending upon the number of Entities taking part in the Relationship. There is another term that determines the type of Relationship among Entities in DBMS which is called Cardinality.
In Mathematics, Cardinality is defined as the number of elements in a given set. But, cardinality in DBMS refers to the Relationship which specifies how many times a particular Entity occurs in the Relationship. For example, if only one student has enrolled in exactly one course, this Relationship will be called a One-to-One Relationship.
Importance of Cardinality in DBMS
The importance of Cardinality lies in the fact that one table should be linked to another table in a structured manner so that accessing data becomes easier. Cardinality allows the Database Administrators to effectively analyze the linking between the two tables in the database. This assures the well-optimized query execution in the database system.
For example, there are two tables in the Database. One table represents the Students of a College and another Table represents the courses in which they are enrolled. Here, one student can enroll in many courses. Also, many students can enroll in a single course. Thus, this relationship can be represented as a Many-to-Many Relationship, which we will discuss later. This Relationship helps the Database Administrator to clearly understand how tables are linked with each other.
Types of Cardinality
The following types of Cardinality are found in a Database Model:
One-to-One (1:1)
One-to-One Cardinality is found when one occurrence of an Entity is associated with exactly one occurrence of another Entity. For example, if a person and passport are two identities, then one person can have exactly one passport and only one passport can be assigned to a person.
One-to-Many (1:N)
This cardinality in DBMS relates one occurrence of an Entity to multiple occurrences of another entity. One-to-Many Cardinality can be observed in the database when one row of a table is related to many rows of another table. For example, one teacher can teach many students at a time. But, many teachers cannot teach a single student at a time. Hence, this is One-to-Many Cardinality.
Many-to-One (N:1)
When Many occurrences of an Entity are associated with a single occurrence of another Entity, it is termed a Many-to-One Cardinality in DBMS. This means that many rows of a table are related to a single row of another table. An example of this Cardinality is surgeries done by a doctor. Many surgeries can be performed by a single doctor. But one surgery cannot be performed by many doctors at a time.
Many-to-Many (N: N)
This cardinality in DBMS is found when multiple occurrences of one Entity are related to Multiple Occurrences of another Entity. In other words, multiple rows of a table are related to multiple rows of another table. For example, in an IT company, many software engineers can work on many projects, and many projects can be handled by multiple people in the company.
High Cardinality and Low Cardinality
The extent of cardinality is described by the number of unique values that exist in the database. High Cardinality refers to the higher number of unique values in the table while low cardinality means less number of unique values in the table.
In other words, if there are lower numbers of unique values in the dataset, there are more chances that the value will be repeated. Thus Cardinality is low. On the other hand, if there are more unique values in the dataset, there are more chances that the values will vary. Thus Cardinality is high.
This high and low cardinality score helps in query optimization. If there are more unique values, the query will execute faster due to the repetition of data. While if there are non-unique values in the table, the query may return data that is different from the desired one. Thus, you need to run queries again and again until you get the correct data, which will take more time. Thus Cardinality in DBMS helps in optimizing Data retrieval.