Relational databases store data in two-dimensional tables, a concept that's relatively simple to understand. However, putting data into a relational database does not guarantee that the data will remain correct or logically consistent. Unless the database is designed correctly, it may have problems:

? No place to store some information. We might need to store six things but have room for only five.

? Contradictory information. We might have two different mailing addresses for someone, but not know which is the correct one.

? Information that's difficult to extract. It may be stuffed inside an overlarge field along with other information.

Such problems are called anomalies.

The best defense against anomalies is to normalize the database in a way that will prevent anomalies. Normalization is the process of determining what physical files (called "relations" in database theory) are needed and what data should be in each one.

Let's consider how we might normalize the information a personnel department maintains about employees.

The Normalization Process

Physical files contain information about entities, such as employees, customers, suppliers, invoices, checks, and inventory items. Each different piece of information is known as an attribute. Attributes are the facts we know about people or things. Common attributes of a customer, for instance, are account number, name, address, phone number, and credit limit. A database for an office telephone directory might have three attributes?employee name, office number, and extension number.

There are two types of attributes?key attributes and nonkey attributes. Do not confuse keys with the key fields defined in data description specifications (DDS). In DDS, the key is simply an ordering sequence. In relational database terminology, the key is the set of attributes that uniquely identifies each record in a file. For instance, an employee could be uniquely identified by his badge number or social security number, so either one could serve as the key attribute for an employee file. The name and date of birth are not acceptable keys, since two or more employees could have the same name or birth date.

Many people have suggested ways of going about normalization, but an easy one that works well is decomposition. Imagine that all information is to be stored in one physical file. Examine that file to see if any of the information should be stored in another physical file, and if so, create a new file to hold that information. Continue to divide files until no file needs to be divided further.

While you are normalizing the database, do not concern yourself with the physical aspects of the database, such as file and record names, field names, data types, and field sizes. All of that will come later. Concentrate on what information you must store, not how you will store it.

1 shows the starting file of employee information.

Figure 1 shows the starting file of employee information.

First Normal Form

To be in first normal form (1NF), all values must be atomic. English translation: don't store more than one piece of information in one field. There are two ways to violate this rule: by combining fields into one field and by repeating a field.

Returning to the employee information file, we see that the address attribute looks suspicious. It consists of four parts. If we always manipulate the address as a whole, not really concerned about which part is which, then it's fine as one attribute. However, if we need to consider the different parts (to generate mailing labels, to select employees by state, or to sort by zip code), we divide the address into other attributes.

Repeating groups also contain more than one piece of information. Take the languages attribute, for example. We could define a language-1 attribute and a language-2 attribute, but those would still be one type of information. Besides, how many languages are we going to leave room for? Two? What about the fellow who speaks three foreign languages? Four? What happens when we hire a guy who speaks five or more?

By defining more than one language attribute, we'd be trying to store the information that Joe Smith speaks Spanish and French and German as one fact. What we'll have to store instead are three distinct facts: Joe Smith speaks Spanish. Joe Smith speaks French. Joe Smith speaks German.

To bring this part of the database to 1NF, we have to create a new file for languages spoken. When we create a new physical file, we must include the key attributes of the file we derived it from, so this new file will have the badge number as a part of the key. Each occurrence of the repeated group will be a record in the new file, so the repeated attribute becomes a single attribute in the new file. In other words, a person who speaks two foreign languages has in the new file two records with his badge number.

Of course, the badge number alone will not uniquely distinguish every record in the file since one person may speak more than one foreign language. We'll need a second key attribute?language. Two or more records could have Joe Smith's badge number, and many records could have "Spanish" in the language code. But there will never be two records with the same badge number and language code, as there is no need to store twice the fact that Joe Smith speaks Spanish. Keys of two or more attributes are common in relational databases and are usually called concatenated keys or multiattribute keys.

Here's the file to store foreign language skills.

Languages spoken

Badge number (key)

Language (key)

There is another repeating group that we need to normalize?training courses taken. We have to do a little analysis here before we can determine the key. Badge number and course number have to be key attributes, of course. The completion date may or may not be part of the key. If an employee never repeats a training course, badge number and course number are sufficient to distinguish each record. If an employee may repeat a course, we would add completion date to the key. Let's assume here that employees do not repeat courses.

2 shows the database in 1NF.

Figure 2 shows the database in 1NF.

Second Normal Form

A file is in second normal form (2NF) if all nonkey attributes are functionally dependent on the entire key, not just a portion of it. This form applies only to files that have two or more key attributes.

Functional dependence means that given one piece of information, we can determine another piece of information. For example, suppose you and I are talking about the states of the United States. If I tell you that I am talking about a state called Mississippi, you can tell me that the capital is Jackson, the state tree is the magnolia, and the state bird is the mockingbird. State capital, tree, and bird are all functionally dependent on the state name. If the only information I give you, however, is that the state bird is the mockingbird, you cannot determine exactly which state I am talking about, since there are several states whose state bird is the mockingbird. State name, tree, and capital are not functionally dependent on the state bird.

Let's take a look at the files that have multiattribute keys. Languages spoken does not have any nonkey attributes, so it's OK as is. Courses taken does have some nonkey attributes. Can any of them be determined by only a part of the key? You've probably already noticed that the course name is functionally dependent on the course number only, not badge number and course number together. That is, if we want to know the name of a course, we have to know only its number, not anyone's badge number. We will have to remove course name from the file and place it in a file of courses.

Most midrange programmers would have already created a course master file. It's just common sense to us. So here's the courses file taken to 2NF.

Training courses taken

Badge number (key)

Course number (key)

Completion date

Score

Training courses

Course number (key)

Course name

Third Normal Form

A file is in third normal form (3NF) if nonkey attributes are not functionally dependent on other nonkey fields. It's easier to understand if we say what 3NF is not. A file is not in 3NF if any nonkey attribute is functionally dependent on another nonkey attribute.

Look again at the employee information file in 2. The key field is the employee's badge number. Are any fields functionally dependent on something other than badge number? You probably noticed that the supervisor's name is functionally dependent on the supervisor's badge number. The department name is functionally dependent on the department number. The supervisor's name and the department name are not needed in the employee information file. The supervisor is also an employee, so his name is in his record in the employee information file. We can store the department name once in a department file. This is common sense. Most of us would have already created a department master file.

Look again at the employee information file in Figure 2. The key field is the employee's badge number. Are any fields functionally dependent on something other than badge number? You probably noticed that the supervisor's name is functionally dependent on the supervisor's badge number. The department name is functionally dependent on the department number. The supervisor's name and the department name are not needed in the employee information file. The supervisor is also an employee, so his name is in his record in the employee information file. We can store the department name once in a department file. This is common sense. Most of us would have already created a department master file.

3 shows the personnel database in 3NF.

Figure 3 shows the personnel database in 3NF.

Other Normal Forms

These are not the only proposed normal forms. Others that I have heard of are Boyce-Codd Normal Form (a stronger version of 3NF), Fourth Normal Form, Project-join Normal Form (a stronger version of 4NF), Fifth and Sixth Normal Forms, and Domain-key Normal Form. These forms have their merits, but you do not need them for most data processing applications. If you will normalize a database through 3NF, it will probably be in 5NF (at least) anyway.

Implementing the Design

We have ignored the physical aspects of our database to this point. We haven't even named any files or written any DDS. It's time to translate the database design into something the computer can work with. What we have developed could be implemented on any computer system with a relational database management system.

We now have a set of physical files to be built on the AS/400. We can use the Create Physical File (CRTPF) command if we define the file with DDS. With SQL, we use the CREATE TABLE command.

The key attributes should be unique keys in the files. We can specify them in the physical file DDS or build a logical file with the unique key. In SQL, we use the CREATE UNIQUE INDEX command.

That is sufficient to define the database. We can add logical files to define other ways of looking at the data. Logical files do not have to be normalized! We can concatenate fields into one field, violating 1NF, or join files to each other, creating record formats that violate 2NF and 3NF.

Exceptions

As with most rules, there are exceptions. The following material will take a look at some exceptions.

We don't have to normalize every file on the system. Temporary work files (commonly called "scratch files") do not need to be normalized. Archive or history files, especially those used only for inquiry and reporting, do not need to be normalized. Of course, we can partially or fully normalize these types of files if there is some advantage to doing so. As a rule, the only files we need to normalize are the ones that maintain the current state of your organization.

As mentioned previously, repeating groups should be placed in a separate file and each occurrence of the repeated group should become a separate record. There may be situations in which you don't normalize the repeating group because doing so may cost you in terms of performance. For example, you may have 12 periods of sales figures, and the number of periods is not going to change. If you're stuck with a machine with poor performance, you might have to consider a repeating group.

In the employee information file example, the city and state appear to be functionally dependent on the zip code. Should we remove the city and state from the employee information and create another file consisting of zip code (key), city, and state? Yes, but here are a few reasons you might consider leaving these fields unnormalized. If you store all three fields in the employee information file, an incorrect zip code would not necessarily produce an unusable address, since the city and state might still be correct. Some residents use a different city in their address than the city where the post office for their zip code is located. If you treat city, state, and zip code as one piece of information, a record can contain different cities for the same zip code. You might also want to leave city, state, and zip code in the employee file for performance reasons.

Since each employee has a unique social security number, you could argue that all nonkey attributes in the employee information file are functionally dependent on a nonkey attribute, a violation of 3NF. Technically, that is true?but what file would we move the social security number to? What would it profit us to store it somewhere else? Keep in mind the information we're trying to store. Social security number could be a key attribute, but we're not using it as one. To us, it's just information we need so we can comply with laws. As far as we're concerned, it doesn't have to be unique.

Situation Normal: All Facts Understandable!

You can find academic explanations of the normal forms in textbooks, if you're into that sort of thing. For those of us who are a little more down to earth, here are some alternative rules for normalization.

1. Don't make one field out of two.

2. Don't store array variables or repeat scalar variables in a record.

3. Every nonkey attribute must be functionally dependent on the key, the whole key, and nothing but the key.

These rules restate the first three normal forms in more down-to-earth English. However you choose to express them, use the principles of normalization in your information system. They really work.

Ted Holt is an associate technical editor for Midrange Computing.

Common Sense Normalization

Figure 1: Personnel Database Before Normalization

Employee information

Badge number (key)

Name

Mailing address (street, city, state, zip code)

Telephone number

Social security number

Supervisor's badge number

Supervisor's name

Department number

Department name

Languages employee speaks (other than English)

Training courses employee has taken (course number, course name, completion date, score)

Common Sense Normalization

Figure 2: Personnel Database in 1NF

Employee information

Badge number (key)

Name

Street address

City

State

Zip code

Telephone number

Social security number

Supervisor's badge number

Supervisor's name

Department number

Department name

Languages spoken

Badge number (key)

Language (key)

Training courses taken

Badge number (key)

Course number (key)

Course name

Completion date

Score

Common Sense Normalization

Figure 3: Personnel Database in 3NF

Employee information

Badge number (key)

Name

Street address

City

State

Zip code

Telephone number

Social security number

Supervisor's badge number

Department number

Departments

Department number (key)

Department name

Languages spoken

Badge number (key)

Language (key)

Training courses taken

Badge number (key)

Course number (key)

Completion date

Score

Training courses

Course number (key)

Course name