Relationship and Cardinalities

Definition

A relationship is an association between two or more entities in a database. A cardinality defines the numerical nature of that association, such as whether one entity instance is linked to one, many, or optional instances of another entity.

In simple terms:

Relationship

tells us how entities are connected

Cardinality

tells us how many connections are allowed or required

For example:

A department has many employees
An employee belongs to one department
A student can enroll in many courses
A course can have many students

These rules are used in Entity-Relationship (ER) modeling and relational database design.

Main Content

1. Relationship Types

One-to-One (1:1)

Each instance of Entity A is related to exactly one instance of Entity B, and vice versa. Example: A person may have one passport, and a passport belongs to one person. This type is less common and is used when data needs to be separated for security, specialization, or organizational reasons.

One-to-Many (1:M)

One instance of Entity A can be related to many instances of Entity B, but each instance of Entity B is related to only one instance of Entity A. Example: A department can have many employees, but each employee belongs to one department.

Many-to-Many (M:N)

One instance of Entity A can be related to many instances of Entity B, and one instance of Entity B can also be related to many instances of Entity A. Example: A student can enroll in many courses, and a course can have many students.

2. Cardinality Ratios and Participation

Cardinality ratio

This specifies the maximum number of related instances between entities. The common ratios are 1:1, 1:M, and M:N. Cardinality ratio is important because it helps determine table structure, foreign keys, and relationship implementation in a relational database.

Participation constraints

These specify whether participation in a relationship is mandatory or optional.
Total participation: Every entity instance must participate in the relationship.
Example: Every employee must belong to a department.
Partial participation: Some entity instances may not participate in the relationship.
Example: A customer may or may not place an order.

Minimum and maximum cardinality

Cardinality is often expressed as a range, such as 0..1, 1..1, 0..N, or 1..N. Example: A customer may place 0 or many orders, while each order must belong to exactly one customer.

3. Mapping Relationships into Tables

One-to-One mapping

Usually implemented by placing a foreign key in one table referencing the other, or by merging both entities into a single table if appropriate. Example: Person(PersonID, Name) and Passport(PassportID, PersonID, IssueDate).

One-to-Many mapping

Implemented by placing the primary key of the “one” side as a foreign key in the “many” side. Example: Department(DeptID, DeptName) and Employee(EmpID, EmpName, DeptID).

Many-to-Many mapping

Implemented using an intermediate table called a junction table or associative entity. Example: Student(StudentID, Name), Course(CourseID, Title), and Enrollment(StudentID, CourseID, EnrollDate).
The Enrollment table resolves the M:N relationship by converting it into two 1:M relationships.

Working / Process

1. Identify the entities

Determine the main objects in the real-world system.
Example: In a library system, the entities may be Book, Member, and Loan.

2. Determine the relationships and cardinalities

Ask how entities are associated.
Decide whether the connection is 1:1, 1:M, or M:N.
Also decide whether participation is mandatory or optional.
Example: One member can borrow many books, but each loan record belongs to one member and one book.

3. Convert the model into database tables

Place foreign keys where needed.
Create associative tables for M:N relationships.
Define primary keys, foreign keys, and constraints.
Example: A library system may use Member, Book, and Loan tables, where Loan stores the borrowing relationship with dates and status.

Advantages / Applications

Helps in designing databases that accurately represent real-world scenarios and business rules.
Reduces data redundancy and improves data integrity by enforcing correct associations.
Makes it easier to create efficient relational schemas, foreign key constraints, and normalized tables.
Widely used in ER diagrams, database normalization, software development, and information systems.
Supports better communication between analysts, designers, and developers by providing a clear model of data connections.

Summary

Relationship shows how entities are connected.
Cardinality shows how many instances can be linked.
Common types are one-to-one, one-to-many, and many-to-many.
Important terms to remember: relationship, cardinality, participation, foreign key, junction table.