What is Cardinality in DBMS?
Cardinality at its most basic can be defined as the number of elements in a given set. In the specific case of databases, it refers to the number of lines or records in the record. Yet, when applied to the field of databases to define the cardinality of items within tables, it represents the number of occurrences of one entity that are linked to the other. Cardinality comes in here to define the types of rules and constraints eminent in these relationships.
Cardinality in Relationships
Every table in a relational database contains one or more keys to be used to relate it to other tables. Cardinality deploys the number of records in one table, that may or must be associated with records in another table. The relationships are categorized into four main types:
- One-to-One (1:1)
- One-to-Many (1:M)
- Many-to-One (M:1)
- Many-to-Many (M: N)
All of these relations have their own importance and usage in the design of the database. Now a little more on each type and how they work See sample below.
1. One-to-One Relationship (1:1)
In this type of relationship, one record in one of the tables will relate directly to only one record in another table. Although this kind of relationship is not common more often when handling large amounts of data this type of relationship is useful when handling sensitive data or when a large table is divided into more manageable parts.
Cardinality Example:
Suppose we are given two tables or entities named Person and Passport. Each record in the Person table has a passport number and no passport number is repeated for a different person.
Person Table
| Person_ID | Name | Age |
|---|---|---|
| 1 | John | 30 |
| 2 | Sarah | 25 |
| 3 | Emily | 28 |
Passport Table
| Passport_ID | Person_ID | Issue_Date |
|---|---|---|
| 101 | 1 | 2023-01-01 |
| 102 | 2 | 2023-05-10 |
| 103 | 3 | 2024-03-20 |
Here, every single person has a unique Passport, and vice versa there is one Passport linked to one Person only. This is a one-to-one relationship.
2. One-to-Many Relationship (1:M)
The one-to-many relationship is another type of relationship that is more popular than the other relationships. Here, one row in the first table has a number of rows in the second table, while in the second, each number will only have a row in the first table.
Cardinality Example:
Suppose, there is a Department table, and there is also an Employee Table. Every department can have multiple workers while every worker works in only one department.
Department Table
| Department_ID | Department_Name |
|---|---|
| 1 | HR |
| 2 | IT |
| 3 | Marketing |
Employee Table
| Employee_ID | Name | Department_ID |
|---|---|---|
| 101 | John | 1 |
| 102 | Sarah | 2 |
| 103 | Emily | 1 |
| 104 | James | 3 |
In this case, one-to-many relationships exist between the Department and Employee table where one Department has many employees but one employee can be only of one department.
3. Many-to-One Relationship (M:1)
The many-to-one relationship is almost the opposite of the one-to-many relationship that is defined above. Here, a number of records of the first table is linked to one record of the second table. In other words, there are many instances where several rows in the first table look to one row in the second table.
Cardinality Example:
For our purpose of the explanation let's assume we have an ‘Employee’ table and a ‘Department’ table. Here, it is possible to include many employees in a certain department, however, every employee is included in only one department.
Employee Table
| Employee_ID | Name | Department_ID |
|---|---|---|
| 101 | John | 1 |
| 102 | Sarah | 2 |
| 103 | Emily | 1 |
| 104 | James | 3 |
Department Table
| Department_ID | Department_Name |
|---|---|
| 1 | HR |
| 2 | IT |
| 3 | Marketing |
Here, more employees are allowed for one department e.g. John@HR and Emily@HR making it a many-to-one relationship as viewed from the table Employee.
4. Many-to-Many Relationship (M: N)
Related data records are of two types; in a one-to-one relationship where one record in the first table is related to one record in the second table and in a many-to-many relationship where many records can be related to many records. This kind of relationship needs a junction table to hold the connections.
Cardinality Example:
Let's use the Student table and the Course table as examples. Many students can participate in many courses while many courses can be participated by many students.
Student Table
| Student_ID | Name |
|---|---|
| 1 | John |
| 2 | Sarah |
| 3 | Emily |
Course Table
| Course_ID | Course_Name |
|---|---|
| 101 | Math |
| 102 | Science |
| 103 | History |
Enrollment Table (Junction Table)
| Student_ID | Course_ID |
|---|---|
| 1 | 101 |
| 1 | 102 |
| 2 | 101 |
| 3 | 103 |
| 3 | 102 |
Here, one student may be registered for many courses while a course can accommodate many students. This means that there has to be a junction table- Enrollment table to represent the many-to-many relationship.
Importance of Cardinality in DBMS
Understanding cardinality is essential for effective database design. Here’s why:
- Efficient Data Retrieval: If we know the cardinality of the relationships we can design the queries, which will involve less number of joins and select the data more efficiently.
- Enforcing Data Integrity: Cardinality is used in defining constraints and helps in maintaining the technical integrity of relationships which in turn assists in inserting the right data into the database.
- Normalization: This cardinality normally is an important element in the process of database normalization, which is aimed at the elimination of as much redundancy and dependencies between the tables as possible with the help of correct translation of the database schema.
- Foreign Keys: Cardinality is used to describe the nature of the relationships when we are planning the relationships between tables using a foreign key. Foreign keys help to maintain the relationship between two tables through reference.