Third Normal Form 3 NF Its all about

Third Normal Form - 3 NF It’s all about the key, the whole key, and nothing but the key. DISCOVER! Texas State Technical College

Third Normal Form (3 NF) Overview 3 NF is a sufficient level of normalization for most databases. At this point, the data is sufficiently organized to be useful as information to most organizations or entities. The focus of 3 NF is to eliminate dependencies among non-key fields. DISCOVER! Texas State Technical College

Third Normal Form (3 NF) The Technical Definition In order for a table to be considered 3 NF, it must be 2 NF and, consequentially, 1 NF. In order for a table to be considered 3 NF, all fields in the table must have NO • Functional dependence upon non-key fields • Transitive relationships to the primary key Essentially, all fields must be directly related to or dependent upon the primary key. DISCOVER! Texas State Technical College

Third Normal Form (3 NF) The Layman’s Definition Functional dependence • Field Order_Total is dependent upon fields • • Order_Quantity Order_Item_Price • The functional dependence is defined as • Order_Total = Order_Quantity X Order_Item_Price Transitive dependence • Field Ship_Company is directly dependent upon • Shipment_ID (primary key) • Field Ship_Company_Address is dependent upon • Ship_Company to link or relate to • Shipment_ID DISCOVER! Texas State Technical College

Third Normal Form (3 NF) Why Eliminate … Functional dependence • Increases data redundancy and reduces independence • An update to one field requires update to others Transitive dependence • Indicates a field is not describing the object represented by the record or table • Field describes another object and should be in that object’s table DISCOVER! Texas State Technical College

Third Normal Form (3 NF) For Example… Two examples of functional dependence. Order_IDpk Quantity Price Total Text Number Currency 19568 4 4. 00 16. 00 85466 13 2. 00 26. 00 77788 10 30. 00 300. 00 Shipment IDpk Provider Weight $per. LB Cost Text Number Currency 65664 Fed. Ex 4. 00 16. 00 48. 00 55555 USPS 2. 00 4. 00 87954 USPS 30. 00 2. 00 60. 00 DISCOVER! Texas State Technical College

Third Normal Form (3 NF) For Example… Two examples of transitive dependence. Item IDpk Mfg Address Weight Text Number 19568 Bins 812 Bins Rd. 16. 00 85466 B&D 113 Sears St. 26. 00 77788 Shallot 888 9 th St. 300. 00 Vehicle IDpk Enginefk Max. HP Make Model Text Number Text 65664 SVT-V 8 340 Ford Cobra 55555 COT-82 115 Mercury Cougar 87954 CMN-99 220 Dodge Ram DISCOVER! Texas State Technical College

Third Normal Form (3 NF) Converting to 3 NF 1. For each field, identify its dependencies and their type. 2. If it is a functional dependency on a non-key field, simply delete the dependent field. If there is a functional dependence, the value can be computed when the information is retrieved from the table. 3. If it is transitive dependence, treat it as a partial dependence in 2 NF. Handle like in 2 NF. Either move to the parent table or create a new table. 4. Repeat the process until database is 3 NF. The database is 3 NF when all tables in the database are 3 NF. DISCOVER! Texas State Technical College

Third Normal Form (3 NF) Corrected Example… Functional dependence: The dependent field has been discarded. Order_IDpk Quantity Price Text Number Currency 19568 4 4. 00 85466 13 2. 00 77788 10 30. 00 Shipment IDpk Provider Weight $per. LB Text Number Currency 65664 Fed. Ex 4. 00 16. 00 55555 USPS 2. 00 87954 USPS 30. 00 2. 00 FIXED: The tables are now 3 NF! DISCOVER! Texas State Technical College

Third Normal Form (3 NF) Corrected Example… Engine Transitive dependence: The dependent field moved to own table. Manufacturer IDpk Address Text Bins 812 Bins Rd. B&D 113 Sears St. Item IDpk Mfgfk Weight Text Number 19568 Bins 16. 00 85466 B&D 26. 00 IDpk Max. HP Text Number SVT-V 8 340 COT-82 115 CMN-99 220 Vehicle IDpk Enginefk Make Model Text 65664 SVT-V 8 Ford Cobra 55555 COT-82 Mercury Cougar 87954 CMN-99 Dodge Ram FIXED: The tables are now 3 NF! DISCOVER! Texas State Technical College

Third Normal Form (3 NF) In Summary… • Third normal form (3 NF) is defined as • 1 NF and 2 NF • Having no functional dependencies • Having no transitive dependencies • 3 NF is a sufficient normalization for most databases. • 3 NF guarantees independence and eliminates data redundancy. DISCOVER! Texas State Technical College