Relationships in DBMS

Gaurish Anand
Last Updated: May 13, 2022

Introduction

Database management systems (or DBMS) are used to manage databases. A database is information that is stored electronically in a computer system. Data inside a database can be in texts, tables, charts, etc.

To make processing and querying of data faster, the most common types of databases in use today are often described in rows and columns in a sequence of tables. Data can be easily accessed, managed, updated, regulated, and organized using tables and DBMS.

A group of entities with the same set of attributes is referred to as an entity set. An entity in a database management system is a table or an attribute of a table; hence an ER diagram depicts the entire logical structure by displaying relationships between tables and their characteristics. A basic ER diagram looks like this: 

Relationships in DBMS

Relationships tell us how two or more datasets/entities are linked. It helps in storing the data in separate tables. 
A relationship is represented by a diamond shape in an ER diagram.

Types of relationships between 2 entities

  1. One-to-One Relationship

    According to this relationship, a single record in Table A is related to a single record in Table B, and vice-versa is also true.  

    Example: Consider 2 entities - 
    Person - ID, name, age, address
    Passport - passport_ID, passport_no

    Only one passport is allowed per person, and each passport belongs to only one person. Therefore it is a one-to-one relationship.
     
  2. One-to-Many or Many-to-One Relationship 

    According to this relationship, each record of Table A can be related to one or more than one record of table B. 

    Example: Consider 2 entities - 

    Each customer can have multiple accounts, so it is a one-to-many relationship. 
    But if we see the other way, many accounts are held by one customer only. Therefore it is a many-to-one relationship also.
     
  3. Many-to-Many Relationship

    According to this relationship, each record of Table A can be related to one or more than one record of table B and vice versa is also true, i.e., each record of Table B can be connected to one or more than one record of table A. 

    Example: Consider 2 entities - 

    Each customer can buy more than 1 product, and many customers can buy one. Therefore it is a many-to-many relationship.

Types of Constraints

This constraint defines the number of instances of an entity ( of an entity set) participating in the relationship type.

Types of participation constraints

  1. Partial Participation

    1. It indicates whether or not each entity in the entity set can participate in the relationship instance of the relationship set.
    2. It is indicated using a single line between the entity and relational sets.

      In the above example: A single line between the courses and enrolled in a relationship signifies partial participation. It means there might be some courses that any student does not enroll in.
       
  2. Total Participation

    1. It indicates whether or not each entity in the entity set can participate in the relationship instance of the relationship set.
    2. It is indicated using a double line between the entity and relational sets.

      In the above example: A double line between the student and enrolled in a relationship signifies the total participation, which means that every student must have enrolled at least in 1 course.

Frequently Asked Questions

  1. What is RDBMS?
    A relational database management system is software that stores, manages, queries, and retrieves data from a relational database (RDBMS).
     
  2. What is SQL?
    SQL stands for Structured Query Language. It is used for storing and managing data in a relational database management system (RDBMS).
     
  3. What are the four types of database normalization?
    The 4 types of Normalization in DBMS are 1nF, 2nF, 3nF, BCNF.
     
  4. Why are normalization and denormalization used in databases?
    Normalization is used in databases to reduce redundancy and improve overall data integrity. Denormalization is a technique performed after the normalization of databases. Denormalization is used to increase the performance of select queries where we may be performing multiple join operations.

Key Takeaways

In this article, we learned about different types of relationships in DBMS. We also learned how proper relationships in DBMS are essential, and without the proper relationships, the entire database structure would become useless.

Are you planning to ace the interviews of reputed product-based companies like Amazon, Google, Microsoft, and more? Attempt our Online Mock Test Series on CodeStudio now and check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions.

Was this article helpful ?
0 upvotes