117

Could somebody explain to me, with the help of examples, what is cardinality in databases?

6 Answers 6

Reset to default
178

A source of confusion may be the use of the word in two different contexts - data modelling and database query optimization.

In data modelling terms, cardinality is how one table relates to another.

  • 1-1 (one row in table A relates to one row in tableB)
  • 1-Many (one row in table A relates to many rows in tableB)
  • Many-Many (Many rows in table A relate to many rows in tableB)

There are also optional participation conditions to the above (where a row in one table doesn't have to relate to the other table at all).

See Wikipedia on Cardinality (data modelling).

When talking about database query optimization, cardinality refers to the data in a column of a table, specifically how many unique values are in it. This statistic helps with planning queries and optimizing the execution plans.

See Wikipedia on Cardinality (SQL statements).

4
  • 3
    Would a cardinality of zero mean that the two tables do not relate to each other?
    – Tyler
    Commented Sep 4, 2015 at 4:52
  • Cardinality of zero? Not a term used much - but I suppose that it would mean that. Normally - there would be no line connecting such tables in an ER diagram, so no need to indicate cardinality.
    – Oded
    Commented Sep 4, 2015 at 8:17
  • Zero cardinality in data modeling is used to indicate optional relationships. As in 0..1 or 0..n
    – Walter Mitty
    Commented Dec 10, 2017 at 7:12
  • 1
    making analogy to the maths connotation of the term cardinality: **(data modeling) cardinality between two tables is uniqueness of the values between two tables (cardinality as a function between two sets) **(database query optimization) cardinality of a table is how unique the rows are for that column (cardinality of the set itself)
    – gawkface
    Commented Apr 2, 2018 at 4:36
38

It depends a bit on context. Cardinality means the number of something but it gets used in a variety of contexts.

  • When you're building a data model, cardinality often refers to the number of rows in table A that relate to table B. That is, are there 1 row in B for every row in A (1:1), are there N rows in B for every row in A (1:N), are there M rows in B for every N rows in A (N:M), etc.
  • When you are looking at things like whether it would be more efficient to use a b*-tree index or a bitmap index or how selective a predicate is, cardinality refers to the number of distinct values in a particular column. If you have a PERSON table, for example, GENDER is likely to be a very low cardinality column (there are probably only two values in GENDER) while PERSON_ID is likely to be a very high cardinality column (every row will have a different value).
  • When you are looking at query plans, cardinality refers to the number of rows that are expected to be returned from a particular operation.

There are probably other situations where people talk about cardinality using a different context and mean something else.

14

In database, Cardinality number of rows in the table.

enter image description here img source

enter image description here img source

  • Relationships are named and classified by their cardinality (i.e. number of elements of the set).
  • Symbols which appears closes to the entity is Maximum cardinality and the other one is Minimum cardinality.
  • Entity relation, shows end of the relationship line as follows:
    enter image description here

enter image description here

image source

0
9

Cardinality refers to the uniqueness of data contained in a column. If a column has a lot of duplicate data (e.g. a column that stores either "true" or "false"), it has low cardinality, but if the values are highly unique (e.g. Social Security numbers), it has high cardinality.

1
  • 2
    Cardinality can refer to a couple of different things, but Oded's answer is most likely what you are looking for.
    – Andrew
    Commented May 16, 2012 at 14:56
-2

Cardinality of a set is the namber of the elements in set for we have a set a > a,b,c < so ths set contain 3 elements 3 is the cardinality of that set

-2

Definition: We have tables in database. In relational database, we have relations among the tables. These relations can be one-to-one, one-to-many or many-to-many. These relations are called 'cardinality'.

Significant of cardinality:

Many relational databases have been designed following stick business rules.When you design the database we define the cardinality based on the business rules. But every objects has its own nature as well.

When you define cardinality among object you have to consider all these things to define the correct cardinality.

Not the answer you're looking for? Browse other questions tagged or ask your own question.