First Normal Form 1 NF A group by

First Normal Form - 1 NF A group by any name is oh so lame. DISCOVER! Texas State Technical College

First Normal Form (1 NF) Overview 1 NF is the first step in organizing your data. It forms the basis of all other normal forms • 2 NF requires 1 NF • 3 NF requires 2 NF, 1 NF A database is 1 NF when all of the contained tables are 1 NF. The focus of 1 NF is to eliminate groups and multivalue fields. DISCOVER! Texas State Technical College

First Normal Form (1 NF) The Technical Definition In order for a table to be considered 1 NF, 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 DISCOVER! Texas State Technical College

First Normal Form (1 NF) The Layman’s Definition In order for a table to be considered 1 NF, 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 Vehicle 1 and Vehicle 2 [repeating groups across columns] DISCOVER! Texas State Technical College

First Normal Form (1 NF) Notice… Good database design takes care of the first three constraints for 1 NF. Thus, 1 NF 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. DISCOVER! Texas State Technical College

First Normal Form (1 NF) For Example… [ DMV ] In the example below, the table is not 1 NF because it contains a repeating group. Registrants. With. Vehicles SSNpk Last First Vehicle Text 521443684 Doe John Ford Focus, Kia Spectra 884652471 Smoe Joe Dodge Neon 996335588 Johnson James Ford Mustang, Nissan Titan 114225575 Nelson Jeff Kia Spectra Problem: Repeating groups WITHIN columns DISCOVER! Texas State Technical College

First Normal Form (1 NF) For Example… [ DMV ] An alternative view of the same problem with a different repeating group. Registrants. With. Vehicles SSNpk Last First Vehicle 1 Vehicle 2 Text Text 521443684 Doe John Ford Focus Kia Spectra 884652471 Smoe Joe Dodge Neon 996335588 Johnson James Ford Mustang 114225575 Nelson Jeff Kia Spectra Nissan Titan Problem: Repeating groups ACROSS columns DISCOVER! Texas State Technical College

First Normal Form (1 NF) Ponder… 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 Instances of object type Vehicle But remember, by design we represent each object instance with its own record and relationships with keys. DISCOVER! Texas State Technical College

First Normal Form (1 NF) Converting to 1 NF 1. 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. 2. 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. 3. Repeat the process until database is 1 NF. The database is 1 NF when all tables in the database are 1 NF. DISCOVER! Texas State Technical College

First Normal Form (1 NF) For Example… [ DMV ] The original problem… Registrants. With. Vehicles SSNpk Last First Vehicle Text 521443684 Doe John Ford Focus, Kia Spectra 884652471 Smoe Joe Dodge Neon 996335588 Johnson James Ford Mustang, Nissan Titan 114225575 Nelson Jeff Kia Spectra Problem: Repeating groups WITHIN columns DISCOVER! Texas State Technical College

First Normal Form (1 NF) For Example… [ DMV ] Step 1 is complete and the object vehicle now has its own definition. Vehicle Registrant SSNpk Last First Text 521443684 Doe John 884652471 Smoe Joe 996335588 Johnson James 114225575 Nelson Jeff SSN VINpk Mfg Model Text 521443684 A 14567 Ford Focus 521443684 VE 9803 Kia Spectra 884652471 RTE 786 Dodge Neon 996335588 W 23498 Nissan Titan 996335588 QW 9345 Ford Mustang 114225575 TS 3232 Kia Spectra DISCOVER! Texas State Technical College

First Normal Form (1 NF) For Example… [ DMV ] The foreign key field is added to relate the vehicle records back to the registrants that own them. Vehicle Registrant SSNfk VINpk Mfg Model Text SSNpk Last First Text 521443684 A 14567 Ford Focus 521443684 Doe John 521443684 VE 9803 Kia Spectra 884652471 Smoe Joe 884652471 RTE 786 Dodge Neon 996335588 Johnson James 996335588 W 23498 Nissan Titan 114225575 Nelson Jeff 996335588 QW 9345 Ford Mustang 114225575 TS 3232 Kia Spectra DISCOVER! Texas State Technical College

First Normal Form (1 NF) For Example… [ DMV ] The foreign key values are added to complete the relationships. The database is now 1 NF. Registrant Vehicle SSNpk Last First SSNfk VINpk Mfg Model Text Text 521443684 Doe John 521443684 A 14567 Ford Focus 884652471 Smoe Joe 521443684 VE 9803 Kia Spectra 996335588 Johnson James 884652471 RTE 786 Dodge Neon 114225575 Nelson Jeff 996335588 W 23498 Nissan Titan 996335588 QW 9345 Ford Mustang 114225575 TS 3232 Kia Spectra DISCOVER! Texas State Technical College

First Normal Form (1 NF) 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. DISCOVER! Texas State Technical College

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