RelationalDBDesign RelationalDBDesign



Attributes-Entities   «Prev  Next»
Lesson 7 Multi-valued attributes
Objective Explain the Problem with multi-valued Attributes

Explain the Problem with multi-valued Attributes

A multi-valued attribute is a single attribute with two distinct data values entered for that attribute.
No attribute is permitted to have multiple data values. If so, this would violate the
one attribute |-> one value rule
of relational databases.
For every instance of an entity, each attribute in its group can have only one value. Attributes with more than one value are considered multi-valued, and they must be resolved.
Question: But what exactly constitutes a multi-valued attribute?

Identifying multi-valued Attributes

Consider an attribute called "Name" for a "Customer" entity.
Question: If Susan Anthony were entered as a data value, would this be considered a multi-valued attribute?
Answer: No.
The full name does not violate the one-value-per-attribute rule because it is not multi-valued in the sense of presenting two different values for an attribute.
Question: Why, then, do most databases break down the single-attribute name into two attributes:
  1. first name and
  2. last name?

Here is one good reason to do so. Suppose you want to send form letters to your customers and grab the information for the salutation from the database. Do you really want to send a letter that begins with:
Dear Susan Anthony?
Probably not (at least, not in all situations).
What you really want for the salutation is just the customer's first name.
In other words, while a full name does not violate the "one attribute |->one value" rule for the Name attribute, there are practical reasons for breaking it down.
Now consider the same "Customer" entity with a "Phone" attribute.
If two telephone numbers are entered as data values (perhaps a business phone number and a home phone number) for the Phone attribute, this constitutes a multi-valued attribute.

Problem with multi-valued attributes

Multi-valued attributes make sorting data in a database extremely difficult, if not impossible. They must be resolved, and there are two ways to do so:
  1. create additional attributes for an entity, or
  2. create an entirely new entity.

Question: 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 5-7, 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?

Figure 5-7 Entity instances containing multivalued attributes.

When searching a multivalued attribute [1], 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.
Note: Although it is theoretically possible to write a DBMS that will store an unlimited number of values in an attribute, the implementation would be difficult and searching much slower than if the maximum number of values were specified in the database design.

The next lesson describes how to resolve multi-valued attributes by adding more attributes to describe an entity.
[1] multi-valued attribute: An attribute with more than one data value; must be resolved into a single-valued attribute.