Resolve multi-valued attributes: Create a new entity
Objective
Describe How to resolve Multi-valued Attributes by creating a New Entity
How to resolve Multi-valued Attributes by creating a New Entity
Consider a scenario where a company has decided to add the names and birth dates of all the children of its employees. The company can not just add Child and Birth Date to its list of attributes for the employee entity. Why not? Because any employee with more than one child would have more than one data value entered for each of the two attributes. This violates the single-value attribute rule. On the other hand, the company can not resolve these multi-valued attributes in the same way that the telephone-numbers situation was resolved. How many child attributes and birth date attributes would we create?
Maybe three (called Child 1, Child 2, Child 3; and Birth Date 1, Birth Date 2, Birth Date 3).
But what if an employee has seven children? Dismissing additional children is not like dismissing additional phone numbers and the additional children have to be taken into account. Furthermore, the amount of storage space that would be wasted if we had, say, fifteen child attributes and fifteen birth date attributes would be significant.
Create new Entity
In this (and similar situations), multi-valued attributes are resolved by creating a new entity.
In the case of employees's children, a new entity called Child is created, with attributes such as Name, Birth Date, and SSN.
A relationship is then established between the Employee and Child entities for data retrieval.
The original Employee entity with attributes would look like this:
EMPLOYEE
EmpID LastName FirstName Street City Zip State Children Birthdates
Revised Employee Entity and new Child entity
A revised Employee entity and a new Child entity would look like this:
EMPLOYEE (revised entity)
CHILD (new entity)
EmpID
LastName
FirstName
Street
City
Zip
State
EmpID
Name
BirthDate
The following diagram illustrates how removing all references to children in the Employee entity and creating a new Child entity (ultimately) results in two linked tables. Inserting the EmpID primary key (entity identifier) into the Children table creates the link.
Analysis of the Image:
The image depicts a process of normalization where the "Employees" table is being split into two related tables: "Employees" and "Children". The goal is to normalize the data and create a new entity, "Children," where each child's information is stored in its own row, avoiding multivalued attributes.
Top "Employees" Table"
Explanation of the Transformation**
Top Table (Employees):
Contains EmpID, and two multivalued attributes:
Children: A comma-separated list of child names.
BirthDates: A comma-separated list of corresponding birth dates for the children.
Bottom Table (Children):
Normalizes the data by creating a new entity (Children) where:
Each child has its own row.
The EmpID serves as a foreign key to relate children back to their respective employee.
Purpose of the Transformation**
This transformation aligns with database normalization principles, specifically:
Eliminating multivalued attributes to make the data easier to query.
Creating a one-to-many relationship between employees and their children, where each child is represented in its own row.
Inserting the EmpID primary key (entity identifier) into the Children table which creates the link.
It is now possible to sort information about children. In the Children table above, notice the sort performed on the BirthDate field: from oldest to youngest child. Suppose the organization wanted information about how many employees's children were born before 1990. A query could now retrieve that information, a nearly impossible task in a multi-valued attribute field. If you can resolve a multi-valued attribute by adding one or two additional attributes to an existing entity, then do so. Otherwise, resolve muti-valued attributes by creating a new entity.
General Rule for Multivalued Attribute
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 5-9). 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.
Avoiding Collections of Database Entities
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 DistributedNetworks.
Figure 5-9 Using multiple instances of an entity to handle a multivalued attribute.
Question: Is "inventory" an entity? Answer: 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:
1) merchandise item number, 2) item title, 3) number in stock, 4) retail price. 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.
MultiValued Attributes - Exercise
Before moving on to the next lesson, click the Exercise link below to test your ability to resolve multi-valued attributes. MultiValued Attributes - Exercise
The next lesson lists entity and attribute constraints.
