🔢

Cardinality in DBMS

How many entities relate to how many — connectivity, the l..h range, participation, and how each cardinality type turns into real tables with foreign keys and junction tables.

1:1 · 1:M · M:M l..h Notation Participation Mapping to Tables

Press Next → or use ← → arrow keys

Section 01

The Story — The Wedding Seating Question

💒 REAL-WORLD ANALOGY

Two questions every planner asks

A wedding planner seating guests asks two things about every pairing: what type of pairing is this (couples? a whole table to one host?), and exactly how many are allowed at each end. Database designers ask the identical two questions of every relationship — how entities pair, and how many occurrences are permitted on each side.

💡

Core Definition

The cardinality of a relationship is the actual number of related occurrences for each of the two entities. The ER diagram marks that count directly on the edges connecting entities to the relationship.

🚨

Why It Matters

Get cardinality wrong and your tables either store impossible data or reject valid data.

Section 02

Two (Really Three) Ideas Hidden in One Word

🔗

Connectivity

the shape

How instances associate — only "one" or "many". Four patterns: 1:1, 1:M, M:1, M:M. "What shape is the link?"

🔢

Cardinality (count)

the range l..h

The exact numbers — l = minimum, h = maximum. "How many, at least and at most?"

✔️

Participation

optional or must

Flows from the minimum. min = 1 → total (mandatory); min = 0 → partial (optional). "Is taking part compulsory?"

🧠

Memory Hook

Connectivity answers "one or many?" · Cardinality answers "how many, at least and at most?" · Participation answers "is taking part compulsory?"

Section 03

Connectivity — The Four Mapping Types

🔁

M:1 Is Just 1:M in Reverse

Each side is either "one" or "many," giving exactly four patterns. M:1 is the same relationship as 1:M, simply read from the opposite direction.

Section 04

One-to-Many in Action — Customer & Loan

🔍

Read the Pattern From the Data

Pick one customer, count its loans: C1 has two → the loan side is "many". Pick one loan, count its customers: L1 has one → the customer side is "one". Confirmed 1:M — and C3 with no loan shows the customer's participation is optional.

Section 05

Many-to-Many in Action — Supplier & Part

📦

Why M:M Needs Its Own Table

A supplier supplies many parts, and a part comes from many suppliers. There is nowhere to store QTY in the Supplier table or the Part table — it depends on the pair. QTY is an attribute of the link itself.

Section 06

Cardinality Notation — The l..h Range

l..h	Min	Max	Meaning
1..1	1	1	Exactly one — mandatory & single
0..1	0	1	At most one — optional, single
1..*	1	many	At least one — mandatory, many
0..*	0	many	Any number — optional, many

Section 07

Connectivity vs Cardinality — Side by Side

🔗

Connectivity — the headline

Describes: the mapping of instances.
Values: only "one" or "many".
Types: 1:1, 1:M, M:1, M:M.
Answers: "What shape is the link?"

🔢

Cardinality — the fine print

Describes: the actual number of occurrences.
Expression: a precise range.
Notation: l..h (min..max) on an edge.
Answers: "How many, at least and at most?"

📰

Headline + Fine Print

"One-to-many" is the headline; the ranges make it precise: the "one" side is 1..1 and the "many" side is 0..*. Connectivity is the shape; cardinality is the exact count.

Section 08

Cardinality & Participation — Single vs Double Line

📏

Both Read From the Same Label

The minimum of l..h decides participation: min = 1 → total (double line, mandatory); min = 0 → partial (single line, optional). Cardinality (count) and participation are separate decisions, but both come from the same l..h.

Section 09

How Cardinality Shapes Your Tables

🔑

1:M → FK on "many" side

no extra table

The "many" side gets a foreign-key column pointing at the "one" side. The one customer's key can repeat across many loan rows — perfectly legal.

🔗

M:M → junction table

both keys + link attrs

A new table holds both primary keys plus any attribute of the link (like QTY). Its primary key is the pair of foreign keys.

🧷

1:1 → merge or FK

often + UNIQUE

A single foreign key (usually with a uniqueness constraint) is enough. Sometimes both entities collapse into one table.

🧭

The Rule Behind the Rules

A foreign-key column holds one value per row — so it can represent "many on one side" but never "many on both". That single fact is why M:M always needs a junction table.

Section 09 · Example

Cardinality → SQL

1:M — the FK lives on the many side; M:M — a junction table holds the pair.

-- 1 : M  →  foreign key on the "many" (LOAN) side
CREATE TABLE CUSTOMER ( Cust_ID PRIMARY KEY, Name, City );
CREATE TABLE LOAN (
    Loan_No  PRIMARY KEY,
    Amount,
    Cust_ID  REFERENCES CUSTOMER(Cust_ID)   -- FK repeats freely
);

-- M : M  →  junction table holds both keys + the link attribute
CREATE TABLE SUPPLIER ( SNo PRIMARY KEY, Name );
CREATE TABLE PART ( PNo PRIMARY KEY, PName );
CREATE TABLE SUPPLIES (
    SNo  REFERENCES SUPPLIER(SNo),
    PNo  REFERENCES PART(PNo),
    QTY,                              -- belongs to the pair
    PRIMARY KEY (SNo, PNo)
);

✅

One Row per Pair

SUPPLIES stores exactly one row per (supplier, part) pair, with QTY attached — the only place QTY can honestly live.

Section 10

Three Common Mistakes to Avoid

↔️

Reading it backwards

The "1" sits beside the one-side entity but describes how the other side links to it. Always test with data — pick one row and count its partners on the opposite table.

🚫

M:M in two tables

You can't. A foreign-key column holds only one value per row, so it can never represent "many on both sides." Junction tables are mandatory — no exceptions.

🔀

Count vs participation

The maximum (1 or many) is connectivity; the minimum (0 or 1) is participation. A relationship can be 1:M and total at once.

🧪

The Data Test Always Wins

Unsure of a pattern? Take real sample rows, pick one instance on each side, and count its partners. Two counts reveal whether it's 1:1, 1:M, M:1, or M:M.

Section 11 · Quick Reference

Connectivity → Table Mapping

Connectivity	"One" side l..h	"Many" side l..h	Maps to	Example
1:1	0..1 / 1..1	0..1 / 1..1	FK on either side	Person – Passport
1:M	1..1	0..*	FK on many side	Customer – Loan
M:1	0..*	1..1	FK on many side	Employee – Dept
M:M	0..*	0..*	Junction table	Supplier – Part

Symbol / Label	Means
1 beside edge	The "one" side of connectivity
M or *	The "many" side of connectivity
l..h	Minimum (l) and maximum (h) cardinality
Single line	Partial participation (min = 0)
Double line	Total participation (min = 1, i.e. 1..*)

Section 12 · Part 1

Golden Rules of Cardinality — 1 to 3

🏆 NON-NEGOTIABLE RULES · 1–3

1

Determine cardinality from business rules, not convenience. "Can a customer have two loans?" is a question for the stakeholder, not the diagram designer.

2

Test connectivity with sample data. Pick one instance on each side and count its partners. Two counts reveal the pattern — 1:1, 1:M, M:1 or M:M.

3

Read l..h as "minimum then maximum." The first number controls participation; the second controls connectivity. Never reverse them.

Section 12 · Part 2

Golden Rules of Cardinality — 4 to 6

🏆 NON-NEGOTIABLE RULES · 4–6

4

Every M:M relationship becomes a junction table. If a relationship has its own attribute (like QTY), that alone proves the junction table is necessary.

5

Place the foreign key on the "many" side of a 1:M. The single side never holds the key — it would need many values in one cell.

6

Treat cardinality and participation as separate decisions. First ask "how many?" then ask "is it mandatory?" A relationship can be 1:M and total at once.

🧵

The Thread

Cardinality is where a business rule becomes a table shape. Ask the stakeholder "how many?" and "is it required?", write l..h, and the foreign keys and junction tables follow automatically.

FINAL

Cardinality, Complete

4Connectivity patterns

l..hMin–max range

2Participation types

3Table-mapping rules

6Golden rules

🎯

The Foundation Is Set

You can now name the four connectivity patterns, write exact l..h ranges, read participation from the minimum, and turn any cardinality into the right tables — a foreign key for 1:M, a junction table for M:M. Next comes shaping those tables well: keys, functional dependencies and normalization.

🧠

One Sentence to Remember

The maximum tells you the shape (one or many); the minimum tells you if it's mandatory — and M:M always needs a table of its own.

🔢 End of tutorial · Press ← to review, or click Restart