Cardinality in DBMS refers to the number of times an entity of an entity set participates in a relationship set. It is a vital piece of information about a relation between two entities. In the context of databases, cardinality is used to create links between entities that portray meaningful relationships among entities. Cardinality is applied to databases for a variety of reasons, and it has a significant impact on query execution.
Cardinality can be of different types, including one-to-one, one-to-many, many-to-one, and many-to-many. One-to-one cardinality mapping means that each entity in the first set is linked to only one entity in the second set, and vice versa. One-to-many cardinality mapping means that each entity in the first set is linked to one or more entities in the second set, but each entity in the second set is linked to only one entity in the first set. Many-to-one cardinality mapping means that each entity in the second set is linked to one or more entities in the first set, but each entity in the first set is linked to only one entity in the second set. Many-to-many cardinality mapping means that each entity in the first set is linked to one or more entities in the second set, and each entity in the second set is linked to one or more entities in the first set.
Cardinality is also used in the context of query optimization, where it refers to the uniqueness of data values that are contained in a column. High cardinality means that the column contains a large percentage of totally unique values, while low cardinality means that the column has a lot of "repeats" in its data range. This statistic helps with planning queries and optimizing the execution plans.
