Relationships in Relation Modeling Notes

Relationships in Relational Modeling

Textbook Sections

• 3.5 Relationships within the Relational Database

• 3.6 Data Redundancy Revisited

Relationships

• Relationships represent the association among entities.

• A relationship should be interpreted in both directions. For example:

  • "A student is enrolled in a course."

  • "A course has students enrolled in it."

Cardinality

• Cardinality defines the numerical attributes of the relationship between two entities or tables.

1:M (One-to-Many)

• This is the most common relationship in a relational model.

• One entity is related to multiple entities.

• Example: "A patient is subjected to multiple procedures/tests, but each test is for a single patient."

M:N (Many-to-Many)

• Multiple items are related to multiple other items.

• Example: "A patient is prescribed multiple medicines, and a medicine is prescribed to multiple people."

1:1 (One-to-One)

• A single item is related to a single item in the other table.

• This is a somewhat rare occurrence in relational databases.

Examples of Cardinality

1:M (One-to-Many) Example

Consider the relationship between Patient and Appointment entities:

• A patient can have multiple appointments.

• Each appointment is associated with a single patient.

1:1 (One-to-One) Example

Consider the relationship between Staff and UserAccount entities:

• Each staff member has one user account.

• Each user account belongs to one staff member.

M:N (Many-to-Many) Example

Consider the relationship between Patient and Medicine entities:

• A patient can be prescribed multiple medicines.

• A medicine can be prescribed to multiple patients.

This M:N relationship is typically resolved using an intermediate table, such as Prescription, which contains foreign keys referencing both Patient and Medicine.