Normalization of Tables - From 2NF to 3NF

In our PowerPoint lecture notes Topics-Ch4Ch5.ppt we discussed the various normal forms and the reasons for using them when designing our database tables. Namely, that they minimize the chance of update anomalies occurring. Tables that are in second normal form can still have problems. Consider the following Customer table (p. 160 of our textbook). The same set of problems exist here as did in our Orders table.

• Making a change to the name of a sales rep may require changes in many rows.
   
• A rep's name that appears in many rows could have many different spellings.
   
• To add a new sales rep he/she would already have to have been assigned to a customer. This is not realistic.
   
• Deleting all customers represented by a given sales rep would cause one to lose all the information pertaining to that sales rep.

Again, why do all these anomalies exist?
In this case it is because the nonkeys, LastName and FirstName depend on RepNum. But RepNum is not the primary key. This is the 3NF condition we call transitive dependency.

Our author calls an attribute like RepNum a determinant because it determines other attributes.

To remove transitive dependencies.

1. For each determinant that is not a candidate key^*, remove from the table the columns that depend on this determinant (but don't remove the determinant).
2. Create a new table containing all the columns from the original table that depend on this determinant.
3. Make the determinant the primary key of this new table.
4. Name the new table appropriately.

 ^* A candidate key is any key that could serve as a primary key.

Customer (CustomerNum, CustomerName, Balance, CreditLimit, RepNum)
Rep (RepNum, LastName, FirstName)