Skip to content Skip to main navigation Skip to footer

Database Relationships

The previous section introduced entities and attributes. Database relationships are the third component of a data model (the “R” in “ER modeling”).

Database Relationships

Objectives

This article covers the following objectives:

  • Interpret and describe relationship optionality
  • Interpret and describe relationship cardinality
  • Relate (connect or join) entities by applying the rules of cardinality and optionality

Purpose of the Database Relationships

  • Being able to identify the relationships between entities makes it easier to understand the connections between different pieces of data.
  • Database relationships help you see how different parts of a database system affect each other.
  • For example, the entities STUDENT and COURSE are related to each other.
  • To accurately model the business, the relationships between entities are as important as the entities themselves.

Question: What is the relationship between student and library book?

Answer: A student checks out a book.

If you received a fine for a book checked out by another student, you would certainly protest because the student who checked out the book (this is the relationship) should also be responsible for any associated fines. This emphasizes the importance of clarifying relationships, especially in a business

Relationships in Families

  • A relationship is the way in which two or more people or things are connected.
  • Family relationships categorize relationships between people, for example mother, father, aunt and cousin.
  • The name of the relationship tells us how the family members are connected.
Relationships in Families

Relationships in Data Models

Relationships:

  • Represent something of significance or importance to the business
  • Show how entities are related to each other
  • Exist only between entities (or one entity and itself)
  • Are bi-directional
  • Are named at both ends
  • Have optionality
  • Have cardinality

What is Optionality in a Relationship?

  • Relationships are either mandatory or optional.
  • Consider the two entities EMPLOYEE and JOB.
  • Based on what you know about instances of the entities, you can determine optionality by answering two questions:
  • Must every employee have a job?
    • In other words, is this a mandatory or optional relationship for an employee?
  • Must every job be assigned to an employee?
    • In other words, is this a mandatory or optional relationship for a job?

The answer to these questions may vary from one organization to another, depending on the business rules, but generally, if you are an employee, you would have a job.

It is possible however that there will be jobs in the organization that are currently not filled or are obsolete, so will not be held by any employees.

What is Cardinality in a Relationship?

  • Cardinality measures the quantity of something.
  • In a relationship, it determines the degree to which one entity is related to another by answering the question, “How many?”
  • For example:
    • How many jobs can one employee hold? One job only? Or more than one job?
    • How many employees can hold one specific job? One employee only? Or more than one employee?
  • Note: The cardinality of a relationship only answers whether the number is singular or plural; it does not answer with a specific plural number.

Again, different organizations may have different business rules that define this relationship.

In general, however, each employee will hold one job, and each job may be held by zero or more employees.

Optionality and Cardinality

Examples:

  • Each EMPLOYEE must hold one and only one JOB
  • Each JOB may be held by one or more EMPLOYEEs
  • Each PRODUCT must be classified by one and only one PRODUCT TYPE
  • Each PRODUCT TYPE may classify one or more PRODUCTs

Optionality: Must or may?

Cardinality: How many?

It is often necessary to add a small “s” to the end of the second entity name to make the sentence more readable.

What Are Database Relationships

  • Each SEAT may be sold to one or more PASSENGERs
  • Each PASSENGER may purchase one SEAT
  • SEAT is sold to a PASSENGER (or PASSENGERs — hence, overbooking)
  • PASSENGER purchases or books a SEAT
Relationships

Have you ever been scheduled for an airplane flight and heard the check-in desk attendant ask if anyone wanted to give up their seat because the plane was overbooked? Why does an airline sell one seat to two people? Is every seat sold to more than one person? Why does it happen sometimes and not all the time? Is it normal for a person to buy more than one seat for themself on a plane? Is it possible that some seats will not be sold to a passenger?

The answer to these questions will depend on the business rules for the organization you are modeling

Business Scenario 1

  • What are the relationships in the following business scenario?
  • “In our restaurant, a customer walks up to the counter and places their order. A customer can order for him or herself only, or for him/herself and others. For example, a mother orders for herself and her children.
  • We consider the mother to be the customer who owns the order and is responsible for payment. Over a period of time, a customer can place as many orders as he wants.”
  • CUSTOMER places ORDERs: optionality and cardinality
  • Optionality = Must or may?
  • Each ORDER must be placed by one (and only one) CUSTOMER.
  • Each CUSTOMER must place one or more ORDERs.
Business Scenario 1 - Relationships
  • Cardinality = How many?
  • Each ORDER must be placed by one and only one CUSTOMER.
  • Each CUSTOMER must place one or more ORDERs.

Business Scenario 2

  • A relationship can join one entity to itself.
  • Examine the following scenario:
    • “We need to keep track of our employees and their managers. Every employee has one manager, including the managing director who manages him/herself. Each manager can manage several employees.”.
Business Scenario 2 - Relationships
  • Since managers are also employees, both are listed in the same entity: EMPLOYEE.
Business Scenario 2

Terminology

Key terms used in this tutorial included:

  • Cardinality
  • Optionality
  • Database Relationship

Summary

In this tutorial, you should have learned how to:

  • Interpret and describe relationship optionality
  • Interpret and describe relationship cardinality
  • Relate (connect or join) entities by applying the rules of cardinality and optionality

Was This Article Helpful?

1
Related Articles
0 Comments

There are no comments yet

Leave a comment

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