Illustrating many-to-many Relationships
Degree of a Relationship
The degree of a relationship is the "number of entities" associated with the relationship.
The n-ary relationship is the general form for degree n. Special cases are the binary, and ternary forms, where the degree is 2, and 3, respectively.
In a binary relationship, the association between two entities represents the most common type in real world modeling scenarios. A recursive binary relationship occurs when an entity is related to itself. An example might be "some employees are married to other employees". A ternary relationship involves three entities and is used when a binary relationship is not sufficient to represent your modeling scenario. Many modeling approaches recognize only binary relationships. When ternary or n-ary relationships are present in a data modeling use case, they are decomposed into two or more binary relationships.
In a binary relationship, the association between two entities represents the most common type in real world modeling scenarios. A recursive binary relationship occurs when an entity is related to itself. An example might be "some employees are married to other employees". A ternary relationship involves three entities and is used when a binary relationship is not sufficient to represent your modeling scenario. Many modeling approaches recognize only binary relationships. When ternary or n-ary relationships are present in a data modeling use case, they are decomposed into two or more binary relationships.
Connectivity and Cardinality
The connectivity of a relationship describes the mapping of associated entity instances in the relationship.
The values of connectivity are "one" or "many". The cardinality of a relationship is the actual number of related occurences for each of the two entities. The basic types of connectivity for relations are: 1) one-to-one, 2) one-to-many, and 3) many-to-many.
A one-to-one (1:1) relationship is when at most one instance of a entity A is associated with one instance of entity B. For example, "employees in the company are each assigned their own office. For each employee there exists a unique office and for each office there exists a unique employee.
A one-to-many (1:N) relationship is when for one instance of entity A, there are zero, one, or many instances of entity B, but for one instance of entity B, there is only one instance of entity A. An example of a 1:N relationships is
A one-to-one (1:1) relationship is when at most one instance of a entity A is associated with one instance of entity B. For example, "employees in the company are each assigned their own office. For each employee there exists a unique office and for each office there exists a unique employee.
A one-to-many (1:N) relationship is when for one instance of entity A, there are zero, one, or many instances of entity B, but for one instance of entity B, there is only one instance of entity A. An example of a 1:N relationships is
a department has many employees
each employee is assigned to one department
A many-to-many (M:N) relationship, sometimes called non-specific, is when for one instance of entity A, there are zero, one, or many instances of entity B and for one instance of entity B there are zero, one, or many instances of entity A. An example is:
employees can be assigned to no more than two projects at the same time;
projects must have assigned at least three employees
A single employee can be assigned to many projects and conversely, a single project can have assigned to it many employee. Here the cardinality for the relationship between employees and projects is two and the cardinality between project and employee is three.
Many-to-many relationships cannot be directly translated to relational tables but instead must be transformed into two or more one-to-many relationships using associative entities.
Conceptual Modeling
In conceptual modeling you can combine any of the cardinality choices on either side of the relationship. There are no parents or children in a relationship such as the one shown in Figure 6-9. The entities are simply noted as
being related and must be resolved by an associative or intersection entity when greater detail is necessary.
Student Class Entities
- The STUDENT and CLASS entities cannot actually be put into a direct relationship, because this is forbidden in a relational database.
- The ClassID (key attribute) from CLASS is inserted into STUDENT to form the link.
- ClassName attribute is inserted into STUDENT as well, in order to identify the classes in which a student is enrolled (not just through cryptic ClassID, but by ClassName as well).
- Assume the two entities have been converted into tables and are populated with data appropriate to each. The STUDENTs table is shown here.
- The STUDENTs table reveals a great deal of redundant data. (look at the duplicate entries in the ClassName field alone). Redundant data, you will see, is the source of the problems with many-to-many relationships.
