The only purpose for putting the data that describe an entity into a database is to retrieve the data at some later date. This means that we must have some way of distinguishing one entity from another so that we can always be certain that we are retrieving the precise entity we want. We do this by ensuring that each entity has some attribute values that distinguish it from every other entity in the database (an entity identifier).

Assume, for example, that Antique Opticals has only two customers named John Smith. If an employee searches for the items John Smith has ordered, which John Smith will the DBMS retrieve? In this case, the answer is both of them. Because there is no way to distinguish between the two customers, the result of the query will be inaccurate. Antique Opticals solved the problem by creating customer numbers that were unique. That is indeed a common solution to identifying instances of entities where there is no simple unique identifier suggested by the data itself.

Another solution would be to pair the customer's first name and last name with his or her telephone number. This combination of data values (a concatenated identifier) would also uniquely identify each customer. There are, however, two drawbacks to doing so this. First, the identifier is long and clumsy; it would be easy to make mistakes when entering any of the parts. Second, if the customer's phone number changes, then the identifier must also change. As you read in Chapter 3, changes made in an entity identifier can cause serious problems in a database.

Some entities, such as invoices, come with natural identifiers (the invoice number). We assign unique, meaningless numbers to others, especially accounts, people, places and things. Still others require concatenated identifiers.

When we store an instance of an entity in a database, we want the DBMS to ensure that the new instance has a unique identifier. This is an example of a constraint on a database, a rule to which data must adhere. The enforcement of a variety of database constraints helps us to maintain data consistency and accuracy.

Because we are eventually going to create a relational database, the attributes in our data model must be single-valued. This means that for a given instance of an entity, each attribute can have only one value. For example, a customer entity allows only one telephone number for each customer. If a customer has more than one phone number and wants all of them in the database, then the customer entity cannot handle them. The existence of more than one phone number turns the phone number attribute into a multivalued attribute. Because an entity in a relational database cannot have multivalued attributes, you must handle those attributes by creating an entity to hold them.

In the case of the multiple phone numbers, we could create a phone number entity. Each instance of the entity would include the customer number of the person to whom the phone number belonged along with the telephone number. If a customer had three phone numbers, then there would be three instances of the phone number entity for the customer. The entity's identifier would be the concatenation of the customer number and the telephone number.

What is the problem with multivalued attributes? Multivalued attributes can cause problems with the meaning of data in the database, significantly slow down searching, and place unnecessary restrictions on the amount of data that can be stored.

Assume, for example, that you have an employee entity with attributes for the name and birth dates of dependents. Each attribute is allowed to store multiple values, as in Figure 4-2, where each gray blob represents a single instance of the employee entity. How will you associate the correct birth date with the name of the dependent to which it applies? Will it be by the position of a value stored in the attribute (i.e., the first name is related to the first birth date, and so on)? If so, how will you ensure that there is a birth date for each name and a name for each birth date? How will you ensure that the order of the values is never mixed up?

When searching a multivalued attribute, a DBMS must search each value in the attribute, most likely scanning the contents of the attribute sequentially. A sequential search is the slowest type of search available.

In addition, how many values should a multivalued attribute be able to store? If you specify a maximum number, what will happen when you need to store more than the maximum number of values? For example, what if you allow room for 10 dependents in the employee entity just discussed and you encounter an employee with 11 dependents? Do you create another instance of the employee entity for that person? Consider all the problems that doing so would create, particularly in terms of the unnecessary duplicated data.

As a general rule, if you run across a multivalued attribute, this is a major hint that you need another entity. The only way to handle multiple values of the same attribute is to create an entity in which you can store multiple instances, one for each value of the attribute (for example, Figure 4-3). In the case of the employee entity, we would need a dependent entity that could be related to the employee entity. There would be one instance of the dependent entity related to an instance of the employee entity for each of an employee's dependents. In this way, there is no limit to the number of an employee's dependents. In addition, each instance of the dependent entity would contain the name and birth date of only one dependent, eliminating any confusion about which name was associated with which birth date. Searching would also be faster because the DBMS could use fast searching techniques on the individual dependent entity instances without resorting to the slow sequential search.

When you first begin to work with entities, you may find the nature of an entity to be somewhat confusing. Consider, for example, the merchandise inventory handled by Antique Opticals. Is “inventory” an entity? No. Inventory is a collection of the merchandise items handled by the store. The entity is actually the merchandise item. Viewing all of the instances of the merchandise item entity as a whole provides the inventory.

To make this a bit clearer, consider the attributes you would need if you decided to include an inventory entity: merchandise item number, item title, number in stock, retail price, and so on. But because you are trying to describe an entire inventory with a single entity, you need multiple values for each of those attributes. As you read earlier, however, attributes cannot be multivalued. This tells you that inventory cannot stand as an entity. It must be represented as a collection of instances of a merchandise item entity.

As another example, consider a person's medical history maintained by a doctor. Like an inventory, a medical history is a collection of more than one entity. A medical history is made up of appointments and the events that occur during those appointments. Therefore, the history is really a collection of instances of appointment entities and medical treatment entities. The “history” is an output that a database application can obtain by gathering the data stored in the underlying instances.

Entity-relationship (ER) diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them. There are actually several styles of ER diagrams. Today there are three major methods: the Chen model (named after the originator of ER modeling, Dr. Peter P.S. Chen), Information Engineering (IE, or “crows feet”), and Unified Modeling Language (UML).

If you are not including object-oriented concepts in a data model, it really doesn't matter which you use, as long as everyone who is using the diagram understands the symbols. However, UML is specifically intended for the object-oriented environment and is usually the choice when objects are included.

All three diagramming styles use rectangles to represent entities. Each entity's name appears in the rectangle and is expressed in the singular, as in

The original Chen model has no provision for showing attributes on the ER diagram itself. However, many people have extended the model to include the attributes in ovals:

The entity's identifier is underlined (id_numb).

The IE and UML styles of ER diagramming include the attributes in the rectangle with the entity:

Because the IE and UML approaches tend to produce a less-cluttered diagram and because they are the more flexible styles, we will be using IE for most of the diagrams in this book, although you will be introduced to elements of the Chen style and the UML style throughout this chapter.

The major entities and their attributes for the Antique Opticals database can be found in Figure 4-4. As you will see, the design will require additional entities as we work with the relationships between those already identified. In particular, there is no information in Figure 4-4 that indicates which items appear on which orders and no information about which used laser discs are purchased by the store during a single transaction. This occurs because the missing information is a part of the logical relationships between customers, orders, purchases, and items.

Figure 4-4 demonstrates some of the choices made for the Antique Opticals database. Notice, for example, that there is only one entity for merchandise items, yet the store carries new DVDs, new high-definition DVDs, and used laser discs. The item_type attribute distinguishes the three types of merchandise. Because all merchandise items are stored as the same type of entity, queries such as “Show me Star Wars IV in any format” will be easy to perform using just the item name, and queries such as “Do you have a used Star Wars IV laser disc?” will be easy to satisfy using both the title of the item and its type.

The common formats used for ER diagrams do not usually include domains on the diagrams themselves but store the domains in an associated document (usually a data dictionary, something you will learn more about later in this book). However, the version of the Chen method that includes attributes can also include domains underneath each attribute. Notice in Figure 4-5 that three of the domains are fairly general (integer and character), while the domain for the telephone number attribute includes a very specific format. Whether a domain can be constrained in this way depends on the DBMS.

The domains that Antique Opticals chooses for its attributes should theoretically be independent of the DBMS that the company will use. In practical terms, however, it makes little sense to assign domains that you cannot implement. Therefore, the database designer working for Antique Opticals takes a look at the DBMS to see what data types are supported.

Most relational DBMSs that use SQL as their query language provide the following among their data types, any of which can be assigned as a domain to an attribute:

Many current DBMSs also support a data type known as a BLOB (binary large object), which can store anything binary, such as a graphic.

Choosing the right domain can make a big difference in the accuracy of a database. For example, a U.S. zip code is made up of five or nine digits. Should an attribute for a zip code therefore be given a domain of INT? The answer is no, for two reasons. First, it would be nice to be able to include the hyphen in nine-digit zip codes. Second, and more important, zip codes in the Northeast begin with a zero. If they are stored as a number, the leading zero disappears. Therefore, we always choose a CHAR domain for zip codes. Since we never do arithmetic with zip codes, nothing is lost by using character rather than numeric storage.

By the same token, it is important to choose domains of DATE and TIME for chronological data. As an example, consider what would happen if the dates 01/12/2009 and 08/12/2008 were stored as characters. If you ask the DBMS to choose which date comes first, the DBMS will compare the character strings in alphabetical order and indicate that 01/12/2009 comes first, because 01 alphabetically precedes 08. The only way to get character dates to order correctly is to use the format YYYY/MM/DD, a format that is rarely used anywhere in the world. However, if the dates were given a domain of DATE, then the DBMS would order them properly. The DBMS would also be able to perform date arithmetic, finding the interval between two dates or adding constants (for example, 30 days) to dates.

Consider, for a moment, an airport in a small town, where both the airport and the town are described in a database of small-town airports. Each of these might be represented by an instance of a different type of entity. The relationship between the two instances can be expressed as “The airport is located in one and only one town, and the town has one and only one airport.”

This is a true one-to-one relationship because at no time can a single airport be related to more than one town, and no town can be related to more than one airport. (Although there are municipalities that have more than one airport, the towns in the database are too small for that to ever happen.)

If we have instances of two entities (A and B) called A_i and B_i, then a one-to-one relationship exists if at all times A_i is related to no instances of entity B or one instance of entity B, and B_i is related to no instances of entity A or one instance of entity A.

True one-to-one relationships are very rare in business. For example, assume that Antique Opticals decides to start dealing with a new distributor of DVDs. At first, the company orders only one specialty title from the new distributor. If we peered inside the database, we would see that the instance of the distributor entity was related to just the one merchandise item instance. This would then appear to be a one-to-one relationship. However, over time, Antique Opticals may choose to order more titles from the new distributor, which would violate the rule that the distributor must be related to no more than one merchandise item. (This is an example of a one-to-many relationship, which is discussed in the next section of this chapter.)

By the same token, what if Antique Opticals created a special credit card entity to hold data about the credit cards that customers use to place orders? Each order can be charged to only one credit card. Thus, there would seem to be a one-to-one relationship between an instance of an order entity and an instance of a credit card entity. However, in this case we are really dealing with a single entity. The credit card number and the credit card's expiration date can become attributes of the order entity. Given that only one credit card is allowed per order, the attributes are not multivalued, and no separate entity is needed.

If you think you are dealing with a one-to-one relationship, look at it very carefully. Be sure that you are not really dealing with a special case of a one-to-many relationship or two entities that should really be one.

The most common type of relationship is a one-to-many relationship. (In fact, most relational databases are constructed from the rare one-to-one relationship and numerous one-to-many relationships.) For example, Antique Opticals typically orders many titles from each distributor, and a given title comes from only one distributor. By the same token, a customer places many orders, but an order comes from only one customer. If we have instances of two entities (A and B), then a one-to-many relationship exists between two instances (A_i and B_i) if A_i is related to zero, one, or more instances of entity B and B_i is related to zero or one instance of entity A.

Other one-to-many relationships include that between a daughter and her biological mother. A woman may have zero, one, or more biological daughters; a daughter can have only one biological mother. As another example, consider a computer and its CPU. A CPU may not be installed in any computer, or it may be installed in at most one computer. A computer may have no CPU, one CPU, or more than one CPU.

Our previous example of Antique Opticals and the distributor from which it ordered only one title is actually a one-to-many relationship where the “many” is currently “one.” Remember that when we are specifying data relationships, we are indicating possible relationships and not necessarily requiring that all instances of all entities participate in every documented relationship. There is absolutely no requirement that a distributor be related to any merchandise item, much less one or more merchandise items. (It might not make much sense to have a distributor in the database from which the company does not order, but there is nothing to prevent data about that distributor from being stored.)

Many-to-many relationships are also very common. There is, for example, a many-to-many relationship between an order placed by an Antique Opticals customer and the merchandise items carried by the store. An order can contain multiple items, and each item can appear on more than one order. The same is true of the orders placed with distributors. An order can contain multiple items and each item can appear on more than one order.

A many-to-many relationship exists between entities A and B if for two instances of those entities (A_i and B_i) A_i can be related to zero, one, or more instances of entity B and B_i can be related to zero, one, or more instances of entity A.

Many-to-many relationships present two major problems to a database's design. These issues and the way in which we solve them are discussed later in the next major section, of this chapter (“Dealing with Many-to-Many Relationships”).

In our discussion of types of data relationships, we have defined those relationships by starting each with “zero,” indicating that the participation by a given instance of an entity in a relationship is optional. For example, Antique Opticals can store data about a customer in its database before the customer places an order. Therefore, an instance of the customer entity does not have to be related to any instances of the order entity.

However, the reverse is not true in this database. An order must be related to a customer. Without a customer, an order cannot exist. An order is therefore an example of a weak entity, one that cannot exist in the database unless a related instance of another entity is present and related to it. An instance of the customer entity can be related to zero, one, or more orders. However, an instance of the order entity must be related to one and only one customer. The “zero” option is not available to a weak entity. The relationship between an instance of the order entity and an instance of the customer entity is therefore a mandatory relationship.

Identifying weak entities and their associated mandatory relationships can be very important for maintaining the consistency and integrity of the database. Consider the effect, for example, of storing an order without knowing the customer to which it belongs. There would be no way to ship the item to the customer, and the company would lose business.

By the same token, we typically specify the relationship between an order and the order lines (the specific items on the order) as mandatory because we don't want to allow an order line to exist in the database without it being related to an order. (An order line is meaningless without knowing the order to which it belongs.)

In contrast, we can allow a merchandise item to exist in a database without indicating the supplier from which is comes (assuming that there is only one source per item). This lets us store data about new items before we have decided on a supplier. In this case, the relationship between a supplier and an item is not mandatory (often described as zero-to-many rather than one-to-many).

The Chen and UML methods of drawing ER diagrams have very different ways of representing relationships, each of which has its advantages in terms of the amount of information it provides and its complexity.

The major entities in the Antique Opticals database are diagrammed in Figure 4-12. You read the relationships in the following way:

The major thing to notice about this design is that there are four many-to-many relationships: order to item, purchase to item, actor to item, and producer to item. Before you can map this data model to a relational database, they must be handled in some way.

Entities that exist to represent the relationship between two or more other entities are known as composite entities. As an example of how composite entities work, consider once again the relationship between an order placed by an Antique Opticals customer and the items on that order.

What we need is an entity that tells us that a specific title appears on a specific order. If you look at Figure 4-13, you will see three order instances and three merchandise item instances. The first order for customer 0985 (Order #1) contains only one item (item 09244). The second order for customer 0985 (Order #2) contains a second copy of item 09244 as well as item 10101. Order #3, which belong to customer 1212, also has two items on it (item 10101 and item 00250).

There are five items ordered among the three orders. The middle of the diagram therefore contains five instances of a composite entity we will call a “line item” (think of it as a line item on a packing slip). The line item entity has been created solely to represent the relationship between an order and a merchandise item.

Each order is related to one line item instance for each item in the order. In turn, each item is related to one line item instance for each order on which it appears. Each line item instance is related to one and only one order, and it is also related to one and only one merchandise item. As a result, the relationship between an order and its line items is one-to-many (one order has many line items), and the relationship between an item and the orders on which it appears is one-to-many (one merchandise item appears in many line items). The presence of the composite entity has removed the original many-to-many relationship.

If necessary, the composite entity can be used to store relationship data. In the preceding example, we might include an attribute for the quantity ordered, a flag to indicate whether it has been shipped, and a shipping date.

In some extensions of the Chen method for drawing ER diagrams, the symbol for a composite entity is the combination of the rectangle used for an entity and the diamond used for a relationship:

The IE and UML styles, however, have no special symbol for a composite entity.

To eliminate Antique Opticals' many-to-many relationships, the database designer must replace each many-to-many relationship with a composite entity and two one-to-many relationships. As you can see in Figure 4-14, the four new entities are as follows:

Because composite entities exist primarily to indicate a relationship between two other entities, they must be related to both of their parent entities. This is why the relationship between each composite entity in Figure 4-14 and its parents is mandatory.