The First Normal Form

Eliminate Repetition

Normalizing a database begins by eliminating repetitions. A database that is free of repetitions is said to be in the first normal form (or 1NF). Our sample database, on the previous page, has repetitions: the tricks are repeated three times.

If we break the data into two separate tables, we eliminate the repetition, and we fix several flaws. With two tables we can now store as few or as many tricks as we want (storage space permitting). Also, there is no wasted space because we only insert a record into the tricks table as necessary.

Here are the two tables in the first normal form:

DOG Table

Dog	First Name	Last Name	Sex	Kennel	Street	City	State	ZIP	Phone
3	Tuck	Yang	M	Ruff Haus	1200 Frank St.	Chattanooga	TN	37411	423-820-2412
12	Nip	Yin	M	K9HQ	4051 Lewis St.	Chattanooga	TN	37412	423-833-4122
13	Shadow	Muttley	U	K9HQ	4051 Lewis St.	Chattanooga	TN	37412	615-833-4122


SKILL Table

Dog	Trick	Skill
3	Rollover	53
3	Retrieve	71
3	Tightrope	91
12	Roll Over	40
12	Play Dead	70
12	Fetch	50
13	Sit	10
13	Stay	10
13	Heel	12

Top of page  

We also get an efficiency benefit from the first normal form. When this database grows to a larger size we can easily query the database to determine which dogs know a particular trick. Not only will the queries be easier to write, the less complex queries also run faster! In an unnormalized database, we're basically running three queries! The queries below are written in SQL, which you aren't expected to know at this point, but they should be fairly easy to read.

      SELECT dog
      FROM dog
      WHERE trick1 = 'SIT'
      OR    trick2 = 'SIT'
      OR    trick3 = 'SIT';
      

      SELECT dog
      FROM skill
      WHERE trick = 'SIT';