Cardinality in DBMS: Types, Ratio, Constraints & SQL Examples

Cardinality in DBMS: Types, Ratio, Constraints & SQL Examples

Course Lessons

    Deep Understanding of Distributed Database in DBMS

    What are Keys in DBMS?

    Understanding the Difference between Rollback and Commit

    Learn about Attributes in DBMS

    A Quick Guide to Entities in DBMS

    A Quick Guide to Relational Model in DBMS

    NoSQL Databases: What are They?

    Integrity Constraints in DBMS: Types, Examples & SQL Code

    Databases Types

    ER (Entity Relationship Model) in DBMS

    Indexing in DBMS

    SELF JOIN IN SQL

    Deadlock in DBMS

    Relational Algebra in DBMS

    ACID Properties in DBMS with examples

    Applications of DBMS

    Normalization in DBMS with Examples

    Star Schema and Snowflake Schema in Data Warehousing

    Primary Key and Unique Key in DBMS: Differences

    Structure of DBMS Explained: Components, Diagram & Architecture

    Cardinality in DBMS: Types, Ratio, Constraints & SQL Examples

    DDL and DML Commands in SQL: Differences, Syntax & Examples

    INDEX in SQL

    Second Normal Form in DBMS

    File Organization in DBMS

    Functional Dependency in DBMS with examples

    Natural Join in DBMS SQL

    OLAP vs OLTP: Differences

    Domain Constraints in DBMS

Every relational database is built on relationships between tables. Before you can implement those relationships correctly - using foreign keys, junction tables, or composite keys - you need to understand exactly how many instances of one entity can relate to instances of another. That is what cardinality tells you.

Cardinality in DBMS has two related but distinct meanings. In the context of relationships and ER diagrams, it describes how many instances of one entity can be associated with instances of another - one-to-one, one-to-many, or many-to-many. In the context of individual columns and query optimisation, it describes how many unique values a column contains - high, normal, or low cardinality.

This guide covers both meanings in full: the four types of cardinality in DBMS with ER diagrams, the cardinality ratio, cardinality constraints, column-level cardinality, cardinality vs modality vs ordinality, SQL implementation for each type, mapping rules for database design, and GATE exam tips. Understanding keys in DBMS is the essential prerequisite - primary keys and foreign keys are the SQL mechanism through which cardinality relationships are enforced.

Who This Guide Is For

What is Cardinality in DBMS?

Cardinality in DBMS defines the numerical relationship between instances of one entity and instances of another entity in a relationship set. When you model a database using an Entity-Relationship (ER) diagram, cardinality tells you the maximum number of entity instances that can participate on each side of a relationship.

More formally: the cardinality of a relationship is the number of tuples (rows) in a relation, or in the relational context, the number of times an entity from one entity set can be associated with entities from another entity set through a given relationship.

Cardinality is determined by real-world business rules, not by the database designer's preference. If a hospital policy states that one patient can have multiple doctors but each doctor can attend to multiple patients, that real-world rule dictates a many-to-many cardinality - the database must reflect it accurately.

Cardinality in DBMS means two different things depending on context. In ER modelling and relationship design, it refers to the number of entity instances participating in a relationship (one-to-one, one-to-many, many-to-many). In SQL and query optimisation, it refers to the number of unique values in a column (high vs low cardinality). Both are important - this guide covers both in separate sections.

Types of Cardinality in DBMS

There are four types of cardinality mapping in a relational database. Each type directly determines the structure of the SQL schema used to implement the relationship.

Types of Cardinality in DBMS

1:1
One-to-One
Each A maps to at most one B and vice versa
1:N
One-to-Many
One A can relate to many B, but each B relates to one A
N:1
Many-to-One
Many A can relate to one B, each A maps to at most one B
M:N
Many-to-Many
Many A relate to many B - requires a junction table

Type 1: One-to-One (1:1) Cardinality

In a one-to-one relationship, each entity instance in set A is associated with at most one entity instance in set B, and each entity instance in set B is associated with at most one entity instance in set A.

Real-world examples:

  • Each employee has exactly one employee profile; each profile belongs to exactly one employee
  • Each country has one capital city; each capital city belongs to one country
  • Each person has one Aadhaar number; each Aadhaar number belongs to one person
One-to-one relationships are used when you want to separate a large table into two for performance, security, or organisational reasons - not because the data cannot fit in one table. The classic use case is splitting a main entity table (Employee) from a sensitive or rarely accessed details table (Employee_Profile or Salary_Details), so the main table stays lean for frequent queries.

Type 2: One-to-Many (1:N) Cardinality

In a one-to-many relationship, one entity instance in set A can be associated with multiple entity instances in set B, but each entity instance in set B is associated with at most one entity instance in set A.

Real-world examples:

  • One department has many employees; each employee belongs to one department
  • One author writes many books; each book has one author
  • One customer places many orders; each order belongs to one customer

Type 3: Many-to-One (N:1) Cardinality

Many-to-one is simply the reverse perspective of one-to-many. Many entity instances in set A can be associated with at most one entity instance in set B. Structurally, it is implemented identically to one-to-many - the distinction is only in how you describe the direction of the relationship.

Real-world examples:

  • Many surgeries are performed by one surgeon
  • Many students belong to one batch or cohort
  • Many transactions are processed by one bank branch
Many-to-one and one-to-many describe the same relationship from opposite directions. "Many employees belong to one department" (N:1) and "One department has many employees" (1:N) are the same relationship. The foreign key always sits on the "many" side of the relationship - in this case, the Employee table holds the Dept_ID foreign key, not the Department table.

Type 4: Many-to-Many (M:N) Cardinality

In a many-to-many relationship, entity instances in set A can be associated with multiple entity instances in set B, and entity instances in set B can also be associated with multiple entity instances in set A.

Real-world examples:

  • Students enroll in many courses; each course has many students
  • Employees work on many projects; each project involves many employees
  • Books are written by many authors; each author writes many books

Many-to-many relationships cannot be directly implemented with just two tables. A junction table (also called an associative table, bridge table, or linking table) is required to break the M:N relationship into two 1:N relationships.

In GATE and university exams, a frequently tested question is: "How do you implement a many-to-many relationship in a relational database?" The answer is always a junction (associative) table with a composite primary key made up of the foreign keys from both participating tables. Without the junction table, you cannot enforce the relationship while maintaining First Normal Form (no repeating groups).

Cardinality Ratio in DBMS

The cardinality ratio is the precise numerical notation used in ER diagrams to express the maximum number of relationship instances an entity can participate in. It is expressed as a pair of numbers or symbols attached to each side of a relationship in the ER diagram.

Two notation systems are widely used:

The cardinality ratio is determined by the business rules of the system being modelled. The database designer reads the real-world constraints and translates them into the appropriate ratio. Choosing the wrong cardinality ratio leads to a flawed schema that either allows data that should not exist or prevents data that should exist.

Cardinality Constraints in DBMS

Cardinality constraints are the rules that enforce the cardinality of a relationship in the actual database implementation. They specify the minimum and maximum number of relationship instances each entity must or can participate in.

There are two dimensions to cardinality constraints:

Cardinality constraints have two parts: maximum cardinality (1 or N - the relationship type) and minimum cardinality (0 or 1 - also called participation constraint). When minimum cardinality is 1 for every entity, it is called Total Participation - every entity must participate in the relationship. When minimum cardinality allows 0, it is Partial Participation - participation is optional. In SQL, NOT NULL on a foreign key enforces total participation; allowing NULL enforces partial participation.

Column-Level Cardinality: High, Normal, and Low

Beyond relationship cardinality, the term is also used at the column level to describe how many unique values a column contains. This type of cardinality directly affects query performance and indexing decisions.

Cardinality vs Modality vs Ordinality

These three terms frequently appear together in database design and are often confused in exams and interviews.

GATE frequently tests the distinction between cardinality (maximum instances) and modality/participation constraint (minimum instances). Remember: cardinality answers "how many at most?" (1 or many). Modality answers "is it required?" (yes = total participation, no = partial participation). A foreign key with NOT NULL enforces both: the relationship exists (modality 1) and is constrained to one parent (cardinality 1 on the parent side).

Database Design Rules Based on Cardinality

The cardinality of a relationship directly determines how the database schema should be structured. These rules are used in the process of converting an ER diagram to a relational schema.

Real-World Examples of Cardinality

Conclusion

Cardinality in DBMS is not a single concept - it is a multi-layered idea that operates at the relationship level (how many entities can be related) and at the column level (how many unique values a column has). Both meanings are practically important: relationship cardinality determines your schema structure, and column cardinality determines your indexing strategy.

Three things to take away: first, the four cardinality types (1:1, 1:N, N:1, M:N) determine where foreign keys go and whether a junction table is needed. Second, cardinality constraints define the maximum, while modality (participation constraints) defines the minimum - both together give the full picture of a relationship's rules. Third, high-cardinality columns are the best candidates for indexing; low-cardinality columns are not.

For GATE and interviews, the most frequently tested points are: which table holds the foreign key in 1:N relationships (always the N side), why M:N requires a junction table, and the difference between cardinality and modality. Board Infinity's guide on integrity constraints in DBMS is the natural next step - integrity constraints are the formal mechanism through which cardinality rules are enforced at the database level.

Frequently Asked Questions

Q1. What is cardinality in DBMS? Cardinality in DBMS refers to the numerical relationship between instances of entities in a relationship. In ER modelling, it defines how many instances of one entity can be associated with instances of another - the four types are one-to-one, one-to-many, many-to-one, and many-to-many. In column-level context, it refers to the number of unique values in a column.

Q2. What are the types of cardinality in DBMS? The four types are: one-to-one (1:1) where each entity relates to at most one other; one-to-many (1:N) where one entity relates to many others; many-to-one (N:1) where many entities relate to one; and many-to-many (M:N) where many entities relate to many others.

Q3. What is cardinality ratio in DBMS? The cardinality ratio is the notation used in ER diagrams to express the maximum number of relationship instances an entity can participate in. It is written as 1:1, 1:N, N:1, or M:N and is determined by the real-world business rules of the system being modelled.

Q4. What are cardinality constraints in DBMS? Cardinality constraints are the database rules that enforce the cardinality of a relationship. They have two parts: maximum cardinality (enforced by UNIQUE for one, or FK alone for many) and minimum cardinality - also called participation constraint - which is enforced by NOT NULL (mandatory/total participation) or allowing NULL (optional/partial participation).

Q5. What is the cardinality of a relationship in DBMS? The cardinality of a relationship is the count of how many entity instances from one entity set can participate in a relationship with entity instances from another entity set. A one-to-many relationship has a cardinality of 1 on the parent side and N on the child side, enforced through a foreign key on the child (many) side.

Q6. What is high cardinality vs low cardinality in DBMS? High cardinality means a column has many unique values relative to the total number of rows - like a primary key or email address. Low cardinality means a column has very few distinct values repeated many times - like a gender or status flag. High-cardinality columns are excellent for B-tree indexing; low-cardinality columns may benefit from bitmap indexes instead.

Q7. What is the difference between cardinality and modality in DBMS? Cardinality defines the maximum number of instances on each side of a relationship (1 or many). Modality defines the minimum - whether participation is mandatory (1) or optional (0). In SQL, maximum cardinality is controlled by UNIQUE constraints on foreign keys, while minimum cardinality is controlled by NOT NULL (mandatory) or nullable foreign key columns (optional).

Further Reading

Board Infinity Guides:

External Resources:

Mark Lesson Complete (Cardinality in DBMS: Types, Ratio, Constraints & SQL Examples)