When each record of the first table can be associated to one or more records of the second table, and a single record of the second table may be related to one or more records of the first table, such a relationship exists. A many-to-many relationship is formed by two one-to-many relationships that are connected by a 'linking table' or 'associate table.' By having fields that are the primary keys of the other two tables, the bridging table connects two tables. The following example will help us comprehend this.
For example, if the entity types 'Customer' and 'Product' exist, each customer can purchase several products, and a product can be purchased by multiple customers.
To grasp the concept of a linking table in this context, consider the 'Order' entity as a linking table that connects the 'Customer' and 'Product' entities. This many-to-many relationship can be broken down into two one-to-many partnerships. To begin with, each 'Customer' can have several 'Orders,' whereas each 'Order' is only associated with one 'Customer.' Second, each 'Order' is associated with only one Product, despite the fact that several orders for the same Product may exist.
The idea of connecting described above can be grasped by considering all of the attributes of the entities 'Customer,' 'Order,' and 'Product.' The main keys of both the 'Customer' and 'Product' entities are included in the connecting table, i.e. the 'Order' table, as can be seen. When referring to the respective table from the 'Order' table, these keys operate as foreign keys.
A strong entity can have a relationship with another strong entity, or a strong entity can have a relationship with a weak entity. Participation in the relationship can be partial or whole, depending on the type of entity involved. There are two different sorts of participation barriers:
Participation in Parts
Partial Participation occurs when all of the entities of one entity type are not associated with any of the entities of another entity type. This is represented by a single line connecting the relationship to the entity type.
For example, we have two types of entities: 'Customer' and 'Order.' There may also be 'Customers' who have not placed an order. As a result, the entity is only partially involved in the relationship.
Total Participation: When all of the entities of one entity type are linked to one or more entities of another entity type, the term "total participation" is used. This is represented as a double parallel line connecting the relationship to the entity type. A powerful entity and a weak entity usually have this kind of relationship.
We have two entity types: 'Employee' and 'Dependant', for example. Then each of the 'Dependent' entities is linked to one of the 'Employee' entities. This is known as the entity's whole participation in the relationship. However, it's likely that some of the 'Employees' are unrelated to any of the 'Dependent' businesses. As a result, the 'Employee' represents half engagement in the relationship, but the 'Dependant' represents whole participation.
One to One Relationship (1:1): This is a relationship between two tables in which a single row of the first table can only be associated to one and only one record in the second table. In the same way, any row of the first table can be connected to any row of the second table.
When each record in one table is linked to only one record in the other table, this is known as a one-to-one relationship.
If there are two entities, 'Person' (Id, Name, Age, and Address) and 'Passport' (Passport id, Passport no), for example. As a result, each individual can only have one passport, and each passport can only belong to one person.
It's not very usual to have a connection like this. This type of interaction is, nonetheless, used for security reasons. We can easily save the passport id in the 'Person' database in the following example. However, we create a separate database for the 'Passport' because the passport number is potentially sensitive information that should be kept from some users. As a result, by creating a separate table, we can add an extra layer of security by limiting access to only certain database users.
|
|
