Types of Relationship in DBMS

Relationships

Let us first define the term relationships.

What Are Relationships?

A relationship can be defined as:

a connection or set of associations, or
a rule for communication among entities:

Example: In college the database, the association between student and course entity, i.e., “Student opts course” is an example of a relationship.

Relationship sets

A relationship set is a set of relationships of the same type.

For example, consider the relationship between two entity sets student and course. Collection of all the instances of relationship opts forms a relationship set called relationship type.

Degree

degree of a relationship type is the number of participating entity types. The relationship between two entities is called binary relationship. A relationship among three entities is called ternary relationship. . Similarly relationship among n entities is called n-ry relationship. Types of Relationship in DBMS

Relationship Cardinality

Cardinality specifies the number of instances of an entity associated with another entity participating in a relationship. Based on the cardinality binary relationship can be further classified into the following categories: Types of Relationship in DBMS

One-to-one: An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A.

Example : Relationship between college and principal

One college can have at the most one principal and one principal can be assigned to only one college.

Similarly we can define the relationship between university and Vice Chancellor.

One-to-many: An entity in A is associated with any number of entities in B. An entity in B is associated with at the most one entity in A. Types of Relationship in DBMS

Example : Relationship between department and faculty.

One department can appoint any number of faculty members but a faculty member is assigned to only one department..

Many-to-one: An entity in A is associated with at most one entity in B. An entity in B is associated with any number in A.

Example : Relationship between course and instructor. An instructor can teach various courses but a course can be taught only by one instructor. Please note this is an assumption. Types of Relationship in DBMS

Many-to-many: Entities in A and B are associated with any number of entities from each other.

Example : Taught_by Relationship between course and faculty. One faculty member can be assigned to teach many courses and one course may be taught by many faculty members.