Types of Relationship in DBMS: Explained with Examples

In database management, a relationship can be defined as when one or some field in a particular table corresponds to one or some field in another different table. These relations are notable for the organisation of entities that are linked with each other and for effective work with data. They are usually depicted by keys. These are cells or a set of two-dimensional cells that consist of fields that are used to identify records and define relationships between tables.

For instance, think about a university database. Entities such as students, courses, and instructors could each be represented by different tables. The potential join between these tables could be connecting students to courses that they can take. The work of putting, accessing, and manipulating these relationships, often with a vast amount of data and complicated operations, is handled by database management systems, and the data must remain consistent and correct. Therefore, relationships are an essential ingredient in ensuring that the quality and reliability of data are maintained during all operations.

Therefore, when we need to define relational databases, simply put,  a collection of data that are organised into relations of rows and columns (tables) is a relational database.

Now let’s look at the types of relationship in DBMS: 

One-to-One Relationship

In a one-to-one constraint, one record in a table is related to only one record in the other, and vice versa. In other words, this means that every entry in Table A is uniquely matched with an entry in Table B. Of course, this type of relationship is less common in databases while still being quite helpful for the organization of the data that correlate.

Example:

Consider a Person table and a Passport table. Every individual has his or her own passport, and no two passports are assigned to the same person. In this case, each record in the Person table will be related to only one record in the Passport table, a one-to-one relationship.

Below is an example of a relational table in DBMS

In the relational table in DBMS, each Person_ID corresponds to a unique Passport_ID, and vice versa. Therefore, each person has only one passport, and each passport is assigned to only one person.

SQL to Create Tables and Establish One-to-One Relationship:

In this example, we see a One-to-One Relationship in DBMS using Employees and Passports tables. Each employee is uniquely linked to one passport through the Employee_ID column, which is a primary key in the Employees table and a unique foreign key in the Passports table. This maintains data consistency and integrity, allowing efficient management of sensitive information like passports. For instance, Employee John Doe (ID 1) is associated with Passport A1234567, and similar links exist for others. 

-- Create the Employees Table
CREATE TABLE Employees (
	Employee_ID INT PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	Department VARCHAR(50)
);
 
-- Create the Passports Table
CREATE TABLE Passports (
	Passport_Number VARCHAR(20) PRIMARY KEY,
	Issue_Date DATE NOT NULL,
	Employee_ID INT UNIQUE,
	FOREIGN KEY (Employee_ID) REFERENCES Employees(Employee_ID)
);
 
-- Insert data into Employees Table
INSERT INTO Employees (Employee_ID, Name, Department)
VALUES
(1, 'John Doe', 'HR'),
(2, 'Jane Smith', 'Finance'),
(3, 'Emily Clark', 'IT');
 
-- Insert data into Passports Table
INSERT INTO Passports (Passport_Number, Issue_Date, Employee_ID)
VALUES
('A1234567', '2022-01-10', 1),
('B7654321', '2023-03-15', 2),
('C9876543', '2021-07-30', 3);

Employees Table:

Passports Table:

One-to-Many Relationship

In a One-to-Many relationship, a record in one table can be associated with multiple records in another table, but each record in the second table is linked to only one record in the first table. This type of relationship is very common in database design.

Consider a relational table in DBMS with a Teacher table and a Student table. A single teacher can have many students, but each student is assigned to only one teacher.

Teacher Table

Student Table

In this example:

• Teacher_ID 1 (Mr. Johnson) is linked to Students 101 and 102 (Alice and Bob).
• Teacher_ID 2 (Ms. Thompson) is linked to Students 103 and 104 (Charlie and David).

The example demonstrates a One-to-Many relationship where one teacher can be assigned to multiple students, but each student is assigned to only one teacher.

Code for table:

-- Create the Teacher table
CREATE TABLE Teacher (
	Teacher_ID INT PRIMARY KEY,
	Teacher_Name VARCHAR(50) NOT NULL
);
 
-- Create the Student table
CREATE TABLE Student (
	Student_ID INT PRIMARY KEY,
	Student_Name VARCHAR(50) NOT NULL,
	Teacher_ID INT,
	FOREIGN KEY (Teacher_ID) REFERENCES Teacher(Teacher_ID)
);
 
-- Insert data into the Teacher table
INSERT INTO Teacher (Teacher_ID, Teacher_Name)
VALUES
(1, 'Mr. Johnson'),
(2, 'Mr. Thompson');
 
-- Insert data into the Student table
INSERT INTO Student (Student_ID, Student_Name, Teacher_ID)
VALUES
(101, 'Alice', 1),
(102, 'Bob', 1),
(103, 'Charlie', 2),
(104, 'David', 2);

‍Many-to-Many Relationship

In a Many-to-Many relationship, multiple records in one table can be associated with multiple records in a different table. To implement this relationship in relational databases, we need what is called a junction table. This table is also known as a linking or bridge table and is used to connect the two tables.

Consider a Student table and a Course table. A single student can enroll in multiple courses, and each course can have multiple students enrolled.

Student Table

Course Table

Enrollment Table (Junction Table)

In this example, which shows many-to-many relationships, the Enrollment Table acts as a bridge, linking students and courses. This table allows each student to be associated with multiple courses and each course to have multiple students:

• Alice (Student_ID 1) is enrolled in Mathematics (Course_ID 101) and Computer Science (Course_ID 102).
• Bob (Student_ID 2) is enrolled in Computer Science (Course_ID 102) and Physics (Course_ID 103).
• Charlie (Student_ID 3) is enrolled in Mathematics (Course_ID 101).

Code for table:

-- Create the tables and insert data in one step
CREATE TABLE Student (
	Student_ID INT PRIMARY KEY,
	Student_Name VARCHAR(50) NOT NULL
) AS
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
 
CREATE TABLE Course (
	Course_ID INT PRIMARY KEY,
	Course_Name VARCHAR(50) NOT NULL
) AS
VALUES
(101, 'Mathematics'),
(102, 'Computer Science'),
(103, 'Physics');
 
CREATE TABLE Enrollment (
	Student_ID INT,
	Course_ID INT,
	PRIMARY KEY (Student_ID, Course_ID),
	FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID),
	FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID)
) AS
VALUES
(1, 101),
(1, 102),
(2, 102),
(2, 103),
(3, 101);

Self-Referencing Relationship

When talking about different types of relationship in dbms it’s important to mention this one. The Self-Referencing Relationship is also known as a Recursive Relationship. It occurs when a table has a relationship with itself. This means that a record in the table is related to another record in the same table. Self-referencing relationships are useful when representing hierarchical structures, such as employees and their managers or categories and subcategories.

Look at this Employee table where each employee can have a manager who is also an employee. In this case, the Employee table references itself.

In the example, the Manager_ID column acts as a foreign key that references the Employee_ID in the same table. Therefore, it creates a self-referencing relationship where employees can have other employees as their managers, which ultimately forms a hierarchical structure:

• Alice (Employee_ID 1) has no manager (Manager_ID = NULL), meaning she is at the top of the hierarchy.
• Bob (Employee_ID 2) and Charlie (Employee_ID 3) report to Alice (Manager_ID 1).
• David (Employee_ID 4) and Eve (Employee_ID 5) report to Bob (Manager_ID 2).

Code for table:

-- Create the Employee table with a self-referencing relationship
CREATE TABLE Employee (
	Employee_ID INT PRIMARY KEY,
	Employee_Name VARCHAR(50),
	Manager_ID INT,
	FOREIGN KEY (Manager_ID) REFERENCES Employee(Employee_ID)
);
 
-- Insert data into the Employee table
INSERT INTO Employee (Employee_ID, Employee_Name, Manager_ID)
VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

Weak Relationship  

A weak relationship also called the Weak Entity Relationship, is defined as a situation whereby at least one of the entities in the given table cannot come up with a list of attributes unique enough to identify it. In this case, the entity will identify itself with a strongly related entity. To create a weak relationship, the weak entity in DBMS has a foreign key pointing to the primary key of a strong entity.  Then, it creates the compound key, which is a combination of attributes.

In a weak relationship, one of the two entities has a dependency either on itself or the other entity. It is very important to understand that the weak entity cannot survive without the backing of the strong entity. Now let’s look at some strong and weak entity in DBMS example. 

Consider an Order table (strong entity in DBMS) and an Order Item table (weak entity in DBMS). An order can have multiple items. However, it’s not possible for an item to exist independently without an associated order.

‍Order Table (Strong Entity)

‍Order Item Table (Weak entity)

In the example: 

• The Order Table contains orders placed by customers and is a strong entity because each order can be uniquely identified by its Order_ID.
• The Order Item Table depends on the Order Table and is a weak entity because each item is uniquely identified only in combination with the Order_ID.
• The Order_ID in the Order Item Table acts as a foreign key, linking items to their corresponding orders.

Strong Relationship 

A Strong Relationship is said to exist when an entity can be uniquely identified by its own attributes, without relying on another entity. In this case, the related entities are independent and do not depend on each other for identification. This type of relationship typically occurs between two strong entities that have their own primary keys.

Example:

Consider a Customer table and an Order table. Each customer and order can exist independently, and their relationship does not require either entity to rely on the other for its identification.

Customer Table:

Order Table:

In this,

• The Customer Table and Order Table are strong entities, as each can be uniquely identified by its own primary key (Customer_ID and Order_ID, respectively).
• The relationship between them is based on their primary keys without dependency for uniqueness.

Participation Constraints describe the level of participation of parties in a relationship. They define if, in a set of entities, all of them have to be part of a relation or if participation is voluntary. Due to participation constraints, relationships between entities are preserved with respect to integrity.

There are two types of participation constraints: 

Total Participation

In this, every entity in the entity set must participate in the relationship. Hence, all records in the entity set are required to have at least one associated record in the related entity set.

Consider a Student and an Enrollment relationship. Every student must be enrolled in at least one course: 

Student Table

Enrollment Table

Here, every student in the Student Table participates in the Enrollment relationship. You will also notice obviously that no student exists without being enrolled in a course.

Partial Participation 

In Partial Participation, some entities in the entity set may not participate in the relationship. Therefore, participation in the relationship is optional for some entities.

For example, think about this Department and an Employee relationship. Not all employees need to be assigned to a department; some might be contractors or freelancers.

Employee Table

Department Table

Here, it can be observed that Sarah is not assigned to any department. Hence it shows that not all employees participate in the Department relationship.

Significance of Relationship in DBMS

These are some of the important things about relationship in DBMS:

1. Data Integrity: The linking of data, guaranteed by relationships, always respects consistency, as well as the correctness of the data inserted.
2. Efficient Data Retrieval: It facilitates data searches across multiple tables. Therefore, it minimises redundant entries and speeds up query execution.
3. Normalisation: relationships reduce data redundancy and increase concurrency. Therefore it is able to maintain the relevance of data within relationships while keeping it in order.
4. Complex Data Analysis: Relationships enable more sophisticated querying, grouping, sorting, and a wide array of other processing and analytical capabilities from a user’s data.‍
5. Scalability: Well-defined relationships develop an organisation’s database structure that is system-friendly and capable of accommodating increasing organisational demands.

Relationships in DBMS are basics that every would-be software developer must be in a position to understand. Explaining and grasping these comprising notions establishes a great basis for developing sound and robust databases, a core competency in web development, data analysis and backend engineering. If you fully understand the relationships in DBMS, you’ll be more equipped to address diverse questions on data consistency, query performance optimisation, and software development. It may lay the basis for a competitive edge within a saturated technology market. To learn more and acquire an edge in your career, enrol in the CCBP Academy 4.0 programme.

1. What are various relation types in DBMS?

The main ones are One–to–One, One–to–Many, Many–to–Many, Self–Referencing and Weak Relationship. Each one specifies the logical relationship of the record in one table to another in another table.

2. Why is relationship significant when designing a database?

Relationships guarantee the best data integrity, reduce redundancy and provide the best means of accessing related information in multiple Tables.

3. What is the difference between Total and Partial Participation?

Total Participation means that every defined entity must join the relationship, but in partial participation, some entities do not have to participate.

4. What does the junction table mean in Many-to-Many relationships?

A junction table links two tables having a Many-to-Many relationship by having foreign keys from two tables.

5. How does the Self-Referencing relationship work?

Such a type of relationship is where one table is related to another by linking it to the same table. For instance, an employee table where the employee also doubles up as a manager.