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: What are the disadvantages of using a multi-valued attribute in an entity relationship model?
In an Entity Relationship Model (ERM), a multi-valued attribute is an attribute that can have multiple values for a single entity instance. While multi-valued attributes can be useful in certain situations, they also introduce several disadvantages. The primary drawbacks of using multi-valued attributes in an ERM include:

1. Complexity: Multi-valued attributes can increase the complexity of the model, making it more difficult to understand, maintain, and manage. This added complexity can also lead to errors when modeling the relationships between entities and attributes.
2. Normalization Violation: Multi-valued attributes violate the first normal form (1NF) of database normalization, which requires that each attribute within a relation should have a single atomic value. This can lead to data redundancy, inconsistencies, and anomalies during the insertion, deletion, or updating of records.
3. Querying Difficulties: Multi-valued attributes can make querying the database more complex, as SQL does not natively support querying or manipulating multi-valued attributes. To handle such attributes, developers need to use more complex query structures, which can impact the performance and efficiency of the database system.
4. Data Integrity Issues: Ensuring data integrity becomes more challenging with multi-valued attributes, as it is difficult to implement constraints like uniqueness and referential integrity. This can result in the entry of duplicate or inconsistent data, which can lead to inaccurate query results and other issues.
5. Limited Compatibility: Many database management systems (DBMS) do not support multi-valued attributes directly, which can create compatibility issues when attempting to implement an ERM that includes them. This may require additional effort to adapt the model to a specific DBMS or to use custom data structures to store and manage the multi-valued attributes.
6. Inefficient Storage: Storing multi-valued attributes in a database can lead to inefficient storage, as each instance of the attribute must be stored separately. This can consume more space, impact performance, and increase the time required for backup and recovery operations.

While multi-valued attributes can provide flexibility in some cases, they introduce several disadvantages that can impact the overall efficiency, performance, and maintainability of an Entity Relationship Model. In many situations, it is advisable to avoid multi-valued attributes and adopt alternative modeling techniques, such as normalization, to better structure and manage the data.

Question: What exactly constitutes a multi-valued attribute?
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.

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.

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?

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.

[1] multi-valued attribute: An attribute with more than one data value; must be resolved into a single-valued attribute.