Modeling hobbies for our contacts
Introduction
Attributes (like phone numbers) that are explicitly repeated in a class definition are not the only design problem that we might have to correct. Suppose that we want to know what hobbies each person on our contact list is interested in (perhaps to help us pick birthday or holiday presents). We might add an attribute to hold these. More likely, someone else has already built the database, and added this attribute without thinking about it.
The multivalued attribute is obvious in this example as its name is in plural. Be aware that this won’t always be the case. We can only be sure that there’s a design problem when we find data in a table as depicted below.
| 1639 | George | Barnes | reading |
| 5629 | Susan | Noble | hiking, movies |
| 3388 | Erwin | Star | hockey, skiing |
| 5772 | Alice | Buck | |
| 1911 | Frank | Borders | photography, travel, art |
| 4848 | Hanna | Diedrich | gourmet cooking |
In this case, the hobby attribute wasn’t repeated in the scheme, but there are many distinct values entered for it in the same column of a row. This is called a multivalued attribute. The problem with this design is that it is now difficult (but possible) to search the table for any particular hobby that a person might have, and it is impossible to create a query that will individually list the hobbies that are shown in the table. Unlike the phone book example, NULL is probably not part of the problem here, even if we don’t know the hobbies for everyone in the database.
Using UML Multiplicity for multivalued attributes
In UML, we can again use the multiplicity notation to show that a contact may have more than one value for hobby.
Mapping to the relational model
As you should expect by now, we can’t represent the multivalued attribute directly in the Contacts relation scheme. Instead, we will model it using its own relation scheme. Thus, we remove the old hobbies attribute and create a new scheme, very similar to the one that we created for phone numbers in the repeated attribute design pattern.
The relationship between Contacts and Hobbies is one-to-many, so we create the usual pk-fk pair. The new scheme has only one descriptive attribute, the hobby name. To uniquely identify each row of the table, we need to know both which contact this hobby belongs to and which hobby it is—so both attributes form the pk of the scheme.
With data entered, the new table looks similar to the PhoneNumbers. It can also be joined to Contacts on matching pk-fk contactID pairs, re-creating the original data in a form that we can now conveniently use for queries.
| 1639 | reading |
| 5629 | hiking |
| 5629 | movies |
| 3388 | hockey |
| 3388 | skiing |
| 1911 | photography |
| 1911 | travel |
| 1911 | art |
| 4848 | gourmet cooking |
