Types of Relationship in DBMS – Guide with Examples

It’s as important in a database management system as knowing what it is and what else is related to it. Think about your daily life: Simpler than that has to do with how you act when you interact with people (in this case, friends, family, colleagues). Data just needs to be ordered so it makes sense to us, just like databases where pieces of data need to relate to each other. That is exactly where relationships enter: database management systems (DBMS).

Databases store structured data to be organized. But to make that data useful, we need to define relationships between entities. In real life, these relationships exist in all different shapes and sizes. In this article, we explain these relationships, why they are important, and how DBMS uses them to build efficient databases.

What is the Relationship in DBMS?

So, first things first, let’s define what a relationship means in DBMS. When we discuss a relationship in a database, we talk about how one table relates to another. These are important because linking them together allows databases to be fast at storing and, at a later date, to be fast at retrieving that data.

Say you might have a “Customer” table and an “Order” table in a company database. If there were a relationship between these two, it would tell us which customers placed which order. You’ll always be able to quickly see all orders made by a specific customer or match an order to its matching customer. Now, let’s look at the three major types of relationships in DBMS:

One-to-One Relationship

One-to-Many Relationship

Many-to-Many Relationship

Also Read: Entity in DBMS

Other Types of Relationships

Apart from the primary types, some additional relationships are less frequently used but still significant in certain database designs:

Self-Referencing Relationship

An entity relating to itself is a case of a self-reference relationship. Records within an entity may have hierarchical dependencies, which are used when records need to be updated according to those dependencies.

Example:

Each employee belongs to an ‘Employee’ entity, and each employee’s manager can be another employee. This relationship leverages the entity (Employee) to be connected to itself; thus, it creates a relationship where the entity (Employee) has and can have a supervisor-subordinate hierarchy. There is a foreign key index on the same table’s primary key. With this setup, the entity can relate different records within itself, thus having a self-referencing relation.

Weak Relationship

A weak relationship occurs when one entity’s presence relies on another. A dependent entity (also a weak entity) cannot be uniquely identified unless it is in conjunction with the primary key of the related parent entity.

Example:

Suppose we have a database with Customers and Address entities. The “Address” entity has a weak relationship with the “Customer” table because addresses are associated with a single one of them, and one address typically belongs to a single customer. If the customer is deleted, the address record becomes inaccurate. To achieve this relationship, the weak entity (Address here in the example) then contains a foreign key linking to the primary key of the parent entity (Customer here in the example) to ensure that the parent entity is linked to a weak entity and that data is not tampered with.

Also Read: Relational Model in DBMS

Participation Constraints

Participation constraints, in particular, describe how many entities participate in a relationship in a database. They then decide if all or just a few instances of an entity are supposed to be involved in this relationship. There are two types of participation constraints:

Partial Participation

The relationship is associated only with some instances of the entity, which means not every instance of the entity must participate.

Example: Let’s say we have an Employees table and a ParkingSpots table in a company database. Not all employees are assigned parking. This relationship is only with the employees with parking spots on that side of the equation. It is called partial participation when only a part of the entity (Employees) participates.

Total Participation

The relationship can only be held between all instances of an entity. Every instance has to be associated.

Example: We have a “Students” table and a “Courses” table in a school database. Every student must enroll in at least one course. In this case, all student records have to have a relationship with the Courses table. This is called total because all the entity (students) instances are in the relationship.

Conclusion

Fundamentally, relationships are needed in DBMS to create data in structured, reliable, and efficient databases. It doesn’t matter if you’re talking about one-to-one, one-to-many, or many-to-many. You must know these relationships to create databases that help any application or system. The key to an efficient and easy-to-manage database is to find the right type of relationship and follow these best practices. Remember that a good database design is a strong weapon for storing and accessing data and the relationship to make this run nicely. If you want to explore DBMS more deeply, consider pursuing the Accelerator Program in Business Analytics and Data Science at Hero Vired.