1NF

Slide 1: 

First Normal Form - 1NF A group by any name is oh so lame.

Slide 2: 

First Normal Form (1NF) 1NF is the first step in organizing your data. It forms the basis of all other normal forms 2NF requires 1NF 3NF requires 2NF, 1NF A database is 1NF when all of the contained tables are 1NF. The focus of 1NF is to eliminate groups and multi-value fields. Overview

Slide 3: 

In order for a table to be considered 1NF, it must contain NO Top-to-bottom ordering dependencies Left-to-right ordering dependencies Duplicate rows/records Repeating groups within columns Repeating groups across columns The Technical Definition First Normal Form (1NF)

Slide 4: 

In order for a table to be considered 1NF, it must contain Independent records and columns, no ordering constraints on either A way to uniquely identify a single record and prevent duplicate records (primary key) No multi-value fields, each field in a record can hold at most one value [repeating groups within columns] No duplicate columns such as Vehicle1 and Vehicle2 [repeating groups across columns] The Layman’s Definition First Normal Form (1NF)

Slide 5: 

Good database design takes care of the first three constraints for 1NF. Thus, 1NF is primarily two constraints Only one value per record in a data field Only one field per attribute of an object It can be summed up as Each component of a field (both definition and data) must be atomic. Notice… First Normal Form (1NF)

Slide 6: 

In the example below, the table is not 1NF because it contains a repeating group. For Example… [ DMV ] Problem: Repeating groups WITHIN columns First Normal Form (1NF)

Slide 7: 

An alternative view of the same problem with a different repeating group. For Example… [ DMV ] Problem: Repeating groups ACROSS columns First Normal Form (1NF)

Slide 8: 

The requirement for no repeating groups makes total sense. Each separate value in a repeating group is generally representative of an object instance and a relationship to that instance. Kia Spectra Ford Mustang But remember, by design we represent each object instance with its own record and relationships with keys. Ponder… Instances of object type Vehicle First Normal Form (1NF)

Slide 9: 

Establish a table for each object represented by repeating groups.All repeating groups indicative of a single object type should be moved to their own table. Make sure each new table has a primary key. Establish a relationship between the objects.Upon creating a table for the object, a foreign key should be added to establish a relationship back to the original table. Repeat the process until database is 1NF.The database is 1NF when all tables in the database are 1NF. Converting to 1NF First Normal Form (1NF)

Slide 10: 

The original problem… For Example… [ DMV ] Problem: Repeating groups WITHIN columns First Normal Form (1NF)

Slide 11: 

Step 1 is complete and the object vehicle now has its own definition. For Example… [ DMV ] First Normal Form (1NF)

Slide 12: 

The foreign key field is added to relate the vehicle records back to the registrants that own them. For Example… [ DMV ] First Normal Form (1NF)

Slide 13: 

The foreign key values are added to complete the relationships. The database is now 1NF. For Example… [ DMV ] First Normal Form (1NF)

Slide 14: 

The Result The data is now one step closer to being information. The data is now separate and independent. Eliminates data redundancy. Retrieve only what is now needed. The data now better models the real world with separate objects and relationships between them. First Normal Form (1NF)

Slide 15: 

In Summary… First normal form (1NF) is the first step in organizing data into information. All other normal forms require the database first be 1NF. The primary focus of 1NF is to eliminate repeating groups multi-value fields (repeating groups within columns) duplicate fields (repeating groups across columns) First Normal Form (1NF)