Types of Relationship in DBMS: Explained with Examples

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:

Key Takeaways So Far

• One-to-one ensures exclusive pairing of records between two tables.
• It enhances data separation for privacy or modular design.
• Implemented using a unique foreign key constraint.

One-to-Many and Many-to-One Relationship

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.

Key Features:

• Entity Types: The “one” and “many” sides are usually different entity types, such as “Customer” and “Order”, “Author” and “Book”, or “Student” and “Course”.
• Foreign Keys: The table on the “many” side contains a foreign key that references the primary key of the “one” side.
• Normalized Database Tables: Proper use of one-to-many relationships helps achieve normalized database tables, reducing redundancy and supporting efficient queries.
• Data Consistency & Integrity: By enforcing relationships through keys, the database maintains reliable links between related data, preventing orphaned records and preserving accuracy.

Common Examples:

1. Customer and Order Database:
   
   • Each customer can place multiple orders (one-to-many from customer to order).
   • Each order is placed by a single customer (many-to-one from order to customer).
   • The “Order” table contains a foreign key referencing the “Customer” table’s primary key.
2. Authors and Books:
   
   • An author can write multiple books, but each book is typically written by one author.
   • The “Book” table includes a foreign key referencing the “Author” table.
3. Customer and Account:
   
   • A customer may have several accounts, but each account belongs to only one customer.
   • The “Account” table uses a foreign key to link to the “Customer” table.
4. Students and Courses (with Associative Tables):
   
   • In some cases, a direct one-to-many relationship isn’t sufficient. For example, if students can enroll in multiple courses and each course can have multiple students, an associative table (junction table) is used to create a many-to-many relationship. However, if each student enrolls in only one course, the relationship is one-to-many from course to student.
5. Weak Entities:
   
   • Sometimes, the “many” side is a weak entity that cannot be uniquely identified without the “one” side. For example, “Order Items” (weak entity) depend on the existence of an “Order” (strong entity).

Example SQL Implementation:

-- 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.

Summary:

• One-to-many and many-to-one relationships are essential for modeling real-world associations between entity types.
• They promote data consistency and integrity through the use of primary keys and foreign keys.
• Associative tables and weak entities may be used in more complex scenarios to accurately represent relationships.

Many-to-Many Relationship

This will replace your current content in that section, ensuring thorough coverage and incorporating all the relevant terms. Here’s the suggested content:

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.

How Many-to-Many Relationships Work

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.

Example: Student, Course, and Enrollments

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.

• Student Table: Contains attributes like Student_ID, Student_Name, etc.
• Course Table: Contains attributes like Course_ID, Course_Name, etc.
• Enrollments Table: Contains attributes such as Student_ID and Course_ID (both as foreign keys), and possibly additional attributes like enrollment date.

This structure ensures that:

• A student (entity) can enroll in multiple courses (entities).
• A course (entity) can have multiple students (entities) enrolled.

ER Diagram Representation

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.

Key Terms in Practice

• Entities: The objects or concepts being related (e.g., Student, Course).
• Attributes: Properties of entities or the associative entity (e.g., enrollment date in the Enrollments table).
• Junction Table / Bridge Table / Associative Entity: The table that resolves the many-to-many relationship (e.g., Enrollments).
• Foreign Key: Columns in the junction table that reference the primary keys of the related entities.
• Multi-user Transactions: Many-to-many relationships are important in environments with multi-user transactions, ensuring data integrity when multiple users enroll in courses or register for items simultaneously.

Example SQL Implementation:

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.

Visual Representation in ER Diagram:

• Rectangles for Student and Course entities.
• A diamond labeled "enrolls" connecting both entities.
• Lines from the diamond to both entities, each marked with "N" to indicate many.

Key Takeaways So Far

• Requires a third table (bridge/junction) for implementation.
• Common in education, e-commerce, and social networks.
• Maintains flexibility for dynamic data relationships.

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.

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.

Key Types of Constraints

1. Primary Keys
   
   • 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.
2. Foreign Keys
   
   • 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.
3. Unique Keys
   
   • 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.
4. Super Keys
   
   • 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.
5. Candidate 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.
6. Alternate Keys
   
   • An alternate key is a candidate key that was not selected as the primary key but can still uniquely identify records.
7. Composite Keys
   
   • 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.
8. Surrogate Keys
   
   • 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.
9. Natural Keys
   
   • A natural key is a key that has a logical relationship to the data (e.g., an email address or social security number).
10. Domain Constraints
    
    • Domain constraints restrict the permissible values for a column, such as data type, range, or format (e.g., age must be a positive integer).
11. Entity Integrity Constraints
    
    • 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.
12. Referential Integrity Constraints
    
    • 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.

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.

Quick Note: Without relationships, databases become flat and redundant, losing the power of structured connections.

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.

Why It Matters?

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.

Practical Advice for Learners

• Always define primary and foreign keys clearly.
• Use ER diagrams early in database design.
• Normalize data up to at least 3NF (Third Normal Form) for efficiency.
• Test relationships using sample SQL joins before deployment.

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.