Relationship in DBMS

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning

Overview

A database is a collection of data that contains multiple tables in which the data is stored in the form of rows and columns. The process by which these databases are managed and controlled is known as the database management system (DBMS). Basically, it is software that is used for performing various operations on the data. An example of a database management system can be MySQL, Oracle, etc.

Relationships correspond to connections between things, people, etc. These relationships in DBMS are actually created between entities. These entities are real-life objects that are distinguishable from other objects and have some attributes associated with them.

You can refer to the Entity in DBMS for more information.

What is a relationship in DBMS?

An entity in a database management system is a real-life object distinguishable from other objects. These entities have an attribute that defines the properties and characteristics of the entity.

Examples of an entity can be a student, a car, animals, etc. All these are real-life entities whose data can be stored in a database. A student's attributes can be his Id, course, or semester which can describe a student entity.

Any association between two entities is known as a relationship between those two entities. This relationship in DBMS is represented using a diamond shape in the entity-relationship diagram. An entity-relationship diagram is a graphical representation of entities and the relationships that exist between them.

For example:-

The driver drives a car.

In the above example, the driver and the car are entities whereas the word drives is a relationship between those entities. Moreover, the attributes of the driver entity can be that it is a person, and the car entity can be that it is a vehicle.

example of relationship in DBMS

Similarly, another example of the relationship in DBMS is-

Rohit plays football.

Therefore, here Rohit and football are entities, and plays are the relationship between them. However, the attribute associated with the Rohit entity is that it is a person's name and the football entity belongs to a sport.

Types of Relationships in DBMS

Three types of relationships can exist between two entities of tables, which are given below and discussed with examples.

  • One-to-One relationship
  • One-to-Many relationship or Many-to-One relationship
  • Many-to-Many relationship

One-to-One Relationship

A One-to-one relationship means a single record in Table A is related to a single record in Table B and vice-versa.

For example, If there are two entities, 'Person'(Name, age, address, contact no.) and 'Aadhar card' (Name, Aadhar no.). So each person can have only one aadhar card, and the single aadhar card belongs to only one person.

You can visualize the one-to-one relationship in DBMS like this:

Example of one-to-one relationship in dbms

This type of relationship is used for security purposes. In the above example, we can store the aadhar card number in the same entity, 'Person', but we created another table for the 'Aadhar card' because the aadhar card number may be sensitive data and should be hidden from others. Let's take another example with the databases.

Example:

Consider a table of Employee as shown below:

Table A:

emp_idemp_nameemp_address
001Claira Anderson113, Zaraiah Road, TX 77001
003Marc Doe34343, Palm Jumeriah Road, Dubai 990039
005Bruce Quilt23, Santa Cruiz Road, NY 44303

Now, you can place the employee address in a separate table as shown below:

Employee:

emp_idemp_nameemp_address_id
001Claira Anderson901
003Marc Doe903
005Bruce Quilt905

Employee Address:

emp_address_idemp_address
901113, Zaraiah Road, TX 77001
90334343, Palm Jumeriah Road, Dubai 990039
90523, Santa Cruiz Road, NY 44303

Now, the relationship between the Employees table and Employee Address can be created. In the above example, one emp_address_id belongs to only one emp_address; that is, the relationship is a one-to-one relationship. This type of relationship is not very common as the employee's address can also be included in the Employee table as shown in table A, and this would work too.

One-to-Many Relationship

Such a relationship exists when each record of table A can be related to one or more records of another table, i.e., table B. However, a single record in table B will have a link to a single record in table A. This is the most common relationship you will find that is widely used. A one-to-many relationship in DBMS can also be named a many-to-one relationship, depending on how we view it.

The one-to-many relationship in DBMS exists between the pair of tables if a single record in one table is related to one or more records of the other table. For example, if there are two entities, 'Customer' and 'Account', then each customer can have more than one account, and each account is owned by one customer only.

You can visualize the one-to-many relationship in DBMS like this:

Example of a one-to-many relationship in dbms

Example:

Consider a table of Customers and Orders as shown below:

Customers:

customer_idcustomer_namecustomer_no
111Maria Danzie1199229921
222Alex Brat3939637382
333Sania Martini82492835634

Orders:

order_idorder_amountcustomer_id
100011200222
100022000333
100034500222
100041220111
100053550222

The relationship between the above two tables can be visualized like this:

Example of a one-to-many relationship between the tables

In this example, there is a one-to-many relationship if looked at from the perspective of the Customers. As shown above, the customer_id - 222 is related to the three different order_id. Similarly, there is a many-to-one relationship between the tables if looked at from the perspective of the Orders table.

Many-to-Many Relationship

A many-to-many relationship exists between the tables if a single record of the first table is related to one or more records of the second table and a single record in the second table is related to one or more records of the first table. Consider the tables A and B. In a many-to-many relationship, each record in table A can be linked to one or more records in table B and vice-versa. It is also represented as an N: N relationship.

For example, consider the two tables, i.e., a student table and a courses table. A particular student may enroll himself in one or more than one course, while a course also may have one or more students. Hence, this is a great example of many-to-many relationships.

You can visualize the many-to-many relationship in DBMS like this:

A many-to-many relationship from the perspective of table A.

First example of the many-to-many relationship between the tables

A many-to-many relationship from the perspective of table B.

Second example of the many-to-many relationship between the tables

Example:

Consider the tables of Orders, Items, and Items Orders as shown below:

Orders:

order_idorder_amountcustomer_id
100011200222
100022000333
100034500222
100041220111
100053550222

Items:

item_iditem_name
1201Maggi
1202Pizza
1203Kurtossh

Items Orders:

order_iditem_id
100011201
100011203
100041202
100041203
100051201
100031201

The relationship between the above two tables can be visualized like this:

Example of a many-to-many relationship in dbms

As shown in the above example, the table Items Orders has only one purpose: to create many-to-many relationships between the items.

Type of relationshipDescription
One-to-oneThis type of relationship holds when a record of one table is related to only one record of another table. An example of such a relationship can be the records of people all over India, where each person can have only one Aadhaar card.
One-to-manyThis type of relationship holds when one record of a table is related to many records of another table. An example of such a relationship can be an employee and a project database in which a single employee works on multiple projects simultaneously.
Many-to-ManyThis type of relationship holds when many records of one table are related to many records of another table. An example of such a relationship can be a Course database in which more than one teacher can teach multiple courses.

three types of relationship in dbms

Importance of Relationships in DBMS

  • These relationships help establish a smooth workflow in a database management system. When we require such queries that fetch data from disparate tables, our query would fail and return a null value in the absence of such relationships.
  • These relationships help in minimizing the redundancy of data by combining some cells together. While establishing these relationships between tables, some changes would be applied to the table structure that would remove the irrelevant data from the table.
  • A well-defined relationship provides referential integrity to the table structure, making the database management system more efficient.
  • These relationships make collaboration among multiple users using the database much easier.

Conclusion

  • A relationship is an association between entities in a table.
  • Entities are real-life objects that are distinguishable from the other objects and have an attribute associated with them.
  • These attributes are properties or characteristics of the entities.
  • These entities can be related in any of the following relationships: ' one-to-one, one-to-many, or many-to-many`.
  • The one-to-one relationship is used when one record of a table is related to only one record of another table.
  • A one-to-many relationship is used when one record of a table is related to many records of another table.
  • A many-to-many relationship is used when many records of one table are related to another.
  • These relationships help `minimize data redundancy' in a table.

Read More: