In your discussion of the systems design phase, you mentioned normalization to Cassia. She would like you to explain the basics of normalization in plain English to help her understand the data design tasks.

What will be an ideal response?

Managers will understand that data is a valuable corporate resource that must be accurate, easy to maintain, and adaptable in a changing business environment. The bottom line is that a good design will be significantly less expensive to maintain and help boost customer satisfaction. Although they probably are not interested in the details, managers should know that data must be organized in a special way to achieve these objectives. The process of organizing the data is called normalization.
The normalization process creates specific requirements for data that produce better quality results, just as companies set standards for quality control objectives. First normal form is better than unnormalized data, second normal form is even better, and third normal form is what most businesses use in their day-to-day operations. Tell students that managers might ask for a specific example of how normalization works, and they should be able to respond with a relatively simple answer.
Although the next section goes somewhat beyond the SWL Team Tasks assignment, it can help students test their understanding of normalization. The instructor should be prepared to display specific examples of the record designs described. Although normalization can be complex, some systems analysts like to explain the concept with the following phrase, which is easy to remember: in a properly designed record in third normal form, all nonkey fields depend on the key, the whole key, and nothing but the key. The following example demonstrates how this phrase would apply:
Suppose you wanted to design a system to track your service reps and the customers assigned to them. You could set up a table with one record per service rep and with fields for as many customers as you estimate a service rep could possibly have. The service rep number would serve as the primary key. Suppose the current policy was to assign a maximum of 20 customers to each service rep, so you designed the record with 20 fields. Now ask, what would happen if the company suddenly decided to raise the limit to 25? The current database would be useless and you would have to start all over. This is an example of an unnormalized record, because it contains a repeating group — the customer fields.
First normal form: Suppose that you moved the customer data into a separate table with one record per customer, using the customer number as a primary key. In each customer record, include a field called service rep number, which can relate customers to their assigned reps and to the service rep’s name. You have removed the repeating group and created two records in first normal form.
Second normal form: To be in second normal form, a record must be in first normal form and all fields must depend on the entire primary key — not just a portion of it. Therefore, if a 1NF record has a single primary key such as the service rep number or a customer number to identify each individual uniquely, the record is automatically in second normal form. If a combination of two or more fields forms a primary key that uniquely identifies each record, however, then the record is in second normal form only if all the nonkey fields depend on the entire primary key — not just part of it. Think of it this way: in second normal form, all fields must depend upon the key, the whole key. In this case, you would have to remove all the fields except the ones that depend on the entire primary key and place them into their own separate table.
Third normal form: Now suppose you have a table in second normal form, where all the nonkey fields depend on the entire primary key, but there is one nonkey field that also depends on another nonkey field. For example, in the customer record described above, the service rep number and service rep name both depend on the customer number, but the service rep name also depends on the rep number, which creates redundancy, multiple data entries, and possible inconsistency. To get to third normal form, you must remove the service rep name to a separate table, which can contain other information about the rep, such as telephone number or location. Remember, to be in third normal form, all nonkey fields must depend on the key, the whole key, and nothing but the key.