BUS611: Data Management

The line that connects two tables, in an ERD, indicates the relationship type between the tables: either identifying or non-identifying. An identifying relationship will have a solid line (where the PK contains the FK). A non-identifying relationship is indicated by a broken line and does not contain the FK in the PK. See the section in Chapter 8 that discusses weak and strong relationships for more explanation.

Figure 9.5. Identifying and non-identifying relationship.

Optional relationships

In an optional relationship, the FK can be null or the parent table does not need to have a corresponding child table occurrence. The symbol, shown in Figure 9.6, illustrates one type with a zero and three prongs (indicating many) which is interpreted as zero OR many.

Figure 9.6.

For example, if you look at the Order table on the right-hand side of Figure 9.7, you'll notice that a customer doesn't need to place an order to be a customer. In other words, the many side is optional.

Figure 9.7. Example usage of a zero to many optional relationship symbol.

The relationship symbol in Figure 9.7 can also be read as follows:

• Left side: The order entity must contain a minimum of one related entity in the Customer table and a maximum of one related entity.
• Right side: A customer can place a minimum of zero orders or a maximum of many orders.

Figure 9.8 shows another type of optional relationship symbol with a zero and one, meaning zero OR one. The one side is optional.

Figure 9.8.

Figure 9.9 gives an example of how a zero to one symbol might be used.

Figure 9.9. Example usage of a zero to one optional relationship symbol.

Mandatory relationships

In a mandatory relationship, one entity occurrence requires a corresponding entity occurrence. The symbol for this relationship shows one and only one as shown in Figure 9.10. The one side is mandatory.

Figure 9.10.

See Figure 9.11 for an example of how the one and only one mandatory symbol is used.

Figure 9.11. Example of a one and only one mandatory relationship symbol.

Figure 9.12 illustrates what a one to many relationship symbol looks like where the many side is mandatory.

Figure 9.12.

Refer to Figure 9.13 for an example of how the one to many symbol may be used.

Figure 9.13. Example of a one to many mandatory relationship symbol.

So far we have seen that the innermost side of a relationship symbol (on the left-side of the symbol in Figure 9.14) can have a 0 (zero) cardinality and a connectivity of many (shown on the right-side of the symbol in Figure 9.14), or one (not shown).

Figure 9.14.

However, it cannot have a connectivity of 0 (zero), as displayed in Figure 9.15. The connectivity can only be 1.

Figure 9.15.

The connectivity symbols show maximums. So if you think about it logically, if the connectivity symbol on the left side shows 0 (zero), then there would be no connection between the tables.

The way to read a relationship symbol, such as the one in Figure 9.16, is as follows.

• The CustID in the Order table must also be found in the Customer table a minimum of 0 and a maximum of 1 times.
• The 0 methat the CustID in the Order table may be null.
• The left-most 1 (right before the 0 representing connectivity) says that if there is a CustID in the Order table, it can only be in the Customer table once.
• When you see the 0 symbol for cardinality, you can assume two things: T
1. the FK in the Order table allows nulls, and
2. the FK is not part of the PK since PKs must not contain null values.

Figure 9.16. The relationship between a Customer table and an Order table.