When creating a table What are the two types of relationships you can create?

A one-to-one relationship means that a record in the first table can have one and only one record related to it in a second table. For example, a product listed in a products table would have only one country of origin record in a operations table.

Practice Question

One-to-Many Relationships

One of the most common relationships between tables is the one-to-many relationship. In a one-to-many relationship, a record in the first table is related to multiple records in the second table. For example, an employee table could have relationships with a payroll table that contains multiple records of paychecks.

Practice Question

Many-to-Many Relationships

Many-to-many relationships are defined as a record in the first table having multiple relationships in the second table and those records in the second table also having multiple relationships within the first table. For example, a streaming service would have TV series that are selected to view by multiple customers while each customer of the service would watch multiple TV series.

We are related to things around us in some or the other way. We are related to our family, friends, etc and these relationships are of different kinds. For example , you and your father are related. There is a parent-child relationship between you. You have only one father but he may have many children. You and your siblings are related. You and your teacher are related. The teacher teaches you as well as many other students. You also study from different teachers.

Database relationships are also very similar to such relationships. We are done with the ER model and we also know about the Entity. Now, in this blog, we will see the database relationships i.e. the second part of the ER model in details.

Relationship in DBMS

Any association between two entity types is called a relationship. Entities take part in the relationship. It is represented by a diamond shape.

For example, A teacher teaches students. Here, " teaches " is a relationship and this is the relationship between a Teacher entity and a Student entity.

We have two entity types of 'Customer'(Customer_id, Name, City, Phone) and 'Account'(Account_no, Type, Balance). We store the data of 'Customer' in one table and his accounts details in the 'Account' table. Now, to link these two tables we need to insert the primary key 'Customer_id' of the 'Customer' table in the 'Account' table. This key acts as a foreign key for the 'Account' table and refers to a column with the same name in the 'Customer' table. This is how a relationship between two tables is established. There are three types of relationships that can exist between two entities.

• One-to-One Relationship
• One-to-Many or Many-to-One Relationship
• Many-to-Many Relationship

One-to-One Relationship

Such a relationship exists when each record of one table is related to only one record of the other table.

For example, If there are two entities ‘Person’ (Id, Name, Age, Address)and ‘Passport’(Passport_id, Passport_no). So, each person can have only one passport and each passport belongs to only one person.

Such a relationship is not very common. However, such a relationship is used for security purposes. In the above example, we can easily store the passport id in the ‘Person’ table only. But, we make another table for the ‘Passport’ because Passport number may be sensitive data and it should be hidden from certain users. So, by making a separate table we provide extra security that only certain database users can see it.

One-to-Many or Many-to-One Relationship

Such a relationship exists when each record of one table can be related to one or more than one record of the other table. This relationship is the most common relationship found. A one-to-many relationship can also be said as a many-to-one relationship depending upon the way we view it.

For example, If there are two entity type ‘Customer’ and ‘Account’ then each ‘Customer’ can have more than one ‘Account’ but each ‘Account’ is held by only one ‘Customer’. In this example, we can say that each Customer is associated with many Account. So, it is a one-to-many relationship. But, if we see it the other way i.e many Account is associated with one Customer then we can say that it is a many-to-one relationship.

Many-to-Many Relationship

Such a relationship exists when each record of the first table can be related to one or more than one record of the second table and a single record of the second table can be related to one or more than one record of the first table. A many-to-many relationship can be seen as a two one-to-many relationship which is linked by a 'linking table' or 'associate table'. The linking table links two tables by having fields which are the primary key of the other two tables. We can understand this with the following example.

Example: If there are two entity type ‘Customer’ and ‘Product’ then each customer can buy more than one product and a product can be bought by many different customers.

Now, to understand the concept of the linking table here, we can have the ‘Order’ entity as a linking table which links the ‘Customer’ and ‘Product’ entity. We can break this many-to-many relationship in two one-to-many relationships. First, each ‘Customer’ can have many ‘Order’ whereas each ‘Order’ is related to only one ‘Customer’. Second, each ‘Order’ is related only one Product wheres there can many orders for the same Product.

In the above concept of linking can be understood with the help of taking into consideration all the attributes of the entities 'Customer', 'Order' and 'Product'. We can see that the primary key of both 'Customer' and 'Product' entity are included in the linking table i.e 'Order' table. These key act as foreign keys while referring to the respective table from the 'Order' table.

Participation Constraints

The relationship can be between two strong entity or a strong entity and a weak entity. Depending upon the type of entity participating in the relationship, the participation can be partial or total. There are two types of participation constraints:

• Partial Participation
• Total Participation

Partial Participation: Partial Participation exists when all the entity of an entity type is not associated with one or the other entity of another entity type. This is represented by joining the relationship with the entity type with the help of one line.

Example: We have two entity type ‘Customer’ and ‘Order’. Then there can be ‘Customer’ who have not done any order. So, here there is partial participation of the entity in the relationship.

Total Participation: Total Participation exists when all the entity of an entity type is associated with one or the other entity of another entity type. This is represented by joining the relationship with the entity type with the help of a double parallel line. Such a relationship usually exist between a strong entity and a weak entity.

Example: We have two entity type ‘Employee’ and ‘Dependant’. Then all the ‘Dependent’ entity are related to one or the other ‘Employee’ entity. This is called total participation of the entity in the relationship. But, it may be possible that some ‘Employee’ is not related to any of the ‘Dependant’ entity. So, ‘Employee’ is showing partial participation whereas the ‘Dependant’ is showing total participation in the relationship.

This is all about the type os relationship in E-R model of DBMS. Hope you learned something new today.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here .

When creating a table what are the two types of relationships you can create in Dataverse?

What are the types of relationships which we can create between two tables explain?

What are the different types of table relationships?

Can a table have two relationships?