Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
One-to-One Relationship
2.1.
Example :
3.
One-to-Many or Many-to-One Relationship 
3.1.
Example :
4.
Many-to-Many Relationship
5.
Difference between a Database and a Relational Database
6.
Advantages of Relational Databases 
7.
Types of Constraints
7.1.
Partial Participation
7.2.
Total Participation
8.
Frequently Asked Questions
8.1.
What are the 3 types of relationships in a database?
8.2.
What is relationship in SQL? 
9.
Conclusion
Last Updated: Mar 27, 2024
Easy

​​Types of Relationship in DBMS

Author Gaurish Anand
1 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

Database management systems (or DBMS) are used to manage databases. A database is information that is stored electronically in a computer system. Data inside a database can be in texts, tables, charts, etc.

To make processing and querying of data faster, the most common types of databases in use today are often described in rows and columns in a sequence of tables. Data can be easily accessed, managed, updated, regulated, and organized using tables and DBMS.

A group of entities with the same set of attributes is referred to as an entity set. An entity in a database management system is a table or an attribute of a table; hence an ER diagram depicts the entire logical structure by displaying relationships between tables and their characteristics. A basic ER diagram looks like this: 

​​Types of Relationship in DBMS

Must Recommended Topic, Generalization in DBMS

There are three types of relationships that can exist between two entities, which are given below and also discussed in this article with examples and diagrams.

 

  • One-to-One relationship
  • One-to-Many relationship
  • Many-to-Many relationship

One-to-One Relationship

According to this relationship, a single record in Table A is related to a single record in Table B, and vice-versa is also true.  

Example: Consider 2 entities - 
Person - ID, name, age, address
Passport - passport_ID, passport_no

Only one passport is allowed per person, and each passport belongs to only one person. Therefore it is a one-to-one relationship.
 

Example :

Let’s consider a database of an online bookstore. The main table is “Users,” which stores basic user information, and an auxiliary table is “UserProfiles,” which stores additional information about the users that not all users may have.

‘Users’ Table

UserID Username Email
1 john_doe john_doe@gmail.com
2 john_doe john_doe@gmail.com

‘UserProfiles’ Table:

ProfileID UserID Address Date Of Birth
1 1 123 Elm St. 1990-01-01
2 2 456 Oak St.  1992-02-02

 

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

CREATE TABLE Users (
   UserID INT PRIMARY KEY,
   Username VARCHAR(50) NOT NULL,
   Email VARCHAR(100) NOT NULL
);
CREATE TABLE UserProfiles (
   ProfileID INT PRIMARY KEY,
   UserID INT UNIQUE,
   Address VARCHAR(255),
   DateOfBirth DATE,
   FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

Insert Table Example :

-- Inserting data into Users
INSERT INTO Users (UserID, Username, Email) VALUES
(1, 'john_doe', 'john@email.com'),
(2, 'jane_doe', 'jane@email.com');

-- Inserting data into UserProfiles
INSERT INTO UserProfiles (ProfileID, UserID, Address, DateOfBirth) VALUES
(1, 1, '123 Elm St.', '1990-01-01'),
(2, 2, '456 Oak St.', '1992-02-02');

Also See, File System vs DBMS

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

One-to-Many or Many-to-One Relationship 

According to this relationship, each record of Table A can be related to one or more than one record of Table B. 

Example: Consider 2 entities - 

Each customer can have multiple accounts, so it is a one-to-many relationship. 
But if we see the other way, many accounts are held by one customer only. Therefore it is a many-to-one relationship also.

Example :

Let's use an example of a ‘Library’ database system with two tables: ‘Authors’ and ‘Books’.

Authors’ Table:

AuthorID AuthorName
1 Agatha Christie
2 J.K. Rowling
3 Mark Twain


Books’ Table:

BookID Title AuthorID
1 Murder on the Orient Express 1
2 The ABC Murders 1
3 Harry Potter and the Philosopher's Stone 2
4 Harry Potter and the Chamber of Secrets 2
5 The Adventures of Tom Sawyer 3


 In this example:

AuthorID’ is a primary key in the Authors table.
'BookID' is a primary key in the Books table.
AuthorID’ in the Books table is a foreign key because it references the primary key of the Authors table.

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

CREATE TABLE Authors (
   AuthorID INT PRIMARY KEY,
   AuthorName VARCHAR(100) NOT NULL
);
CREATE TABLE Books (
   BookID INT PRIMARY KEY,
   Title VARCHAR(255) NOT NULL,
   AuthorID INT,
   FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

Insert Data Example :

-- Inserting data into Authors
INSERT INTO Authors (AuthorID, AuthorName) VALUES
(1, 'Agatha Christie'),
(2, 'J.K. Rowling'),
(3, 'Mark Twain');

-- Inserting data into Books
INSERT INTO Books (BookID, Title, AuthorID) VALUES
(1, 'Murder on the Orient Express', 1),
(2, 'The ABC Murders', 1),
(3, 'Harry Potter and the Philosopher''s Stone', 2),
(4, 'Harry Potter and the Chamber of Secrets', 2),
(5, 'The Adventures of Tom Sawyer', 3);

Many-to-Many Relationship

According to this relationship, each record of Table A can be related to one or more than one record of table B and vice versa is also true, i.e., each record of Table B can be connected to one or more than one record of table A. 

Example: Consider 2 entities - 

Each customer can buy more than 1 product, and many customers can buy one. Therefore it is a many-to-many relationship.

Example :

Let's consider a database for a school system where we have ‘Students’ and ‘Courses.’ A student can enroll in many courses, and a course can have many students.

Students’ Table

StudentID StudentName
1 Akash
2 Abhishek
3 Atul

Courses’ Table

CourseID CourseName
1 Computer Science
2 Physics
3 Chemistry

Enrollments’ Junction Table:

EnrollmentID StudentID CourseID
1 1 1
2 1 3
3 2 2
4 3 1
5 3 2
6 3 3

In this example:

  • StudentID is the primary key in the Students table
  • CourseID is the primary key in the Courses table.
  • Enrollments are the junction table with its primary key, EnrollmentID.
  • StudentID and CourseID in the Enrollments table are foreign keys referencing the respective primary keys in the Students and Courses tables.

 

SQL to Create Tables and Establish Many-to-Many Relationships

CREATE TABLE Students (
   StudentID INT PRIMARY KEY,
   StudentName VARCHAR(100) NOT NULL
);
CREATE TABLE Courses (
   CourseID INT PRIMARY KEY,
   CourseName VARCHAR(255) NOT NULL
);
CREATE TABLE Enrollments (
   EnrollmentID INT PRIMARY KEY,
   StudentID INT,
   CourseID INT,
   FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
   FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Insert Data Example

-- Inserting data into Students
INSERT INTO Students (StudentID, StudentName) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
-- Inserting data into Courses
INSERT INTO Courses (CourseID, CourseName) VALUES
(1, 'Mathematics'),
(2, 'Literature'),
(3, 'Computer Science');
-- Inserting data into Enrollments
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) VALUES
(1, 1, 1),
(2, 1, 3),
(3, 2, 2),
(4, 3, 1),
(5, 3, 2),
(6, 3, 3);

 

Difference between a Database and a Relational Database

Feature Database Relational database
Data Structure  Can be hierarchical, network, object-oriented, etc Always tabular with rows and columns
Data Access  May or may not support SQL for data access Typically uses SQL for data access and manipulation
Data Relationships  Relationships can be implicit or maintained as per the database model Relationships are explicit and defined using foreign keys in tables
Schema Flexibility  Varies by type (e.g., NoSQL databases offer schema flexibility) Usually requires a predefined schema and alterations can be complex
Data Integrity  Depends on the specific type of database system Enforced through referential integrity and ACID properties
Scalability Varies by type (e.g., NoSQL databases are often designed for horizontal scalability) Traditionally vertical scalability, but modern RDBMS support horizontal scalability through partitioning and sharding

Also Read - Cardinality In DBMS

Advantages of Relational Databases 

Relational databases, since their inception by E.F. Codd in the 1970s, have been favored for a variety of use cases due to their structured approach and robust feature set. Below are some of the core advantages they offer:

 1. Structured Query Language (SQL) Support:

Relational databases use SQL, a powerful and standardized language for querying and manipulating data, which is widely used and well-understood in the industry.
 

2. Data Integrity:

They enforce data integrity and consistency through constraints, which include primary keys, foreign keys, unique constraints, and not-null constraints.
 

3. ACID Properties:

Adherence to ACID (Atomicity, Consistency, Isolation, Durability) properties ensures reliable transaction processing that is crucial for many business operations.
 

4. Complex Query Handling:

Relational databases excel in handling complex queries and can efficiently manage large-scale data retrieval operations using advanced join capabilities.
 

5. Normalization:

Through normalization, relational databases minimize redundancy and dependency, organizing data efficiently.
 

6. Data Security:

They offer robust data security features, including user-based permissions and access controls.
 

7. Scalability:

Modern relational databases have adapted to provide both vertical and horizontal scalability, accommodating growing data and user loads.
 

8. Mature Tools and Utilities:

A wide range of mature tools and utilities for backup, recovery, and performance tuning are available.
 

9. Strong Transaction Support:

They provide strong support for multi-user transactions, ensuring data consistency in concurrent access scenarios.
 

10. Referential Integrity:

Relational databases maintain referential integrity by ensuring that relationships between tables remain consistent.
 

11. Ease of Use:

The tabular format of relational databases is intuitive, making data easy to comprehend and manipulate.
 

12. Compatibility with Business Logic:

They are highly compatible with business logic and can easily enforce business rules at the database level.
 

13. Data Independence:

Data independence is achieved by separating data from the applications that use the data, which allows for flexibility and changes without disrupting the entire system.
 

14. Support for Data Analytics:

Relational databases provide the necessary infrastructure for complex analytics and reporting.

Types of Constraints

This constraint defines the number of instances of an entity ( of an entity set) participating in the relationship type.

Partial Participation

  1. It indicates whether or not each entity in the entity set can participate in the relationship instance of the relationship set.
  2. It is indicated using a single line between the entity and relational sets.

    In the above example, A single line between the courses and enrollment in a relationship signifies partial participation. It means there might be some courses that any student does not enroll in.
     

Total Participation

  1. It indicates whether or not each entity in the entity set can participate in the relationship instance of the relationship set.
  2. It is indicated using a double line between the entity and relational sets.

    In the above example, A double line between the student and enrolled in a relationship signifies the total participation, which means that every student must have enrolled at least in 1 course.

 

You can also know about different types of DBMS in detail.

Frequently Asked Questions

What is relationship in DBMS?


A relationship in DBMS refers to the logical association between different entities within the database, often established through common attributes. 

What are the 3 types of relationships in a database?


The three types of relationships in a database are one-to-one, one-to-many (or many-to-one), and many-to-many. 

What is relationship in SQL?
 

In SQL, a relationship is the connection between tables in a database, defined by the use of foreign keys that link a column of one table to the primary key of another. 

Conclusion

In this article, we learned about different types of relationships in DBMS. We also learned how proper relationships in DBMS are essential, and without the proper relationships, the entire Database structure would become useless.

Recommended Readings:

 

You can also consider our Database Management Course to give your career an edge over others!

Live masterclass