DBMS Topics
Relationships and Cardinality
Last Updated : 21 May, 2026
A relationship describes an association or connection between two or more entities in the ER model. In the relational model, relationships are implemented using foreign k
What is a Relationship?
A relationship describes an association or connection between two or more entities in the ER model. In the relational model, relationships are implemented using foreign keys.
Types of Relationships by Cardinality
Cardinality specifies the maximum number of instances of one entity that can be associated with instances of another through a relationship.
1. One-to-One (1:1)
Each instance of Entity A is associated with at most one instance of Entity B, and vice versa.
| Person | Passport |
|---|---|
| Alice | PA001 |
| Bob | PB002 |
| Carol | PC003 |
One person has at most one passport, and one passport belongs to exactly one person.
Real-world examples:
- Person ↔ Aadhaar Card
- Country ↔ Capital City
- Employee ↔ Parking Spot (in reserved parking)
Implementation:
CREATE TABLE Person (PersonID INT PRIMARY KEY, Name VARCHAR(100));
CREATE TABLE Passport (
PassportNo VARCHAR(20) PRIMARY KEY,
PersonID INT UNIQUE, -- UNIQUE ensures 1:1
FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);2. One-to-Many (1:N)
One instance of Entity A can be associated with many instances of Entity B, but each instance of B is associated with at most one instance of A.
One department has many employees; each employee belongs to one department.
Real-world examples:
- Department → Employees (1:N)
- Customer → Orders (1:N)
- Parent → Children (1:N)
- Country → Cities (1:N)
Implementation:
CREATE TABLE Department (DeptID INT PRIMARY KEY, DeptName VARCHAR(100));
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(100),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);Foreign key is placed in the "many" side table (Employee).
3. Many-to-Many (M:N)
Each instance of Entity A can be associated with many instances of Entity B, and each instance of B can be associated with many instances of A.
Real-world examples:
- Students ↔ Courses
- Doctors ↔ Patients
- Products ↔ Orders
- Actors ↔ Movies
Implementation (Junction/Bridge Table):
CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(100));
CREATE TABLE Course (CourseID INT PRIMARY KEY, Title VARCHAR(100));
-- Junction table to handle M:N
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
Grade CHAR(2),
EnrollDate DATE,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);Participation Constraints
Total Participation (Mandatory)
Every instance of the entity MUST participate in the relationship.
- Represented by a double line in ER diagrams
- Enforced by NOT NULL constraint on the foreign key
Partial Participation (Optional)
Some instances of the entity may NOT participate in the relationship.
- Represented by a single line in ER diagrams
- Foreign key allows NULL
Cardinality Notation (Min-Max Notation)
The min-max notation specifies the minimum and maximum number of relationship instances for each entity:
(1,1)
(1,N)
- Each employee works in exactly 1 department.
- Each department has at least 1 and at most N employees.
Summary Table
| Relationship | Description | FK Placement | Junction Table |
|---|---|---|---|
| 1:1 | Each A → at most one B | Either table (with UNIQUE) | No |
| 1:N | One A → many B's | In the N-side table | No |
| M:N | Many A's ↔ many B's | Not in either; new table | Yes |
Relationship Attributes
Some relationships themselves have attributes. These are properties of the relationship, not of either entity.
In M:N relationships, relationship attributes are stored in the junction table.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Relationships and Cardinality.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, relationships, and, cardinality
Related DBMS Topics