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

Let’s learn about these relationships in detail:

One-to-One Relationship

In this relationship, there is only one record in each table, and every primary key value belongs to none or a single record in the related table. Business rules commonly force one-to-one relationships and don’t come naturally from the data. For one reason, these relationships are not very common in practice: Perhaps it’s better to combine the two tables into one.

Example

Consider two entities: “Student”(StudentId, Name, DateOfBirth, Email) and “StudentIDCard” (CardId, CardNumber, IssueDate). Every student will have only one student ID card, and a student ID card will be given to only one student. By doing this, a one-to-one relationship between Student and StudentIDCard is established.

One-to-Many Relationship

The most common type of relationship in a DBMS is the one-to-many relationship. To this end, a record of one entity may be associated with many records of another entity, and each record of the second entity is about a single record of the first entity. For example, if we have a customer and order database, we can place multiple orders, but each order exists for just one customer.

Example

The value in the Customer ID field in the Orders table is passed to the Customer ID field in the Customers table, and the relationship above returns related records where the value in the Customer ID field is the same. As it works both ways, there are also many-to-one relationships.

Many-to-Many Relationship

Such a relationship is when every record of the first table can be associated with 0 or more records of the second table but also when a record of the second table has 0 or more records from the first table. Two one-to-many relationships are combined by an ‘associate table’ or ‘linking table’ to form a Many-to-Many relationship. The bridging table connects two tables by having a field from the table with the primary key of the other two tables.

Example

In the following example, we will have a “Students” table, a record for every student, and a Class table for each class. Two one-to-many relationships are created using a join table, Enrollments, between the two tables. Each student in the Students table has a unique key; the Student ID in the Students table is the primary key. The Classes table uses the Class ID to uniquely identify the primary key for each class. The foreign keys in the Enrollments table are Student ID and Class ID.

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.

Importance of Relationships in DBMS

Let’s look at some points that why relationships are important in DBMS:

• Data Integrity: Maintaining the accuracy and consistency of data depends upon relationships (related records should be connected correctly).
• Normalization: They make data redundant in a sense by allowing data to be stored in different tables, hence making your database well structured and optimized.
• Ease of Access: Databases make the retrieval of relevant data interconnected and most efficient by defining the relationships between the objects.

Also Read: DBMS Interview Questions With Answers

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.