- A relationship has
2 properties:
- degree of the relationship
- constraints of
binary relationships:
- Cardinality ratio constraint
- Participation constraint
- degree of the relationship
- The
degree of the relationship
is
number of entity sets
in the relationship type
- The most common
type of relationships is the
binary relationship
which involves
2 entity types
- Less common types of relationships are:
ternary relationship
which involves
3 entity types
- Having 4 or more entity types in one relationship is extremely rare
- Employee = {John, James, Jane, Julie}
- Department = {Payroll, Administration}
- Relationship type: Works-for
- Sample relationship set of the "Works-for" relationship type:
- {(John, Payroll), (James, Payroll), (Jane, Administration), (Julie, Administration)}
- Pictorial representation:
- Some relationship instances
- (John, Payroll) - represents the fact that John works in the Payroll department
- (Julie, Administration) - represents the fact that Julie works in the Administration department
- Supplier = {John, James}
- Part = {Nut16, Nut32, Bolt56, Screw13}
- Project = {Printer, Scanner, Monitor}
- Relationship type:
a certain supplier supplies
a certain part to a certain project
- Sample relationship set of the "Supply" relationship type:
- {(John, Nut16, Printer), (John, Bolt56, Monitor), (James, Nut16, Printer), (James, Screw13, Monitor)}
- Pictorial representation:
- Example relationship instance
- (John, Nut16, Printer) - represents the fact that John supplies the part "Nut16" to the project "Printer"
- Relationships have a number of
constraints
- It is important to
discover all constraints
of the relationships
because these
constraints will help us in the design of
"good" database schemas.
- There are a
two types of constraints:
- Cardinality ratio constraints
- Participation constraints
- Cardinality ratio constraint:
- Cardinality ratio constraint = a constraint that specifies the maximum number of relationship instances that an entity can participate (= connected to) in the relationship
- Important fact:
- A cardinality ratio constraint is only valid on binary relationships (between 2 entity types)
- Example: consider the "Works-for" relationship
In the "Works-for" relationship:
- one employee works for ONE department
- one department has N employees
We say that:
In the "Works for" (binary) relatioship, the cardinality ratio of department:employee is 1:N (i.e., 1 department can be related to N employees)
- Possible cardinality ratios are:
- 1:1,
- 1:N,
- N:1 and
- M:N.
- A
Participation Constraint
specifies whether the participation of an entity
in a relationship is
compulsory or not
- Example:
A person can only be an employee of the company if he/she is employeed by some department of the company So the participation of an employee in the "Works-for" relationship is compulsory
- There are 2 types of participations:
- Total (full or complete - i.e., compulsory)
- Partial ("optional", i.e., not compulsory)
- Total participation:
-
every entity of the entity set
must participate
in the relationship
- Partial participation:
-
entities of the entity set
may
but is
NOT required to participate
in the relationship
- Consider the "Works For" relationship:
- Participation constraints:
- Participation of
Employee
in "Works-for"
is total:
-
Every
employee must be included in the "Works-for"
relationship.
I.e.: there is a line to every employee in the diagram
(Makes sense: if he/she does not belong to some department, he/she should NOT be an employee...)
NOTE:
-
Total participation
is also known as
existence dependency
- because: the existence of the entity
depends on the participation
in the relationship.
- The participation of
Department
in "Works-for"
is partial:
NOTE:
-
Notice that the
research
department does not have any employees
- and yet, this department exists in the database.
So in partial participation, the entity's existence does NOT depend on its being related to some other entity.
- Participation of
Employee
in "Works-for"
is total: