| Lesson 8 | Resolve multi-valued attributes: Add more attributes |
| Objective | Describe How to Resolve multi-valued Attributes by adding more Attributes |
How to Resolve multi-valued Attributes
Create Additional Attributes
Suppose that Stories on CD, Inc. concluded that the majority of its customers had at least two home phone numbers. Could they store both phone numbers in the phone attribute? No. Two phone numbers would be two completely different data values for the phone attribute, and would violate the single-value attribute[1] rule.
One option is to create two telephone attributes for the Customers entity: phone #1 and phone #2. In this particular case, that would not be a bad solution. This would waste some data storage space since some customers only have one phone number. However, the waste would not be significant. On the other hand, customers with three home phone numbers could have only two of them stored in the database. Again, this is not a significant issue. Two home contact numbers are sufficient.
Following is the entity/attribute format before and after resolution of the multi-valued phone attribute:
One option is to create two telephone attributes for the Customers entity: phone #1 and phone #2. In this particular case, that would not be a bad solution. This would waste some data storage space since some customers only have one phone number. However, the waste would not be significant. On the other hand, customers with three home phone numbers could have only two of them stored in the database. Again, this is not a significant issue. Two home contact numbers are sufficient.
Following is the entity/attribute format before and after resolution of the multi-valued phone attribute:
| CUSTOMER (old entity) | CUSTOMER(new entity) |
|
CustID LastName FirstName Street City State Zip Phone |
CustID LastName FirstName Street City State Zip Phone1 Phone2 |
The graphic below illustrates how creating a second phone attribute for the Customers entity (ultimately) results in two phone fields in a Customers table.
Top "Customers" Table:
| **CustID** | **Last Name** | **Phone** | |------------|---------------|-----------------------------------| | 76213 | DeWitt | 222-555-5525, 222-555-5921 | | 76214 | Erikson | 211-612-2231 | | 76215 | Zimmerman | 412-811-3171, 412-811-9112 |
Bottom "Customers" Table:
| **CustID** | **Last Name** | **Phone** | |------------|---------------|-------------------| | 76213 | DeWitt | 222-555-5525 | | 76213 | DeWitt | 222-555-5921 | | 76214 | Erikson | 211-612-2231 | | 76215 | Zimmerman | 412-811-3171 | | 76215 | Zimmerman | 412-811-9112 |
Explanation of the Transformation
- The top table has "Phone" as a single column that contains comma-separated values (multiple phone numbers).
- The bottom table normalizes the data by expanding each phone number into a separate row, while retaining the other columns (CustID and Last Name) for each phone number.
This type of transformation is often used in database normalization to eliminate multivalued attributes and ensure that each piece of data exists in a unique row for better data integrity and querying. Creating a second phone attribute for the Customers entity results in 2 phone fields in the Customers table.
Single-Valued versus Multivalued Attributes
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 will not be able to hold all the phone numbers. 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.
While it is true that the entity-relationship model[2] of a database is independent of the formal data model used to express the structure of the data to a DBMS, we often make decisions on how to model the data based on the requirement of the formal data model we will be using. Removing multivalued attributes is one such case. You will also see an example of this when we deal with many-to-many relationships between entities.
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.
While it is true that the entity-relationship model[2] of a database is independent of the formal data model used to express the structure of the data to a DBMS, we often make decisions on how to model the data based on the requirement of the formal data model we will be using. Removing multivalued attributes is one such case. You will also see an example of this when we deal with many-to-many relationships between entities.
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.
The majority of multi-valued attributes are not resolved by simply adding an attribute or two to a single entity. Instead, a new entity must be created and the next lesson describes how.
[1]single-valued attribute: An attribute with more than one data value; must be resolved into a single-valued attribute.
[2]entity-relationship model: An entity-relationship diagram is another form of object model that in many ways is similar to a semantic object model. It also allows you to represent objects and their relationships, although it uses different symbols.