ANOTHER EXAMPLE

One approach is to fool the system by defining a fixed number of separate fields with different names, when they are really repetitions of the same field. The result is a violation of first normal form. Normalization can improve the quality of a database design. The following table, UnPerson, is not normalized because it has multiple values for language for each person.

To appreciate the problems of using an unnormalize database, write the following queries

1. List all people who speak French.
2. List those who speak English and no other language.
3. Who speaks the most languages?
4. Add the fact that Rita speaks French.
5. Suppose that Lang1 is considered the primary or native language or each person. After many years in New Zealand, Wei wants English listed as his primary language.
6. George, whose record is not shown, just joined the firm. He speaks English and French.

A normalized design

The correct data model, which recognizes a m:m relationship between person and language, generally requires three tables. In this particular case, the Lang table is not necessary since there are no additional attributes of language to be stored. A separate Lang table would be necessary if you wanted to keep a master list of all languages, to control the set of valid values for language, or to store any languages which did not appear in the UnPerson table. Whether you decide to include the the Lang table is not a major issue since it takes little effort to create and maintain, and takes little storage space.

A 1:m relationship between lang and person can record the fact that a person has a single primary language and that a language can be a primary language for many people. The revised tables, showing the foreign key for the new relationship, are: