Chapter 3 The Many-to-Many Relationship

Fearful concatenation of circumstances.

Daniel Webster

Learning objectives

Students completing this chapter will be able to

  • model a many-to-many relationship between two entities;
  • define a database with a many-to-many relationship;
  • write queries for a database with a many-to-many relationship.

3.1 The many-to-many relationship

Consider the case when items are sold. We can immediately identify two entities: SALE and ITEM. A sale can contain many items, and an item can appear in many sales. We are not saying the same item can be sold many times, but the particular type of item (e.g., a compass) can be sold many times; thus we have a many-to-many (m:m) relationship between SALE and ITEM. When we have an m:m relationship, we create a third entity to link the entities through two 1:m relationships. Usually, it is fairly easy to name this third entity. In this case, this third entity, typically known as an associative entity, is called LINE ITEM. A typical old style sales form lists the items purchased by a customer. Each of the lines appearing on the order form is generally known in retailing as a line item, which links an item and a sale.

A sales form

The representation of this m:m relationship is shown. We say many-to-many because there are two relationships—an ITEM is related to many SALEs, and a SALE is related to many ITEMs. This data model can also be read as: “a sale has many line items, but a line item refers to only one sale. Similarly, an item can appear as many line items, but a line item references only one item.”

An m:m relationship between SALE and ITEM

The entity SALE is identified by saleno and has the attributes saledate and saletext (a brief comment on the customer—soft information). LINEITEM is partially identified by lineno and has attributes lineqty (the number of units sold) and lineprice (the unit selling price for this sale). ITEM is identified by itemno and has attributes itemname, itemtype (e.g., clothing, equipment, navigation aids, furniture, and so on), and itemcolor.

If you look carefully at the m:m relationship figure, you will notice that there is a plus sign (+) above the crow’s foot at the “many” end of the 1:m relationship between SALE and LINEITEM. This plus sign provides information about the identifier of LINEITEM. As you know, every entity must have a unique identifier. A sales order is a series of rows or lines, and lineno is unique only within a particular order. If we just use lineno as the identifier, we cannot guarantee that every instance of LINEITEM is unique. If we use saleno and lineno together, however, we have a unique identifier for every instance of LINEITEM. Identifier saleno is unique for every sale, and lineno is unique within any sale. The plus indicates that LINEITEM’s unique identifier is the concatenation of saleno and lineno. The order of concatenation does not matter.

LINEITEM is termed a weak entity because it relies on another entity for its existence and identification.

3.1.1 MySQL Workbench

An m:m relationship with Workbench

Workbench distinguishes between two types of relationships. An identifying relationship, shown by a solid line, is used when the entity at the many end of the relationship is a weak entity and needs the identifier of the one end of the relationship to uniquely identify an instance of the relationship, as in LINEITEM. An identifying relationship corresponds to the + sign associated with a crow’s foot. The other type of relationship, shown by a dashed line, is known as a non-identifying relationship. The mapping between the type of relationship and the representation (i.e., dashed or solid line) is arbitrary and thus not always easily recalled. We think that using a + on the crow’s foot is a better way of denoting weak entities.

When the relationship between SALE and ITEM is drawn in Workbench, as shown in the following figure, there are two things to notice. First, the table, lineitem, maps the associative entity generated for the m:m relationship. Second, lineitem has an identifying relationship with sale and a non-identifying relationship with item.

An m:m relationship between SALE and ITEM in MySQL Workbench

3.1.2 Why did we create a third entity?

When we have an m:m relationship, we create an associative entity to store data about the relationship. In this case, we have to store data about the items sold. We cannot store the data with SALE because a sale can have many items, and an instance of an entity stores only single-value facts. Similarly, we cannot store data with ITEM because an item can appear in many sales. Since we cannot store data in SALE or ITEM, we must create another entity to store data about the m:m relationship.

You might find it useful to think of the m:m relationship as two 1:m relationships. An item can appear on many line item listings, and a line item entry refers to only one item. A sale has many line items, and each line item entry refers to only one sale.

Social Security number is notunique!

Two girls named Sarah Lee Ferguson were born on May 3, 1959. The U.S. government considered them one and the same and issued both the same Social Security number (SSN), a nine-digit identifier of U.S. residents. Now Sarah Lee Ferguson Boles and Sarah Lee Ferguson Johnson share the same SSN.3

Mrs. Boles became aware of her SSN twin in 1987 when the Internal Revenue Service claimed there was a discrepancy in her reported income Because SSN is used as an identifier in IRS systems. Mrs. Boles encountered other incidents of mistaken identity. Some of Mrs. Johnson’s purchases appeared on Mrs. Boles’ credit reports.

In late 1989, the Social Security Administration notified Mrs. Boles that her original number was given to her in error and she had to provide evidence of her age, identity, and citizenship to get a new number. When Mrs. Boles got her new SSN, it is likely she had to also get a new driver’s license and establish a new credit history.

3.2 Querying an m:m relationship

3.2.1 A three-table join

The join operation can be easily extended from two tables to three or more merely by specifying the tables to be joined and the matching conditions. For example:

SELECT * FROM sale JOIN lineitem
    ON sale.saleno = lineitem.saleno
    JOIN item
    ON item.itemno = lineitem.itemno;

There are two matching conditions: one for sale and lineitem (sale.saleno = lineitem.saleno) and one for the item and lineitem tables (item.itemno = lineitem.itemno). The table lineitem is the link between sale and item and must be referenced in both matching conditions.

You can tailor the join to be more precise and report some columns rather than all.

List the name, quantity, price, and value of items sold on January 16, 2011.

SELECT itemname, lineqty, lineprice, lineqty*lineprice AS total
    FROM sale, lineitem, item
        WHERE lineitem.saleno = sale.saleno
        AND item.itemno = lineitem.itemno
        AND saledate = '2011-01-16';
Table 3.1: 5 records
itemname lineqty lineprice total
Safari chair 50 36.0 1800
Hammock 50 40.5 2025
Tent - 8 person 8 153.0 1224
Tent - 2 person 1 60.0 60
Pocket knife - Avon 1 0.0 0

Set operations

Set operators are useful for combining the values derived from two or more SQL queries. The UNION operation is equivalent to or.

List items that were sold on January 16, 2011, or are brown.

Resolution of this query requires two tables: one to report items sold on January 16, 2011, and one to report the brown items. UNION (i.e., or) then combines the results of the tables, including any rows in both tables and excluding duplicate rows.

SELECT itemname FROM item JOIN lineitem
    ON item.itemno = lineitem.itemno
    JOIN sale
    ON lineitem.saleno = sale.saleno
    WHERE saledate = '2011-01-16'
UNION
    SELECT itemname FROM item WHERE itemcolor = 'Brown';
Table 3.2: 8 records
itemname
Safari chair
Hammock
Tent - 8 person
Tent - 2 person
Pocket knife - Avon
Pocket knife - Nile
Map case
Stetson

❓ Skill builder

List the items that contain the words “Hat”, “Helmet”, or “Stetson” in their names

Summary

There can be a many-to-many (m:m) relationship between entities, which is represented by creating an associative entity and two 1:m relationships. An associative entity stores data about an m:m relationship. The join operation can be extended from two tables to three or more tables. EXISTS tests whether a table has at least one row that meets a specified condition. NOT EXISTS tests whether all rows in a table do not satisfy a specified condition. Both EXISTS and NOT EXISTS can return true or false. The relational operation divide, also known as forall, can be translated into a double negative. It is represented in SQL by a query containing two NOT EXISTS statements. Set operations enable the results of queries to be combined.

Key terms and concepts

Associative entity Many-to-many (m:m) relationship
Existential quantifier UNION
Universal quantifier

Exercises

  1. Write the following SQL queries for the database described in this chapter:

    1. List the names of items for which the quantity sold is greater than one for any sale.

    2. Compute the total value of sales for each item by date.

    3. Report all items of type “F” that have been sold.

    4. List all items of type “F” that have not been sold.

    5. Compute the total value of each sale.

  2. Why do you have to create a third entity when you have an m:m relationship?

  3. What does a plus sign near a relationship arc mean?

  4. How does EXISTS differ from other clauses in an SQL statement?

  5. Answer the following queries based on the described relational database.

    1. List the phone numbers of donors Hays and Jefts.

    2. How many donors are there in the donor table?

    3. How many people made donations in 1999?

    4. What is the name of the person who made the largest donation in 1999?

    5. What was the total amount donated in 2000?

    6. List the donors who have made a donation every year.

    7. List the donors whose average donation is more than twice the average donation of all donors.

    8. List the total amount given by each person across all years; sort the report by the donor’s name.

    9. Report the total donations in 2001 by state.

    10. In which years did the total donated exceed the goal for the year?

  1. “Two women share a name, birthday, and S.S. number!” Athens Daily News, January 29 1990, 7A. Also, see https://www.computerworld.com/article/3004659/a-tale-of-two-women-same-birthday-same-social-security-number-same-big-data-mess.html↩︎