Published: 11 Nov 2025 | Reading Time: 7 min read
A database may be described as a collection of orderly information that is stored electronically and processed within a given computer network. It mainly does the job of archiving, sorting, and providing easy access to data, and because of that the information is retrievable and up-to-date.
However, a relational database goes even further by organizing data into tables with predefined relationships between them. The organization makes it easier to manage and query complex datasets. While a database simply stores information, a relational database structures it systematically, using relationships to connect data points. This distinction is crucial in scenarios where data interconnections are key to understanding the information fully.
In reality, data is hardly ever standalone as it is often linked. Many of these relations between pieces of data are indirect or even latent, which only demonstrates the importance of proper data management. The patterns useful in data are important to highlight to enable one to understand types of relationship in DBMS. It helps in developing a clearer perception and understanding of various relationships, ensuring proper and efficient mapping of databases.
A database is a broad term that refers to any organized collection of data stored electronically. Databases can take many forms, such as hierarchical, network, object-oriented, or relational. Each type has its own way of structuring, storing, and accessing data, often depending on specific use cases or application requirements.
A relational database, on the other hand, is a specific type of database that organizes data into tables (also called relations) using the relational model. Each table consists of rows and columns, where each row represents a unique record and each column represents an attribute. The relational model uses concepts like primary key, foreign key, unique key, and surrogate key to ensure data integrity and to define clear relationships between tables.
Databases are managed at different levels:
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.
Quick Note: Relationships are often visualized via Entity-Relationship (ER) diagrams to clarify how entities interact.
Now let's look at the types of relationship in DBMS:
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.
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:
Person Table:
| Person_ID | Person_Name | Passport_ID |
|---|---|---|
| 1 | John Doe | 101 |
| 2 | Jane Smith | 102 |
Passport Table:
| Passport_ID | Passport_Number | Issue_Country |
|---|---|---|
| 101 | A1234567 | USA |
| 102 | B9876543 | UK |
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.
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:
| Employee_ID | Name | Department |
|---|---|---|
| 1 | John Doe | HR |
| 2 | Jane Smith | Finance |
| 3 | Emily Clark | IT |
Passports Table:
| Passport_Number | Issue_Date | Employee_ID |
|---|---|---|
| A1234567 | 2022-01-10 | 1 |
| B7654321 | 2023-03-15 | 2 |
| C9876543 | 2021-07-30 | 3 |
A one-to-many relationship in DBMS occurs when a single entity in one table (the "one" side) can be associated with multiple entities in another table (the "many" side). Conversely, a many-to-one relationship is simply the same association viewed from the perspective of the "many" side, where multiple entities in one table relate back to a single entity in another.
These relationships are fundamental to database design and are crucial for ensuring data consistency and data integrity. They are commonly implemented using foreign keys, which link the primary key of one table to a corresponding field in another table.
Customer and Order Database:
Authors and Books:
Customer and Account:
Students and Courses (with Associative Tables):
Weak Entities:
-- Customers and Orders Example
CREATE TABLE Customer (
Customer_ID INT PRIMARY KEY,
Name VARCHAR(50)
);
CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Order_Date DATE,
Customer_ID INT,
FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);
In this example, the "Order" table's Customer_ID field is a foreign key that establishes the one-to-many relationship.
A Many-to-Many Relationship in DBMS occurs when multiple entities in one set can be associated with multiple entities in another set. This is often referred to as an N : N relationship. In real-world scenarios, this type of relationship is common—for example, a student can enroll in multiple courses, and each course can have multiple students enrolled.
In relational databases, many-to-many relationships cannot be represented directly between two tables. Instead, they are implemented using an associative entity—often called a junction table or bridge table. This table acts as a connector between the two entities, holding references (usually as foreign keys) to the primary keys of each related table.
Consider two entities: Student and Course. To represent which students are enrolled in which courses, you use an Enrollments table (the associative entity). Each record in the Enrollments table associates a Student with a Course.
This structure ensures that:
In an ER diagram, a many-to-many relationship is shown by a diamond connecting the two entity rectangles, often labeled with the relationship name (e.g., "enrolls"). The diamond may have lines connecting it to both entities, indicating the N : N cardinality.
CREATE TABLE Student (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50)
);
CREATE TABLE Course (
Course_ID INT PRIMARY KEY,
Course_Name VARCHAR(50)
);
CREATE TABLE Enrollments (
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)
);
This setup maintains the integrity of the many-to-many relationship, with the Enrollments table acting as the bridge.
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.
Employee Table:
| Employee_ID | Employee_Name | Manager_ID |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
| 4 | David | 2 |
| 5 | Eve | 2 |
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:
-- 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);
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_ID | Customer_Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
Order Item Table (Weak Entity):
| Item_ID | Order_ID | Item_Name | Quantity |
|---|---|---|---|
| 101 | 1 | Laptop | 1 |
| 102 | 1 | Mouse | 2 |
| 103 | 2 | Keyboard | 1 |
| 104 | 2 | Monitor | 1 |
In the example:
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.
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:
| Customer_ID | Customer_Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
Order Table:
| Order_ID | Order_Amount |
|---|---|
| 101 | $250 |
| 102 | $450 |
In this:
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:
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:
| Student_ID | Student_Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
Enrollment Table:
| Student_ID | Course_Name |
|---|---|
| 1 | Mathematics |
| 2 | Computer Science |
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.
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:
| Employee_ID | Employee_Name | Department_ID |
|---|---|---|
| 1 | John | 101 |
| 2 | Sarah | NULL |
Department Table:
| Department_ID | Department_Name |
|---|---|
| 101 | HR |
| 102 | IT |
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.
In a DBMS, constraints are rules applied to table columns and relationships to ensure data accuracy, consistency, and validity. These constraints play a crucial role in defining how relationships between entities are established and maintained, directly impacting the reliability and structure of your database.
A primary key is a unique identifier for each record in a table. It ensures that no two rows have the same value for the primary key column(s), supporting entity integrity constraints.
Example: In a "Student" table, Student_ID could be the primary key.
A foreign key creates a link between two tables by referencing the primary key of another table. This enforces referential integrity constraints, ensuring that relationships between tables remain valid.
Example: An "Order" table may have a Customer_ID foreign key referencing the Customer table's primary key.
A unique key constraint ensures that all values in a column or a set of columns are distinct across the table, but unlike primary keys, unique keys can accept null values.
A super key is any combination of columns that can uniquely identify a row in a table. Every primary key is a super key, but not all super keys are primary keys.
Candidate keys are columns, or sets of columns, that qualify as unique identifiers for table records. One candidate key is chosen as the primary key; others are called alternate keys.
An alternate key is a candidate key that was not selected as the primary key but can still uniquely identify records.
A composite key consists of two or more columns used together to uniquely identify a record. Useful when a single column is insufficient for uniqueness.
Example: In an "Order_Item" table, (Order_ID, Product_ID) together form a composite key.
A surrogate key is an artificial, system-generated value (often an auto-incremented integer) used as a unique identifier when no natural key is available.
A natural key is a key that has a logical relationship to the data (e.g., an email address or social security number).
Domain constraints restrict the permissible values for a column, such as data type, range, or format (e.g., age must be a positive integer).
These constraints ensure that the primary key column(s) in a table cannot have null values, guaranteeing that every entity (row) can be uniquely identified.
These ensure that a foreign key value in one table matches an existing value in the referenced table, or is null if allowed, preventing orphaned records.
These are some of the important things about relationship in DBMS:
The linking of data, guaranteed by relationships, always respects consistency, as well as the correctness of the data inserted.
It facilitates data searches across multiple tables. Therefore, it minimises redundant entries and speeds up query execution.
Relationships reduce data redundancy and increase concurrency. Therefore it is able to maintain the relevance of data within relationships while keeping it in order.
Relationships enable more sophisticated querying, grouping, sorting, and a wide array of other processing and analytical capabilities from a user's data.
Well-defined relationships develop an organisation's database structure that is system-friendly and capable of accommodating increasing organisational demands.
Quick Note: Without relationships, databases become flat and redundant, losing the power of structured connections.
Relationships in DBMS are fundamental because they define how data connects across different tables, enabling meaningful organization and efficient retrieval. They ensure data integrity, reduce redundancy, and make complex queries possible. Well-structured relationships power modern applications, analytics, and business intelligence by turning raw data into actionable, interconnected insights.
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.
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.
Relationships guarantee the best data integrity, reduce redundancy and provide the best means of accessing related information in multiple Tables.
Total Participation means that every defined entity must join the relationship, but in partial participation, some entities do not have to participate.
A junction table links two tables having a Many-to-Many relationship by having foreign keys from two tables.
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.
Source: NxtWave CCBP - https://www.ccbp.in/blog/articles/types-of-relationship-in-dmbs