ER Models

Relationships

Recall

• Entity
• Attribute
• Domain
• Simple
• Composite
• Multi-valued
• Derived
• Key

​

​

Relationship

• More formally, a relationship-type
• Represents an association between entities
• Usually verbs
• Use relationships, not attributes, to associate entities
• Degree – how many entities are related (binary, ternary)
• Represented by a diamond

​

​

works_at

CLINIC

DOCTOR

Relationship Constraints

• Cardinality
• How many instances of one entity relate to one instance of the other?
• one or many
• singular or plural (zybook terminology)
• Participation
• Does every instance of an entity participate in the relationship?
• Total/mandatory/required or partial/optional

Cardinality

• Possibilities:
• 1-to-1
• 1-to-many (1-to-N)
• many-to-many (N-to-M)
• Chen notation
• put the numbers (1, N, M ) on each relationship line
• Crow’s foot notation
• crow’s foot on the N side, small line on the 1 side

Participation

• Mandatory/total/required participation
• Every instance of the entity must participate in the relationship
• Partial/optional participation
• There may be an instance of the entity that does not participate in the relationship
• Chen – double line for total, single for partial
• Crow’s foot – small line for total, small O for partial

Examples

Relationship Attributes

• Relationships may have their own attributes
• These attributes do not belong to either entity

MedEx Company (so far)

Group Activity: Abstract ER Part 1

Identifying Relationship – Weak Entity

• A weak entity has no individual unique identity without its relationship to another entity
• Does not exist without the identifying relationship
• Partial key combined with identifying key form unique identifier

Identifying Relationship – Weak Entity

Legal

Illegal

Multiple Identifying Relationships

Ternary Relationships

• Imagine that MedX company has partnership agreements with some pharmacies in the area
• Assume the MedX clinic doctors can prescribe/order medication for a patient from a partner pharmacy so that the patient can just go and pick it up or maybe have it delivered to their home or to the clinic

Ternary Relationships cont.

• This relationship is many-to-many in all directions:
• One doctor can prescribe meds for one patient from different pharmacies
• One doctor can prescribe meds from one pharmacy to different patients
• One patient can be prescribed meds from one pharmacy by different doctors
• One patient can be prescribed meds by one doctor from different pharmacies
• One pharmacy can provide meds prescribed by one doctor to different patients
• One pharmacy can provide meds to one patient that were prescribed by different doctors

A Ternary Relationship

• Many-to-many in all directions
• M-N-K (in order not to imply equality)

A Ternary Relationship

A ternary relationship as a weak entity

Appointment

• What is it?
• A relationship between doctor and patient, with another relationship to clinic
• A ternary relationship (doctor, patient, clinic)
• An entity with relationships to doctor, patient, and clinic
• A weak entity identified by doctor
• A weak entity identified by doctor and patient

Appointment represented as a ternary relationship with many attributes.

Appointment represented as a weak entity identified by both doctor and patient.

Appointment represented as a weak entity identified only by doctor. This interpretation allows for empty appointment “slots”.

Recursive Relationship

• A relationship between an instance of one entity and another instance of the same entity
• For example,
• One employee may be the manager of zero or more other employees
• Not every employee has a manager (top boss)

Group Activity: Abstract ER Part 2