To normalize databases, there are certain rules to keep in mind. These pages will illustrate the basics of normalization in a simplified way, followed by some examples.

Database normalization Rule 1: Eliminate Repeating Groups. Make a separate table for each set of related attributes, and give each table a primary key.

Unnormalized Data Items for Puppies

  • puppy number
  • puppy name
  • kennel code
  • kennel name
  • kennel location
  • trick ID
  • trick name
  • trick where learned
  • skill level

In the original list of data, each puppy description is followed by a list of tricks the puppy has learned. Some might know 10 tricks, some

might not know any. To answer the question “Can Fifi roll over?” we need first to find Fifi’s puppy record, then scan the list of tricks associated with the record.This is awkward, inefficient, and extremely untidy.

Moving the tricks into a seperate tablehelps considerably. Seperating the repeating groupsof tricks from the puppy information results in first normal form. The puppy number in the trick table matches the primarykey in the puppy table, providing a foreign key for relating the two tables with a join operation. Now we can answer our question with a direct retrieval look to see if Fifi’s puppy number and the trick ID for “roll over” appear together in the trick table.

First Normal Form:

Puppy Table
puppy number                     — primary key
puppy name
kennel name
kennel location

Trick Table
puppy number
trick ID
trick name
trick where learned
skill level