What is cardinality, Types With Example IN DBMS
What is cardinality, Types With Example IN DBMS: In the context of databases, cardinality refers to the distinctiveness of information values contained in a column. High cardinality implies that the column contains an outsized proportion of all distinctive values. Low cardinality implies that the column contains plenty of “repeats” in its information vary. It’s not common; however cardinality conjointly sometimes refers to the relationships between tables. Cardinality between tables is often one-to-one, many-to-one or many-to-many. High cardinality columns are those with terribly distinctive or uncommon information values.
Also See: Explain Relational Algebra in DBMS
Example:
For example, in an exceedingly database table that stores bank account numbers, the “Account Number” column ought to have terribly high cardinality – by definition, each item of information during this column ought to be entirely distinctive. Traditional cardinality columns are those with a somewhat distinctive percentage of information values. For example, if a table holds client data, the “Last Name” column would have traditional cardinality. Not every last name will be distinctive (for example, there’ll probably be many occurrences of “Smith”) but on the total, the information is fairly non-repetitive.
Low cardinality columns are those with only a few distinctive values. In a client table, an occasional cardinality column would be the “Gender” column. This column can probably only have “M” and “F” because the range of values to choose from, and all the thousands or a lot of records within the table will solely decide one amongst these 2 values for this column. Cardinality relationships between tables will take the shape of one-to-one, one-to-many (whose reversal is many-to-one) or many-to-many. These terms merely consult with the relationships of information between the tables. Let’s say, the link between the “Customers” table and therefore the “Bank Accounts” table is one-to-many, that is, one client will have many accounts, however one account cannot belong to more than one client. That is, of course, assuming this bank has never heard of joint accounts!
Also See: Explain Database Transactions
What is Cardinality, Types With Example IN DBMS
Types of Degrees of Relationship (Cardinality)
The degrees of relationship (Cardinality) is explained in the below diagram:
Fig1: Types of Degrees of Relationship (Cardinality)
- One-to-one (1:1): This is where an occurrence of an entity relates to just one occurrence in another entity. A one-to-one relationship seldom exists in practice, but it can. However, you will contemplate combining them into one entity.
For example:
An employee is allotted an organization vehicle, which might solely be driven by that employee. Therefore, there’s a one-to-one relationship between employee and company vehicle.
Fig2: a one-to-one relationship
- One-to-many (1: M): In this relationship, one occurrence of an entity relates to many occurrences in another entity.
For example:
Taking the employee and department entities, an employee works in one department however a department has several employees. Therefore, there’s a one-to-many relationship between department and employee.
Fig3: one-to-many relationship
Also See: What is Database Management System
- Many-to-many: This is where several occurrences in an entity relate to several occurrences in another entity. The standardization method mentioned earlier would stop any such relationships but the definition is enclosed here for completeness. Like one-to-one relationships, many-to-many relationships seldom exist. Usually, they occur because an entity has been lost.
For example:
This many-to-many relationship can be seen between project and employee. An employee works on many projects and at the same time, a project has several employees.
Fig4: many-to-many relationship
Optional relationship:
A relationship may be optional, either end of the relationship will embody zero occurrences as a possibility. This is often outlined by the business rules of the system being enforced.
Example:
Taking the 3 examples above (from fig2 to fig4), the business rules could give the following:
- Not all workers are allotted an organization automobile.
- An automobile is defined as a pool automobile and not allotted to a particular employee.
- A new department is formed but, as yet, there aren’t any employees operating among it.
- A new project is outlined but so far the team has not been established.
- A new employee starts among the corporate but, as yet, isn’t appointed to a project.
Also See: What is RDBMS
Recursive relationships:
A recursive relationship has continuously been between occurrences in 2 totally different entities. However, it’s attainable for an equivalent entity to participate in the relationship. This is often termed a recursive relationship.
Cardinality constraints:
These constraints specify the number of entity instances which associates with instances of another entity. The types of cardinality constraints are mentioned below:
- Mandatory one
- Mandatory many
- Optional one
- Optional many
Also See: Keys in DBMS
How to find cardinality
When you are working with databases, you may encounter some situations that whether to create a new index or split the current index. Here cardinality comes into existence. And the best part is that we can calculate cardinality before creating new index.
It’s a very easy job to calculate cardinality. Firstly you need to know the total rows in the table, like this
Select count (*) from {table}
And then you can use distinct value using SELECT COUNT command.
So the formula is
Distinct Values Using SELECT COUNT
Total Rows in The Table
So it was all about Cardinality with example, its types with diagram, and the formula for how to find Cardinality in DBMS.
Leave a Reply