Relationship Types and Relationship Sets in DBMS

In relational databases, it is essential to understand the concept of relationships. Relationships are used for managing how the data interacts. Relationships help define how data interacts within the database. The concepts of relationship types and relationship sets are used to structure these interactions, allowing for meaningful and efficient data storage and retrieval.

In this chapter, we'll explore these ideas through examples to clarify their purpose and significance.

Basic Concepts of Relationship Types

Relationship types refer to the specific ways in which entities relate to each other within a database system. You can think of them as the rules or "kinds" of connections established between different data tables. Each relationship type defines how one entity interacts with another, guiding the structure and logic of data linkage.

To grasp the basics, consider how relationships in data resemble real-life connections. For example, in a school database, Students and Courses are two separate entities. The relationship between them could be defined as Enrollment. This relationship shows which students are enrolled in which courses. Here, Enrollment is the relationship type that links the Student entity set with the Course entity set.

Characteristics of Relationship Types

In relationships, we see several characteristics, as given below:

• Cardinality − Defines the number of entities involved. These could be one-to-one, one-to-many, or many-to-many relationship.
• Attributes − Sometimes, relationships have their own attributes. Like, the date of enrollment could be an attribute of the "enrollment" relationship type.
• Participation Constraints − Specifies whether an entity's participation in the relationship is total or partial.

Relationship Sets Explained

Next we must focus on the relationship sets. A relationship set is essentially a collection of relationships of the same type. It is a specific instance of all possible pairs (or combinations) of entity instances that satisfy the relationship type. If the "enrollment" is our relationship type, then the set of all current enrollments in the school makes up the relationship set.

Let us see the school database again. If three students, Alice, Bob, and Carlos, are enrolled in two courses, the Math and Science, the relationship set of "enrollment" might look like this:

• Alice is enrolled in Math
• Bob is enrolled in Science
• Carlos is enrolled in both Math and Science

Here, the set includes these specific instances. The mapping out the active links between the Student and Course entity sets.

Examples of Relationship Sets

Consider the "Works_On" relation. It needs the Employee and Project relations.

Employee Relation:

Project Relation

Works_On relation

Attributes − The relationship type "Works_On" have attributes like "Hours," this is indicating how many hours an employee works on a specific project.

Relationship Set − Now consider John, who works 20 hours on Project X and 10 hours on Project Y. Jane, on the other hand, spends 15 hours on Project X and 25 hours on Project Z. So, the set of these instances forms the relationship set for "Works_On."

Degree of a Relationship Type

The term "degree of a relationship type" indicates the number of participating entity types. This section covers the most common degrees: binary, ternary, and higher-order relationships.

Binary Relationships

Binary relationships, as the name suggests, needs two entity types. The "Works_On" example is a classic case of a binary relationship, as it connects "Employee" and "Project".

Let's take another example. Suppose in a school database, a "Teaches" relationship between the entity types "Teacher" and "Subject" is a binary relationship.

Ternary and Higher-Degree Relationships

Relations could be more expanded. Ternary relationships involve three entity types. For instance, if a company needs to model which "Employee" works on which "Project" and for which "Client," is another relation. It is a ternary relationship.

In higher-degree relationships, complexity increases. We can imagine a four-way link connecting the relations "Employee," "Project," "Client," and "Location".

Attributes of Relationship Types

As we know, the columns are attributes and they do not just belong to entities. The relationship types can have their own attributes that provide more detail.

Attributes in "Works_On"

As we have seen in the "Works_On" relationship, the attributes called "Hours" are important. They indicate how much time an employee dedicates to a project. If John works 30 hours on Project Z, the attribute "Hours" would hold that data in the relationship set entry linking John and Project Z.

Mapping Cardinalities

Mapping cardinalities indicate the number of associations between entities. Mapping cardinalities play an important role in accurately describing real-world constraints.

• One-to-One (1:1) − A one-to-one relationship means one entity in set A is related to one entity in set B. For example, each "Principal" (entity type) will have only one "School" (another entity type).
• One-to-Many (1 : N) − A one-to-many relationship means one entity in set A is connected to multiple entities in set B. In the eBook example, each "Employee" may work on several "Projects."
• Many-to-Many (M : N) − Many-to-many relationships mean entities from both sets can be connected in numerous ways. The "Works_On" relationship type is a perfect example. Employees can work on different projects, and projects can have multiple employees.

Conclusion

In this chapter, we explained in detail the concepts of "relationship types" and "relationship sets". We understood their importance in databases, and how they illustrate connections between data. We also explored the basic definitions, learned how to represent the relationships in ER diagrams.

In addition, we touched upon the concept of attributes and degrees of relationship types, before concluding the chapter with a brief introduction of "mapping cardinalities" and covering how these concepts work in real-world database modeling.