What is cardinality in Databases?

database oracle database-design cardinality
Some Body picture Some Body · May 16, 2012 · Viewed 104.5k times · Source

I have been searching all over the internet but I couldn't seem to find an answer that I can understand.

So kindly, if somebody could explain to me with the help of examples what is cardinality in databases?

Thank you.

Answer

Oded picture Oded · May 16, 2012

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).

Related questions

"date" as a column name

I have a table called calendars. One of its columns is named 'date' When I want to select the date column it gives error ORA-01747 namely invalid table.column. select date from calendars I guess this happens because 'date' is …

Read More
How many database indexes is too many?

I'm working on a project with a rather large Oracle database (although my question applies equally well to other databases). We have a web interface which allows users to search on almost any possible combination of fields. To make these …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More