Cardinality in database management refers to the relationship between two tables in a relational database. It defines the number of occurrences or instances of one entity that can be associated with another entity through a relationship.
In a relational database, cardinality is classified into three types: one-to-one (1:1), one-to-many (1:N), and many-to-many (N:M).
1) One-to-One (1:1) Cardinality: One-to-one cardinality represents a relationship where each record in one table is associated with exactly one record in another table, and vice versa. This type of cardinality is not commonly used in databases but can be useful in specific scenarios such as storing additional optional information about an entity.
Example: Consider two tables, “Employee” and “Address.” Each employee can have only one address, and each address can belong to only one employee. Therefore, the cardinality between these two tables would be 1:1.
2) One-to-Many (1:N) Cardinality: One-to-many cardinality represents a relationship where each record in one table can be associated with multiple records in another table, but each record in the second table can only be associated with one record in the first table. This is the most common type of cardinality used in databases.
Example: Consider two tables, “Department” and “Employee.” Each department can have multiple employees, but each employee can belong to only one department. Thus, the cardinality between these two tables would be 1:N.
3) Many-to-Many (N:M) Cardinality: Many-to-many cardinality represents a relationship where multiple records in one table can be associated with multiple records in another table. This type of cardinality requires an intermediary table to establish the relationship.
Example: Consider two tables, “Student” and “Course.” Each student can enroll in multiple courses, and each course can have multiple students. To represent this relationship, an intermediary table called “Enrollment” is created, which stores the student and course IDs. Thus, the cardinality between these two tables would be N:M.
