Do you think IIT Guwahati certified course can help you in your career?
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:
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');
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
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
It indicates whether or not each entity in the entity set can participate in the relationship instance of the relationship set.
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
It indicates whether or not each entity in the entity set can participate in the relationship instance of the relationship set.
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.
24+ registered 
