Скачать или смотреть LECTURE #15| CONSTRAINTS ON BINARY RELATIONSHIP: CARDANILITY RATIOS-PARTICIPATION | TOTAL-PARTIAL

NOTES LINK: https://drive.google.com/file/d/1r9QK...
Constraints on Binary Relationship Types
Relationship types usually have certain constraints that limit the possible combinations
of entities that may participate in the corresponding relationship set. These
constraints are determined from the miniworld situation that the relationships represent.
For example, in Figure 3.9, if the company has a rule that each employee
must work for exactly one department, then we would like to describe this constraint
in the schema. We can distinguish two main types of binary relationship
constraints: cardinality ratio and participation.
Cardinality Ratios for Binary Relationships. The cardinality ratio for a binary
relationship specifies the maximum number of relationship instances that an entity
can participate in. For example, in the WORKS_FOR binary relationship type,
DEPARTMENT:EMPLOYEE is of cardinality ratio 1:N, meaning that each department
can be related to (that is, employs) any number of employees (N),9 but an employee
can be related to (work for) at most one department (1). This means that for
this particular relationship type WORKS_FOR, a particular department entity can
be related to any number of employees (N indicates there is no maximum number).
On the other hand, an employee can be related to a maximum of one department.
The possible cardinality ratios for binary relationship types are 1:1, 1:N, N:1,
and M:N.
An example of a 1:1 binary relationship is MANAGES (Figure 3.12), which relates a
department entity to the employee who manages that department. This represents
the miniworld constraints that—at any point in time—an employee can manage at most one department and a department can have at most one manager. The relationship
type WORKS_ON (Figure 3.13) is of cardinality ratio M:N, because the
miniworld rule is that an employee can work on several projects and a project can
have several employees.
Cardinality ratios for binary relationships are represented on ER diagrams by displaying
1, M, and N on the diamonds as shown in Figure 3.2. Notice that in this
notation, we can either specify no maximum (N) or a maximum of one (1) on participation.Participation Constraints and Existence Dependencies. The participation
constraint specifies whether the existence of an entity depends on its being related
to another entity via the relationship type. This constraint specifies the minimum
number of relationship instances that each entity can participate in and is sometimes
called the minimum cardinality constraint. There are two types of participation
constraints—total and partial—that we illustrate by example. If a company
policy states that every employee must work for a department, then an employee
entity can exist only if it participates in at least one WORKS_FOR relationship
instance (Figure 3.9). Thus, the participation of EMPLOYEE in WORKS_FOR is
called total participation, meaning that every entity in the total set of employee
entities must be related to a department entity via WORKS_FOR. Total participation
is also called existence dependency. In Figure 3.12 we do not expect every
employee to manage a department, so the participation of EMPLOYEE in theMANAGES relationship type is partial, meaning that some or part of the set of
employee entities are related to some department entity via MANAGES, but not
necessarily all. We will refer to the cardinality ratio and participation constraints,
taken together, as the structural constraints of a relationship type.
In ER diagrams, total participation (or existence dependency) is displayed as a double
line connecting the participating entity type to the relationship, whereas partial participation
is represented by a single line (see Figure 3.2). Notice that in this notation,
we can either specify no minimum (partial participation) or a minimum of one (total
participation). An alternative notation (see Section 3.7.4) allows the designer to specify
a specific minimum number on participation in the relationship,