Convert many-to-many relationships into one-to-many relationships.
Convert many-to-many Relationships into one-to-many Relationships
Entities in a many-to-many relationship must be linked in a special way, that is through a third entity, called a composite entity also known as an associative entity.
A composite entity has only one function: to provide an indirect link between two entities in a M:N relationship.
In the language of tables, a composite entity is termed a linking table. A composite entity has no key attribute of its own; rather, it receives the key attributes from each of the two entities it links, and combines them to form a composite key attribute.
In the language of tables, a composite key attribute is termed a composite primary key.
The following graphic illustrates a composite entity that now indirectly links the STUDENT and CLASS entities:
The M:N relationship between STUDENT and CLASS has been dissolved into two one-to-many relations:
The 1:N relationship between STUDENT and STUDENT CLASSES reads this way: for one instance of STUDENT, there exists zero, one, or many instances of STUDENT CLASSES; but for one instance of STUDENT CLASSES, there exists zero or one instance of STUDENT.
The 1:N relationship between CLASS and STUDENT CLASSES reads this way: For one instance of CLASS, there exists zero, one, or many instances of STUDENT CLASSES; but for one instance of STUDENT CLASSES, there exists zero or one instance of CLASS.
Sometimes, but by no means always, the composite entity will “swipe” attributes from one or both of the entities it links, because those attributes would be more logically placed in the composite entity. In the case of STUDENT CLASSES, however, none of the non-key attributes from STUDENT or CLASS should be removed to the composite entity. The designer makes this decision on a case-by-case basis. The next lesson describes types of participation in relationships.
composite entity: In relational database design, a composite entity is used to provide an indirect link between two entities in a M:N (many-to-many) relationship.Also called a linking table.
composite key attribute: A key attribute belonging to a composite entity, comprising the key attributes from each of the two entities linked by the composite entity.Also called a composite primary key.
composite primary key: Another name for a composite key attribute.