Relations between Dataset in DBMS

Note: this article is published on 06/12/2024.

This series of articles is about relationship between entity or datasets.

• Relations between Dataset in DBMS --- this article
  • ER Diagram 

A - Introduction

A relationship in DBMS is the way in which two or more data sets are linked. This article will give a conceptual discussion about this topic. The content of this article:

• A - Introduction
• B - Types of Relationsip in DBMS
• C - Implementations in database

B - Types of Relationsip in DBMS

In a relational database, the data in one table is related to the data in other tables. In general, tables can be related in one of three different ways:

• One-to-One --- A row in a table is related to only one role in another table and vice versa.
• One-to-Many --- A row from one table can have multiple matching rows in another table.
• Many-to-Many ---   A row from one table can have multiple matching rows in another table and vice versa.

Demo

One to One Relationship:

In restaurant, each customer (Table A) has one and only one order (Table B):

One to Many Relationship:

Each Customer (Table A) could have multiple Orders (Table B)

Many to Many Relationship:

Each employee could have multiple Project assigned, 

Each Project chould be worked by multiple employees.

C - Implementations in database

• One to One Relationship: Enforced by Primary key vs. Unique foreign key constraints

• One to Many Relationship: Enforced by Primary key vs. Foreign key (not unique) relationship

• Many to Mane Relationship: Enforced by a junction or bridging table as a connection for the two tables.

Linked by a Middle Connection Table

References

• How To Implement One to One, One to Many and Many to Many Relationships When Designing A Database. | by Chukwuemeka Godswill Dan-Chuku | Medium
• Types of Relationship in DBMS - javatpoint
• One-to-many relationships (claris.com)