Cardinality in structured query language (SQL) can be defined as the mean of unique data value present in certain column. Cardinality is also known as data cardinality or attribute of database table. There are three types of data cardinality
- High Data Cardinality
- Normal Data Cardinality
- Low Data Cardinality
High Data Cardinality
High data cardinality can be defined as the instance at which the values present in the data column are unique. For example consider a table which contains the details of employees. The table will contain various fields like employee id, name, salary, designation and department. All fields except employee id may have same record but employee id is unique to every employee. Such instance of having unique value for every record is called as high data cardinality. Records like email id, username and social security number are examples for high data cardinality.
Normal Data Cardinality
Normal data cardinality can be defined as the instance of having uncommon values in the data column. In normal data cardinality repeated data may be a rare occurrence but they are not entirely unique. For example: an employee table with a field containing the last name of the employees may look unique but it is possible that the field may several instances of having same last name. If the common last names are combined with unique first name then it will result in a unique value such value occurrences are called as normal data cardinality.
- SEE: SQL GRANT Command
Low Data Cardinality
Low data cardinality can be defined as the instance having usual values inside the data column. The tables which have low data cardinality use very minimum values. The table which uses boolean algebra is an example for low data cardinality.
Data cardinality is a valuable resource used to determine the relationship between two entities in a data modelling. The types of data cardinality in defining relationship are explained below
Link cardinality is an instance of relationship where one side does not need the other
Sub-type cardinality is an instance of relationship which has only one optional side.
- Related: SQL Three Valued Logic
Physical Segment Cardinality
In physical segment cardinality, the relationship between both the entities is mandatory.
Possession cardinality is an instance in which both entities have relationship with several other entities.
The relationship between one entity and several other entities is called as child cardinality. It is the most popular relationship used in the database.
In characteristic cardinality one to many relationship is mandatory between both sides.
The relationship is mandatory for only one entity but relationship between many entities are also allowed.
The relationship between many entities is declared as optional in association cardinality.
How to Determine the Data Cardinality of an Executed Query?
Using the following ways data cardinality of an executed query can be determined.
- Execution plan property
- XML Execution Plan
Execution Plan Property
Execution plans will be enabled while executing the queries. One can right click the mouse or use F4 to select the execution plan properties. From the executional plan properties select CardinalityEstimationModelVersion. This will show the data cardinality value of particular query.
XML Execution Plan
First enable the execution plan by using ctrl+M. Then right click on the execution plan will popup a menu which contains a tab for “Show Execution Plan XML”. After selecting Show Execution Plan XML; a window will be opened. The window will contain a tab named “CardinalityEstimationModelVersion” which can be used to estimate the data cardinality of an executed query.
What is the Cardinality of a Primary key?
The cardinality of a primary is similar to number of rows present in a column. Consider that we have a primary key for a table which contain 100 rows. We already know that the values present in the column will be unique so there are 100 rows in our table and each row will have a corresponding entries into the column. The corresponding entries present in the column will amounted as the cardinality of the primary key. According to our example table, the cardinality of its primary key is 100.