Tutorial – Cardinality in SQL

Cardinality in SQL identifies the most optimized query plan in the given query. Read on the article to learn about Cardinality in SQL

In a database management system, the cardinality of the database or a record means the uniqueness of the data in a specific column. Cardinality plays a vital role in DBMS in maintaining the integrity of the database and preventing the database from storing duplicate or redundant data. In simple words, cardinality is defined as the maximum number of participants of an entity in a relationship, OR cardinality is defined as the total number of tuples (rows) that participated in a relationship. Cardinality in SQL databases is used to identify a given query’s most optimized query plan.

Mapping Cardinality:

In DBMS,  Mapping cardinality is also known as cardinality ratio. This denotes the number of entities to which another entity is connected to a related set. Mapping cardinality is helpful in binary relation sets, although it can be used for relations with more than two entities. 

Must Read: SQL SELECT

Must Read: SQL LIMITS

Types of Cardinality:

The cardinality in the SQL database is of four types. The entities can be mapped in the following ways:

• One-to-One
• One-to-Many
• Many-to-One
• Many-to-Many

One-to-One:

In One-to-One mapping cardinality,  one entity of first relation R1 is connected or associated with at most one entity of second relation R2, or vice versa. 

Also explore – Introduction to Data Analysis Using SQL

Example:

The relationship between a student and their Unique Identity is an example of a one-to-one relationship. A student can have only one roll no.[Unique ID].

One-to-Many:

In One-to-Many cardinality, one entity of first relation R1 can be associated with multiple entities of second relation R2. Or One entity in the second relation R2 can be associated with at most one entity in the first relation R1. 

Example:

The relationship between college and faculty is an example of a one-to-many relationship. A single college can have multiple faculties. 

Many-to-One:

In Many-to-one cardinality, an entity set of first relation R1 is associated or connected with at most one entity of second relation R2. In contrast, second relation R2 can be associated with multiple entities of first relation R1. 

Example:

The relationship between college and faculty is also an example of a many-to-one relationship. Multiple faculties work in a single college.

Many-to-Many:

In many-to-many cardinality, multiple entities of first relation R1 are associated with multiple entities of second relation R2, and vice versa, i.e., multiple entities of second relation R2 are associated with multiple entities of first relation R1.

Many-to-Many cardinality is equal to the addition of Many-to-one and One-to-many.

Example:

The relationship between students and faculties is an example of a many-to-many relationship. Multiple faculties teach multiple students. 

Must Read – SQL Tutorial for Beginners

Values of Cardinality:

In SQL databases, cardinality means the uniqueness of data present in the columns of a table when working with columnar data sets or with query optimization. There are three types of cardinality, commonly known as values of cardinality. So, those three types/values of cardinality are:

• High Cardinality
• Normal Cardinality
• Low Cardinality

High Cardinality:

High cardinality means all the values present in columns are uncommon and unique. High cardinality columns generally contain user_ids, user_name, email address, or any identification number. Whenever a new user is added to the system, it must have a unique user_id, so this column contains high uncommon values and hence has High cardinality.

Must Read: Introduction to SQL

Must Read: What is the difference between SQL and MySQL?

Normal Cardinality:

Average cardinality refers to the columns that have some unique values. Columns with Normal cardinality generally consist of Name, Street_address, etc. While adding a new user, there is a chance that their name is already present in the column as two persons can have the same name. So, columns with such data having somewhat uncommon values are referred to as columns with Normal cardinality. 

Low Cardinality:

Low cardinality refers to the column that has very few uncommon values. Columns with Low cardinality generally consist of binary values, such as Gender, or a column with a Yes or No choice only. So, columns with such data are referred to as low cardinality.