CS457 Syllabus & Progress

## Relationships

• Definitions: (1) Relationship Instances, (2) Relationship Sets and (3) Relationship Types

• A relationship instance among n entity types E1, E2, ..., En is:

 Any (one) tuple from the cartesian product E1 × E2 × ... × En

Example:

• E1 = {John, James, Mary}
• E2 = {CS170, CS255, CS377, CS450}

Then:

 (John, CS170) is a relationship instance (it could mean that "John took CS170")

• A relationship set is:

 the set (= collection) of relationship instances at a given moment in time.

Example:

• E1 = {John, James, Mary}
• E2 = {CS170, CS255, CS377, CS450}

Then:

 {(John, CS170), (John, CS255), (Mary, CS170)} is a relationship set

(A relationship set is the same as a relation in Mathematics)

• A relationship type R is:

 defines a association (= description of a relationship) among the entity types E1, E2, ..., En.

Example:

• E1 = {John, James, Mary}
• E2 = {CS170, CS255, CS377, CS450}

Enrollment:

 E1 is enrolled in E2

We call a relationship type: a relationship for short.

• Comment

• We will mainly discuss relationships

• The terminology:

 Relationship instance           Relationship set Relationship type

are mainly definitions to give you a better understanding of what a relationship is about

• Properties of a relationship

• A relationship has 2 properties:

 degree of the relationship constraints of the relationship

• Degree of a 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

• Example Binary Relationship (between 2 entities)

• Employee = {John, James, Jane, Julie}

• Relationship type: Works-for

• Sample relationship set of the "Works-for" relationship type:

• 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

• Example of a Ternary Relationship (between 3 entities)

• 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"

• Constraints on Relationships

• 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 constraints on relationships

• 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.

• Participation constraints on relationships

• 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:

1. 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.

2. 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.

• Attributes of Relationships

• Just like entities, relationships can be attributes

• We will see an example of this soon.