Databases Reference
In-Depth Information
If the entities on the B side of the relationship exist without participating
in a relationship, then entity set B is said to have partial participation in the
relationship. Exhibit 9 shows a variation in a one-to-many relationship
where one of the B entities is required to participate in the relationship.
This constraint often is referred to as total participation.
Exhibit 10 summarizes the English wording for partial and total partici-
pation of entities for both sides of a one-to-many relationship in the English
CDDL. Total participation is denoted in entity-relationship diagrams with a
thick line connecting the rectangle representing the entity — which must
participate in the relationship — and the relationship diamond. In SQL,
total participation is denoted by declaring the foreign key to be “NOT
NULL.” In Exhibit 9, the DeptName of the Employee is declared NOT NULL.
This requires the Employee table to contain a value for the primary key of
some Department record. This enforces the total constraint by requiring
that each Employee works in some Department. As a comparison, Exhibit 9
shows that the DeptName of Employee has not been declared NOT NULL.
This means that DeptName may be null, which in turn implies that the
Employee does not work in any department. In relational diagrams, the
words “NOT NULL” have been added near the relationship arrow to indi-
cate total participation.
Many-to-Many Relationships
Exhibit 11 illustrates a many-to-many relationship between two entity
sets. A many-to-many relationship implies that any number of entities from
each of two entity sets can participate in a single relationship. Many-to-
many relationships occur frequently in the real world. For example, pairs of
entity sets — employees and projects, students and classes, and producers
and suppliers — often are related by many-to-many relationships. There
are two approaches for dealing with many-to-many relationships in the
conceptual schema:
• explicit representation, as presented in entity-relationship diagrams
• implicit representation by two one-to-many relationships, as shown in
SQL notation and relational diagrams
Using English, a DBA can describe a many-to-many relationship either:
• directly, by specifying the participation of each entity set within the re-
lationship; or example, one or more employees may be assigned to
one or more projects
• indirectly, by specifying two one-to-many relationships involving an in-
duced entity set; for example, each employee is related to an Assign-
ment, and each project is related to an Assignment
Search WWH ::
Custom Search