Skip to content Skip to main navigation Skip to footer

Database Relationship Types

The database relationships topic had been already discussed and fully explained. This article sheds the light on the different Database Relationship Types.

Objectives

This tutorial covers the following objectives:

  • Recognize the Database Relationship Types
  • Recognize and give examples of a one-to-one relationship
  • Recognize and give examples of a one-to-many relationship
  • Recognize and give examples of a many-to-many relationship
  • Recognize redundant relationships and remove them from the ERD
Database Relationship Types

Purpose of the Database Relationship Types Article

  • Can one PERSON own many DVDs, or only one ?
  • Can one DVD be owned by many PERSONs?
  • As we refine and improve our model, we want to make sure our entity relationships correctly model our business rules.
  • Remember, you can avoid future costly mistakes by thinking through the details early on.

One-to-Many (1:M) Relationships

One-To-Many Relationship
One-to-Many (1:M) Relationships
  • The various types of 1:M relationships are most common in an ER Model.
  • You have seen several examples already.
One-to-Many (1:M) Relationships

“Many” can mean one-or-more or zero-or-more, depending on the optionality.

Mandatory at both ends: This type of relationship typically models entities that cannot exist without each other. This usually represents an ideal situation—we cannot have ORDER ITEMs without ORDERs.

Mandatory on the one side, optional on the many side: This is rarely used. You will see it only when the relationship expresses that an entity instance exists only when it is a nonempty set, and where the elements of the set can exist independently. A MUSICIAN may be part of one BAND. A BAND is of no interest if it is empty. How can you have a BAND without MUSICIANs?

Many-to-Many (M:M) Relationships

Many-to-Many-Relationships
Many-to-Many (M:M) Relationships
  • The various types of M:M relationships are common, particularly in a first version of an ER model.
  • In later stages of the modeling process, all M:M relationships will be resolved, and disappear
Many-to-Many (M:M) Relationships

In most cases, M:M relationships indicate that an entity is missing from the model. In the next lesson, we will learn to resolve the many-to-many relationships.

One-to-One Relationships For Roles

One-to-One Relationships
One-to-One Relationships For Roles
  • Usually you will find just a few of the various types of 1:1 relationships in every ER model.
  • Mandatory at one end of the 1:1 relationship commonly occurs when roles are modeled.
One-to-One Relationships For Roles

Alternatively, TEACHER and STUDENT could have been modeled as subtypes of PERSON, unless a PERSON can be both a TEACHER and a STUDENT at the same time.

One-to-One Relationships For Processes

  • 1:1 relationships (of all three variations) also occur when some of the entities represent various stages in a process.
One-to-One Relationships For Processes

Redundant Relationships

  • A redundant relationship can be derived from another relationship in the model.
  • In this example, you can derive the relationship from PERSON to COUNTRY from the other two relationships (COUNTRY to TOWN, TOWN to PERSON), so you should remove the direct relationship from COUNTRY to PERSON .
Redundant Relationships
  • However, be careful of concluding that a relationship is redundant based on the structure alone.
  • Read the relationships to check.
  • The ERD shown here does not reflect a redundant relationship.
Redundant Relationships

The relationship on the right tells us the country of a PERSONs birth, and the relationship on the left tells us the TOWN and COUNTRY that a person currently lives in. It is possible that someone could be living in a different COUNTRY from where they were born.

Terminology

Key terms used in this tutorial included:

  • Many-to-many (M:M)
  • One-to-many (1:M)
  • One-to-one (1:1)
  • Redundant

Summary

In this tutorial, you should have learned how to:

  • Recognize and give examples of a one-to-one relationship
  • Recognize and give examples of a one-to-many relationship
  • Recognize and give examples of a many-to-many relationship
  • Recognize redundant relationships and remove them from the ERD

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *