Database Cardinality | Database Cardinality with Real Examples

In my previous articles I have given some essence of Database cardinality in data modeling articles. In this article I would like to give you detailed information on database cardinality with its definition and examples as well.The first question in users mind is what exactly the cardinality means?The simple meaning of cardinality is nothing but the number of elements in specific set or any other grouping. The cardinality in database also means the same. In simple words if user wants to check higher cardinality in the specific data then user needs to check for more distinct values. The lower cardinality always contains more duplicate or repetitive values.

There are two types of Database Cardinality :

1.Database Cardinality in terms of Data Model

2.Database Cardinality in terms of Query Tuning

Database Cardinality for Data Model / Database Design :

In this section i would like to give you details about cardinality of database used in different data models.

Definition :

The database cardinality is nothing but the relationship between two or more tables.

There are three types of cardinalities :

1.One to One relationship

2.One to Many relationship

3.Many to One relationship

4.Many to many relationship

This is nothing but the degree of relationships between the table. These relationships are mostly used to create different data models in database.This is one of the type of database design technique.

Type 1 : One to One Relationship

In this type of entity the occurrence of one entity will be directly in relationship with only one occurrence of another entity. There should not be more than one occurrences for each entity.

Example :

Student is allocated to only one hostel room. So there is always one -to- one relationship between Student–> Hostel Room.

Type 2 : One to many relationship

In this type one occurrence of entity is always related to more than one occurrences (many) to other entity .

Example :

If one Student has to complete exams for more than one subject. That relationship is always One to many relationship

Type 3 : Many to One relationship

In this type many occurrences of one entity is always related to one occurrence of other entity.

Example :

If multiple employees needs to allocated on One Project then that relationship is always Many to one relationship.

Type 4 : Many to many relationship

In this type many occurrences of one entity is always related to more than one occurrences to other entity.

Example :

Many Students needs to complete many exams so that relationship is always many to many relationship.

The star schema is always having one to many relationship between the tables.

Zero Cardinality or Optional Relationship :

Sometimes there is the situation where the relationship between two occurrences are optional. It is called as Optional relationship or Zero cardinality.

Example :

If Employee has to assign with department but if there is newly joined employee who still do not assigned to department. It is called as Optional relationship.

Database Cardinality for SQL Statements / Query Optimization :

In terms of SQL statement cardinality is nothing but the uniqueness of values. There is way to calculate cardinality in SQL statements which is :

Select distinct Count(column_name) from Tablename;

Definition of Database Cardinality for SQL Statements :

The database cardinality is nothing but the uniqueness of values in SQL tables which helps to determine Query plan for performance optimization.

The higher the cardinality in database the lower the duplicate values in the database.There are three types of SQL statement cardinality :

Type 1 : Higher Cardinality

If the specific table contains very less duplicate values or mostly it is unique values then it is called as higher cardinality columns. Unique index will help to improve performance of this column.

Example :

Typically the cellphone numbers,Email address,User_names are the columns which are mostly unique. These columns are called as Higher cardinality columns.

Type 2: Normal Cardinality

If the specific table contains somewhat duplicate values and other are unique values then it is called as normal cardinality columns.Normal indexes are helpful for normal cardinality column.

Example :

There are some columns where address is duplicated or name is same. These columns needs to be considered as normal cardinality columns.

Type 3 : Lower Cardinality:

If the specific table contains lot of duplicate values then it is called as lower cardinality columns.Bit-map indexes helps to improve performance of these columns.

Example :

There are so many columns like Gender,joining year which contains repetitive duplicate values.

These are two types of Database Cardinality. I hope you get nice and sufficient information about database cardinalities with multiple examples. If you have any issues and concerns with the same kindly comment in comments section.