DBMS Cardinality

In DBMS you may hear cardinality term at two different places and it has two different meanings as well.

In Context of Data Models:
In terms of data models, cardinality refers to the relationship between two tables. Relationship can be of four types as we have already seen in Entity relationship model:

One to One – A single row of first table associates with single row of second table. For example, a relationship between person and passport table is one to one because a person can have only one passport and a passport can be assigned to only one person.

One to Many – A single row of first table associates with more than one rows of second table. For example, relationship between customer and order table is one to many because a customer can place many orders but a order can be placed by a single customer alone.

Many to One – Many rows of first table associate with a single row of second table. For example, relationship between student and university is many to one because a university can have many students but a student can only study only in single university at a time.

Many to Many – Many rows of first table associate with many rows of second table. For example, relationship between student and course table is many to many because a student can take many courses at a time and a course can be assigned to many students.

In Context of Query Optimization: In terms of query, the cardinality refers to the uniqueness of a column in a table. The column with all unique values would be having the high cardinality and the column with all duplicate values would be having the low cardinality. These cardinality scores helps in query optimization.